Modifier and Type	Class and Description
public static class	DataValidationEvaluator.DataValidationContext This class organizes and encapsulates all the pieces of information related to a single data validation configuration for a single cell.
public static enum	DataValidationEvaluator.OperatorEnum Not calling it OperatorType to avoid confusion for now with DataValidationConstraint.OperatorType.
public static enum	DataValidationEvaluator.ValidationEnum Not calling it ValidationType to avoid confusion for now with DataValidationConstraint.ValidationType.

Modifier and Type	Field and Description
private final Map<String, List<? extends DataValidation>>	validations Expensive to compute, so cache them as they are retrieved.
private final Workbook	workbook
private final WorkbookEvaluator	workbookEvaluator

Access	Constructor and Description
public	DataValidationEvaluator(Workbook the workbook this operates on wb, WorkbookEvaluatorProvider provider for formula evaluation provider) Use the same formula evaluation context used for other operations, so cell value changes are automatically noticed

Method Summary

Modifier and Type	Method and Description
public void	clearAllCachedValues() Call this whenever validation structures change, so future results stay in sync with the Workbook state.
public DataValidationEvaluator.DataValidationContext	Returns: the DataValidationContext applicable to the given cell, or null if no validation applies getValidationContextForCell(CellReference reference to check cell) Finds and returns the `DataValidationContext` for the cell, if there is one.
public DataValidation	Returns: the DataValidation applicable to the given cell, or null if no validation applies getValidationForCell(CellReference reference to check - use this in case the cell does not actually exist yet cell) Finds and returns the `DataValidation` for the cell, if there is one.
private List<? extends DataValidation>	Returns: The `DataValidation`s for the sheet getValidations(Sheet The `Sheet` to load validations for. sheet) Lazy load validations by sheet, since reading the CT* types is expensive
public List<ValueEval>	Returns: returns an unmodifiable `List` of `ValueEval`s if applicable, or null getValidationValuesForCell(CellReference reference to check - use this in case the cell does not actually exist yet cell) If `getValidationForCell(CellReference)` returns an instance, and the `ValidationType` is `ValidationType#LIST`, return the valid values, whether they are from a static list or cell range.
protected static List<ValueEval>	Returns: returns an unmodifiable `List` of `ValueEval`s, which may be empty getValidationValuesForConstraint(DataValidationEvaluator.DataValidationContext context) static so enums can reference it without creating a whole instance
protected WorkbookEvaluator	Returns: evaluator getWorkbookEvaluator()
public static boolean	Returns: true if the cell or cached cell formula result type match the given type isType(Cell The `Cell` to check. cell, CellType The `CellType` to check for. type) Note that this assumes the cell cached value is up to date and in sync with data edits
public boolean	Returns: true if the cell has no validation or the cell value passes the defined validation, false if it fails isValidCell(CellReference The reference of the cell to evaluate cellRef) Use the validation returned by `getValidationForCell(CellReference)` if you want the error display details.

Inherited from java.lang.Object:: clone equals finalize getClass hashCode notify notifyAll toString wait wait wait

validations	back to summary
private final Map<String, List<? extends DataValidation>> validations Expensive to compute, so cache them as they are retrieved. Sheets don't implement equals, and since its an interface, there's no guarantee instances won't be recreated on the fly by some implementation. So we use sheet name.

workbook	back to summary
private final Workbook workbook

workbookEvaluator	back to summary
private final WorkbookEvaluator workbookEvaluator

Constructor Detail

DataValidationEvaluator	back to summary
public DataValidationEvaluator(Workbook wb, WorkbookEvaluatorProvider provider) Use the same formula evaluation context used for other operations, so cell value changes are automatically noticed Parameters wb:Workbook the workbook this operates on provider:WorkbookEvaluatorProvider provider for formula evaluation

DataValidationEvaluator

back to summary

public DataValidationEvaluator(Workbook wb, WorkbookEvaluatorProvider provider)

Use the same formula evaluation context used for other operations, so cell value changes are automatically noticed

Parameters

wb:Workbook: the workbook this operates on
provider:WorkbookEvaluatorProvider: provider for formula evaluation

Method Detail

clearAllCachedValues	back to summary
public void clearAllCachedValues() Call this whenever validation structures change, so future results stay in sync with the Workbook state.

getValidationContextForCell back to summary

getValidationContextForCell	back to summary
public DataValidationEvaluator.DataValidationContext getValidationContextForCell(CellReference cell) Finds and returns the `DataValidationContext` for the cell, if there is one. Lookup is based on the first match from `DataValidation#getRegions()` for the cell's sheet. DataValidation regions must be in the same sheet as the DataValidation. Allowed values expressions may reference other sheets, however. Parameters cell:CellReference reference to check Returns:DataValidationEvaluator.DataValidationContext the DataValidationContext applicable to the given cell, or null if no validation applies

