Add data validation for an Excel worksheet in Java

In an Excel worksheet, we can use data validation to add some restrictions while entering data in cells, for example, we only enter integers, decimals or dates in cells. This article will use Java codes to demonstrate how to set data validation, so that data can be inputted only when it fits some request.

Dependency

It’s worth mentioning that I used a free third-party library called Free Spire.XLS for Java in this tutorial, and before running codes, we need to insert the Jar file in the library into IDEA. You can download it from the website or directly reference it using the following Maven configurations.

<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>3.9.1</version>
</dependency>
</dependencies>

Using the code

import com.spire.xls.*;

public class ApplyDataValidation {
    public static void main(String[] args) {
        //Create a Workbook object
        Workbook workbook = new Workbook();

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

        //Apply decimal validation in the cell B3
        sheet.getCellRange("B2").setText("Input number between 1-100:");
        CellRange rangeNumber = sheet.getCellRange("B3");
        rangeNumber.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
        rangeNumber.getDataValidation().setFormula1("1");
        rangeNumber.getDataValidation().setFormula2("100");
        rangeNumber.getDataValidation().setAllowType(CellDataType.Decimal);
        rangeNumber.getDataValidation().setErrorMessage("Please input correct number!");
        rangeNumber.getDataValidation().setShowError(true);
        rangeNumber.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

        //Apply date validation in the cell B6
        sheet.getCellRange("B5").setText("Input date between 1/1/2020-12/31/2020:");
        CellRange rangeDate = sheet.getCellRange("B6");
        rangeDate.getDataValidation().setAllowType(CellDataType.Date);
        rangeDate.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
        rangeDate.getDataValidation().setFormula1("1/1/1970");
        rangeDate.getDataValidation().setFormula2("12/31/1970");
        rangeDate.getDataValidation().setErrorMessage("Please input correct date!");
        rangeDate.getDataValidation().setShowError(true);
        rangeDate.getDataValidation().setAlertStyle(AlertStyleType.Warning);
        rangeDate.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

        //Apply text length validation in the cell B9
        sheet.getCellRange("B8").setText("Input text less than 5 characters:");
        CellRange rangeTextLength = sheet.getCellRange("B9");
        rangeTextLength.getDataValidation().setAllowType(CellDataType.TextLength);
        rangeTextLength.getDataValidation().setCompareOperator(ValidationComparisonOperator.LessOrEqual);
        rangeTextLength.getDataValidation().setFormula1("5");
        rangeTextLength.getDataValidation().setErrorMessage("Enter a Valid String!");
        rangeTextLength.getDataValidation().setShowError(true);
        rangeTextLength.getDataValidation().setAlertStyle(AlertStyleType.Stop);
        rangeTextLength.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

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

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

Output