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

Modifier and Type	Field and Description
private EvaluationCache	_cache
private CollaboratingWorkbooksEnvironment	_collaboratingWorkbookEnvironment
private final IEvaluationListener	_evaluationListener
private boolean	_ignoreMissingWorkbooks
private final Map<String, Integer>	_sheetIndexesByName
private final Map<EvaluationSheet, Integer>	_sheetIndexesBySheet
private final IStabilityClassifier	_stabilityClassifier
private final AggregatingUDFFinder	_udfFinder
private final EvaluationWorkbook	_workbook
private int	_workbookIx part of cache entry key (useful when evaluating multiple workbooks)
private boolean	dbgEvaluationOutputForNextEval whether print detailed messages about the next formula evaluation
private int	dbgEvaluationOutputIndent
private static final Logger	EVAL_LOG
private static final Logger	LOG

Access	Constructor and Description
public	WorkbookEvaluator(EvaluationWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder pass `null` for default (AnalysisToolPak only) udfFinder)
pack-priv	WorkbookEvaluator(EvaluationWorkbook workbook, IEvaluationListener evaluationListener, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder)

Method Summary

Modifier and Type	Method and Description
private NotImplementedException	addExceptionInfo(NotImplementedException inner, int sheetIndex, int rowIndex, int columnIndex) Adds the current cell reference to the exception for easier debugging.
private boolean	Returns: true if any Ptg references were shifted adjustRegionRelativeReference(Ptg[] ptgs, CellReference cell within the region to use. target, CellRangeAddressBase containing the cell, OR, for conditional format rules with multiple ranges, the region with the top-left-most cell region) Adjust formula relative references by the offset between the start of the given region and the given target cell.
pack-priv void	attachToEnvironment(CollaboratingWorkbooksEnvironment collaboratingWorkbooksEnvironment, EvaluationCache cache, int workbookIx)
public void	clearAllCachedResultValues() Should be called whenever there are changes to input cells in the evaluated workbook.
private static int	Returns: the number of tokens (starting from `startIndex+1`) that need to be skipped to achieve the specified `distInBytes` skip distance. countTokensToBeSkipped(Ptg[] ptgs, int startIndex, int distInBytes) Calculates the number of tokens that the evaluator should skip upon reaching a tAttrSkip.
private static ValueEval	Returns: a `NumberEval`, `StringEval`, `BoolEval`, or `ErrorEval`. Never `null`. `BlankEval` is converted to `NumberEval#ZERO` dereferenceResult(ValueEval evaluationResult, OperationEvaluationContext ec) Dereferences a single value from any AreaEval or RefEval evaluation result.
public static ValueEval	Returns: a `NumberEval`, `StringEval`, `BoolEval`, or `ErrorEval`. Never `null`. `BlankEval` is converted to `NumberEval#ZERO` dereferenceResult(ValueEval evaluationResult, int srcRowNum, int srcColNum) Dereferences a single value from any AreaEval or RefEval evaluation result.
pack-priv void	detachFromEnvironment() Discards the current workbook environment and attaches to the default 'empty' environment.
public ValueEval	evaluate(EvaluationCell srcCell)
public ValueEval	Returns: value evaluate(String to evaluate formula, CellReference defines the optional sheet and row/column base for the formula, if it is relative ref) Evaluate a formula outside a cell value, e.g. conditional format rules or data validation expressions
public ValueEval	Returns: value evaluate(String The formula to evaluate formula, CellReference cell context for the operation target, CellRangeAddressBase containing the cell region) Some expressions need to be evaluated in terms of an offset from the top left corner of a region, such as some data validation and conditional format expressions, when those constraints apply to contiguous cells.
private ValueEval	evaluate(String formula, CellReference target, CellRangeAddressBase region, FormulaType formulaType)
private ValueEval	Returns: never `null`, never `BlankEval` evaluateAny(EvaluationCell srcCell, int sheetIndex, int rowIndex, int columnIndex, EvaluationTracker tracker)
pack-priv ValueEval	evaluateFormula(OperationEvaluationContext ec, Ptg[] ptgs)
public ValueEval	Returns: ValueEval for one or more values evaluateList(String The formula to evaluate formula, CellReference cell context for the operation target, CellRangeAddressBase containing the cell region) Some expressions need to be evaluated in terms of an offset from the top left corner of a region, such as some data validation and conditional format expressions, when those constraints apply to contiguous cells.
pack-priv ValueEval	evaluateNameFormula(Ptg[] ptgs, OperationEvaluationContext ec) YK: Used by OperationEvaluationContext to resolve indirect names.
pack-priv ValueEval	evaluateReference(EvaluationSheet sheet, int sheetIndex, int rowIndex, int columnIndex, EvaluationTracker tracker) Used by the lazy ref evals whenever they need to get the value of a contained cell.
public FreeRefFunction	findUserDefinedFunction(String functionName)
pack-priv CollaboratingWorkbooksEnvironment	getEnvironment()
private ValueEval	getEvalForNameRecord(EvaluationName nameRecord, OperationEvaluationContext ec)
private ValueEval	getEvalForPtg(Ptg ptg, OperationEvaluationContext ec) returns an appropriate Eval impl instance for the Ptg.
pack-priv IEvaluationListener	getEvaluationListener()
pack-priv EvaluationName	getName(String name, int sheetIndex)
public static Collection<String>	Returns: names of functions NOT supported by POI getNotSupportedFunctionNames() Return a collection of functions that POI does not support
pack-priv WorkbookEvaluator	Returns: the evaluator for another workbook which is part of the same `CollaboratingWorkbooksEnvironment` getOtherWorkbookEvaluator(String workbookName)
pack-priv EvaluationSheet	getSheet(int sheetIndex)
private int	getSheetIndex(EvaluationSheet sheet)
pack-priv int	Returns: -1 if sheet with specified name does not exist getSheetIndex(String sheetName) Case-insensitive.
pack-priv int	getSheetIndexByExternIndex(int externSheetIndex)
pack-priv String	getSheetName(int sheetIndex) also for debug use.
public static Collection<String>	Returns: names of functions supported by POI getSupportedFunctionNames() Return a collection of functions that POI can evaluate
pack-priv static ValueEval	Returns: `BlankEval` if cell is `null` or blank, never `null` getValueFromNonFormulaCell(EvaluationCell may be `null` cell) Gets the value from a non-formula cell.
pack-priv EvaluationWorkbook	getWorkbook()
public boolean	isDebugEvaluationOutputForNextEval()
public boolean	isIgnoreMissingWorkbooks()
public void	notifyDeleteCell(EvaluationCell cell) Should be called to tell the cell value cache that the specified cell has just been deleted.
public void	notifyUpdateCell(EvaluationCell cell) Should be called to tell the cell value cache that the specified (value or formula) cell has changed.
private ValueEval	processNameEval(ValueEval eval, OperationEvaluationContext ec)
public static void	registerFunction(String the function name name, FreeRefFunction the function to register func) Register a ATP function in runtime.
public static void	registerFunction(String the function name name, Function the function to register func) Register a function in runtime.
public void	setDebugEvaluationOutputForNextEval(boolean value)
public void	setIgnoreMissingWorkbooks(boolean whether to ignore missing references to external workbooks ignore) Whether to ignore missing references to external workbooks and use cached formula results in the main workbook instead.

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

