Apache POI - Common 5.3.0 · org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator

Modifier and Type	Field and Description
private final HSSFWorkbook	_book

Access	Constructor and Description
public	HSSFFormulaEvaluator(HSSFWorkbook workbook)
public	HSSFFormulaEvaluator(HSSFWorkbook The workbook to perform the formula evaluations in workbook, IStabilityClassifier used to optimise caching performance. Pass `null` for the (conservative) assumption that any cell may have its definition changed after evaluation begins. stabilityClassifier)
private	HSSFFormulaEvaluator(HSSFWorkbook The workbook to perform the formula evaluations in workbook, IStabilityClassifier used to optimise caching performance. Pass `null` for the (conservative) assumption that any cell may have its definition changed after evaluation begins. stabilityClassifier, UDFFinder pass `null` for default (AnalysisToolPak only) udfFinder)

Method Summary

Modifier and Type	Method and Description
public static HSSFFormulaEvaluator	create(HSSFWorkbook The workbook to perform the formula evaluations in workbook, IStabilityClassifier used to optimise caching performance. Pass `null` for the (conservative) assumption that any cell may have its definition changed after evaluation begins. stabilityClassifier, UDFFinder pass `null` for default (AnalysisToolPak only) udfFinder)
protected RichTextString	createRichTextString(String str) Implements abstract org.apache.poi.ss.formula.BaseFormulaEvaluator.createRichTextString.
public void	evaluateAll() Implements org.apache.poi.ss.usermodel.FormulaEvaluator.evaluateAll. Loops over all cells in all sheets of the supplied workbook.
public static void	evaluateAllFormulaCells(HSSFWorkbook wb) Loops over all cells in all sheets of the supplied workbook.
public static void	evaluateAllFormulaCells(Workbook wb) Hides org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateAllFormulaCells. Loops over all cells in all sheets of the supplied workbook.
protected CellValue	evaluateFormulaCellValue(Cell The cell with the formula cell) Implements abstract org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateFormulaCellValue. Returns a CellValue wrapper around the supplied ValueEval instance.
public HSSFCell	evaluateInCell(Cell The `Cell` to evaluate and modify. cell) Overrides org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateInCell. Implements org.apache.poi.ss.usermodel.FormulaEvaluator.evaluateInCell. If cell contains formula, it evaluates the formula, and puts the formula result back into the cell, in place of the old formula.
public void	notifyDeleteCell(HSSFCell cell) Should be called to tell the cell value cache that the specified cell has just been deleted.
public void	notifyDeleteCell(Cell cell) Implements org.apache.poi.ss.usermodel.FormulaEvaluator.notifyDeleteCell. Should be called to tell the cell value cache that the specified cell has just become a formula cell, or the formula text has changed
public void	notifySetFormula(Cell cell) Implements org.apache.poi.ss.usermodel.FormulaEvaluator.notifySetFormula. Should be called to tell the cell value cache that the specified (value or formula) cell has changed.
public void	notifyUpdateCell(HSSFCell cell) Should be called to tell the cell value cache that the specified (value or formula) cell has changed.
public void	notifyUpdateCell(Cell cell) Implements org.apache.poi.ss.usermodel.FormulaEvaluator.notifyUpdateCell. Should be called to tell the cell value cache that the specified (value or formula) cell has changed.
public void	setDebugEvaluationOutputForNextEval(boolean whether to perform detailed output value) Overrides org.apache.poi.ss.formula.BaseFormulaEvaluator.setDebugEvaluationOutputForNextEval. Implements org.apache.poi.ss.usermodel.FormulaEvaluator.setDebugEvaluationOutputForNextEval. Perform detailed output of formula evaluation for next evaluation only?
public void	setIgnoreMissingWorkbooks(boolean whether to ignore missing references to external workbooks ignore) Overrides org.apache.poi.ss.formula.BaseFormulaEvaluator.setIgnoreMissingWorkbooks. Implements org.apache.poi.ss.usermodel.FormulaEvaluator.setIgnoreMissingWorkbooks. Whether to ignore missing references to external workbooks and use cached formula results in the main workbook instead.
public static void	setupEnvironment(String[] the simple file names used to identify the workbooks in formulas with external links (for example "MyData.xls" as used in a formula "[MyData.xls]Sheet1!A1") workbookNames, HSSFFormulaEvaluator[] all evaluators for the full set of workbooks required by the formulas. evaluators) Coordinates several formula evaluators together so that formulas that involve external references can be evaluated.
public void	setupReferencedWorkbooks(Map<String, FormulaEvaluator> Map of workbook names (no square brackets) to an evaluator on that workbook evaluators) Overrides org.apache.poi.ss.formula.BaseFormulaEvaluator.setupReferencedWorkbooks. Implements org.apache.poi.ss.usermodel.FormulaEvaluator.setupReferencedWorkbooks. Sets up the Formula Evaluator to be able to reference and resolve links to other workbooks, eg [Test.xls]Sheet1!A1.

