Microsoft Excel writer
The Microsoft Excel writer step writes incoming rows to a Microsoft Excel file.
It supports both:
.xls(binary format; best for simple content).xlsx(Open XML format; better for templates because it can preserve charts and other objects)
Step name
Step name specifies the unique name of the step on the canvas. Default: Microsoft Excel writer.
Configure the step (tabs)
The Microsoft Excel writer step includes two tabs:
File & Sheet
Content
File & Sheet tab
File panel
Use this panel to define the output file name, extension, and splitting behavior.
Filename
Output file name and location. Supports variables and parameters. Do not include a file extension when date/time is appended and Extension is specified.
Extension
Output format: .xls Excel 97 and above or .xlsx Excel 2007 and above . Default: .xls.
Create parent folder
Creates the parent folder if it does not exist. If cleared and the folder does not exist, the step errors.
Stream XLSX data
Streams .xlsx output for large files, avoiding memory restrictions (up to Excel’s limits: 1,048,575 rows and 16,384 columns). Available only when Extension is .xlsx.
Split every ... data rows
Creates a new output file every N data rows (files are numbered).
Include stepnr in filename?
Appends the copy number (for example, _0) when running multiple copies.
Include date in filename?
Appends system date (for example, _20181231).
Include time in filename?
Appends system time (for example, _235959).
Specify Date time format?
Appends date/time using Date time format.
Date time format
Date/time format. See Common Formats.
Show filename(s)...
Shows a simulated list of the files that will be generated.
If output file exists
Choose whether to Use existing file for writing or Replace with new output file.
Wait for first row before creating file
Creates a file only after the first row arrives. Prevents empty files when no rows are written.
Add filenames to result
Adds output file names to the transformation result file list.
Sheet panel
Use this panel to control sheet naming, default sheet behavior, and (for .xls) sheet protection.
Sheet name (max. 31 characters)
Worksheet name. Supports variables and parameters.
Make this the active sheet
When selected, the workbook opens to this sheet by default.
If sheet exists in output file
Choose whether to Write to existing sheet or Replace with new sheet.
Protect sheet? (XLS format only)
Protects the sheet from editing using a user name and password.
Protected by user
User name to display as the protector. Supports variables and parameters.
Password
Password required to unprotect the sheet. Supports variables and parameters.
Template panel
Use this panel to create output files/sheets from templates.
If you use a template file, it must match the selected output type (.xls vs .xlsx).
Use template when creating new files
Creates new output files by copying a template file.
Template file
Template workbook to copy (when Use template when creating new files is selected).
Use template when creating new sheets
Creates new sheets by copying a template sheet.
Template sheet
Template sheet to copy (when Use template when creating new sheets is selected).
Hide Template sheet
Hides the template sheet in the output workbook.
Content tab
Content options panel
Use these options to control where and how data is written.
Start writing at cell
Starting cell in Excel notation (for example, A1).
When writing rows
Controls behavior when the sheet already contains data: overwrite existing cells or shift existing cells down.
Write Header
Writes a header row using field titles (see Fields panel).
Write Footer
Writes a footer row using field titles (see Fields panel).
Auto size columns
Attempts to auto-size columns to fit content. Results may vary; install appropriate fonts on the server for best accuracy.
Retain NULL values
Keeps null cell values. If cleared, nulls are replaced with empty strings.
Force formula recalculation
For .xls, sets a flag so Excel recalculates formulas when the file opens. For .xlsx, attempts recalculation, but the underlying POI library may not support all formulas.
Leave styles of existing cells unchanged
Preserves styles in existing cells (useful for pre-formatted templates).
Extend data validation range
Extends template data validation to include all rows written.
When writing to existing sheet panel
Use these options when writing to a sheet that already exists.
Start writing at end of sheet (appending lines)
Appends rows after the last defined row. If cleared, overwrites existing defined rows.
Offset by ... rows
Moves the write position by N rows before writing. Use negative values to preserve a pre-styled footer.
Begin by writing ... empty lines
Leaves N empty rows before writing. Useful when visually separating appended blocks of rows.
Omit header
Omits the header when appending to an existing sheet (useful to avoid repeating headers).
Fields panel
Use the Fields table to define how fields are written to Excel.
Name
Field name from the incoming stream.
Type
Data type: String, Date, or Number.
Format
Excel format string. Use Excel documentation or references for valid formats.
Style from cell
Cell reference (for example, A1) to copy styling from (typically a pre-styled template cell).
Field title
Header/footer title to use instead of the field name.
Header/Footer style from cell
Cell reference to copy styling for header/footer cells.
field contains formula
Set to Yes when the field contains an Excel formula. Do not include the leading = in the value.
Hyperlink
Field containing hyperlink targets (cell links, URLs, FTP, email, local documents).
Cell comment (XLSX)
Field containing a cell comment (available in .xlsx).
Cell comment author (XLSX)
Field containing the author name for the comment (available in .xlsx).
Metadata injection support
This step supports metadata injection. You can use it with ETL metadata injection to pass metadata to your transformation at runtime.
The following Value fields support metadata injection:
Name
Type
Format
Style from cell
Field title
Header/Footer style from cell
field contains formula
Hyperlink
Cell comment (XLSX)
Cell comment author (XLSX)
Last updated
Was this helpful?