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:
<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.
<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">