Inherited from org.apache.poi.ss.formula.BaseFormulaEvaluator:: _getWorkbookEvaluator clearAllCachedResultValues evaluate evaluateAllFormulaCells evaluateFormulaCell getEvaluationWorkbook setCellType setCellType setCellValue setupEnvironment

_book	back to summary
private final HSSFWorkbook _book

HSSFFormulaEvaluator	back to summary
public HSSFFormulaEvaluator(HSSFWorkbook workbook)

HSSFFormulaEvaluator	back to summary
public HSSFFormulaEvaluator(HSSFWorkbook workbook, IStabilityClassifier stabilityClassifier) Parameters workbook:HSSFWorkbook The workbook to perform the formula evaluations in stabilityClassifier:IStabilityClassifier used to optimise caching performance. Pass `null` for the (conservative) assumption that any cell may have its definition changed after evaluation begins.

HSSFFormulaEvaluator	back to summary
private HSSFFormulaEvaluator(HSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) Parameters workbook:HSSFWorkbook The workbook to perform the formula evaluations in stabilityClassifier:IStabilityClassifier used to optimise caching performance. Pass `null` for the (conservative) assumption that any cell may have its definition changed after evaluation begins. udfFinder:UDFFinder pass `null` for default (AnalysisToolPak only)

Method Detail

create back to summary

create	back to summary
public static HSSFFormulaEvaluator create(HSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) Parameters workbook:HSSFWorkbook The workbook to perform the formula evaluations in stabilityClassifier:IStabilityClassifier used to optimise caching performance. Pass `null` for the (conservative) assumption that any cell may have its definition changed after evaluation begins. udfFinder:UDFFinder pass `null` for default (AnalysisToolPak only)

public static HSSFFormulaEvaluator create(HSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder)

Parameters

workbook:HSSFWorkbook: The workbook to perform the formula evaluations in
stabilityClassifier:IStabilityClassifier: used to optimise caching performance. Pass null for the (conservative) assumption that any cell may have its definition changed after evaluation begins.
udfFinder:UDFFinder: pass null for default (AnalysisToolPak only)

createRichTextString	back to summary
protected RichTextString createRichTextString(String str) Implements abstract org.apache.poi.ss.formula.BaseFormulaEvaluator.createRichTextString. Annotations @Override

createRichTextString

back to summary

protected RichTextString createRichTextString(String str)

Implements abstract org.apache.poi.ss.formula.BaseFormulaEvaluator.createRichTextString.

Annotations

@Override

evaluateAll	back to summary
public void evaluateAll() Implements org.apache.poi.ss.usermodel.FormulaEvaluator.evaluateAll. Loops over all cells in all sheets of the supplied workbook. For cells that contain formulas, their formulas are evaluated, and the results are saved. These cells remain as formula cells. For cells that do not contain formulas, no changes are made. This is a helpful wrapper around looping over all cells, and calling evaluateFormulaCell on each one. Annotations @Override

evaluateAll

back to summary

public void evaluateAll()

