insertColumnValues
This command requires an extra license.
A child command for modifyWorkbook will, through its child commands, set values into one or multiple cells of the specified column. Any cell content on the specified column or to the right of it (ie. has a higher column index) on or between the rows that are being modified gets shifted one column to the right, so insertColumnValues will never overwrite any existing cell content.
Parent commands
modifyWorkbook
Required. This parent defines the workbook that is modified.
Child commands
columnCell
Required. At least one of these child commands is required to set a new value for a cell in the specified column.
Attributes
column | ||
|---|---|---|
Required | Value type | EL-evaluated |
Yes | Number, String | Yes |
Defines the column whose cells will be modified by the child commands. The resolved value can be a String specifying a column name, such as "A", "C" or "BW", or a String specifying the column index, such as "1", "2" or "32", or a Number specifying the index. The indexes start from 1, so column "A" is 1, "B" is 2, and so on. | ||
sheet | ||
|---|---|---|
Required | Value type | EL-evaluated |
Yes | String | Yes |
Defines the sheet where the target column is in. The resolved String is expected to be the name of a sheet in the workbook. | ||
Examples
As insertColumnValues provides a simple way to set values into multiple cells of a column, it pairs well with forEach for producing multiple content-filled columns. One could, for example, make rows out of Salesforce query results - the following goes through a Collection of Opportunity records, setting the Name and Amount of each Opportunity onto its column. The (OLD / LEGACY)Loop status provided by forEach is used to generate the sequential column indexes, with the first column to modify in this example being column 2, or B if going by column name.
<forEach value="${opportunities}" var="opp" varStatus="status"><insertColumnValues column="${status.count + 1}" sheet="Sheet1"><columnCell row="4" value="${opp.Name}"/><columnCell row="6" value="${opp.Amount}"/>Any existing cell content on the sheet that is on column B or to the right of it on rows 4, 5, or 6 is shifted right as many columns as there are Opportunities to loop through. Any content on those columns on any other row would remain in place.