evalFormulas

This command requires an extra license.

Evaluate specified formulas found in an XLSX worksheet and store the results in the variable context. Specify the formulas with child eval commands.

The format of the result stored in the variable context is a (OLD / LEGACY)Map of Maps. The primary Map links sheet names to Maps of which each then links cell addresses to the formula results. An expression to access a formula may therefore look something like this:

${formulaResults["Product data"]["B18"]}

In this expression "formulaResults" is the name of the variable this command produces, "Product data" is the name of the sheet, and "B18" is the formula cell.

Child commands

eval
Required. These child commands specify the cells whose formulas are to be evaluated, and at least one cell needs to be specified.

Attributes

var
Required	Value type	EL-evaluated
Yes	String	No
Defines the name of the variable that will hold the Map of formula results.

workbook
Required	Value type	EL-evaluated
Yes	File	Yes
Defines the XLSX workbook whose formulas are to be evaluated. The resolved File is therefore expected to be an XLSX document.

Examples

Add one eval child command for every formula you wish to evaluate:

CODE

<evalFormulas var="evalResults" workbook="${workbook}">  <eval cell="D4" sheet="Sheet1">  <eval cell="F24" sheet="Sheet1">

The results of these evaluations could then be accessed with expressions ${evalResults["Sheet1"]["D4"]} and ${evalResults["Sheet1"]["F24"]}.

The formula evaluation can be used together with the modifyWorkbook and its child commands to modify values of cells the formula uses to change the results. Do the modifications before the formula evaluation.

CODE

<modifyWorkbook var="modifiedWorkbook" workbook="${workbook}">  <setCellValue cell="B4" sheet="Sheet1" value="${differentValue}">  <setDefinedNameValue name="Q3_Estimate" value="${q3Guesstimate}"><evalFormulas var="differentEvalResults" workbook="${modifiedWorkbook}">  <eval cell="D4" sheet="Sheet1">  <eval cell="F24" sheet="Sheet1">