Implements org.apache.poi.ss.usermodel.FormulaEvaluator.evaluateAll.

Loops over all cells in all sheets of the supplied workbook. For cells that contain formulas, their formulas are evaluated, and the results are saved. These cells remain as formula cells. For cells that do not contain formulas, no changes are made. This is a helpful wrapper around looping over all cells, and calling evaluateFormulaCell on each one.

Annotations

@Override

evaluateAllFormulaCells	back to summary
public static void evaluateAllFormulaCells(HSSFWorkbook wb) Loops over all cells in all sheets of the supplied workbook. For cells that contain formulas, their formulas are evaluated, and the results are saved. These cells remain as formula cells. For cells that do not contain formulas, no changes are made. This is a helpful wrapper around looping over all cells, and calling evaluateFormulaCell on each one.

evaluateAllFormulaCells

back to summary

public static void evaluateAllFormulaCells(HSSFWorkbook wb)

evaluateAllFormulaCells	back to summary
public static void evaluateAllFormulaCells(Workbook wb) Hides org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateAllFormulaCells. Loops over all cells in all sheets of the supplied workbook. For cells that contain formulas, their formulas are evaluated, and the results are saved. These cells remain as formula cells. For cells that do not contain formulas, no changes are made. This is a helpful wrapper around looping over all cells, and calling evaluateFormulaCell on each one.

evaluateAllFormulaCells

back to summary

public static void evaluateAllFormulaCells(Workbook wb)

Hides org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateAllFormulaCells.

evaluateFormulaCellValue	back to summary
protected CellValue evaluateFormulaCellValue(Cell cell) Implements abstract org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateFormulaCellValue. Returns a CellValue wrapper around the supplied ValueEval instance. Parameters cell:Cell The cell with the formula Annotations @Override

evaluateFormulaCellValue

back to summary

protected CellValue evaluateFormulaCellValue(Cell cell)

Implements abstract org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateFormulaCellValue.

Returns a CellValue wrapper around the supplied ValueEval instance.

Parameters

cell:Cell: The cell with the formula

Annotations

@Override

evaluateInCell back to summary

evaluateInCell	back to summary
public HSSFCell evaluateInCell(Cell cell) Overrides org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateInCell. Implements org.apache.poi.ss.usermodel.FormulaEvaluator.evaluateInCell. Doc from org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateInCell. If cell contains formula, it evaluates the formula, and puts the formula result back into the cell, in place of the old formula. Else if cell does not contain formula, this method leaves the cell unchanged. Note that the same instance of `Cell` is returned to allow chained calls like: int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType(); Be aware that your cell value will be changed to hold the result of the formula. If you simply want the formula value computed for you, use `evaluateFormulaCell(Cell)`} Parameters cell:Cell The `Cell` to evaluate and modify. Returns:HSSFCell the `cell` that was passed in, allowing for chained calls Annotations @Override

public HSSFCell evaluateInCell(Cell cell)

Overrides org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateInCell.

Implements org.apache.poi.ss.usermodel.FormulaEvaluator.evaluateInCell.

Doc from org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateInCell.

If cell contains formula, it evaluates the formula, and puts the formula result back into the cell, in place of the old formula. Else if cell does not contain formula, this method leaves the cell unchanged. Note that the same instance of Cell is returned to allow chained calls like:

int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType();

Be aware that your cell value will be changed to hold the result of the formula. If you simply want the formula value computed for you, use evaluateFormulaCell(Cell)}

Parameters

cell:Cell: The Cell to evaluate and modify.

Returns:HSSFCell

the cell that was passed in, allowing for chained calls

Annotations

@Override

notifyDeleteCell	back to summary
public void notifyDeleteCell(HSSFCell cell) Should be called to tell the cell value cache that the specified cell has just been deleted. Failure to call this method after changing cell values will cause incorrect behaviour of the evaluate~ methods of this class