_cache	back to summary
private EvaluationCache _cache

_collaboratingWorkbookEnvironment	back to summary
private CollaboratingWorkbooksEnvironment _collaboratingWorkbookEnvironment

_evaluationListener	back to summary
private final IEvaluationListener _evaluationListener

_ignoreMissingWorkbooks	back to summary
private boolean _ignoreMissingWorkbooks

_sheetIndexesByName	back to summary
private final Map<String, Integer> _sheetIndexesByName

_sheetIndexesBySheet	back to summary
private final Map<EvaluationSheet, Integer> _sheetIndexesBySheet

_stabilityClassifier	back to summary
private final IStabilityClassifier _stabilityClassifier

_udfFinder	back to summary
private final AggregatingUDFFinder _udfFinder

_workbook	back to summary
private final EvaluationWorkbook _workbook

_workbookIx	back to summary
private int _workbookIx part of cache entry key (useful when evaluating multiple workbooks)

dbgEvaluationOutputForNextEval	back to summary
private boolean dbgEvaluationOutputForNextEval whether print detailed messages about the next formula evaluation

dbgEvaluationOutputIndent	back to summary
private int dbgEvaluationOutputIndent

EVAL_LOG	back to summary
private static final Logger EVAL_LOG

LOG	back to summary
private static final Logger LOG