public DataValidationEvaluator.DataValidationContext getValidationContextForCell(CellReference cell)

Finds and returns the DataValidationContext for the cell, if there is one. Lookup is based on the first match from DataValidation#getRegions() for the cell's sheet. DataValidation regions must be in the same sheet as the DataValidation. Allowed values expressions may reference other sheets, however.

Parameters

cell:CellReference: reference to check

Returns:DataValidationEvaluator.DataValidationContext

the DataValidationContext applicable to the given cell, or null if no validation applies

getValidationForCell back to summary

getValidationForCell	back to summary
public DataValidation getValidationForCell(CellReference cell) Finds and returns the `DataValidation` for the cell, if there is one. Lookup is based on the first match from `DataValidation#getRegions()` for the cell's sheet. DataValidation regions must be in the same sheet as the DataValidation. Allowed values expressions may reference other sheets, however. Parameters cell:CellReference reference to check - use this in case the cell does not actually exist yet Returns:DataValidation the DataValidation applicable to the given cell, or null if no validation applies

public DataValidation getValidationForCell(CellReference cell)

Finds and returns the DataValidation for the cell, if there is one. Lookup is based on the first match from DataValidation#getRegions() for the cell's sheet. DataValidation regions must be in the same sheet as the DataValidation. Allowed values expressions may reference other sheets, however.

Parameters

cell:CellReference: reference to check - use this in case the cell does not actually exist yet

Returns:DataValidation

the DataValidation applicable to the given cell, or null if no validation applies

getValidations back to summary

getValidations	back to summary
private List<? extends DataValidation> getValidations(Sheet sheet) Lazy load validations by sheet, since reading the CT* types is expensive Parameters sheet:Sheet The `Sheet` to load validations for. Returns:List<? extends DataValidation> The `DataValidation`s for the sheet

private List<? extends DataValidation> getValidations(Sheet sheet)

Lazy load validations by sheet, since reading the CT* types is expensive

Parameters

sheet:Sheet: The Sheet to load validations for.

Returns:List<? extends DataValidation>

The DataValidations for the sheet

getValidationValuesForCell back to summary

getValidationValuesForCell	back to summary
public List<ValueEval> getValidationValuesForCell(CellReference cell) If `getValidationForCell(CellReference)` returns an instance, and the `ValidationType` is `ValidationType#LIST`, return the valid values, whether they are from a static list or cell range. For all other validation types, or no validation at all, this method returns null. This method could throw an exception if the validation type is not LIST, but since this method is mostly useful in UI contexts, null seems the easier path. Parameters cell:CellReference reference to check - use this in case the cell does not actually exist yet Returns:List<ValueEval> returns an unmodifiable `List` of `ValueEval`s if applicable, or null

public List<ValueEval> getValidationValuesForCell(CellReference cell)

If getValidationForCell(CellReference) returns an instance, and the ValidationType is ValidationType#LIST, return the valid values, whether they are from a static list or cell range.

For all other validation types, or no validation at all, this method returns null.

This method could throw an exception if the validation type is not LIST, but since this method is mostly useful in UI contexts, null seems the easier path.

Parameters

cell:CellReference: reference to check - use this in case the cell does not actually exist yet

Returns:List<ValueEval>

returns an unmodifiable List of ValueEvals if applicable, or null

getValidationValuesForConstraint back to summary

getValidationValuesForConstraint	back to summary
protected static List<ValueEval> getValidationValuesForConstraint(DataValidationEvaluator.DataValidationContext context) static so enums can reference it without creating a whole instance Returns:List<ValueEval> returns an unmodifiable `List` of `ValueEval`s, which may be empty

protected static List<ValueEval> getValidationValuesForConstraint(DataValidationEvaluator.DataValidationContext context)

static so enums can reference it without creating a whole instance

Returns:List<ValueEval>: returns an unmodifiable List of ValueEvals, which may be empty

getWorkbookEvaluator	back to summary
protected WorkbookEvaluator getWorkbookEvaluator() Returns:WorkbookEvaluator evaluator

isType back to summary

isType	back to summary
public static boolean isType(Cell cell, CellType type) Note that this assumes the cell cached value is up to date and in sync with data edits Parameters cell:Cell The `Cell` to check. type:CellType The `CellType` to check for. Returns:boolean true if the cell or cached cell formula result type match the given type

