Add Data Validations to Excel Cells in Java

Data validation is an Excel feature that you can use to define restrictions on what data can or should be entered in a cell. For example, you may want to restrict data entry to a certain range of numbers or limit choices by using a list. In this article, I am going to show you how to insert numeric, text, date and list validation to cells by using Free Spire.XLS for Java.

Installing Spire.Xls.jar

If you create a Maven project, you can easily import the jar in your application using the following configurations. For non-Maven projects, download the jar file from this link and add it as a dependency in your application.

<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>

Add numeric, text, date and list validation to cells

Spire.XLS provides a class named CellRange which contains DataValidation property, enabling programmers to apply validation in a specific cell or range directly.

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);

        //Add a numeric validation to C2
        sheet.getCellRange("B2").setText("Input number between 1-100:");
        CellRange rangeNumber = sheet.getCellRange("C2");
        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);

        //Add a date validation to C5
        sheet.getCellRange("B5").setText("Input date between 1/1/2010-21/31/2010:");
        CellRange rangeDate = sheet.getCellRange("C5");
        rangeDate.getDataValidation().setAllowType(CellDataType.Date);
        rangeDate.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
        rangeDate.getDataValidation().setFormula1("1/1/2010");
        rangeDate.getDataValidation().setFormula2("12/31/2010");
        rangeDate.getDataValidation().setErrorMessage("Please input correct date!");
        rangeDate.getDataValidation().setShowError(true);
        rangeDate.getDataValidation().setAlertStyle(AlertStyleType.Warning);
        rangeDate.getCellStyle().setKnownColor(ExcelColors.Gray25Percent);

        //Add a text length validation to C8
        sheet.getCellRange("B8").setText("Input text less than 5 characters:");
        CellRange rangeTextLength = sheet.getCellRange("C8");
        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);

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

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

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

Output