WorkbookEvaluator	back to summary
public WorkbookEvaluator(EvaluationWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) Parameters udfFinder:UDFFinder pass `null` for default (AnalysisToolPak only)

WorkbookEvaluator	back to summary
pack-priv WorkbookEvaluator(EvaluationWorkbook workbook, IEvaluationListener evaluationListener, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder)

Method Detail

addExceptionInfo	back to summary
private NotImplementedException addExceptionInfo(NotImplementedException inner, int sheetIndex, int rowIndex, int columnIndex) Adds the current cell reference to the exception for easier debugging. Would be nice to get the formula text as well, but that seems to require too much digging around and casting to get the FormulaRenderingWorkbook.

adjustRegionRelativeReference back to summary

adjustRegionRelativeReference	back to summary
private boolean adjustRegionRelativeReference(Ptg[] ptgs, CellReference target, CellRangeAddressBase region) Adjust formula relative references by the offset between the start of the given region and the given target cell. That is, treat the region top-left cell as "A1" for the purposes of evaluating relative reference components (row and/or column), and further move references by the position of the target within the region. `formula ref + range top-left + current cell range offset` which simplifies to `formula ref + current cell ref` Parameters target:CellReference cell within the region to use. region:CellRangeAddressBase containing the cell, OR, for conditional format rules with multiple ranges, the region with the top-left-most cell Returns:boolean true if any Ptg references were shifted Exceptions IndexOutOfBoundsException: if the resulting shifted row/column indexes are over the document format limits IllegalArgumentException: if target is not within region.

private boolean adjustRegionRelativeReference(Ptg[] ptgs, CellReference target, CellRangeAddressBase region)

Adjust formula relative references by the offset between the start of the given region and the given target cell. That is, treat the region top-left cell as "A1" for the purposes of evaluating relative reference components (row and/or column), and further move references by the position of the target within the region.

formula ref + range top-left + current cell range offset which simplifies to formula ref + current cell ref

Parameters

target:CellReference: cell within the region to use.
region:CellRangeAddressBase: containing the cell, OR, for conditional format rules with multiple ranges, the region with the top-left-most cell

Returns:boolean

true if any Ptg references were shifted

Exceptions

IndexOutOfBoundsException:: if the resulting shifted row/column indexes are over the document format limits
IllegalArgumentException:: if target is not within region.

attachToEnvironment	back to summary
pack-priv void attachToEnvironment(CollaboratingWorkbooksEnvironment collaboratingWorkbooksEnvironment, EvaluationCache cache, int workbookIx)

clearAllCachedResultValues	back to summary
public void clearAllCachedResultValues() Should be called whenever there are changes to input cells in the evaluated workbook. Failure to call this method after changing cell values will cause incorrect behaviour of the evaluate~ methods of this class

countTokensToBeSkipped back to summary

countTokensToBeSkipped	back to summary
private static int countTokensToBeSkipped(Ptg[] ptgs, int startIndex, int distInBytes) Calculates the number of tokens that the evaluator should skip upon reaching a tAttrSkip. Returns:int the number of tokens (starting from `startIndex+1`) that need to be skipped to achieve the specified `distInBytes` skip distance.

private static int countTokensToBeSkipped(Ptg[] ptgs, int startIndex, int distInBytes)

