DEV Community

loading...

Add Data Validations to Excel Cells in Java

E-iceblue Product Family
Your Office Development Master
・2 min read

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>
Enter fullscreen mode Exit fullscreen mode

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

Output

DataValidation

Discussion (0)