Data Validation & Conditional Formatting

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

Baseline date: 2026-10-09. This page was written against Apache POI 5.5.1 (the latest release on that date; the 6.0 line is announced but has not been released), Java 21 and later, Spring Boot 4.x on Spring Framework 7 and Jakarta EE 11. It covers two features that change how a spreadsheet behaves for the person who opens it: data validation (the rules that decide what a user may type into a cell, including drop-down lists) and conditional formatting (the rules that decide how a cell looks depending on its value). Both are stored as sheet-level metadata, both are created from the Sheet object described in Spreadsheet Usermodel Basics, and the colours and fonts used by formatting rules relate to the ones described in Cell Styles, Fonts & Formats. The examples add the validation column and the highlighting rules of the Bookshelf books-inventory.xlsx catalogue described in the section landing page.

A point worth knowing before any code: neither feature is enforced by POI. Writing a cell value with the usermodel API never consults the validation rules, and POI does not paint anything when a conditional-formatting rule matches. Both are instructions stored in the file that Excel, LibreOffice and other spreadsheet applications obey when a person works with it. If a server must guarantee that uploaded data is valid, it has to check the data itself (see Reading validation rules back and the import pages of the section).

How the rules are stored in the file

Validation and conditional formatting are not attached to cells; they are attached to ranges of a sheet. That has a useful consequence: a rule covering A2:A1001 costs the same whether the 1000 cells exist or not, and an empty cell inside the range is still validated or formatted when somebody types into it.

Aspect XLSX (OOXML, XSSF) XLS (OLE2, HSSF)

Validation storage

<dataValidations> element of the worksheet part (xl/worksheets/sheetN.xml), one <dataValidation> per rule with type, operator, sqref, <formula1> and <formula2>

DVAL and DV records in the worksheet stream, one DV record per rule

Conditional formatting storage

<conditionalFormatting sqref="..."> elements with <cfRule> children; the formatting itself (font, fill, border) is stored once in the <dxfs> table of xl/styles.xml and referenced by dxfId

CFHEADER and CFRULE records; the formatting lives inside the rule record

Order in the worksheet XML

<conditionalFormatting> comes before <dataValidations>; both come after <sheetData> and <mergeCells>

Not applicable (record stream)

Excel 2010 extensions

Cross-sheet list references and some data-bar settings are stored in an <extLst> block (x14 namespace)

Not available

POI classes

XSSFDataValidation, XSSFDataValidationHelper, XSSFSheetConditionalFormatting, XSSFConditionalFormattingRule

HSSFDataValidation, HSSFDataValidationHelper and DVConstraint, HSSFSheetConditionalFormatting
flowchart LR A["Sheet (ss.usermodel)"] --> B["getDataValidationHelper()"] A --> C["getSheetConditionalFormatting()"] B --> D["createXxxConstraint(...)"] D --> E["createValidation(constraint, CellRangeAddressList)"] E --> F["addValidationData(validation)"] C --> G["createConditionalFormattingRule(...)"] G --> H["createPatternFormatting() / createFontFormatting()"] G --> I["addConditionalFormatting(CellRangeAddress[], rule)"] F --> J["dataValidations in sheetN.xml"] I --> K["conditionalFormatting in sheetN.xml and dxfs in styles.xml"]

Both APIs are defined on the common org.apache.poi.ss.usermodel interfaces, so the same code compiles against HSSFWorkbook and XSSFWorkbook. The differences are limits (documented below) and a few rule types that only XSSF implements. They are part of the poi jar (the interfaces and HSSF) and poi-ooxml (XSSF and SXSSF); no extra dependency is needed. See Setup & Dependencies.

Data validation

The API in one picture

Every validation is built in three steps: get the helper from the sheet, create a constraint (what is allowed), wrap it in a validation together with the range where it applies, then add the validation to the sheet.

Constraint factory (DataValidationHelper) What it creates

createExplicitListConstraint(String[])

A drop-down with fixed items. Stored as one comma separated string, so items cannot contain a comma, and the whole string is limited to 255 characters.

createFormulaListConstraint(String)

A drop-down whose items come from a range (Lists!$A$2:$A$20), a named range (GenreList) or a formula such as INDIRECT($C2).

createIntegerConstraint(operator, formula1, formula2)

Whole numbers (type="whole").

createDecimalConstraint(operator, formula1, formula2)

Decimal numbers.

createDateConstraint(operator, formula1, formula2, dateFormat)

Dates. See the pitfall in Pitfalls about the dateFormat argument.

createTimeConstraint(operator, formula1, formula2)

