Menu

evalFormulas

This command requires extra license.

Evaluates specified formulas found in an XLSX worksheet and stores the results into the variable context. Specify the formulas with child eval commands.            

The format of the result stored in the variable context is a Map of Maps. The primary Map links sheet names to Maps of which each then link 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 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 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">  

Comments

0 comments