Java - How to Apply or Remove Data Validation in Excel

Data Validation feature in Excel allows you to restrict what data can or should be entered in a specific cell. For example, when you collect users' satisfaction index of products through Excel, you may want them to enter any number between 1-10.

In this article, you will learn how to apply various kinds of data validation (including number validation, text length validation, date validation, time validation, and list validation) to Excel cells, as well as how to remove data validation in specified cells by using Spire.XLS for Java.

Add Spire.Xls jar as dependency

If you are working on a maven project, you can include the dependency in pom.xml file using this:

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

If you are not using maven, then you can find the required jar files from the zip file available in this location. Include all the jar files into the application lib folder to run the sample code given in this tutorial.

Apply Data Validation to Excel Cells

The following are the steps to add various types of data validation to cells using Spire.XLS for Java.

• Create a Workbook object
• 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 Integer, Time, Date, TextLength, Decimal, etc. as the data type.
• Set the comparison operator using CellRange.getDataValiation().setCompareOperator() method. The comparison operators include Between, NotBetween, Less, Greater, and Equal.
• 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.

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

        //Insert text in cells
        sheet.getCellRange("B2").setText("Input a number:");
        sheet.getCellRange("B4").setText("Input a date:");
        sheet.getCellRange("B6").setText("Input text:");
        sheet.getCellRange("B8").setText("Select an item from the list:");
        sheet.getCellRange("B10").setText("Input a time:");

        //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.getStyle().setKnownColor(ExcelColors.BlueGray);

        //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.getStyle().setKnownColor(ExcelColors.BlueGray);

        //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.getStyle().setKnownColor(ExcelColors.BlueGray);

        //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.getStyle().setKnownColor(ExcelColors.BlueGray);

        //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.getStyle().setKnownColor(ExcelColors.BlueGray);

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

        //Set the width of column 3
        sheet.getColumns()[2].setColumnWidth(20);

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

Output

Remove Data Validation from Excel Cells

Below are the steps to remove data validation from the specified cell using Spire.XLS for Java.

• Create a Workbook object.
• Load the Excel file containing data validation using Workbook.loadFromFile() method.
• Get the first worksheet using Workbook.getWorksheets().get() method.
• Create an array of rectangles, which is used to locate the cells where the validation will be removed.
• Remove the data validation from the selected cells using Worksheet.getDVTable().remove() method.
• Save the workbook to another Excel file using Workbook.saveToFile() method.

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("RemoveDataValidation.xlsx");
    }
}

Output