Calculates the number of tokens that the evaluator should skip upon reaching a tAttrSkip.

Returns:int: the number of tokens (starting from startIndex+1) that need to be skipped to achieve the specified distInBytes skip distance.

dereferenceResult back to summary

dereferenceResult	back to summary
private static ValueEval dereferenceResult(ValueEval evaluationResult, OperationEvaluationContext ec) Dereferences a single value from any AreaEval or RefEval evaluation result. If the supplied evaluationResult is just a plain value, it is returned as-is. Returns:ValueEval a `NumberEval`, `StringEval`, `BoolEval`, or `ErrorEval`. Never `null`. `BlankEval` is converted to `NumberEval#ZERO`

private static ValueEval dereferenceResult(ValueEval evaluationResult, OperationEvaluationContext ec)

Dereferences a single value from any AreaEval or RefEval evaluation result. If the supplied evaluationResult is just a plain value, it is returned as-is.

Returns:ValueEval: a NumberEval, StringEval, BoolEval, or ErrorEval. Never null. BlankEval is converted to NumberEval#ZERO

dereferenceResult back to summary

dereferenceResult	back to summary
public static ValueEval dereferenceResult(ValueEval evaluationResult, int srcRowNum, int srcColNum) Dereferences a single value from any AreaEval or RefEval evaluation result. If the supplied evaluationResult is just a plain value, it is returned as-is. Returns:ValueEval a `NumberEval`, `StringEval`, `BoolEval`, or `ErrorEval`. Never `null`. `BlankEval` is converted to `NumberEval#ZERO`

public static ValueEval dereferenceResult(ValueEval evaluationResult, int srcRowNum, int srcColNum)

Dereferences a single value from any AreaEval or RefEval evaluation result. If the supplied evaluationResult is just a plain value, it is returned as-is.

Returns:ValueEval: a NumberEval, StringEval, BoolEval, or ErrorEval. Never null. BlankEval is converted to NumberEval#ZERO

detachFromEnvironment	back to summary
pack-priv void detachFromEnvironment() Discards the current workbook environment and attaches to the default 'empty' environment. Also resets evaluation cache.

evaluate	back to summary
public ValueEval evaluate(EvaluationCell srcCell)

evaluate	back to summary
public ValueEval evaluate(String formula, CellReference ref) Evaluate a formula outside a cell value, e.g. conditional format rules or data validation expressions Parameters formula:String to evaluate ref:CellReference defines the optional sheet and row/column base for the formula, if it is relative Returns:ValueEval value

evaluate

back to summary

public ValueEval evaluate(String formula, CellReference ref)

Evaluate a formula outside a cell value, e.g. conditional format rules or data validation expressions

Parameters

formula:String: to evaluate
ref:CellReference: defines the optional sheet and row/column base for the formula, if it is relative

Returns:ValueEval

value

evaluate	back to summary
public ValueEval evaluate(String formula, CellReference target, CellRangeAddressBase region) Some expressions need to be evaluated in terms of an offset from the top left corner of a region, such as some data validation and conditional format expressions, when those constraints apply to contiguous cells. When a relative formula is used, it must be evaluated by shifting by the target offset position relative to the top left of the range. Returns a single value e.g. a cell formula result or boolean value for conditional formatting. Parameters formula:String The formula to evaluate target:CellReference cell context for the operation region:CellRangeAddressBase containing the cell Returns:ValueEval value Exceptions IllegalArgumentException: if target does not define a sheet name to evaluate the formula on.

evaluate

back to summary

public ValueEval evaluate(String formula, CellReference target, CellRangeAddressBase region)

Some expressions need to be evaluated in terms of an offset from the top left corner of a region, such as some data validation and conditional format expressions, when those constraints apply to contiguous cells. When a relative formula is used, it must be evaluated by shifting by the target offset position relative to the top left of the range.

Returns a single value e.g. a cell formula result or boolean value for conditional formatting.

Parameters

