DEV Community: Dheeraj Malik

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

Dheeraj Malik — Tue, 15 Sep 2026 16:00:00 +0000

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.

How to Extract Tables from Word Documents with Python

Dheeraj Malik — Tue, 15 Sep 2026 03:41:03 +0000

Word documents often use tables to store structured information such as inventory lists, inspection records, project data, and reports. Extracting one table manually is simple enough, but it quickly becomes tedious when a document contains several tables or when many Word files need to be processed.

With Python, you can read the rows and cells of a Word table and convert the content into a structure that is easier to reuse or export.

This guide covers how to read a table from a Word document, extract all tables to CSV files, and process multiple Word files in a folder.

Environment Setup

To run the examples below, install the required Python module for Word processing:

pip install Spire.Doc

Read a Table from a Word Document

A Word table can be accessed through its section and then read row by row.

The example below reads the first table in the first section and stores its content in a two-dimensional Python list:

from spire.doc import *
from spire.doc.common import *

input_file = "input.docx"

document = Document()
document.LoadFromFile(input_file)

section = document.Sections.get_Item(0)
table = section.Tables.get_Item(0)

table_data = []

for r in range(table.Rows.Count):
    row = table.Rows.get_Item(r)
    row_data = []

    for c in range(row.Cells.Count):
        cell = row.Cells.get_Item(c)

        paragraphs = []

        for p in range(cell.Paragraphs.Count):
            text = cell.Paragraphs.get_Item(p).Text.strip()

            if text:
                paragraphs.append(text)

        row_data.append(" ".join(paragraphs))

    table_data.append(row_data)

for row in table_data:
    print(row)

document.Close()

A table cell may contain more than one paragraph, so the code reads all paragraphs in the cell instead of assuming that only one exists.

The extracted data may look like this:

[
    ["Name", "Department", "Position"],
    ["John Smith", "Development", "Software Engineer"],
    ["Emma Lee", "Testing", "QA Engineer"]
]

If the original paragraph breaks need to be preserved, replace:

" ".join(paragraphs)

with:

"\n".join(paragraphs)

Extract All Tables from Word to CSV

A Word document may contain tables in more than one section. To extract all of them, iterate through every section and its Tables collection.

The following code saves each table as a separate CSV file:

import csv
import os
from spire.doc import *
from spire.doc.common import *

input_file = "input.docx"
output_folder = "ExtractedTables"

os.makedirs(output_folder, exist_ok=True)

document = Document()
document.LoadFromFile(input_file)

table_number = 0

for s in range(document.Sections.Count):
    section = document.Sections.get_Item(s)

    for t in range(section.Tables.Count):
        table = section.Tables.get_Item(t)
        table_number += 1

        output_file = os.path.join(
            output_folder,
            f"table_{table_number}.csv"
        )

        with open(
            output_file,
            "w",
            newline="",
            encoding="utf-8-sig"
        ) as csv_file:

            writer = csv.writer(csv_file)

            for r in range(table.Rows.Count):
                row = table.Rows.get_Item(r)
                row_data = []

                for c in range(row.Cells.Count):
                    cell = row.Cells.get_Item(c)

                    paragraphs = []

                    for p in range(cell.Paragraphs.Count):
                        text = cell.Paragraphs.get_Item(p).Text.strip()

                        if text:
                            paragraphs.append(text)

                    row_data.append(" ".join(paragraphs))

                writer.writerow(row_data)

document.Close()

print(f"Extracted {table_number} tables.")

If the document contains three tables, the output folder will look like this:

ExtractedTables/
├── table_1.csv
├── table_2.csv
└── table_3.csv

The CSV files use utf-8-sig, which helps avoid encoding problems when text containing non-ASCII characters is opened directly in spreadsheet applications such as Excel.

Batch Extract Tables from Multiple Word Files

For multiple documents, it is cleaner to move the extraction logic into a reusable function instead of repeating the same code for every file.

import csv
import os
from spire.doc import *
from spire.doc.common import *


def extract_tables(word_file, output_folder):
    os.makedirs(output_folder, exist_ok=True)

    document = Document()
    document.LoadFromFile(word_file)

    table_number = 0

    for s in range(document.Sections.Count):
        section = document.Sections.get_Item(s)

        for t in range(section.Tables.Count):
            table = section.Tables.get_Item(t)
            table_number += 1

            output_file = os.path.join(
                output_folder,
                f"table_{table_number}.csv"
            )

            with open(
                output_file,
                "w",
                newline="",
                encoding="utf-8-sig"
            ) as csv_file:

                writer = csv.writer(csv_file)

                for r in range(table.Rows.Count):
                    row = table.Rows.get_Item(r)
                    row_data = []

                    for c in range(row.Cells.Count):
                        cell = row.Cells.get_Item(c)

                        text = " ".join(
                            cell.Paragraphs.get_Item(p).Text.strip()
                            for p in range(cell.Paragraphs.Count)
                            if cell.Paragraphs.get_Item(p).Text.strip()
                        )

                        row_data.append(text)

                    writer.writerow(row_data)

    document.Close()

    return table_number


input_folder = "WordFiles"
output_folder = "ExtractedTables"

for file_name in os.listdir(input_folder):

    if not file_name.lower().endswith((".doc", ".docx")):
        continue

    input_file = os.path.join(input_folder, file_name)

    document_name = os.path.splitext(file_name)[0]

    document_output = os.path.join(
        output_folder,
        document_name
    )

    count = extract_tables(
        input_file,
        document_output
    )

    print(f"{file_name}: extracted {count} tables")

Each document gets its own output folder, so tables from different files do not overwrite one another:

ExtractedTables/
├── report/
│   ├── table_1.csv
│   └── table_2.csv
├── inventory/
│   └── table_1.csv
└── records/
    ├── table_1.csv
    └── table_2.csv

A Note on Merged Cells

Merged cells need extra attention when exporting Word tables to CSV.

Word supports both horizontal and vertical cell merging, while CSV only stores rows and columns and has no concept of merged cells. As a result, a complex Word table may not map cleanly to a flat CSV structure.

If the extracted data will be imported into a database or used for analysis, it is worth checking tables with merged headers or grouped rows and normalizing them as needed after extraction.