notifyDeleteCell	back to summary
public void notifyDeleteCell(Cell cell) Implements org.apache.poi.ss.usermodel.FormulaEvaluator.notifyDeleteCell. Doc from org.apache.poi.ss.usermodel.FormulaEvaluator.notifyDeleteCell. Should be called to tell the cell value cache that the specified cell has just become a formula cell, or the formula text has changed Annotations @Override

notifyDeleteCell

back to summary

public void notifyDeleteCell(Cell cell)

Implements org.apache.poi.ss.usermodel.FormulaEvaluator.notifyDeleteCell.

Doc from org.apache.poi.ss.usermodel.FormulaEvaluator.notifyDeleteCell.

Should be called to tell the cell value cache that the specified cell has just become a formula cell, or the formula text has changed

Annotations

@Override

notifySetFormula	back to summary
public void notifySetFormula(Cell cell) Implements org.apache.poi.ss.usermodel.FormulaEvaluator.notifySetFormula. Should be called to tell the cell value cache that the specified (value or formula) cell has changed. Failure to call this method after changing cell values will cause incorrect behaviour of the evaluate~ methods of this class Annotations @Override

notifySetFormula

back to summary

public void notifySetFormula(Cell cell)

Implements org.apache.poi.ss.usermodel.FormulaEvaluator.notifySetFormula.

Should be called to tell the cell value cache that the specified (value or formula) cell has changed. Failure to call this method after changing cell values will cause incorrect behaviour of the evaluate~ methods of this class

Annotations

@Override

notifyUpdateCell	back to summary
public void notifyUpdateCell(HSSFCell cell) Should be called to tell the cell value cache that the specified (value or formula) cell has changed. Failure to call this method after changing cell values will cause incorrect behaviour of the evaluate~ methods of this class

notifyUpdateCell	back to summary
public void notifyUpdateCell(Cell cell) Implements org.apache.poi.ss.usermodel.FormulaEvaluator.notifyUpdateCell. Doc from org.apache.poi.ss.usermodel.FormulaEvaluator.notifyUpdateCell. Should be called to tell the cell value cache that the specified (value or formula) cell has changed. Failure to call this method after changing cell values will cause incorrect behaviour of the evaluate~ methods of this class Annotations @Override

notifyUpdateCell

back to summary

public void notifyUpdateCell(Cell cell)

Implements org.apache.poi.ss.usermodel.FormulaEvaluator.notifyUpdateCell.

Doc from org.apache.poi.ss.usermodel.FormulaEvaluator.notifyUpdateCell.

Annotations

@Override

setDebugEvaluationOutputForNextEval	back to summary
public void setDebugEvaluationOutputForNextEval(boolean value) Overrides org.apache.poi.ss.formula.BaseFormulaEvaluator.setDebugEvaluationOutputForNextEval. Implements org.apache.poi.ss.usermodel.FormulaEvaluator.setDebugEvaluationOutputForNextEval. Doc from org.apache.poi.ss.usermodel.FormulaEvaluator.setDebugEvaluationOutputForNextEval. Perform detailed output of formula evaluation for next evaluation only? Is for developer use only (also developers using POI for their XLS files). Log-Level WARN is for basic info, INFO for detailed information. These quite high levels are used because you have to explicitly enable this specific logging. Parameters value:boolean whether to perform detailed output Annotations @Override

setDebugEvaluationOutputForNextEval

back to summary

public void setDebugEvaluationOutputForNextEval(boolean value)

Overrides org.apache.poi.ss.formula.BaseFormulaEvaluator.setDebugEvaluationOutputForNextEval.

Implements org.apache.poi.ss.usermodel.FormulaEvaluator.setDebugEvaluationOutputForNextEval.

Doc from org.apache.poi.ss.usermodel.FormulaEvaluator.setDebugEvaluationOutputForNextEval.

Perform detailed output of formula evaluation for next evaluation only? Is for developer use only (also developers using POI for their XLS files). Log-Level WARN is for basic info, INFO for detailed information. These quite high levels are used because you have to explicitly enable this specific logging.

Parameters

value:boolean: whether to perform detailed output

