Validation

How to Work with Validation in Java

Overview

This guide shows how to add data validation rules and conditional formatting to Excel worksheets using Aspose.Cells FOSS for Java. Data validation constrains cell input to an accepted range or list by calling setType(), setOperator(), setFormula1(), and setFormula2() on a Validation object. Conditional formatting applies styles automatically by evaluating FormatConditionType.CELL_VALUE rules against each cell.

Add a Whole-Number Validation Rule

Add a Validation to the worksheet’s ValidationCollection by calling sheet.getValidations().add(cellArea). Then set the validation type, operator, and bounds using setType(), setOperator(), setFormula1(), and setFormula2():

import com.aspose.cells_foss.CellArea;
import com.aspose.cells_foss.OperatorType;
import com.aspose.cells_foss.Validation;
import com.aspose.cells_foss.ValidationType;
import com.aspose.cells_foss.Workbook;
import com.aspose.cells_foss.Worksheet;

try (Workbook workbook = new Workbook()) {
    WorksheetCollection sheets = workbook.getWorksheets();
    Worksheet sheet = sheets.get(0);
    int vi = sheet.getValidations().add(new CellArea(1, 0, 10, 0));
    Validation validation = sheet.getValidations().get(vi);
    validation.setType(ValidationType.WHOLE_NUMBER);
    validation.setOperator(OperatorType.BETWEEN);
    validation.setFormula1("1");
    validation.setFormula2("100");
    workbook.save("validated.xlsx");
}

Add Conditional Formatting

Apply bold font formatting to cells whose value falls between 1 and 100, using FormatConditionType.CELL_VALUE and OperatorType.BETWEEN. Retrieve the FormatConditionCollection, add an area and a condition, then modify the Style returned by condition.getStyle():

import com.aspose.cells_foss.CellArea;
import com.aspose.cells_foss.FormatCondition;
import com.aspose.cells_foss.FormatConditionCollection;
import com.aspose.cells_foss.FormatConditionType;
import com.aspose.cells_foss.OperatorType;
import com.aspose.cells_foss.Style;
import com.aspose.cells_foss.Workbook;
import com.aspose.cells_foss.Worksheet;

try (Workbook workbook = new Workbook()) {
    WorksheetCollection sheets = workbook.getWorksheets();
    Worksheet sheet = sheets.get(0);
    int cfIndex = sheet.getConditionalFormattings().add();
    FormatConditionCollection conditions = sheet.getConditionalFormattings().get(cfIndex);
    conditions.addArea(CellArea.createCellArea("B2", "B11"));
    int condIndex = conditions.addCondition(
        FormatConditionType.CELL_VALUE, OperatorType.BETWEEN, "1", "100");
    FormatCondition condition = conditions.get(condIndex);
    Style style = condition.getStyle();
    style.getFont().setBold(true);
    condition.setStyle(style);
    workbook.save("conditional.xlsx");
}

Supported Validation Types

ValidationType	Description
`WHOLE_NUMBER`	Integer values only
`DECIMAL`	Decimal numeric values
`LIST`	Predefined list of values
`DATE`	Date values
`TEXT_LENGTH`	Character count

How to Work with Validation in Java

Overview

Add a Whole-Number Validation Rule

Add Conditional Formatting

Supported Validation Types

See Also