Times of day (a fraction of a day).

createTextLengthConstraint(operator, formula1, formula2)

Length of the entered text.

createCustomConstraint(String formula)

Any formula that evaluates to TRUE for an accepted value; the formula is written relative to the first cell of the range.

createNumericConstraint(validationType, operator, formula1, formula2)

The generic form of the integer, decimal, date and time factories.

The operator argument is one of the DataValidationConstraint.OperatorType constants and the type of a constraint is one of DataValidationConstraint.ValidationType:

Group Constants

OperatorType

BETWEEN, NOT_BETWEEN, EQUAL, NOT_EQUAL, GREATER_THAN, LESS_THAN, GREATER_OR_EQUAL, LESS_OR_EQUAL. BETWEEN and NOT_BETWEEN use both formulas; the others only formula1 (pass null as the second).

ValidationType

ANY, INTEGER, DECIMAL, LIST, DATE, TIME, TEXT_LENGTH, FORMULA. FORMULA is the custom constraint.

Formulas are written without the leading = and as Excel would store them: a literal number, a cell reference or any formula. A constraint value that is a formula is evaluated by the spreadsheet application, not by POI.

Options of a validation

The DataValidation object has the options that control the dialogs Excel shows:

Method Effect

setShowErrorBox(boolean)

Show the error dialog when a value is rejected. Default in XSSF: off (HSSF defaults to on); without it Excel silently accepts anything. Set it to true for every rule you add, whatever the format.

createErrorBox(title, text)

Title (at most 32 characters) and message (at most 255). HSSF throws IllegalStateException beyond the limit; XSSF throws for a message over 255 characters but accepted a 40 character title in my test with 5.5.1, and Excel would then repair the file. Keep within the limits.

setErrorStyle(int)

DataValidation.ErrorStyle.STOP (reject, the default), WARNING (ask whether to keep the value) or INFO (inform, keep the value).

setShowPromptBox(boolean) and createPromptBox(title, text)

An input message shown when the cell is selected; same 32 and 255 character limits.

setEmptyCellAllowed(boolean)

Whether blank is accepted (allowBlank); true by default.

setSuppressDropDownArrow(boolean)

For list constraints, hides the in-cell drop-down arrow (the list is still enforced) in HSSF. XSSF behaves the other way round: it writes showDropDown as the negation of the argument, and in OOXML showDropDown="1" is what hides the arrow, so true keeps the arrow (as does not calling it) and false hides it. Checked with 5.5.1.

getRegions()

The CellRangeAddressList the validation applies to (used when reading).

Example: the validation column of books-inventory.xlsx

The example below creates a Lists sheet with two lists named Fiction and NonFiction, then a Books sheet whose columns carry seven different rules: a genre drop-down, a dependent subgenre drop-down, a whole number, a date range, a decimal with a warning, a fixed text length and a custom formula. Run it with an output path as the first argument.

package bookshelf;

import java.io.File;
import java.io.IOException;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationConstraint.OperatorType;
import org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;

public class ValidationExample {

    private static final int FIRST_DATA_ROW = 1;
    private static final int LAST_DATA_ROW = 1000;