formula:String: The formula to evaluate
target:CellReference: cell context for the operation
region:CellRangeAddressBase: containing the cell

Returns:ValueEval

value

Exceptions

IllegalArgumentException:: if target does not define a sheet name to evaluate the formula on.

evaluate	back to summary
private ValueEval evaluate(String formula, CellReference target, CellRangeAddressBase region, FormulaType formulaType)

evaluateAny	back to summary
private ValueEval evaluateAny(EvaluationCell srcCell, int sheetIndex, int rowIndex, int columnIndex, EvaluationTracker tracker) Returns:ValueEval never `null`, never `BlankEval`

evaluateFormula	back to summary
pack-priv ValueEval evaluateFormula(OperationEvaluationContext ec, Ptg[] ptgs) Annotations @Internal

evaluateList	back to summary
public ValueEval evaluateList(String formula, CellReference target, CellRangeAddressBase region) Some expressions need to be evaluated in terms of an offset from the top left corner of a region, such as some data validation and conditional format expressions, when those constraints apply to contiguous cells. When a relative formula is used, it must be evaluated by shifting by the target offset position relative to the top left of the range. Returns a ValueEval that may be one or more values, such as the allowed values for a data validation constraint. Parameters formula:String The formula to evaluate target:CellReference cell context for the operation region:CellRangeAddressBase containing the cell Returns:ValueEval ValueEval for one or more values Exceptions IllegalArgumentException: if target does not define a sheet name to evaluate the formula on.

evaluateList

back to summary

public ValueEval evaluateList(String formula, CellReference target, CellRangeAddressBase region)

Returns a ValueEval that may be one or more values, such as the allowed values for a data validation constraint.

Parameters

formula:String: The formula to evaluate
target:CellReference: cell context for the operation
region:CellRangeAddressBase: containing the cell

Returns:ValueEval

ValueEval for one or more values

Exceptions

IllegalArgumentException:: if target does not define a sheet name to evaluate the formula on.

evaluateNameFormula	back to summary
pack-priv ValueEval evaluateNameFormula(Ptg[] ptgs, OperationEvaluationContext ec) YK: Used by OperationEvaluationContext to resolve indirect names.

evaluateReference	back to summary
pack-priv ValueEval evaluateReference(EvaluationSheet sheet, int sheetIndex, int rowIndex, int columnIndex, EvaluationTracker tracker) Used by the lazy ref evals whenever they need to get the value of a contained cell.

findUserDefinedFunction	back to summary
public FreeRefFunction findUserDefinedFunction(String functionName)

getEnvironment	back to summary
pack-priv CollaboratingWorkbooksEnvironment getEnvironment()

getEvalForNameRecord	back to summary
private ValueEval getEvalForNameRecord(EvaluationName nameRecord, OperationEvaluationContext ec)

getEvalForPtg	back to summary
private ValueEval getEvalForPtg(Ptg ptg, OperationEvaluationContext ec) returns an appropriate Eval impl instance for the Ptg. The Ptg must be one of: Area3DPtg, AreaPtg, ReferencePtg, Ref3DPtg, IntPtg, NumberPtg, StringPtg, BoolPtg special Note: OperationPtg subtypes cannot be passed here!

getEvalForPtg

back to summary

private ValueEval getEvalForPtg(Ptg ptg, OperationEvaluationContext ec)

returns an appropriate Eval impl instance for the Ptg. The Ptg must be one of: Area3DPtg, AreaPtg, ReferencePtg, Ref3DPtg, IntPtg, NumberPtg, StringPtg, BoolPtg

special Note: OperationPtg subtypes cannot be passed here!

getEvaluationListener	back to summary
pack-priv IEvaluationListener getEvaluationListener()

getName	back to summary
pack-priv EvaluationName getName(String name, int sheetIndex)

getNotSupportedFunctionNames	back to summary
public static Collection<String> getNotSupportedFunctionNames() Return a collection of functions that POI does not support Returns:Collection<String> names of functions NOT supported by POI

getNotSupportedFunctionNames

back to summary

