insertColumnValues

This command requires extra license.

A child command for modifyWorkbook that will, through its own 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 higher column index) on or between the rows that are being modified get 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", 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 a name of a sheet in the workbook.

Examples

As insertColumnValues provides a simple way to setting 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 own column. The 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 are on column B or to the right of it on rows 4, 5 or 6 are 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.