Java - 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 instance, when you need to collect users' satisfaction index of your products, you may want them to enter any number between 1 and 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 selected cells by suing Spire.XLS for Java.

If you use Maven, you can easily import the jar in your application by adding the following code to your project’s pom.xml file. 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</artifactId>
        <version>5.1.5</version>
    </dependency>
</dependencies>

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

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 ow 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");
    }
}