    public static void main(String[] args) throws IOException {
        File target = new File(args.length > 0 ? args[0] : "books-inventory.xlsx");
        try (Workbook workbook = new XSSFWorkbook();
             FileOutputStream out = new FileOutputStream(target)) {
            Sheet lists = workbook.createSheet("Lists");
            String[] fiction = {"Novel", "Short stories", "Science fiction"};
            String[] nonFiction = {"Biography", "Science", "History"};
            lists.createRow(0).createCell(0).setCellValue("Fiction");
            lists.getRow(0).createCell(1).setCellValue("NonFiction");
            for (int i = 0; i < fiction.length; i++) {
                Row row = lists.createRow(i + 1);
                row.createCell(0).setCellValue(fiction[i]);
                row.createCell(1).setCellValue(nonFiction[i]);
            }
            defineName(workbook, "Fiction", "Lists!$A$2:$A$4");
            defineName(workbook, "NonFiction", "Lists!$B$2:$B$4");

            Sheet books = workbook.createSheet("Books");
            Row header = books.createRow(0);
            String[] titles = {"ISBN", "Title", "Genre", "Subgenre", "Pages", "Published", "Price", "Rating"};
            for (int i = 0; i < titles.length; i++) {
                header.createCell(i).setCellValue(titles[i]);
            }

            DataValidationHelper helper = books.getDataValidationHelper();

            // Explicit list for Genre (column C)
            DataValidation genre = helper.createValidation(
                    helper.createExplicitListConstraint(new String[] {"Fiction", "NonFiction"}),
                    range(2));
            genre.setShowErrorBox(true);
            genre.setErrorStyle(DataValidation.ErrorStyle.STOP);
            genre.createErrorBox("Unknown genre", "Pick Fiction or NonFiction from the list.");
            genre.createPromptBox("Genre", "Choose from the list.");
            genre.setShowPromptBox(true);
            books.addValidationData(genre);

            // Dependent list for Subgenre (column D)
            DataValidation subgenre = helper.createValidation(
                    helper.createFormulaListConstraint("INDIRECT($C2)"), range(3));
            subgenre.setShowErrorBox(true);
            subgenre.createErrorBox("Unknown subgenre", "Choose a subgenre that matches the genre.");
            books.addValidationData(subgenre);

            // Whole number between 1 and 5000 (Pages)
            DataValidation pages = helper.createValidation(
                    helper.createIntegerConstraint(OperatorType.BETWEEN, "1", "5000"), range(4));
            pages.setShowErrorBox(true);
            pages.createErrorBox("Invalid pages", "Enter a whole number between 1 and 5000.");
            books.addValidationData(pages);

            // Date between two dates (Published)
            DataValidationConstraint dateRule = helper.createDateConstraint(
                    OperatorType.BETWEEN, "DATE(1900,1,1)", "DATE(2100,12,31)", null);
            DataValidation published = helper.createValidation(dateRule, range(5));
            published.setShowErrorBox(true);
            published.createErrorBox("Invalid date", "Enter a date between 1900 and 2100.");
            books.addValidationData(published);

            // Decimal >= 0 (Price) with a warning instead of a stop
            DataValidation price = helper.createValidation(
                    helper.createDecimalConstraint(OperatorType.GREATER_OR_EQUAL, "0", null), range(6));
            price.setErrorStyle(DataValidation.ErrorStyle.WARNING);
            price.setShowErrorBox(true);
            price.createErrorBox("Negative price", "A price should not be negative. Keep it anyway?");
            books.addValidationData(price);

            // Text length exactly 13 (ISBN)
            DataValidation isbn = helper.createValidation(
                    helper.createTextLengthConstraint(OperatorType.EQUAL, "13", null), range(0));
            isbn.setShowErrorBox(true);
            isbn.createErrorBox("Invalid ISBN", "An ISBN-13 has exactly 13 characters.");
            books.addValidationData(isbn);

            // Custom formula: rating is a whole number from 1 to 5 (Rating)
            DataValidation rating = helper.createValidation(
                    helper.createCustomConstraint("AND(ISNUMBER(H2),H2>=1,H2<=5,H2=INT(H2))"), range(7));
            rating.setShowErrorBox(true);
            rating.createErrorBox("Invalid rating", "Use 1 to 5.");
            books.addValidationData(rating);

            // Generic numeric constraint with explicit ValidationType
            DataValidationConstraint generic = helper.createNumericConstraint(
                    ValidationType.DECIMAL, OperatorType.LESS_THAN, "10000", null);
            System.out.println("Generic constraint type: " + generic.getValidationType());

            workbook.write(out);
        }
    }

    private static CellRangeAddressList range(int column) {
        return new CellRangeAddressList(FIRST_DATA_ROW, LAST_DATA_ROW, column, column);
    }

    private static void defineName(Workbook workbook, String name, String reference) {
        Name n = workbook.createName();
        n.setNameName(name);
        n.setRefersToFormula(reference);
    }
}

Source: POI Quick Guide, Data Validations, DataValidationHelper Javadoc and the official examples in poi-examples (ss package)

The workbook produced by the example contains these rules (the sheet is shown as a table; the "Rejected when" column describes what Excel does when a person types a value):

Column Header Rule Rejected when

A

ISBN

Text length equal to 13

Anything that is not 13 characters

C

Genre

List Fiction,NonFiction, with prompt

A value outside the list

D

Subgenre

List INDIRECT($C2)

A value not in the list named after the genre of the same row

E

Pages

Whole number between 1 and 5000

Text, decimals, 0 or 5001

F

Published

Date between DATE(1900,1,1) and DATE(2100,12,31)

Non-dates and dates outside the range

G

Price

Decimal greater than or equal to 0, error style WARNING

Never rejected: a negative price asks for confirmation

H

Rating

Custom formula AND(ISNUMBER(H2),H2>=1,H2<=5,H2=INT(H2))

Anything for which the formula is FALSE

Dependent drop-down lists with INDIRECT

