How to Add Data Validation to Excel in Java (Step-by-Step)

Excel data validation is useful when a worksheet is meant to be filled in by other people. Instead of accepting any value, a cell can be limited to a number range, a date period, a certain text length, or a predefined list of choices.

This is especially helpful for forms and reusable templates, where inconsistent input can create extra cleanup work later.

In this tutorial, we’ll add several common validation rules to an Excel worksheet with Java, including whole number, date, text length, list, and time validation.

Common Excel Data Validation Types

Excel supports several validation types for different kinds of input:

Validation Type	Typical Use
Whole number	Quantities, counts, IDs
Decimal	Prices, percentages, measurements
List	Status, department, category
Date	Deadlines, submission dates, schedules
Time	Appointments, shifts, working hours
Text length	Codes, abbreviations, identifiers

Most rules are built from three parts: the allowed data type, a comparison operator, and one or two limits.

For example, a quantity field may accept only whole numbers from 1 to 100, while a date field may allow only dates within a given year.

Environment Setup

Add the required Excel processing dependency to your Java project.

For Maven, add the following to pom.xml:

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

Import the required classes:

import com.spire.xls.*;

Step 1: Create a Workbook and Access the Worksheet

Create a workbook and get the first worksheet:

Workbook workbook = new Workbook();

Worksheet sheet = workbook.getWorksheets().get(0);

To make the sample worksheet easier to test, add a label for each validation type:

sheet.getCellRange("B2").setText("Whole 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:");

The corresponding input cells will be placed in column C.

Step 2: Apply Data Validation

Whole Number Validation

Suppose C2 is used for a quantity and should accept only whole numbers from 1 to 100:

CellRange rangeNumber = sheet.getCellRange("C2");

// Allow whole numbers only
rangeNumber.getDataValidation()
        .setAllowType(CellDataType.Integer);

// Restrict the value to a range
rangeNumber.getDataValidation()
        .setCompareOperator(
                ValidationComparisonOperator.Between
        );

// Set the minimum and maximum values
rangeNumber.getDataValidation().setFormula1("1");
rangeNumber.getDataValidation().setFormula2("100");

// Show an input prompt
rangeNumber.getDataValidation()
        .setInputMessage("Enter a whole number between 1 and 100");

// Highlight the input cell
rangeNumber.getCellStyle()
        .setKnownColor(ExcelColors.Gray25Percent);

This kind of rule works well for quantities, headcounts, inventory levels, and similar fields where decimal values should not be accepted.

Date Validation

For C4, limit the input to dates within 2026:

CellRange rangeDate = sheet.getCellRange("C4");

// Allow dates only
rangeDate.getDataValidation()
        .setAllowType(CellDataType.Date);

// Restrict the date to a specific range
rangeDate.getDataValidation()
        .setCompareOperator(
                ValidationComparisonOperator.Between
        );

// Set the start and end dates
rangeDate.getDataValidation()
        .setFormula1("1/1/2026");

rangeDate.getDataValidation()
        .setFormula2("12/31/2026");

// Show an input prompt
rangeDate.getDataValidation()
        .setInputMessage(
                "Enter a date between 1/1/2026 and 12/31/2026"
        );

rangeDate.getCellStyle()
        .setKnownColor(ExcelColors.Gray25Percent);

The comparison does not have to use Between. For a deadline field, for example, you could allow only dates on or after a certain day.

Text Length Validation

Text length validation is useful for fields such as internal codes, abbreviations, or short identifiers.

Here, C6 is limited to 10 characters:

CellRange rangeTextLength =
        sheet.getCellRange("C6");

// Validate the length of the entered text
rangeTextLength.getDataValidation()
        .setAllowType(CellDataType.TextLength);

// Limit the text to 10 characters
rangeTextLength.getDataValidation()
        .setCompareOperator(
                ValidationComparisonOperator.LessOrEqual
        );

rangeTextLength.getDataValidation()
        .setFormula1("10");

// Show an input prompt
rangeTextLength.getDataValidation()
        .setInputMessage(
                "Enter no more than 10 characters"
        );

rangeTextLength.getCellStyle()
        .setKnownColor(ExcelColors.Gray25Percent);

If a field must contain an exact number of characters, the comparison operator can be changed accordingly.

List Validation

For fields with a fixed set of values, a drop-down list helps avoid variations in spelling and wording.

The code below adds a department list to C8:

CellRange rangeList =
        sheet.getCellRange("C8");

// Define the available choices
rangeList.getDataValidation().setValues(
        new String[]{
                "Development",
                "Testing",
                "Marketing",
                "Finance"
        }
);

// Show the drop-down arrow
rangeList.getDataValidation()
        .isSuppressDropDownArrow(false);

// Show an input prompt
rangeList.getDataValidation()
        .setInputMessage("Select a department from the list");

rangeList.getCellStyle()
        .setKnownColor(ExcelColors.Gray25Percent);

For a short, stable list, defining the values directly in code is usually sufficient.

If the list changes frequently, it is better to keep the values in a worksheet range and use that range as the validation source.

Time Validation

C10 can be restricted to times between 9:00 and 18:00:

CellRange rangeTime =
        sheet.getCellRange("C10");

// Allow time values only
rangeTime.getDataValidation()
        .setAllowType(CellDataType.Time);

// Restrict the value to a time range
rangeTime.getDataValidation()
        .setCompareOperator(
                ValidationComparisonOperator.Between
        );

rangeTime.getDataValidation()
        .setFormula1("9:00");

rangeTime.getDataValidation()
        .setFormula2("18:00");

// Show an input prompt
rangeTime.getDataValidation()
        .setInputMessage(
                "Enter a time between 9:00 and 18:00"
        );

rangeTime.getCellStyle()
        .setKnownColor(ExcelColors.Gray25Percent);

This can be useful for appointment times, work shifts, or any field that should stay within defined working hours.

Step 3: Adjust the Worksheet Layout

A little formatting makes the generated sheet easier to use:

// Auto-fit column B
sheet.autoFitColumn(2);

// Set the width of column C
sheet.setColumnWidth(3, 20);

Step 4: Save the Workbook

Save the finished workbook:

workbook.saveToFile(
        "DataValidation.xlsx",
        ExcelVersion.Version2016
);

workbook.dispose();

The generated worksheet now contains five different validation rules:

• C2 accepts whole numbers from 1 to 100.

• C4 accepts dates within 2026.

• C6 accepts up to 10 characters.

• C8 provides a department drop-down list.

• C10 accepts times between 9:00 and 18:00.

These rules stay with the workbook, so the same input restrictions apply whenever the file is reused as a template.