org.apache.poi.ss.formula

Class ConditionalFormattingEvaluator

java.lang.Object

org.apache.poi.ss.formula.ConditionalFormattingEvaluator

public class ConditionalFormattingEvaluator
extends java.lang.Object

Evaluates Conditional Formatting constraints.

For performance reasons, this class keeps a cache of all previously evaluated rules and cells. Be sure to call clearAllCachedFormats() if any conditional formats are modified, added, or deleted, and clearAllCachedValues() whenever cell values change.

Constructor Summary

Constructors

Constructor and Description
ConditionalFormattingEvaluator(Workbook wb, WorkbookEvaluatorProvider provider)

Method Summary

Modifier and Type	Method and Description
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)
void	clearAllCachedValues() Call this whenever cell values change in the workbook, so condional formats are re-evaluated for all cells.
java.util.List<EvaluationConditionalFormatRule>	getConditionalFormattingForCell(Cell cell) This checks all applicable ConditionalFormattingRules for the cell's sheet, in defined "priority" order, returning the matches if any.
java.util.List<EvaluationConditionalFormatRule>	getConditionalFormattingForCell(CellReference cellRef) This checks all applicable ConditionalFormattingRules for the cell's sheet, in defined "priority" order, returning the matches if any.
java.util.List<EvaluationConditionalFormatRule>	getFormatRulesForSheet(Sheet sheet) Retrieve all formatting rules for the given sheet.
java.util.List<EvaluationConditionalFormatRule>	getFormatRulesForSheet(java.lang.String sheetName) Retrieve all formatting rules for the sheet with the given name.
java.util.List<Cell>	getMatchingCells(EvaluationConditionalFormatRule rule) Retrieve all cells where the given formatting rule evaluates to true.
java.util.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.
static CellReference	getRef(Cell cell)
protected java.util.List<EvaluationConditionalFormatRule>	getRules(Sheet sheet) lazy load by sheet since reading can be expensive
protected WorkbookEvaluator	getWorkbookEvaluator()

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

ConditionalFormattingEvaluator

public ConditionalFormattingEvaluator(Workbook wb,
                                      WorkbookEvaluatorProvider provider)

Method Detail

getWorkbookEvaluator

protected WorkbookEvaluator getWorkbookEvaluator()

clearAllCachedFormats

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

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.

getRules

protected java.util.List<EvaluationConditionalFormatRule> getRules(Sheet sheet)

lazy load by sheet since reading can be expensive

Parameters:

sheet - The sheet to look at

Returns:

unmodifiable list of rules

getConditionalFormattingForCell

public java.util.List<EvaluationConditionalFormatRule> getConditionalFormattingForCell(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 - NOTE: if no sheet name is specified, this uses the workbook active sheet

Returns:

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

public java.util.List<EvaluationConditionalFormatRule> getConditionalFormattingForCell(Cell cell)

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:

cell - The cell to look for

Returns:

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

getRef

public static CellReference getRef(Cell cell)

getFormatRulesForSheet

public java.util.List<EvaluationConditionalFormatRule> getFormatRulesForSheet(java.lang.String sheetName)

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

Parameters:

sheetName - The name of the sheet to look at

Returns:

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

getFormatRulesForSheet

public java.util.List<EvaluationConditionalFormatRule> getFormatRulesForSheet(Sheet sheet)

Retrieve all formatting rules for the given sheet.

Parameters:

sheet - The sheet to look at

Returns:

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

getMatchingCells

public java.util.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 - containing the rule

conditionalFormattingIndex - of the ConditionalFormatting instance in the sheet's array

ruleIndex - of the ConditionalFormattingRule instance within the ConditionalFormatting

Returns:

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

getMatchingCells

public java.util.List<Cell> getMatchingCells(EvaluationConditionalFormatRule rule)

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

Parameters:

rule - The rule to look at

Returns:

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