A dependent list is the classic way to make the second drop-down depend on the first. POI has no dedicated API for it; it is a formula list constraint whose formula calls Excel’s INDIRECT function:

  1. Put each child list in a range and give it a workbook-level name equal to the parent value (Fiction, NonFiction). The example creates them with Workbook.createName(), Name.setNameName() and Name.setRefersToFormula(). Names cannot contain spaces or most punctuation, so a parent value like Non fiction needs a name such as Non_fiction and a helper column that maps one to the other.

  2. Create the parent list on the first column.

  3. Create the child validation with the formula INDIRECT($C2), written relative to the first row of the range. The reference is relative: when Excel applies the rule to row 3 it evaluates INDIRECT($C3).

INDIRECT is evaluated by the spreadsheet application when the person opens the drop-down. If the parent cell is empty or holds a value with no matching name, the child list is empty and Excel shows an error. POI does not check that the names exist. More about names and their scope in Tables, Named Ranges & Pivot Tables.

Limits and differences between formats

Topic XLSX (XSSF) XLS (HSSF)

Explicit list length

255 characters including separators; createExplicitListConstraint throws IllegalArgumentException beyond it (checked with 5.5.1)

Same Excel limit; HSSF accepted a longer list at creation time in my 5.5.1 test, so POI will not warn you. Use a range instead.

List items from another sheet

Allowed directly (Lists!$A$2:$A$20) in Excel 2010 and later; POI writes the formula as is

Excel 2003 needs a named range for a list on another sheet; use Workbook.createName() and reference the name

Error and prompt titles

Not validated by POI; Excel truncates or repairs beyond 32 characters

IllegalStateException over 32 characters

Error and prompt text

IllegalStateException over 255 characters

IllegalStateException over 255 characters

Overlapping ranges

Avoid them: the result in Excel is hard to predict and POI does not warn

Same

Reading validation rules back

Sheet.getDataValidations() returns every rule on the sheet, for both formats. It is useful on the import side: a template workbook prepared by a person often carries the drop-down lists that describe the valid values, so a server can read them instead of duplicating them in code.

package bookshelf;

import java.io.File;
import java.io.IOException;
import java.util.Arrays;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ReadValidations {
    public static void main(String[] args) throws IOException {
        try (Workbook workbook = WorkbookFactory.create(new File(args[0]))) {
            Sheet sheet = workbook.getSheet("Books");
            for (DataValidation validation : sheet.getDataValidations()) {
                DataValidationConstraint constraint = validation.getValidationConstraint();
                StringBuilder where = new StringBuilder();
                for (CellRangeAddress region : validation.getRegions().getCellRangeAddresses()) {
                    where.append(region.formatAsString()).append(' ');
                }
                System.out.printf("%s type=%d operator=%d f1=%s f2=%s list=%s%n",
                        where.toString().trim(),
                        constraint.getValidationType(),
                        constraint.getOperator(),
                        constraint.getFormula1(),
                        constraint.getFormula2(),
                        constraint.getExplicitListValues() == null
                                ? "-" : Arrays.toString(constraint.getExplicitListValues()));
            }
        }
    }
}

Running it on the workbook produced above prints one line per rule, with the numeric ValidationType and OperatorType codes of the Javadoc constants:

C2:C1001 type=3 operator=0 f1="Fiction,NonFiction" f2=null list=[Fiction, NonFiction]
D2:D1001 type=3 operator=0 f1=INDIRECT($C2) f2=null list=-
E2:E1001 type=1 operator=0 f1=1 f2=5000 list=-
F2:F1001 type=4 operator=0 f1=DATE(1900,1,1) f2=DATE(2100,12,31) list=-
G2:G1001 type=2 operator=6 f1=0 f2=null list=-
A2:A1001 type=6 operator=2 f1=13 f2=null list=-
H2:H1001 type=7 operator=0 f1=AND(ISNUMBER(H2),H2>=1,H2<=5,H2=INT(H2)) f2=null list=-

Source: Sheet.getDataValidations Javadoc and DataValidationConstraint Javadoc

Reading the rules gives the declared constraint. POI will not tell you whether a cell value satisfies it, because it has no evaluator for validation formulas. For a server-side import, implement the check in Java (or with Jakarta Bean Validation, as the import pages of the section do) and use the workbook rules only as documentation of the contract.

Conditional formatting

The API

Conditional formatting works the same way in both formats: get SheetConditionalFormatting from the sheet, create one or more rules, attach formatting to each rule, then add the rules to a set of ranges.

Method (SheetConditionalFormatting) What it creates

createConditionalFormattingRule(byte comparison, String formula1, String formula2)

