Apply or Remove Data Validation in Excel Using Java

The data validation in Excel helps control what kind of data can or should be entered into a worksheet. In other words, any input entered into a particular cell must meet the criteria set for that cell. In this article, you will learn how to apply or remove data validation in Excel by using Free Spire.XLS for Java.

Introduction of the above mentioned API: https://www.e-iceblue.com/Introduce/free-xls-for-java.html

Installation

Method 1: Download the Free Spire.XLS for Java and unzip it, then add the Spire.Xls.jar file to your project as dependency.

Method 2: Directly add the jar dependency to maven project by adding the following configurations to the pom.xml.

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

Apply Data Validation to Excel Cells

Free Spire.XLS for Java allows you to set various types of data validation such as number validation, date validation, text length validation, list validatio n and time validation. The follwing are the complete sample code.

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

Remove Data Validation from Selected Cell Ranges

The Worksheet.getDVTable().remove() method offered by Free Spire.XLS for Java can help to remove the data validation from the selected cell ranges. The complete sample code are as follows.

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