XLSX DATA

Provides tags for using Excel workbooks as sources of data as well as lightly modifying them .

createDataSource

Creates a new variable for a Data source built out of a specified sheet of an XLSX workbook. This Data source can be used with setMap to provide other tags access to the sheet's cell values.

Attributes

var
Required	Value type	EL-evaluated
Yes	String	No
Defines the name of the variable that will hold the Data source.

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

sheet
Required	Value type	EL-evaluated
No	String	Yes
Defines the sheet whose data is read. The resolved String is expected to be a name of a sheet on the workbook. If not defined, the workbook's first sheet is used.

eval

A child tag for evalFormulas that specifies a cell whose formula is to be evaluated.

Parent tags

• evalFormulas
  Required. This parent tag specifies the workbook whose formulas are being evaluated.

Attributes

cell
Required	Value type	EL-evaluated
Yes	String	Yes
Defines the cell whose formula is to be evaluated. The resolved String is expected to specify the column and row, like "B18" for the cell in column B, row 18.

sheet
Required	Value type	EL-evaluated
Yes	String	Yes
Defines the sheet where the target formula cell is in. The resolved String is expected to be a name of a sheet in the workbook.

Examples

See the documentation of evalFormulas for examples.

evalDefinedName

Evaluates and retrieves the values of all cells belonging to a defined name of an XLSX workbook. This includes formula cells as well as cells with static content, and completely empty cells too. This tag is very similar to evalRows, 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. The 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.

name
Required	Value type	EL-evaluated
Yes	String	Yes
Defines the name of the defined name whose cells' data is retrieved.

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.

Examples

This tag has the same uses as evalRows due to the same format of value they both produce - the cells organized in a Collection of Collections. So this example is pretty much the same as well.  

The evalDefinedName can be used to easily present the defined name's area on a Form or a document.  So, a Step preceding a Form can have an evalDefinedName tag like this:

<evalDefinedName var="nameData" name="SpecialPrices" workbook="${xlsxFile}">

The values of cells that the name "SpecialPrices" refers to are now in a variable. A dynamic table in HTML can present the data like this:

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

The data format is somewhat inconvenient 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 name's referred area's starting cell (which can be found by inspecting the XLSX workbook in Excel). So if the area starts at cell B4, that starting cell's value is accessed with ${sheetData[0][0]}, while cell C5 would be behind ${sheetData[1][1]}.

evalFormulas

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

The format of the result stored in the variable context is a Map of Maps. The primary Map links sheet names to Maps that 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 tag produces, "Product data" is name of the sheet, and "B18" is the formula cell.

Child tags

• eval
  Required. These child tags 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 tag 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, setCellValue and setDefinedNameValue tags 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">  

evalRows

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 tag 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 tag 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 tags needed seems like an inconvenience. 

The format of the data the tag 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 tag 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]}.  

modifyWorkbook

Prepares an XLSX workbook for modification, which is done by this tag's child tags. After the child tags have done their jobs, the modified workbook is saved into the variable context.

Child tags

• setCellValue
  Optional. One or more of these child tags can be used to modify cell values of the workbook.
• setDefinedNameValue
  Optional. One or more of these child tags can be used to modify cell values of the workbook.

Attributes

var
Required	Value type	EL-evaluated
Yes	String	No
Defines the name of the variable that will hold the modified workbook File.

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

setCellValue

A child tag for modifyWorkbook that sets a new value for a cell on the workbook.

Parent tags

• modifyWorkbook
  Required. This parent defines the workbook that is modified.

Attributes

cell
Required	Value type	EL-evaluated
Yes	String	Yes
Defines the cell that receives the new value. The resolved String is expected to specify the column and row, like "D44" for the cell in column D, row 44.

sheet
Required	Value type	EL-evaluated
Yes	String	Yes
Defines the sheet where the target cell is in. The resolved String is expected to be a name of a sheet in the workbook.

value
Required	Value type	EL-evaluated
Yes	Any	Yes
Defines the new value of the cell. If the resolved value is a Number or a Boolean, the cell's type is set to numeric or boolean, as appropriate. If the value is an empty String, the cell's value is set to null. If it's a value of any other type, that value is converted into a String and the cell type set to text.

Examples

Add one setCellValue tag for every cell whose value you wish to modify.

<modifyWorkbook var="modifiedWorkbook" workbook="${workbook}">
  <setCellValue cell="B4" sheet="Sheet1" value="${differentValue}">
  <setCellValue cell="C4" sheet="Sheet1" value="${differentMultiplier}">

If you're setting fixed values into cells, be particularly careful when setting numbers. A number that is not wrapped with the EL-expression notation is actually a String, and String values do not function as the numbers they may look like when they are components of formula cells' calculations. So, if you'd like to set a cell's value as the number 22 for example, set the value attribute into ${22}, not 22.  

Also see the documentation of evalFormulas for an example of using this tag to execute dynamic formula alterations.

setDefinedNameValue

A child tag for modifyWorkbook that sets a new value for a cell referenced by a defined name. If the defined name refers to an area rather than a single cell, the new value is set into the first (top-left) cell of the area.

Parent tags

• modifyWorkbook
  Required. This parent defines the workbook that is modified.

Attributes

name
Required	Value type	EL-evaluated
Yes	String	Yes
Defines the defined name whose cell receives the new value.

value
Required	Value type	EL-evaluated
Yes	Any	Yes
Defines the new value of the cell. If the resolved value is a Number or a Boolean, the cell's type is set to numeric or boolean, as appropriate. If the value is an empty String, the cell's value is set to null. If it's a value of any other type, that value is converted into a String and the cell type set to text.

Examples

Add one setDefinedNameValue tag for every defined name whose referenced value you wish to modify.

<modifyWorkbook var="modifiedWorkbook" workbook="${workbook}">
  <setDefinedNameValue name="CarrotPrice" value="${newCarrotPrice}">
  <setDefinedNameValue name="BlueberryShipments" value="${expectedBBShipments}">

If you're setting fixed values into cells, be particularly careful when setting numbers. A number that is not wrapped with the EL-expression notation is actually a String, and String values do not function as the numbers they may look like when they are components of formula cells' calculations. So, if you'd like to set a cell's value as the number 15 for example, set the value attribute into ${15}, not 15.  

Also see the documentation of evalFormulas for an example of using this tag to execute dynamic formula alterations.

setMap

Creates a Map populated with data read from an XLSX Data source created with createDataSource. The Map's keys are from one column of the worksheet and values from another column. A key and a value on the same row then form an entry for the Map.

Attributes

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

value
Required	Value type	EL-evaluated
Yes	Data source	Yes
Defines the data used to populate the Map. The resolved value is expected to be an XLSX Data source.

keyColumnName
Required	Value type	EL-evaluated
No	String	Yes
Defines which column provides the keys for the Map's key-value pairs. The resolved String should be the "name" of a column, the name being the text content of a column's first row cell. If not defined or if the specified column was not found, the first column of the sheet is used as the key column.

valueColumnName
Required	Value type	EL-evaluated
Yes	String	Yes
Defines which column provides the values for the Map's key-value pairs. The resolved String should be the "name" of a column, the name being the text content of a column's first row cell. If the specified column cannot be found, the tag produces an error.

Examples

If you'd like to define Maps to use in your logic as Excel workbooks, this setMap tag used together with createDataSource can help. This approach has mostly been used for translation purposes.  

<createDataSource var="translationSource" data="${translationXLSX}" />
<setMap value="${translationSource}" var="translated" valueColumnName="${translateTo}" keyColumnName="original" />