public static boolean isType(Cell cell, CellType type)

Note that this assumes the cell cached value is up to date and in sync with data edits

Parameters

cell:Cell: The Cell to check.
type:CellType: The CellType to check for.

Returns:boolean

true if the cell or cached cell formula result type match the given type

isValidCell back to summary

isValidCell	back to summary
public boolean isValidCell(CellReference cellRef) Use the validation returned by `getValidationForCell(CellReference)` if you want the error display details. This is the validation checked by this method, which attempts to replicate Excel's data validation rules. Note that to properly apply some validations, care must be taken to offset the base validation formula by the relative position of the current cell, or the wrong value is checked. Parameters cellRef:CellReference The reference of the cell to evaluate Returns:boolean true if the cell has no validation or the cell value passes the defined validation, false if it fails

public boolean isValidCell(CellReference cellRef)

Use the validation returned by getValidationForCell(CellReference) if you want the error display details. This is the validation checked by this method, which attempts to replicate Excel's data validation rules.

Note that to properly apply some validations, care must be taken to offset the base validation formula by the relative position of the current cell, or the wrong value is checked.

Parameters

cellRef:CellReference: The reference of the cell to evaluate

Returns:boolean

true if the cell has no validation or the cell value passes the defined validation, false if it fails

org.apache.poi.ss.formula back to summary

public Class DataValidationEvaluator.DataValidationContext

Class Inheritance

extends Object

java.lang.Object
org.apache.poi.ss.formula.DataValidationEvaluator.DataValidationContext

This class organizes and encapsulates all the pieces of information related to a single data validation configuration for a single cell. It cleanly separates the validation region, the cells it applies to, the specific cell this instance references, and the validation configuration and current values if applicable.

Field Summary

Modifier and Type	Field and Description
private final DataValidation	dv
private final DataValidationEvaluator	dve
private final CellRangeAddressBase	region
private final CellReference	target

Constructor Summary

Access	Constructor and Description
public	DataValidationContext(DataValidation dv, DataValidationEvaluator dve, CellRangeAddressBase region, CellReference target) Populate the context with the necessary values.

Method Summary

Modifier and Type	Method and Description
public DataValidationEvaluator	Returns: the dve getEvaluator()
public String	getFormula1()
public String	getFormula2()
public int	getOffsetColumns()
public int	getOffsetRows()
public int	getOperator()
public CellRangeAddressBase	Returns: the region getRegion()
public int	getSheetIndex()
public CellReference	Returns: the target getTarget()
public DataValidation	Returns: the dv getValidation()

Inherited from java.lang.Object:: clone equals finalize getClass hashCode notify notifyAll toString wait wait wait

Field Detail

dv	back to summary
private final DataValidation dv

dve	back to summary
private final DataValidationEvaluator dve

region	back to summary
private final CellRangeAddressBase region

target	back to summary
private final CellReference target

Constructor Detail

DataValidationContext	back to summary
public DataValidationContext(DataValidation dv, DataValidationEvaluator dve, CellRangeAddressBase region, CellReference target) Populate the context with the necessary values.

Method Detail

getEvaluator	back to summary
public DataValidationEvaluator getEvaluator() Returns:DataValidationEvaluator the dve

getFormula1	back to summary
public String getFormula1()

getFormula2	back to summary
public String getFormula2()

getOffsetColumns	back to summary
public int getOffsetColumns()

getOffsetRows	back to summary
public int getOffsetRows()

getOperator	back to summary
public int getOperator()

getRegion	back to summary
public CellRangeAddressBase getRegion() Returns:CellRangeAddressBase the region

getSheetIndex	back to summary
public int getSheetIndex()

getTarget	back to summary
public CellReference getTarget() Returns:CellReference the target

getValidation	back to summary
public DataValidation getValidation() Returns:DataValidation the dv

org.apache.poi.ss.formula back to summary

public sealed Enum DataValidationEvaluator.OperatorEnum

Class Inheritance

extends Enum<DataValidationEvaluator.OperatorEnum>

java.lang.Object
java.lang.Enum
org.apache.poi.ss.formula.DataValidationEvaluator.OperatorEnum

Not calling it OperatorType to avoid confusion for now with DataValidationConstraint.OperatorType. Definition order matches OOXML type ID indexes

Field Summary

