Apache POI - Common 5.3.0 · org.apache.poi.ss.formula.ConditionalFormattingEvaluator

Modifier and Type	Field and Description
private final Map<String, List<EvaluationConditionalFormatRule>>	formats All the underlying structures, for both HSSF and XSSF, repeatedly go to the raw bytes/XML for the different pieces used in the ConditionalFormatting* structures.
private final Map<CellReference, List<EvaluationConditionalFormatRule>>	values Evaluating rules for cells in their region(s) is expensive, so we want to cache them, and empty/reevaluate the cache when values change.
private final Workbook	workbook
private final WorkbookEvaluator	workbookEvaluator

Access	Constructor and Description
public	ConditionalFormattingEvaluator(Workbook wb, WorkbookEvaluatorProvider provider)

Method Summary

Modifier and Type	Method and Description
public void	clearAllCachedFormats() Call this whenever rules are added, reordered, or removed, or a rule formula is changed (not the formula inputs but the formula expression itself)
public void	clearAllCachedValues() Call this whenever cell values change in the workbook, so condional formats are re-evaluated for all cells.
public List<EvaluationConditionalFormatRule>	Returns: Unmodifiable List of `EvaluationConditionalFormatRule`s that apply to the current cell value, in priority order, as evaluated by Excel (smallest priority # for XSSF, definition order for HSSF), or null if none apply getConditionalFormattingForCell(final CellReference Note if no sheet name is specified, this uses the workbook active sheet cellRef) This checks all applicable `ConditionalFormattingRule`s for the cell's sheet, in defined "priority" order, returning the matches if any.
public List<EvaluationConditionalFormatRule>	Returns: Unmodifiable List of `EvaluationConditionalFormatRule`s that apply to the current cell value, in priority order, as evaluated by Excel (smallest priority # for XSSF, definition order for HSSF), or null if none apply getConditionalFormattingForCell(Cell The cell to look for cell) This checks all applicable `ConditionalFormattingRule`s for the cell's sheet, in defined "priority" order, returning the matches if any.
public List<EvaluationConditionalFormatRule>	Returns: unmodifiable list of all Conditional format rules for the given sheet, if any getFormatRulesForSheet(String The name of the sheet to look at sheetName) Retrieve all formatting rules for the sheet with the given name.
public List<EvaluationConditionalFormatRule>	Returns: unmodifiable list of all Conditional format rules for the given sheet, if any getFormatRulesForSheet(Sheet The sheet to look at sheet) Retrieve all formatting rules for the given sheet.
public List<Cell>	Returns: unmodifiable List of all cells in the rule's region matching the rule's condition getMatchingCells(Sheet containing the rule sheet, int of the `ConditionalFormatting` instance in the sheet's array conditionalFormattingIndex, int of the `ConditionalFormattingRule` instance within the `ConditionalFormatting` ruleIndex) Conditional formatting rules can apply only to cells in the sheet to which they are attached.
public List<Cell>	Returns: unmodifiable List of all cells in the rule's region matching the rule's condition getMatchingCells(EvaluationConditionalFormatRule The rule to look at rule) Retrieve all cells where the given formatting rule evaluates to true.
public static CellReference	getRef(Cell cell)
protected List<EvaluationConditionalFormatRule>	Returns: unmodifiable list of rules getRules(Sheet The sheet to look at sheet) lazy load by sheet since reading can be expensive
protected WorkbookEvaluator	getWorkbookEvaluator()

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

formats	back to summary
private final Map<String, List<EvaluationConditionalFormatRule>> formats All the underlying structures, for both HSSF and XSSF, repeatedly go to the raw bytes/XML for the different pieces used in the ConditionalFormatting* structures. That's highly inefficient, and can cause significant lag when checking formats for large workbooks. Instead we need a cached version that is discarded when definitions change. 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.

values	back to summary
private final Map<CellReference, List<EvaluationConditionalFormatRule>> values Evaluating rules for cells in their region(s) is expensive, so we want to cache them, and empty/reevaluate the cache when values change. Rule lists are in priority order, as evaluated by Excel (smallest priority # for XSSF, definition order for HSSF) CellReference implements equals().

workbook	back to summary
private final Workbook workbook

workbookEvaluator	back to summary
private final WorkbookEvaluator workbookEvaluator

ConditionalFormattingEvaluator	back to summary
public ConditionalFormattingEvaluator(Workbook wb, WorkbookEvaluatorProvider provider)

Method Detail

clearAllCachedFormats	back to summary
public void clearAllCachedFormats() Call this whenever rules are added, reordered, or removed, or a rule formula is changed (not the formula inputs but the formula expression itself)

clearAllCachedValues	back to summary
public void clearAllCachedValues() Call this whenever cell values change in the workbook, so condional formats are re-evaluated for all cells. Todo eventually this should work like `EvaluationCache#notifyUpdateCell(int, int, EvaluationCell)` and only clear values that need recalculation based on the formula dependency tree.

getConditionalFormattingForCell back to summary

getConditionalFormattingForCell	back to summary
public List<EvaluationConditionalFormatRule> getConditionalFormattingForCell(final CellReference cellRef) This checks all applicable `ConditionalFormattingRule`s for the cell's sheet, in defined "priority" order, returning the matches if any. This is a property currently not exposed from `CTCfRule` in `XSSFConditionalFormattingRule`. Most cells will have zero or one applied rule, but it is possible to define multiple rules that apply at the same time to the same cell, thus the List result. Note that to properly apply conditional rules, care must be taken to offset the base formula by the relative position of the current cell, or the wrong value is checked. This is handled by `WorkbookEvaluator#evaluate(String, CellReference, CellRangeAddressBase)`. If the cell exists and is a formula cell, its cached value may be used for rule evaluation, so make sure it is up to date. If values have changed, it is best to call `FormulaEvaluator#evaluateFormulaCell(Cell)` or `FormulaEvaluator#evaluateAll()` first, or the wrong conditional results may be returned. Parameters cellRef:CellReference Note if no sheet name is specified, this uses the workbook active sheet Returns:List<EvaluationConditionalFormatRule> Unmodifiable List of `EvaluationConditionalFormatRule`s that apply to the current cell value, in priority order, as evaluated by Excel (smallest priority # for XSSF, definition order for HSSF), or null if none apply

public List<EvaluationConditionalFormatRule> getConditionalFormattingForCell(final CellReference cellRef)

This checks all applicable ConditionalFormattingRules for the cell's sheet, in defined "priority" order, returning the matches if any. This is a property currently not exposed from CTCfRule in XSSFConditionalFormattingRule.

Most cells will have zero or one applied rule, but it is possible to define multiple rules that apply at the same time to the same cell, thus the List result.

Note that to properly apply conditional rules, care must be taken to offset the base formula by the relative position of the current cell, or the wrong value is checked. This is handled by WorkbookEvaluator#evaluate(String, CellReference, CellRangeAddressBase).

If the cell exists and is a formula cell, its cached value may be used for rule evaluation, so make sure it is up to date. If values have changed, it is best to call FormulaEvaluator#evaluateFormulaCell(Cell) or FormulaEvaluator#evaluateAll() first, or the wrong conditional results may be returned.

Parameters

cellRef:CellReference: Note
if no sheet name is specified, this uses the workbook active sheet

Returns:List<EvaluationConditionalFormatRule>

Unmodifiable List of EvaluationConditionalFormatRules that apply to the current cell value, in priority order, as evaluated by Excel (smallest priority # for XSSF, definition order for HSSF), or null if none apply

getConditionalFormattingForCell back to summary

getConditionalFormattingForCell	back to summary
public List<EvaluationConditionalFormatRule> getConditionalFormattingForCell(Cell cell) This checks all applicable `ConditionalFormattingRule`s for the cell's sheet, in defined "priority" order, returning the matches if any. This is a property currently not exposed from `CTCfRule` in `XSSFConditionalFormattingRule`. Most cells will have zero or one applied rule, but it is possible to define multiple rules that apply at the same time to the same cell, thus the List result. Note that to properly apply conditional rules, care must be taken to offset the base formula by the relative position of the current cell, or the wrong value is checked. This is handled by `WorkbookEvaluator#evaluate(String, CellReference, CellRangeAddressBase)`. If the cell exists and is a formula cell, its cached value may be used for rule evaluation, so make sure it is up to date. If values have changed, it is best to call `FormulaEvaluator#evaluateFormulaCell(Cell)` or `FormulaEvaluator#evaluateAll()` first, or the wrong conditional results may be returned. Parameters cell:Cell The cell to look for Returns:List<EvaluationConditionalFormatRule> Unmodifiable List of `EvaluationConditionalFormatRule`s that apply to the current cell value, in priority order, as evaluated by Excel (smallest priority # for XSSF, definition order for HSSF), or null if none apply

public List<EvaluationConditionalFormatRule> getConditionalFormattingForCell(Cell cell)

Most cells will have zero or one applied rule, but it is possible to define multiple rules that apply at the same time to the same cell, thus the List result.

Parameters

cell:Cell: The cell to look for

Returns:List<EvaluationConditionalFormatRule>

getFormatRulesForSheet	back to summary
public List<EvaluationConditionalFormatRule> getFormatRulesForSheet(String sheetName) Retrieve all formatting rules for the sheet with the given name. Parameters sheetName:String The name of the sheet to look at Returns:List<EvaluationConditionalFormatRule> unmodifiable list of all Conditional format rules for the given sheet, if any

getFormatRulesForSheet

back to summary

public List<EvaluationConditionalFormatRule> getFormatRulesForSheet(String sheetName)

Retrieve all formatting rules for the sheet with the given name.

Parameters

sheetName:String: The name of the sheet to look at

Returns:List<EvaluationConditionalFormatRule>

unmodifiable list of all Conditional format rules for the given sheet, if any

getFormatRulesForSheet	back to summary
public List<EvaluationConditionalFormatRule> getFormatRulesForSheet(Sheet sheet) Retrieve all formatting rules for the given sheet. Parameters sheet:Sheet The sheet to look at Returns:List<EvaluationConditionalFormatRule> unmodifiable list of all Conditional format rules for the given sheet, if any

getFormatRulesForSheet

back to summary

public List<EvaluationConditionalFormatRule> getFormatRulesForSheet(Sheet sheet)

Retrieve all formatting rules for the given sheet.

Parameters

sheet:Sheet: The sheet to look at

Returns:List<EvaluationConditionalFormatRule>

unmodifiable list of all Conditional format rules for the given sheet, if any

getMatchingCells back to summary

getMatchingCells	back to summary
public List<Cell> getMatchingCells(Sheet sheet, int conditionalFormattingIndex, int ruleIndex) Conditional formatting rules can apply only to cells in the sheet to which they are attached. The POI data model does not have a back-reference to the owning sheet, so it must be passed in separately. We could overload this with convenience methods taking a sheet name and sheet index as well. Parameters sheet:Sheet containing the rule conditionalFormattingIndex:int of the `ConditionalFormatting` instance in the sheet's array ruleIndex:int of the `ConditionalFormattingRule` instance within the `ConditionalFormatting` Returns:List<Cell> unmodifiable List of all cells in the rule's region matching the rule's condition

public List<Cell> getMatchingCells(Sheet sheet, int conditionalFormattingIndex, int ruleIndex)

Conditional formatting rules can apply only to cells in the sheet to which they are attached. The POI data model does not have a back-reference to the owning sheet, so it must be passed in separately.

We could overload this with convenience methods taking a sheet name and sheet index as well.

Parameters

sheet:Sheet: containing the rule
conditionalFormattingIndex:int: of the ConditionalFormatting instance in the sheet's array
ruleIndex:int: of the ConditionalFormattingRule instance within the ConditionalFormatting

Returns:List<Cell>

unmodifiable List of all cells in the rule's region matching the rule's condition

getMatchingCells	back to summary
public List<Cell> getMatchingCells(EvaluationConditionalFormatRule rule) Retrieve all cells where the given formatting rule evaluates to true. Parameters rule:EvaluationConditionalFormatRule The rule to look at Returns:List<Cell> unmodifiable List of all cells in the rule's region matching the rule's condition

getMatchingCells

back to summary

public List<Cell> getMatchingCells(EvaluationConditionalFormatRule rule)

Retrieve all cells where the given formatting rule evaluates to true.

Parameters

rule:EvaluationConditionalFormatRule: The rule to look at

Returns:List<Cell>

unmodifiable List of all cells in the rule's region matching the rule's condition

getRef	back to summary
public static CellReference getRef(Cell cell)

getRules	back to summary
protected List<EvaluationConditionalFormatRule> getRules(Sheet sheet) lazy load by sheet since reading can be expensive Parameters sheet:Sheet The sheet to look at Returns:List<EvaluationConditionalFormatRule> unmodifiable list of rules

getRules

back to summary

protected List<EvaluationConditionalFormatRule> getRules(Sheet sheet)

lazy load by sheet since reading can be expensive

Parameters

sheet:Sheet: The sheet to look at

Returns:List<EvaluationConditionalFormatRule>

unmodifiable list of rules

getWorkbookEvaluator	back to summary
protected WorkbookEvaluator getWorkbookEvaluator()

public Class ConditionalFormattingEvaluator

Field Summary

Constructor Summary

Method Summary

Field Detail

Constructor Detail

Method Detail