Excel Output

Mode

Determines how the Excel file is constructed.

• memory: The entire workbook is constructed in memory.
• stream: The workbook is constructed using temporary files, rows are kept in temporary files. This mode saves memory and is prefereable when generating large files.

When using streaming mode, only a minimal amount of information is kept in memory. As a consequence the following limitations apply:

• sheets cannot be created from template sheets
• can only reference format cells in sheets not written to
• cannot overwrite or shift down any existing rows

Evaluated once when step initializes

Output File

The path of the output file.

If the file does not exist, a new workbook file is created.

Evaluated for each input row

If File Exists

Determines what to do if the output file already exists.

• use_existing: writes data into the existing file
• replace: replaces the file with a new one
• error: throws an error

Evaluated for each input row

Sheet Name

The sheet to write data to. (Max 31 characters)

If the sheet does not exist in the workbook, it is created.

Evaluated for each input row

If Sheet Exists

Determines what to do if the output sheet already exists.

• use_existing: writes data into the existing sheet
• replace: replaces the sheet with a new one
• error: throws an error

Evaluated for each input row

Write Header

If enabled, the step writes a header row with column names before the data rows.

If disabled, only data rows are written.

Evaluated for each input row

Header Format Cell

If a header row is written, the header styles are inherited from the given cell.

You can specify as cell in ColumnRow format, such as A1, or Sheet!ColumnRow format such as TemplateSheet!A1.

This feature is useful when you’re using a template file that contains pre-styled cells.

Evaluated for each input row

Start At

Defines the cell to start writing to.

Specify as cell in ColumnRow format, such as A1, B2, etc.

Evaluated for each input row

Append

If enabled, the step ignores the start row indicated by the Start At setting and appends rows after any existing rows in the sheet.

Evaluated for each input row

Existing Rows

Determines how any existing content in the sheet is handled when writing data

• overwrite: data is written into existing row cells
• shift: existing rows are shifted down

Note that this setting takes effect only when working in ‘memory’ mode. Streaming mode always appends new rows after any existing content.

Evaluated once when step initializes

Auto-Size Columns

If enabled, columns written to the sheet are auto-sized to fit their content.

Please note that enabling this setting can affect step performance. Estimating the size of cell content is computationally intensive.

Evaluated once when step initializes

Formula Recalculation

Determines how formulas are recalculated.

• in excel: formulas are re-calculated when the file is opened in Excel
• in memory: the step attempts to re-calculate all formula values before they are written to disk

It is recommended to let Excel perform formula evaluations upon opening the file.

Note however that if the file is never opened by Excel, formula values are not recalculated. In cases where the generated file is immediately passed on for further processing, programs other than Excel may not see up-to-date values.

To support such cases, in memory evaluation of formulas can be attempted on a best-effort basis. It cannot be guaranteed to work in all circumstances. Test your templates thoroughly if you wish to use in memory formula evaluation.

Evaluated once when step initializes

Template File

Path to an *.xlsx file used as a template use when creating new output files. A copy of the template file is used to write data into.

Leave empty or nil to start with blank output files.

Evaluated for each input row

Template Sheet

Name of a template sheet to clone when creating new output sheets.

Leave empty or nil to start with blank new sheets.

Evaluated for each input row

Columns

How output table structure is established:

• fixed
  The columns of the output file are static. They are specified directly.

• data driven
  The columns of the output file are established at runtime.

Specified at design time

Column Names

A list defining the columns written to file.

Eeach entry can be one of:

• a string indicating the name of the columns
• a dict with the following keys:
  • :name - The name of the column

To specify three output columns you would supply a definition such as:

['id', 'name', 'sku']

The following is equivalent:

[
  {:name 'id'},
  {:name 'name'},
  {:name 'sku'}
]

Evaluated for each new file

Column Values

A list or dict defining the data to write.

If the data structure is a list, the value for each column is extracted in order.

If the data structure is a dict, the values for columns are extracted by using column names as keys.

For example, consider the following column definition:

[
  {:name 'id'},
  {:name 'name'}
]

We can supply column values as a list:

[123, "Sherlock Holmes"]

We can also supply column values as a dict:

{
  :id 123,
  :name "Sherlock Holmes"
}

Evaluated for each input row

Column Formats

A list or dict defining the cell format to write.

If the data structure is a list, the format for each column is extracted in order.

If the data structure is a dict, the format for columns is extracted by using column names as keys.

Each format is expected to to evaluate to a cell format definition.

For example, consider the following column definition:

[
  {:name 'id'},
  {:name 'name'}
]

We can supply cell formats as a list:

[id_format, name_format]

We can also supply formats as a dict:

{
  :id id_format,
  :name name_format
}

Evaluated for each input row