Apply or Remove Data Validation in Excel Using Java

The data validation in Excel helps control what kind of data can or should be entered into a worksheet. In other words, any input entered into a particular cell must meet the criteria set for that cell. In this article, you will learn how to apply or remove data validation in Excel by using Free Spire.XLS for Java.

Introduction of the above mentioned API: https://www.e-iceblue.com/Introduce/free-xls-for-java.html

Installation

Method 1: Download the Free Spire.XLS for Java and unzip it, then add the Spire.Xls.jar file to your project as dependency.

Method 2: Directly add the jar dependency to maven project by adding the following configurations to the pom.xml.

<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls.free</artifactId>
<version>5.1.0</version>
</dependency>
</dependencies>

Apply Data Validation to Excel Cells

Free Spire.XLS for Java allows you to set various types of data validation such as number validation, date validation, text length validation, list validatio n and time validation. The follwing are the complete sample code.

import com.spire.xls.*;

public class DataValidation {

public static void main(String[] args) {

//Create a Workbook object
Workbook workbook = new Workbook();

//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);

//Insert text in cells
sheet.getCellRange("B2").setText("Number Validation:");
sheet.getCellRange("B4").setText("Date Validation:");
sheet.getCellRange("B6").setText("Text Length Validation:");
sheet.getCellRange("B8").setText("List Validation:");
sheet.getCellRange("B10").setText("Time Validation:");

//Add a number validation to C2
CellRange rangeNumber = sheet.getCellRange("C2");
rangeNumber.getDataValidation().setAllowType(CellDataType.Integer);
rangeNumber.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
rangeNumber.getDataValidation().setFormula1("1");
rangeNumber.getDataValidation().setFormula2("10");
rangeNumber.getDataValidation().setInputMessage("Enter a number between 1 and 10");
rangeNumber.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

//Add a date validation to C4
CellRange rangeDate = sheet.getCellRange("C4");
rangeDate.getDataValidation().setAllowType(CellDataType.Date);
rangeDate.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
rangeDate.getDataValidation().setFormula1("1/1/2010");
rangeDate.getDataValidation().setFormula2("12/31/2020");
rangeDate.getDataValidation().setInputMessage("Enter a date between 1/1/2010 and 12/31/2020");
rangeDate.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

//Add a text length validation to C6
CellRange rangeTextLength = sheet.getCellRange("C6");
rangeTextLength.getDataValidation().setAllowType(CellDataType.TextLength);
rangeTextLength.getDataValidation().setCompareOperator(ValidationComparisonOperator.LessOrEqual);
rangeTextLength.getDataValidation().setFormula1("5");
rangeTextLength.getDataValidation().setInputMessage("Enter text lesser than 5 characters");
rangeTextLength.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

//Apply a list validation to C8
CellRange rangeList = sheet.getCellRange("C8");
rangeList.getDataValidation().setValues(new String[]{ "United States", "Canada", "United Kingdom", "Germany" });
rangeList.getDataValidation().isSuppressDropDownArrow(false);
rangeList.getDataValidation().setInputMessage("Choose an item from the list");
rangeList.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

//Apply a time validation to C10
CellRange rangeTime= sheet.getCellRange("C10");
rangeTime.getDataValidation().setAllowType(CellDataType.Time);
rangeTime.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
rangeTime.getDataValidation().setFormula1("9:00");
rangeTime.getDataValidation().setFormula2("12:00");
rangeTime.getDataValidation().setInputMessage("Enter a time between 9:00 and 12:00");
rangeTime.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

//Auto fit width of column 2
sheet.autoFitColumn(2);

//Set the width of column 3
sheet.setColumnWidth(3, 20);

//Save to file
workbook.saveToFile("output/ApplyDataValidation.xlsx", ExcelVersion.Version2016);
}
}

Remove Data Validation from Selected Cell Ranges

The Worksheet.getDVTable().remove() method offered by Free Spire.XLS for Java can help to remove the data validation from the selected cell ranges. The complete sample code are as follows.

import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

import java.awt.*;

public class RemoveDataValidation {

public static void main(String[] args) {

//Create a Workbook object
Workbook workbook = new Workbook();

//Load a sample Excel file
workbook.loadFromFile("output/ApplyDataValidation.xlsx");

//Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);

//Create an array of rectangles, which is used to locate the ranges in worksheet
Rectangle[] rectangles = new Rectangle[]{

//One Rectangle(columnIndex, rowIndex) specifies a specific cell,the column or row index starts at 0
//To specify a cell range, use Rectangle(startColumnIndex, startRowIndex, endColumnIndex, endRowIndex)
new Rectangle(2,1),
new Rectangle(2,3),
new Rectangle(2,5),
new Rectangle(2,7),
new Rectangle(2,9)
};

//Remove the data validation from the selected cells
worksheet.getDVTable().remove(rectangles);

//Save the workbook to an Excel file
workbook.saveToFile("output/RemoveDataValidation.xlsx");
}
}

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store