News Category

Java: Apply or Remove Data Validation in Excel

2022-02-10 01:39:00 Written by  support iceblue
Rate this item
(0 votes)

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. For example, you can create a validation rule that restricts a cell to accept only whole numbers. In this article, you will learn how to apply or remove data validation in Excel by using Spire.XLS for Java.

Install Spire.XLS for Java

First of all, you’re required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project’s pom.xml file.

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

Apply Data Validation to Excel Cells

The following are the main steps to add various types of data validation to cells.

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

Java: Apply or Remove Data Validation in Excel

Remove Data Validation from Selected Cell Ranges

The following are the steps to remove data validation from selected cell ranges.

  • Create a Workbook object.
  • Load a sample Excel file using Workbook.loadFromFile() method.
  • Get the first worksheet using Workbook.getWorksheets().get() method.
  • Create an array of rectangles, which is used to locate the cell ranges where the validation will be removed.
  • Remove the data validation from the selected cell ranges 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("output/RemoveDataValidation.xlsx");
    }
}

Java: Apply or Remove Data Validation in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Additional Info

  • tutorial_title:
Last modified on Tuesday, 27 September 2022 02:29