Menu

evalRows

This command requires extra license.

Evaluates and retrieves the values of all cells within a defined area of an XLSX worksheet. This includes formula cells as well as cells with static content, and completely empty cells too. This command is very similar to evalDefinedName, differing only by the way of specifying the cells whose values to retrieve.

The data is delivered into the variable context as a Collection of Collections. Each of the inner Collections represents a row, with the items within being the values of the cells in that row. The cell values are in the Collection in their column order, so for example the value of cell A2 is followed by the value of B2, which is followed by C2, and so on. These row-representing Collections are ordered with a similar logic, with the first inner Collection being the first row of the defined area, followed by the Collection of the next row, and so on.    

Attributes

var

Required
Value type
EL-evaluated
Yes Collection No
Defines the name of the variable that will hold the data Collection.
areaStart

Required
Value type
EL-evaluated
Yes String Yes
Defines the starting cell of the area to evaluate. The resolved String is expected to specify the column and row, like "A2" for the cell in column A, row 2. This is the top-left point of the area, with the area extending from there up to the column and row of the end cell.
areaEnd


Required
Value type
EL-evaluated
Yes String Yes
Defines the ending cell of the area to evaluate. The resolved String is expected to specify the column and row, like "D14" for the cell in column D, row 14. This is the bottom-right point of the area, with the area extending from the start cell there up to the column and row of this end cell.
workbook


Required
Value type
EL-evaluated
Yes File
Yes
Defines the XLSX workbook whose data is being retrieved. The resolved File is therefore expected to be an XLSX document.
sheet


Required
Value type
EL-evaluated
Yes String Yes
Defines the sheet containing the data to retrieve. The resolved String is expected to be a name of a sheet in the workbook.

Examples

The evalRows command is an alternative to evalFormulas for calculating results of Excel formulas, better suited for cases in which there so many formula cells to handle that the amount of eval commands needed seems like an inconvenience. 

The format of the data the command outputs - a Collection of Collections - is well-suited for producing a table on a Form or a document. So, a Step preceding a Form can have an evalRows command like this:

<evalRows var="sheetData" areaStart="A2" areaEnd="G20" workbook="${xlsxFile}" sheet="Sheet 1">

A following Form could then have a dynamic table like this to present the data:

<table dyn-repeat="sheetData" dyn-repeat-var="row">
  <tr dyn-repeat="row" dyn-repeat-var="cellValue">
    <td dyn-content="cellValue"/>
  </tr>
</table>

The data format is somewhat less convenient if the intention is to access values of specific cells. The only way to access specific values in a Collection is to use indexes in the EL expressions, which requires one to calculate the row and cell index based on the area's starting cell. Using the example evalRows above whose area starts at cell A2, that starting cell's value is accessed with ${sheetData[0][0]}, while cell D5 would be behind ${sheetData[3][3]}.  

Comments

0 comments