evalRows
This command requires an extra license.
Evaluate and retrieve 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 empty cells. 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 (OLD / LEGACY)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 the 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 are 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">The 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 the 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]}.