A cell-value rule (type="cellIs"). The operator is a constant of ComparisonOperator: BETWEEN, NOT_BETWEEN, EQUAL, NOT_EQUAL, GT, LT, GE, LE. An overload without the second formula exists for single-operand comparisons (the example passes "5").

createConditionalFormattingRule(String formula)

A formula rule (type="expression"); the formula is relative to the first cell of the range, so a row-wide highlight is written $F2<TODAY() with an absolute column and a relative row.

createConditionalFormattingColorScaleRule()

A two- or three-colour gradient (type="colorScale").

createConditionalFormattingRule(ExtendedColor)

A data bar (type="dataBar") with the given bar colour; XSSF is the format that stores it.

createConditionalFormattingRule(IconMultiStateFormatting.IconSet)

An icon set (type="iconSet"), for example GYR_3_TRAFFIC_LIGHTS; 3, 4 and 5 icon variants exist.

addConditionalFormatting(CellRangeAddress[], rule)

Attach one rule, two rules or an array of rules to one or more ranges.

Cell-value and formula rules carry formatting objects created on the rule:

  • createFontFormatting(): font colour (setFontColorIndex), bold and italic (setFontStyle(italic, bold)), underline, strikethrough. A conditional format cannot change the font name or size: Excel does not allow it either.

  • createPatternFormatting(): the fill. See the pitfall below about foreground and background.

  • createBorderFormatting(): border styles and colours.

Colour scales, data bars and icon sets carry their own settings objects (ColorScaleFormatting, DataBarFormatting, IconMultiStateFormatting) that you reach with the matching getter on the rule.

Example: highlighting rules for books-inventory.xlsx

The example creates five rules on one sheet: low stock in red, an overdue row in orange, a three-colour scale on the price, data bars on the page count and a traffic-light icon set on the rating.

