Microsoft Excel Writer

Select the Excel file extension to append to the file name. Options include:

• .xls [Excel 97 and above]
• .xlsx [Excel 2007 and above]

The default extension is .xls.

Select this option when writing large XLSX files to the output file.

• Select this option if you want to stream XLSX files. When you select this check box, the system uses a streaming API to write large files without any memory restrictions (not exceeding MS Excel's limit of 1,048,575 rows and 16,384 columns).
• Clear this check box if you do not want to stream XLSX files.

This option is only available if you select the '.xlsx [Excel 2007 and above]' option in the Extension field above.

Select this option to include the copy number in the file name (_0, for example) when you run the step in multiple copies, such as launching several copies of a step simultaneously.

• Select this option to append a copy number to the file name when multiple copies are launched.
• Clear this option to keep the file name as is when multiple copies are launched. A copy number will not be appended.

Select this option to include the system date in the file name, such as in the format _20181231, for example.

• Select this option to append the system date to the file name.
• Clear this option to keep the file name as is. The system date will not be appended.

Select this option to include the system time in the file name, such as in the format _235959, for example.

• Select this option to append the system time to the file name.
• Clear this option to keep the file name as is. The system time will not appended.

Select this option to include the date and time in the file name using a format from the Date time format field below.

• Select this option to append the system date and time to the file name.
• Clear this option to keep the file name as is. The system date and time will not be appended.

If the output file already exists, decide if you want to replace it with the new file or keep the original file.

• Use existing file for writing: Keep the existing output file and write the new data to it.
• Replace with new output file: Overwrite the existing output file with the new output file created in this transformation step.

If this option is selected, the step creates the file only after it has detected a row. If this option is cleared, the output file is always created, regardless of whether rows are actually written to the file or not.

• Select this option if you want this step to create the output file only after the system detects a row for writing to the file. If no rows are detected, no file will be created.
• Clear this option if you want to always create the output file, regardless of whether rows are actually written to the file.

Using this option adds file names to the MS Excel output file.

• Select if you want to add file names to the Excel output file.
• Clear if you do not want to add file names to the Excel output file.

Select this option if you want to use a specified MS Excel template to create the output file.

• Select this option to specify an Excel template for use in creating the output file. If you select this option, you must also specify the template file name in the Template file field below.
• Clear this option if you do not want to specify a template file.

Select this option if you want to use a specified template sheet to create the output sheet.

• Select this option to specify a template sheet for use in creating the output sheet. If you select this option, you must also specify the name of the template sheet in the Template sheet field below.
• Clear this option if you do not want to specify a template sheet.

Decide if you want the template sheet to be visible when the MS Excel output file is opened.

• Select this option to hide the template sheet from users who open the output file.
• Clear this option to show the template sheet to users who open the output file.

Specify how to write in new rows when the spreadsheet contains existing rows with data. Select one of the following options:

• Overwrite existing cells: Keep the existing cells and write the new data to them.
• Shift existing cells down: Append the new rows to the top of the sheet, shifting the existing cells down.

Decide whether you want to use the first row as the header row in the output spreadsheet.

• Select to use the first row of cells as the header row names for the output spreadsheet.
• Clear to keep the first row formatted as is.

Decide whether you want to use the last row as the footer row in the output spreadsheet.

• Select to use the last row of cells as the footer row names for the output spreadsheet
• Clear to keep the last row formatted as is.

Decide whether the columns should automatically resize to fit the content. Note that auto-sizing is not a feature that the XLS and XLSX file formats directly support, so results may vary.

• Select this option to auto-size columns to fit the content.
• Clear this option to have columns maintain their width, despite the size of the content.

To ensure the most accurate column-width calculations when using the auto-size feature, install the appropriate fonts on your server environment.

Decide whether you want the step to attempt to update all the formula fields in the output file.

When this option is selected for the XLS file format, a flag is set by the step. The formulas are recalculated as soon as the file is opened in MS Excel. When this option is selected for the XLSX file format, the step itself must attempt to recalculate the formula fields. The underlying POI library does not support the full set of Excel formulas, which may contribute to errors if the step cannot recalculate the formulas.

• Select this option to force the step to update all the formula fields in the output file.
• Clear this option to keep the formula fields as is and not update them.

Decide whether you want the step to set the style of existing cells it is writing to. This option is useful when you are writing to pre-formatted template sheets.

• Select this option to leave the style information of existing cells unchanged.
• Clear this option to overwrite the style of existing cells.

Decide if you want the output to begin at the last defined row of the spreadsheet.

• Select this option if you want the step to append rows after the last defined row of the spreadsheet.
• Clear this option if you want the step to overwrite existing defined rows.

Specify any positive number to cause the step to leave this amount of rows empty.

When writing to an existing sheet, you may decide to write a few empty lines first. This option is useful if you want to append a few rows to an existing sheet, but offset them to see the bounds of each generated block of rows.

Decide if you want to include a header when writing to an existing spreadsheet. Omitting a header is useful if you want to generate new files with a header, but you do not want to repeat the header in subsequent runs where you append to the same file.

• Select this option to omit the header.
• Clear this option to include the header.

Set to Yes if the field contains an Excel formula.

You do not need to include the notation = before your field value.