Annotations

@Override

setIgnoreMissingWorkbooks back to summary

setIgnoreMissingWorkbooks	back to summary
public void setIgnoreMissingWorkbooks(boolean ignore) Overrides org.apache.poi.ss.formula.BaseFormulaEvaluator.setIgnoreMissingWorkbooks. Implements org.apache.poi.ss.usermodel.FormulaEvaluator.setIgnoreMissingWorkbooks. Doc from org.apache.poi.ss.usermodel.FormulaEvaluator.setIgnoreMissingWorkbooks. 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 `org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.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 Annotations @Override

public void setIgnoreMissingWorkbooks(boolean ignore)

Overrides org.apache.poi.ss.formula.BaseFormulaEvaluator.setIgnoreMissingWorkbooks.

Implements org.apache.poi.ss.usermodel.FormulaEvaluator.setIgnoreMissingWorkbooks.

Doc from org.apache.poi.ss.usermodel.FormulaEvaluator.setIgnoreMissingWorkbooks.

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 org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.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

Annotations

@Override

setupEnvironment	back to summary
public static void setupEnvironment(String[] workbookNames, HSSFFormulaEvaluator[] evaluators) Coordinates several formula evaluators together so that formulas that involve external references can be evaluated. Parameters workbookNames:String[] the simple file names used to identify the workbooks in formulas with external links (for example "MyData.xls" as used in a formula "[MyData.xls]Sheet1!A1") evaluators:HSSFFormulaEvaluator[] all evaluators for the full set of workbooks required by the formulas.

setupEnvironment

back to summary

public static void setupEnvironment(String[] workbookNames, HSSFFormulaEvaluator[] evaluators)

Coordinates several formula evaluators together so that formulas that involve external references can be evaluated.

Parameters

workbookNames:String[]: the simple file names used to identify the workbooks in formulas with external links (for example "MyData.xls" as used in a formula "[MyData.xls]Sheet1!A1")
evaluators:HSSFFormulaEvaluator[]: all evaluators for the full set of workbooks required by the formulas.

setupReferencedWorkbooks	back to summary
public void setupReferencedWorkbooks(Map<String, FormulaEvaluator> evaluators) Overrides org.apache.poi.ss.formula.BaseFormulaEvaluator.setupReferencedWorkbooks. Implements org.apache.poi.ss.usermodel.FormulaEvaluator.setupReferencedWorkbooks. Doc from org.apache.poi.ss.usermodel.FormulaEvaluator.setupReferencedWorkbooks. Sets up the Formula Evaluator to be able to reference and resolve links to other workbooks, eg [Test.xls]Sheet1!A1. For a workbook referenced as [Test.xls]Sheet1!A1, you should supply a map containing the key Test.xls (no square brackets), and an open FormulaEvaluator onto that Workbook. Parameters evaluators:Map<String, FormulaEvaluator> Map of workbook names (no square brackets) to an evaluator on that workbook Annotations @Override

setupReferencedWorkbooks

back to summary

public void setupReferencedWorkbooks(Map<String, FormulaEvaluator> evaluators)

Overrides org.apache.poi.ss.formula.BaseFormulaEvaluator.setupReferencedWorkbooks.

Implements org.apache.poi.ss.usermodel.FormulaEvaluator.setupReferencedWorkbooks.

Doc from org.apache.poi.ss.usermodel.FormulaEvaluator.setupReferencedWorkbooks.

Sets up the Formula Evaluator to be able to reference and resolve links to other workbooks, eg [Test.xls]Sheet1!A1.

For a workbook referenced as [Test.xls]Sheet1!A1, you should supply a map containing the key Test.xls (no square brackets), and an open FormulaEvaluator onto that Workbook.

Parameters

evaluators:Map<String, FormulaEvaluator>: Map of workbook names (no square brackets) to an evaluator on that workbook

Annotations

@Override

public Class HSSFFormulaEvaluator

Field Summary

Constructor Summary

Method Summary

Field Detail

Constructor Detail

Method Detail