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. For example, you can create a validation rule that restricts a cell to accept only whole numbers. In this article, you will learn how to apply or remove data validation in Excel by using Spire.XLS for Java.
Install Spire.XLS for Java
First of all, you’re required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project’s pom.xml file.
<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls</artifactId>
<version>14.11.0</version>
</dependency>
</dependencies>
Apply Data Validation to Excel Cells
The following are the main steps to add various types of data validation to cells.
- Create a Workbook object, and get the first worksheet using Workbook.getWorksheets().get() method.
- Get a specific cell range using Worksheet.getCellRange() method to add data validation.
- Set the data type allowed in the cell using CellRange.getDataValidation().setAllowType() method. You can choose the data type as Integer, Time, Date, TextLength, Decimal, etc.
- Set the comparison operator using CellRange.getDataValiation().setCompareOperator() method. The comparison operators include Between, NotBetween, Less, Greater, Equal, etc.
- Set one or two formulas for the data validation using CellRange.getDataValidation().setFormula1() and CellRange.getDataValidation().setFormula2() methods.
- Set the input prompt using CellRange.getDataValidation().setInputMessage() method.
- Save the workbook to an Excel file using Workbook.saveToFile() method.
- Java
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 following are the steps to remove data validation from selected cell ranges.
- Create a Workbook object.
- Load a sample Excel file using Workbook.loadFromFile() method.
- Get the first worksheet using Workbook.getWorksheets().get() method.
- Create an array of rectangles, which is used to locate the cell ranges where the validation will be removed.
- Remove the data validation from the selected cell ranges using Worksheet.getDVTable().remove() method.
- Save the workbook to another Excel file using Workbook.saveToFile() method.
- Java
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("C:\\Users\\Administrator\\Desktop\\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");
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
