Java - How to Apply or Remove Data Validation in Excel
Tags: java, excel, data, validation
Published: 2022-09-16 07:31:53

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](https://www.e-iceblue.com/Introduce/xls-for-java.html).

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

```java
<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](https://www.e-iceblue.com/Download/xls-for-java.html). 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.

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

![ApplyDataValidation](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0uzdr2ecmxtyyo4aak92.png)
##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.

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

![RemoveValidation](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/721sqn71h04wecd13llz.png)