package bookshelf;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.ComparisonOperator;
import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
import org.apache.poi.ss.usermodel.ConditionalFormattingThreshold.RangeType;
import org.apache.poi.ss.usermodel.ColorScaleFormatting;
import org.apache.poi.ss.usermodel.DataBarFormatting;
import org.apache.poi.ss.usermodel.FontFormatting;
import org.apache.poi.ss.usermodel.IconMultiStateFormatting;
import org.apache.poi.ss.usermodel.IconMultiStateFormatting.IconSet;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PatternFormatting;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ConditionalFormattingExample {

    public static void main(String[] args) throws IOException {
        File target = new File(args.length > 0 ? args[0] : "books-inventory-cf.xlsx");
        try (Workbook workbook = new XSSFWorkbook();
             FileOutputStream out = new FileOutputStream(target)) {
            Sheet sheet = workbook.createSheet("Books");
            String[] titles = {"Title", "Stock", "Price", "Pages", "Rating", "Due date"};
            Row header = sheet.createRow(0);
            for (int i = 0; i < titles.length; i++) {
                header.createCell(i).setCellValue(titles[i]);
            }
            for (int r = 1; r <= 8; r++) {
                Row row = sheet.createRow(r);
                row.createCell(0).setCellValue("Book " + r);
                row.createCell(1).setCellValue(r * 2 - 1);
                row.createCell(2).setCellValue(9.5 + r * 3);
                row.createCell(3).setCellValue(100 + r * 120);
                row.createCell(4).setCellValue(1 + r % 5);
            }

            SheetConditionalFormatting scf = sheet.getSheetConditionalFormatting();

            // 1. Cell-value rule: stock below 5 -> red fill, bold white font
            ConditionalFormattingRule lowStock = scf.createConditionalFormattingRule(
                    ComparisonOperator.LT, "5");
            PatternFormatting fill = lowStock.createPatternFormatting();
            fill.setFillBackgroundColor(IndexedColors.RED.getIndex());
            fill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
            FontFormatting font = lowStock.createFontFormatting();
            font.setFontColorIndex(IndexedColors.WHITE.getIndex());
            font.setFontStyle(false, true);
            scf.addConditionalFormatting(
                    new CellRangeAddress[] {CellRangeAddress.valueOf("B2:B9")}, lowStock);

            // 2. Formula rule: highlight the whole row when the due date has passed
            ConditionalFormattingRule overdue = scf.createConditionalFormattingRule(
                    "AND($F2<>\"\",$F2<TODAY())");
            PatternFormatting overdueFill = overdue.createPatternFormatting();
            overdueFill.setFillBackgroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
            overdueFill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
            scf.addConditionalFormatting(
                    new CellRangeAddress[] {CellRangeAddress.valueOf("A2:F9")}, overdue);

            // 3. Three-colour scale on price
            ConditionalFormattingRule scale = scf.createConditionalFormattingColorScaleRule();
            ColorScaleFormatting colorScale = scale.getColorScaleFormatting();
            colorScale.setNumControlPoints(3);
            colorScale.getThresholds()[0].setRangeType(RangeType.MIN);
            colorScale.getThresholds()[1].setRangeType(RangeType.PERCENTILE);
            colorScale.getThresholds()[1].setValue(50d);
            colorScale.getThresholds()[2].setRangeType(RangeType.MAX);
            colorScale.setColors(new XSSFColor[] {
                    new XSSFColor(new byte[] {(byte) 0xFF, (byte) 0x63, (byte) 0xBE, (byte) 0x7B}),
                    new XSSFColor(new byte[] {(byte) 0xFF, (byte) 0xFF, (byte) 0xEB, (byte) 0x84}),
                    new XSSFColor(new byte[] {(byte) 0xFF, (byte) 0xF8, (byte) 0x69, (byte) 0x6B})});
            scf.addConditionalFormatting(
                    new CellRangeAddress[] {CellRangeAddress.valueOf("C2:C9")}, scale);

            // 4. Data bar on pages
            XSSFColor barColor = new XSSFColor(new byte[] {(byte) 0xFF, (byte) 0x5B, (byte) 0x9B, (byte) 0xD5});
            ConditionalFormattingRule bar = scf.createConditionalFormattingRule(barColor);
            DataBarFormatting dataBar = bar.getDataBarFormatting();
            dataBar.getMinThreshold().setRangeType(RangeType.MIN);
            dataBar.getMaxThreshold().setRangeType(RangeType.MAX);
            dataBar.setWidthMin(10);
            dataBar.setWidthMax(90);
            scf.addConditionalFormatting(
                    new CellRangeAddress[] {CellRangeAddress.valueOf("D2:D9")}, bar);

            // 5. Icon set on rating
            ConditionalFormattingRule icons = scf.createConditionalFormattingRule(IconSet.GYR_3_TRAFFIC_LIGHTS);
            IconMultiStateFormatting multi = icons.getMultiStateFormatting();
            multi.getThresholds()[0].setRangeType(RangeType.NUMBER);
            multi.getThresholds()[0].setValue(0d);
            multi.getThresholds()[1].setRangeType(RangeType.NUMBER);
            multi.getThresholds()[1].setValue(3d);
            multi.getThresholds()[2].setRangeType(RangeType.NUMBER);
            multi.getThresholds()[2].setValue(5d);
            scf.addConditionalFormatting(
                    new CellRangeAddress[] {CellRangeAddress.valueOf("E2:E9")}, icons);

            workbook.write(out);
        }
    }
}

Source: POI Quick Guide, Conditional Formatting, SheetConditionalFormatting Javadoc and the official example ConditionalFormats

The resulting XML shows where the pieces end up. The formatting of the first two rules is in styles.xml, referenced by dxfId; colour scales, data bars and icon sets keep their settings inside the rule:

<conditionalFormatting sqref="B2:B9">
  <cfRule type="cellIs" operator="lessThan" dxfId="0" priority="1"><formula>5</formula></cfRule>
</conditionalFormatting>
<conditionalFormatting sqref="A2:F9">
  <cfRule type="expression" dxfId="1" priority="2"><formula>AND($F2&lt;>"",$F2&lt;TODAY())</formula></cfRule>
</conditionalFormatting>
<conditionalFormatting sqref="C2:C9">
  <cfRule type="colorScale" priority="3">
    <colorScale><cfvo type="min"/><cfvo type="percentile" val="50.0"/><cfvo type="max"/>
      <color rgb="FF63BE7B"/><color rgb="FFFFEB84"/><color rgb="FFF8696B"/></colorScale>
  </cfRule>
</conditionalFormatting>

Rule types and thresholds

Rule type Settings object Notes

Cell value, formula

FontFormatting, PatternFormatting, BorderFormatting

Both HSSF and XSSF. Formula rules are the most flexible: any formula relative to the top-left cell of the range.

Colour scale

ColorScaleFormatting with setNumControlPoints(2 or 3), getThresholds() and setColors(...)

The number of colours must equal the number of control points. Thresholds are ConditionalFormattingThreshold objects with a RangeType (MIN, MAX, NUMBER, PERCENT, PERCENTILE, FORMULA) and an optional value.

Data bar

DataBarFormatting with getMinThreshold(), getMaxThreshold(), setWidthMin(int), setWidthMax(int)