Modifier and Type	Field and Description
public static final DataValidationEvaluator.OperatorEnum	BETWEEN
public static final DataValidationEvaluator.OperatorEnum	EQUAL
public static final DataValidationEvaluator.OperatorEnum	GREATER_OR_EQUAL
public static final DataValidationEvaluator.OperatorEnum	GREATER_THAN
public static final DataValidationEvaluator.OperatorEnum	IGNORED
public static final DataValidationEvaluator.OperatorEnum	LESS_OR_EQUAL
public static final DataValidationEvaluator.OperatorEnum	LESS_THAN
public static final DataValidationEvaluator.OperatorEnum	NOT_BETWEEN
public static final DataValidationEvaluator.OperatorEnum	NOT_EQUAL

Constructor Summary

Access	Constructor and Description
private	OperatorEnum()

Method Summary

Modifier and Type	Method and Description
public abstract boolean	Returns: true if the comparison is valid isValid(Double won't be null, assumption is previous checks handled that cellValue, Double if null, value assumed invalid, anything passes, per Excel behavior v1, Double null if not needed. If null when needed, assume anything passes, per Excel behavior v2) Evaluates comparison using operator instance rules
public static DataValidationEvaluator.OperatorEnum	valueOf(String name)
public static DataValidationEvaluator.OperatorEnum[]	values()

Inherited from java.lang.Enum:: clone compareTo describeConstable equals finalize getDeclaringClass hashCode name ordinal toString valueOf

Field Detail

BETWEEN	back to summary
public static final DataValidationEvaluator.OperatorEnum BETWEEN

EQUAL	back to summary
public static final DataValidationEvaluator.OperatorEnum EQUAL

GREATER_OR_EQUAL	back to summary
public static final DataValidationEvaluator.OperatorEnum GREATER_OR_EQUAL

GREATER_THAN	back to summary
public static final DataValidationEvaluator.OperatorEnum GREATER_THAN

IGNORED	back to summary
public static final DataValidationEvaluator.OperatorEnum IGNORED

LESS_OR_EQUAL	back to summary
public static final DataValidationEvaluator.OperatorEnum LESS_OR_EQUAL

LESS_THAN	back to summary
public static final DataValidationEvaluator.OperatorEnum LESS_THAN

NOT_BETWEEN	back to summary
public static final DataValidationEvaluator.OperatorEnum NOT_BETWEEN

NOT_EQUAL	back to summary
public static final DataValidationEvaluator.OperatorEnum NOT_EQUAL

Constructor Detail

OperatorEnum	back to summary
private OperatorEnum()

Method Detail

isValid	back to summary
public abstract boolean isValid(Double cellValue, Double v1, Double v2) Evaluates comparison using operator instance rules Parameters cellValue:Double won't be null, assumption is previous checks handled that v1:Double if null, value assumed invalid, anything passes, per Excel behavior v2:Double null if not needed. If null when needed, assume anything passes, per Excel behavior Returns:boolean true if the comparison is valid

isValid

back to summary

public abstract boolean isValid(Double cellValue, Double v1, Double v2)

Evaluates comparison using operator instance rules

Parameters

cellValue:Double: won't be null, assumption is previous checks handled that
v1:Double: if null, value assumed invalid, anything passes, per Excel behavior
v2:Double: null if not needed. If null when needed, assume anything passes, per Excel behavior

Returns:boolean

true if the comparison is valid

valueOf	back to summary
public static DataValidationEvaluator.OperatorEnum valueOf(String name)

values	back to summary
public static DataValidationEvaluator.OperatorEnum[] values()

org.apache.poi.ss.formula back to summary

public sealed Enum DataValidationEvaluator.ValidationEnum

Class Inheritance

extends Enum<DataValidationEvaluator.ValidationEnum>

java.lang.Object
java.lang.Enum
org.apache.poi.ss.formula.DataValidationEvaluator.ValidationEnum

Not calling it ValidationType to avoid confusion for now with DataValidationConstraint.ValidationType. Definition order matches OOXML type ID indexes

Field Summary

Modifier and Type	Field and Description
public static final DataValidationEvaluator.ValidationEnum	ANY
public static final DataValidationEvaluator.ValidationEnum	DATE
public static final DataValidationEvaluator.ValidationEnum	DECIMAL
public static final DataValidationEvaluator.ValidationEnum	FORMULA
public static final DataValidationEvaluator.ValidationEnum	INTEGER
public static final DataValidationEvaluator.ValidationEnum	LIST
public static final DataValidationEvaluator.ValidationEnum	TEXT_LENGTH
public static final DataValidationEvaluator.ValidationEnum	TIME

Constructor Summary

Access	Constructor and Description
private	ValidationEnum()

Method Summary

Modifier and Type	Method and Description
private Double	Returns: numeric value or null if not defined or the formula evaluates to an empty/missing cell. evalOrConstant(String The text of the formula or a numeric value formula, DataValidationEvaluator.DataValidationContext The `DataValidationContext` which is used for evaluation context) Evaluate a numeric formula value as either a constant or numeric expression.
public static boolean	Returns: true if validation passes isValid(Cell Cell to check validity of cell, DataValidationEvaluator.DataValidationContext The Data Validation to check against context) Validates against the type defined in context, as an index of the enum values array.
protected boolean	Returns: if the cell is a valid numeric cell for the validation or not isValidNumericCell(Cell cell, DataValidationEvaluator.DataValidationContext context) Uses the cell value, which may be the cached formula result value.
protected boolean	isValidNumericValue(Double value, final DataValidationEvaluator.DataValidationContext context) Is the number a valid option for the validation?
public boolean	isValidValue(Cell cell, DataValidationEvaluator.DataValidationContext context)
public static DataValidationEvaluator.ValidationEnum	valueOf(String name)
public static DataValidationEvaluator.ValidationEnum[]	values()

Inherited from java.lang.Enum:: clone compareTo describeConstable equals finalize getDeclaringClass hashCode name ordinal toString valueOf

Field Detail

ANY	back to summary
public static final DataValidationEvaluator.ValidationEnum ANY

DATE	back to summary
public static final DataValidationEvaluator.ValidationEnum DATE

DECIMAL	back to summary
public static final DataValidationEvaluator.ValidationEnum DECIMAL

FORMULA	back to summary
public static final DataValidationEvaluator.ValidationEnum FORMULA

INTEGER	back to summary
public static final DataValidationEvaluator.ValidationEnum INTEGER

LIST	back to summary
public static final DataValidationEvaluator.ValidationEnum LIST

TEXT_LENGTH	back to summary
public static final DataValidationEvaluator.ValidationEnum TEXT_LENGTH

TIME	back to summary
public static final DataValidationEvaluator.ValidationEnum TIME

Constructor Detail

ValidationEnum	back to summary
private ValidationEnum()

Method Detail

evalOrConstant back to summary

private Double evalOrConstant(String formula, DataValidationEvaluator.DataValidationContext context) throws NumberFormatException

Evaluate a numeric formula value as either a constant or numeric expression. Note that Excel treats validations with constraint formulas that evaluate to null as valid, but evaluations in error or non-numeric are marked invalid.

Parameters

formula:String: The text of the formula or a numeric value
context:DataValidationEvaluator.DataValidationContext: The DataValidationContext which is used for evaluation

Returns:Double

numeric value or null if not defined or the formula evaluates to an empty/missing cell.

Exceptions

NumberFormatException:: if the formula is non-numeric when it should be

isValid

back to summary

public static boolean isValid(Cell cell, DataValidationEvaluator.DataValidationContext context)

Validates against the type defined in context, as an index of the enum values array.

Parameters

cell:Cell: Cell to check validity of
context:DataValidationEvaluator.DataValidationContext: The Data Validation to check against

Returns:boolean

true if validation passes

Exceptions

ArrayIndexOutOfBoundsException:: if the constraint type is an invalid index

isValidNumericCell

back to summary

protected boolean isValidNumericCell(Cell cell, DataValidationEvaluator.DataValidationContext context)

Uses the cell value, which may be the cached formula result value. We won't re-evaluate cells here. This validation would be after the cell value was updated externally. Excel allows invalid values through methods like copy/paste, and only validates them when the user interactively edits the cell.

Returns:boolean: if the cell is a valid numeric cell for the validation or not

isValidNumericValue	back to summary
protected boolean isValidNumericValue(Double value, final DataValidationEvaluator.DataValidationContext context) Is the number a valid option for the validation?

isValidValue	back to summary
public boolean isValidValue(Cell cell, DataValidationEvaluator.DataValidationContext context)

valueOf	back to summary
public static DataValidationEvaluator.ValidationEnum valueOf(String name)

values	back to summary
public static DataValidationEvaluator.ValidationEnum[] values()

public Class DataValidationEvaluator

Nested and Inner Type Summary

Field Summary

Constructor Summary

Method Summary

Field Detail

Constructor Detail

Method Detail

public Class DataValidationEvaluator.DataValidationContext

Field Summary

Constructor Summary

Method Summary

Field Detail

Constructor Detail

Method Detail

public sealed Enum DataValidationEvaluator.OperatorEnum

Field Summary

Constructor Summary

Method Summary

Field Detail

Constructor Detail

Method Detail

public sealed Enum DataValidationEvaluator.ValidationEnum

Field Summary

Constructor Summary

Method Summary

Field Detail

Constructor Detail

Method Detail