public static Collection<String> getNotSupportedFunctionNames()

Return a collection of functions that POI does not support

Returns:Collection<String>: names of functions NOT supported by POI

getOtherWorkbookEvaluator	back to summary
pack-priv WorkbookEvaluator getOtherWorkbookEvaluator(String workbookName) throws WorkbookNotFoundException Returns:WorkbookEvaluator the evaluator for another workbook which is part of the same `CollaboratingWorkbooksEnvironment`

getSheet	back to summary
pack-priv EvaluationSheet getSheet(int sheetIndex)

getSheetIndex	back to summary
private int getSheetIndex(EvaluationSheet sheet)

getSheetIndex	back to summary
pack-priv int getSheetIndex(String sheetName) Case-insensitive. Returns:int -1 if sheet with specified name does not exist

getSheetIndex

back to summary

pack-priv int getSheetIndex(String sheetName)

Case-insensitive.

Returns:int: -1 if sheet with specified name does not exist

getSheetIndexByExternIndex	back to summary
pack-priv int getSheetIndexByExternIndex(int externSheetIndex)

getSheetName	back to summary
pack-priv String getSheetName(int sheetIndex) also for debug use. Used in toString methods

getSupportedFunctionNames	back to summary
public static Collection<String> getSupportedFunctionNames() Return a collection of functions that POI can evaluate Returns:Collection<String> names of functions supported by POI

getSupportedFunctionNames

back to summary

public static Collection<String> getSupportedFunctionNames()

Return a collection of functions that POI can evaluate

Returns:Collection<String>: names of functions supported by POI

getValueFromNonFormulaCell back to summary

pack-priv static ValueEval getValueFromNonFormulaCell(EvaluationCell cell)

Gets the value from a non-formula cell.

Parameters

cell:EvaluationCell: may be null

Returns:ValueEval

BlankEval if cell is null or blank, never null

getWorkbook	back to summary
pack-priv EvaluationWorkbook getWorkbook()

isDebugEvaluationOutputForNextEval	back to summary
public boolean isDebugEvaluationOutputForNextEval()

isIgnoreMissingWorkbooks	back to summary
public boolean isIgnoreMissingWorkbooks()

notifyDeleteCell	back to summary
public void notifyDeleteCell(EvaluationCell cell) Should be called to tell the cell value cache that the specified cell has just been deleted.

notifyUpdateCell	back to summary
public void notifyUpdateCell(EvaluationCell cell) Should be called to tell the cell value cache that the specified (value or formula) cell has changed.

processNameEval	back to summary
private ValueEval processNameEval(ValueEval eval, OperationEvaluationContext ec)

registerFunction

back to summary

public static void registerFunction(String name, FreeRefFunction func)

Parameters

name:String: the function name
func:FreeRefFunction: the function to register

Exceptions

IllegalArgumentException:: if the function is unknown or already registered.

Since

3.8 beta6

registerFunction

back to summary

public static void registerFunction(String name, Function func)

Parameters

name:String: the function name
func:Function: the function to register

Exceptions

IllegalArgumentException:: if the function is unknown or already registered.

Since

3.8 beta6

setDebugEvaluationOutputForNextEval	back to summary
public void setDebugEvaluationOutputForNextEval(boolean value)

setIgnoreMissingWorkbooks back to summary

public void setIgnoreMissingWorkbooks(boolean ignore)

Whether to ignore missing references to external workbooks and use cached formula results in the main workbook instead.

In some cases external workbooks referenced by formulas in the main workbook are not available. With this method you can control how POI handles such missing references:

by default ignoreMissingWorkbooks=false and POI throws WorkbookNotFoundException if an external reference cannot be resolved
if ignoreMissingWorkbooks=true then POI uses cached formula result that already exists in the main workbook

Parameters

ignore:boolean: whether to ignore missing references to external workbooks

See Also

Bug 52575 for details

public final Class WorkbookEvaluator

Field Summary

Constructor Summary

Method Summary

Field Detail

Constructor Detail

Method Detail