Widths are percentages of the cell width. The bar colour comes from the ExtendedColor passed to the factory. Settings that Excel 2010 introduced (solid fill, negative-value colour, axis position) are stored in an x14 extension and have no setters in the usermodel API as far as the Javadoc shows.

Icon set

IconMultiStateFormatting with getThresholds(), setIconOnly(boolean), setReversed(boolean)

One threshold per icon; the first threshold is conventionally NUMBER 0 or PERCENT 0.

When rules overlap on the same cell, Excel applies them by priority. POI assigns priorities in the order in which the rules are added to the sheet (1, 2, 3 in the XML above), so the earliest rule wins for conflicting properties such as fill colour. Add the most important rule first.

Limits and differences between formats

Topic XLSX (XSSF) XLS (HSSF)

Rules per range

No limit in the file; the interface accepts an array of rules

At most 3 rules per addConditionalFormatting call: a fourth makes POI throw IllegalArgumentException with the message Number of rules must not exceed 3 (checked with 5.5.1). This reflects the Excel 2003 limit.

Colour scales, data bars, icon sets

Supported

Excel 2003 never had them; the HSSF support is limited. Use XLSX for these rule types.

Formatting storage

Shared dxf entries in styles.xml

Per-rule record

Colours

Pass an XSSFColor; use four bytes (0xFF alpha first) so the stored rgb has eight hex digits, as Excel itself writes

Indexed palette (IndexedColors)

Evaluating which rules apply to a cell

POI does not render a sheet, but in the 4.x line it gained the ability to evaluate conditional formatting for you. org.apache.poi.ss.formula.ConditionalFormattingEvaluator takes a workbook and a formula evaluator and, for a cell, returns the rules that currently match. That is how you can answer on a server questions such as "which books are low in stock?" from the formatting a person set up in a template, or how the DataFormatter can apply a rule’s number format.

package bookshelf;

import java.io.File;
import java.io.IOException;
import java.util.List;
import org.apache.poi.ss.formula.ConditionalFormattingEvaluator;
import org.apache.poi.ss.formula.EvaluationConditionalFormatRule;
import org.apache.poi.ss.formula.WorkbookEvaluatorProvider;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ConditionalFormatting;
import org.apache.poi.ss.usermodel.PatternFormatting;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class EvaluateFormatting {
    public static void main(String[] args) throws IOException {
        try (Workbook workbook = WorkbookFactory.create(new File(args[0]))) {
            Sheet sheet = workbook.getSheet("Books");
            SheetConditionalFormatting scf = sheet.getSheetConditionalFormatting();
            for (int i = 0; i < scf.getNumConditionalFormattings(); i++) {
                ConditionalFormatting cf = scf.getConditionalFormattingAt(i);
                System.out.println(cf.getNumberOfRules() + " rule(s) on "
                        + cf.getFormattingRanges()[0].formatAsString());
            }
            ConditionalFormattingEvaluator evaluator = new ConditionalFormattingEvaluator(
                    workbook, (WorkbookEvaluatorProvider) workbook.getCreationHelper().createFormulaEvaluator());
            for (Row row : sheet) {
                Cell stock = row.getCell(1);
                if (stock == null || row.getRowNum() == 0) {
                    continue;
                }
                List<EvaluationConditionalFormatRule> rules = evaluator.getConditionalFormattingForCell(stock);
                for (EvaluationConditionalFormatRule rule : rules) {
                    PatternFormatting pattern = rule.getRule().getPatternFormatting();
                    if (pattern != null) {
                        System.out.println(stock.getAddress().formatAsString() + " has a fill rule: "
                                + rule.getRule().getFormula1());
                    }
                }
            }
        }
    }
}

Running it on the workbook produced by the previous example prints the number of rules per range and then the cells matched by the "stock below 5" rule:

1 rule(s) on B2:B9
1 rule(s) on A2:F9
1 rule(s) on C2:C9
1 rule(s) on D2:D9
1 rule(s) on E2:E9
B2 has a fill rule: 5
B3 has a fill rule: 5

Source: ConditionalFormattingEvaluator Javadoc and POI Formula Evaluation

The evaluator caches results; if you change cell values afterwards, create a new evaluator. The same caching rules apply as for the FormulaEvaluator described in Formulas & Evaluation.

