Microsoft Excel Writer

(warning) PLEASE NOTE: This documentation applies to Pentaho 8.1 and earlier. For Pentaho 8.2 and later, see Microsoft Excel Writer on the Pentaho Enterprise Edition documentation site.

Description

The Microsoft Excel Writer step writes incoming rows into an MS Excel file. It supports both the xls and xlsx file formats. The xlsx format is usually a good choice when working with template files, as it is more likely to preserve charts and other misc objects in the output. The proprietary (binary) xls format is not as well understood and deciphered, so moving/replicating nontrivial xls content in non-MS software environments is usually problematic.

Options

File & Sheet Tab

File Section

The file section allows to choose the result filename and extension, possibly including file generation timestamp information. Use the Show filename(s) button to preview see the resulting filename(s). Multiple files will be created if the split data every x rows option is used.

Option

Description

Stream XLSX data

Check this option when writing large XLSX files. It uses internally a streaming API and is able to write large files without any memory restrictions (of course not exceeding Excel's limit of 1,048,575 rows and 16,384 columns). Note: This option is available since version 4.4.0.

If output file exists

Check this option when writing large XLSX files. It uses internally a streaming API and is able to write large files without any memory restrictions (of course not exceeding Excel's limit of 1,048,575 rows and 16,384 columns). Note: This option is available since version 4.4.0.

Wait for first row before creating file

Checking this option makes the step create the file only after it has seen a row. If this is disabled the output file is always created, regardless of whether rows are actually written to the file.

Add filename(s) to result

Check to have the filename added to the result filenames

Sheet Section

Option

Description

Sheet Name

The sheet name the step will write rows to.

Make this the active sheet

If checked the Excel file will by default open on the above sheet when opened in MS Excel.

If sheet exists in output file

The output file already has this sheet (for example when using a template, or writing to existing files), you can choose to write to the existing sheet, or replace it.

Protect Sheet

The XLS file format allows to protect an entire sheet from changes. If checked you need to provide a password. Excel will indicate that the sheet was protected by the user you provide here.

Template Section

When creating new files (when existing files are replaced, or completely fresh files are created) you may choose to create a copy of an existing template file instead. Please make sure that the template file is of the same type as the output file (both must be xls or xlsx respectively).

When creating new sheets, the step may copy a sheet from the current document (the template or an otherwise existing file the step is writing to). A new sheet is created if the target sheet is not present, or the existing one shall be replaced as per configuration above.

Content Tab

Content Options Section

Options

Descriptions

Start writing at cell

This is the cell to start writing to in Excel notation (letter column, number row)

When writing rows

The step may overwrite existing cells (fast), or shift existing cells down (append new rows at the top of sheet)

Write Header

If checked the first line written will contain the field names

Write Footer

If checked the last line written will contains the field names

Auto Size Columns

If checked the step tries to automatically size the columns to fit their content. Since this is not a feature the xls(x) file formats support directly, results may vary.

Force formula recalculation

If checked, the step tries to make sure all formula fields in the output file are updated.

  • The xls file format supports a "dirty" flag that the step sets. The formulas are recalculated as soon as the file is opened in MS Excel.
  • For the xlsx file format, the step must try to recalculate the formula fields itself. Since the underlying POI library does not support the full set of Excel formulas yet, this may give errors. The step will throw errors if it cannot recalculate the formulas.

Leave styles of existing cells unchanged

If checked, the step will not try to set the style of existing cells it is writing to. This is useful when writing to pre-styled template sheets.

When writing to existing sheet Section

Option

Description

Start writing at end of sheet

The step will try to find the last line of the sheet, and start writing from there.

Offset by ... rows

Any non-0 number will cause the step to move this amount of rows down (positive numbers) or up (negative numbers) before writing rows. Negative numbers may be useful if you need to append to a sheet, but still preserve a pre-styled footer.

Begin by writing ... empty lines

The step will try to find the last line of the sheet, and start writing from there.

Omit Header

Any non-0 number will cause the step to move this amount of rows down (positive numbers) or up (negative numbers) before writing rows. Negative numbers may be useful if you need to append to a sheet, but still preserve a pre-styled footer.

Fields Section

The fields table configures the output columns in the excel document.

Option

Description

Name

The field to write

Type

The type of data

Format

The Excel format to use in the sheet. Please consult the Excel manual for valid formats. There are some online references as well.

Style from cell

A cell (i.e. A1, B3 etc.) to copy the styling from for this column (usually some pre-styled cell in a template)

Field Title

If set, this is used for the Header/Footer instead of the kettle field name

Header/Footer style from cell

A cell to copy the styling from for headers/footers (usually some pre-styled cell in a template)

Field Contains Formula

Set to Yes, if the field contains an Excel formula (no leading '=')

Hyperlink

A field, that contains the target to link to. The supported targets are Link to other cells, http, ftp, email, and local documents

Cell Comment / Cell Author

The xlsx format allows to put comments on cells. If you'd like to generate comments, you may specify fields holding the comment and author for a given column.

Metadata Injection Support

You can use the Metadata Injection supported fields with ETL Metadata Injection step to pass metadata to your transformation at runtime. The following Value fields of the Microsoft Excel Writer step support metadata injection