Memory and performance

  • A rule is a few hundred bytes of XML (XSSF) or one record (HSSF) whatever the range size, so a rule over A2:A1000000 costs no more than one over A2:A10. Do not create one rule per cell: that inflates the file and slows Excel down. Create one rule per column and range.

  • In XSSF every distinct font or fill used by a formatting rule is added to the dxfs table of styles.xml. Reusing the same rule objects is not required, but creating hundreds of near-identical rules multiplies the entries.

  • Validation and conditional formatting are sheet-level, not row-level, so they are not affected by the row window of the streaming workbook described in Streaming Writes with SXSSF: add them once, at any point before the workbook is written. They count toward the file size, not toward the retained rows.

  • Evaluating conditional formatting with ConditionalFormattingEvaluator on every cell of a large sheet evaluates the rule formulas for each cell; use it only for the cells you need.

Pitfalls

Pitfall What happens and what to do

Validation is not enforced by POI

cell.setCellValue("not a number") on a cell validated as whole number succeeds. The rule only applies to people who type in the spreadsheet application. Validate on the server.

setShowErrorBox(true) forgotten

In XSSF the default is off: Excel shows no error dialog and accepts the value, so the rule looks present but does nothing. HSSF defaults to on, so code that works for .xls can silently break for .xlsx.

Date strings are not converted in XSSF

createDateConstraint(op, "1900-01-01", "2100-12-31", "yyyy-MM-dd") produced <formula1>1900-01-01</formula1> in XSSF with 5.5.1, which Excel reads as the formula "1900 minus 1 minus 1", a number around 1898, not a date. Use a formula such as DATE(1900,1,1) with null as the format, or the Excel serial number (DateUtil.getExcelDate(date) as text).

Commas and quotes in list items

An explicit list is a comma separated string. A title like War, Peace splits into two items, and a double quote breaks the XML-level string. Use a range on a (hidden) Lists sheet for data-driven lists.

Lists longer than 255 characters

Rejected by XSSF with IllegalArgumentException. Move the items to a range and use a formula list constraint.

Spaces in names used by INDIRECT

INDIRECT("Non fiction") finds no name, because names cannot contain spaces. Name the range with an underscore and map the label to the name.

Fill colour of a conditional format ignored

In conditional formatting the fill is stored in the background colour of the pattern. Use setFillBackgroundColor(...) together with PatternFormatting.SOLID_FOREGROUND (as the example does), not setFillForegroundColor. The resulting XML has patternFill patternType="solid" with a bgColor.

More than 3 rules on HSSF

IllegalArgumentException. Use XLSX, or split the rules over several ranges.

Formula relative references

Both validation and conditional-formatting formulas are relative to the first cell of the range. A rule for A2:F9 must refer to row 2 ($F2), not row 1.

Formulas stored without evaluation

Neither feature evaluates formulas in POI when writing. A typo such as a misspelled function name is stored and only shows when the file is opened. Test by opening the file or by reading it back.

Untrusted workbooks

Rules in an uploaded file are only declarations; reading them does not execute anything. Only ConditionalFormattingEvaluator evaluates their formulas, so apply the upload limits of the section’s security page before using it on a file from a user.

What POI does not provide

  • No enforcement of validation rules and no "check this cell against its rule" call.

  • No evaluation of custom validation formulas (only conditional formatting rules can be evaluated, via ConditionalFormattingEvaluator).

  • No rendering of conditional formatting, colour scales, data bars or icons: POI cannot produce an image of a sheet.

  • No setters for all Excel 2010+ options of data bars and icon sets (the x14 extension).

  • No API to edit an existing conditional-formatting rule in place: read it with getConditionalFormattingAt(i), and to change it remove it (removeConditionalFormatting(i)) and add a new one. I found no method to remove a data validation in the Sheet Javadoc, so rebuild the sheet (or the workbook from a clean template) to drop one.

  • No conversion between the HSSF and XSSF rule sets: rules that only XLSX supports are lost if you convert a file to XLS.

Changes across versions

Version Change relevant to this page

3.x to 4.0

Validation and the classic conditional formatting (cell value, formula) already existed in HSSF and XSSF; colour scales, data bars and icon sets were added to XSSF during the 3.x line. Semantic versioning starts at 4.0.

4.x

ConditionalFormattingEvaluator is added, so conditional-formatting rules can be evaluated for a cell.

5.0 to 5.5.1

No change to the validation or conditional-formatting API that I could confirm. The jar split (poi-ooxml and poi-ooxml-lite) and the OOXML schema update apply to every XSSF feature; see the changes page for per-release fixes.

6.0 (planned, not released as of 2026-10-09)

The project announces removal of the APIs deprecated in 5.x and a baseline of Java 17. No change specific to validation or conditional formatting is announced that I could confirm; re-check the changes page when 6.0 ships.

References