Microsoft Excel Writer
Description:
Use the Microsoft Excel Writer step to export workflow data into Microsoft Excel workbooks. This step belongs to the Output Plugin category and supports both legacy binary (.xls) and modern XML-based (.xlsx) file formats.
You can configure this step to create new workbooks, append data to existing files, or populate specific sheets within a workbook. For strict formatting requirements, the step accepts a pre-formatted Excel template.
Key capabilities include:
- Define dynamic file names and write headers or footers.
- Protect sheets with passwords and manage cell-level security.
- Write formulas, hyperlinks, and cell comments directly from workflow fields.
- Enable streaming for large .xlsx files to minimize memory consumption during write operations.
Configurations:
| Field Name | Description |
|---|---|
| Step name | Specify a unique name for the step. This label identifies the step within the workflow and helps you reference it when debugging or linking steps. |
| File & Sheet Tab: | Use the tab to configure the file location, naming conventions, and specific sheet targets in this tab. |
| Filename | Specify the directory and base name for the output file. You can enter the path manually or click Browse to select a folder. Note: Do not include the file extension in this field. The step appends it automatically based on the Extension selection. |
| Extension | Select the file extension for the Excel file specified in the Filename field. - xls: Use only when legacy support is required for Excel 2003 or earlier versions. - xlsx: Use for large files, improved performance, and modern Excel compatibility. Note: The older XLS (binary) format is not well-supported outside Microsoft Excel. Handling or replicating complex XLS content in non-Microsoft environments may result in errors or loss of formatting. |
| Stream XLSX data | Select to use the internal streaming API. This is required when writing large datasets to prevent memory errors. It supports files up to Excel's limit (1,048,575 rows and 16,384 columns). Note: This feature is available starting from version 4.4.0. |
| Include stepnr in filename? | Select to automatically append a sequence number (such as _0, _1, _2) to each generated file. This ensures that files created by multiple instances of the same step have unique names and do not overwrite each other. Else, all the instances use the same filename, and the step overwrites the file each time as instance tries to create a file with that same name. |
| Include date in filename? | Select to append the system date (for example, _20231027) to the filename. This helps create unique, date-based output files. |
| Include time in filename? | Select to automatically append the system time (24-hour format) to the end of the filename (for example, _235959). This helps create unique, time-based output files. |
| Specify Date time format | Select to apply a custom timestamp format to the filename. Note: Field automatically controls the timestamp, so Include date in file name and Include time in file name becomes disabled. |
| Date time format | Select date time format from the list. Available date time formats are: - yyyyMMddHHmmss - yyyy-MM-dd - yyyyMMdd - MM-dd-yyyy - MM-dd-yy - dd-MM-yyyy |
| If output file exists | Define the behavior if the target file is found: - Replace with new output file: Overwrites the existing file. - Use existing file for writing: Appends data to or updates the existing file. |
| Wait for first row before creating file | Select to delay file creation until the step receives data. If cleared, the step creates an empty file even if no rows are processed. |
| Add filename(s) to result | Select to add the filename of each processed file to the Workflow Result stream when executing through AE UI. This allows subsequent steps to access the list of processed files for logging or auditing purposes. |
| File Password | Specify a password to encrypt the workbook. |
| Sheet: | |
| Sheet Name | Specify the name of the sheet where the step writes data. |
| Make this the active sheet | Select to set this sheet as the active (visible) view when a user opens the file in Excel. |
| If sheet exists in output file | Define the behavior if the sheet already exists (e.g., in a template or existing file): - Replace: Overwrites the sheet content. - Write to existing: Appends new rows to the existing data. |
| Protect Sheet (XLS format only) | Select to lock the sheet against changes for XLS file format only. You must provide a password in the Password field. |
| Template: | 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). Although 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. 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. |
| Use template when creating new files | Select to use a template file as the base for new outputs. Note: You must select the checkbox if you want add multiple templates to an existing file. |
| Template file | Specify the full path to the template file. Or Click Browse to locate the file. |
| Use template when creating new sheets | Select to copy a specific sheet from the template when creating a new sheet in the target file. |
| Is template sheet present in target file? | Select this option if the target file already contains the template sheet and to update it. |
| Template sheet | Specify the name of the source sheet within the template file to copy. |
| Hide Template Sheet | Select to hide the template sheet in the final output file. |
| Content Tab: | Use the Content tab to define exactly where data is written within the sheet, how to handle headers/footers, and how to map specific workflow fields to Excel columns. |
| Start writing at cell | Specify the specific cell to begin writing (for example, A1, B5). This uses standard Excel column-letter and row-number notation. |
| When writing rows | Select the write behavior: - overwrite existing cells: Replaces existing cell content (Faster). - shift existing cells down: Inserts new rows at the top and shifts existing content down. |
| Write Header | Select to include the field names as the first row (Header) of the output. |
| Write Footer | Select to include the field names as the last row (Footer) of the output. |
| Auto Size Columns | Select this option to automatically adjust column widths based on the content length. Note: Results may vary because the system estimates text dimensions. The Excel (xls/.xlsx) format does not provide native support for precise font measurement. |
| Force formula recalculation | Select to ensure that all formula fields in the output file display the correct values. The behavior depends on the file extension you selected: - For .xls files: The step sets a "dirty" flag in the file. This forces Microsoft Excel to recalculate all formulas immediately when you open the file. This is the safest method. - For .xlsx files: The step attempts to recalculate the formula values itself during the workflow. Note: Since the internal engine POI library does not support every complex Excel formula, the step may throw an error if it encounters a formula it cannot calculate. |
| Ignore formula evaluation error | Select to suppress errors if a formula is invalid. In this case, the output file displays the raw formula string instead of a calculated value. |
| Leave styles of existing cells unchanged | Select this option to preserve the original formatting of the target cells. Use this when writing data into a pre-formatted template to ensure the step does not overwrite your specific fonts or colors. |
| When writing to existing sheet: | |
| Start writing at end of sheet | Select to append data after the last populated row in the sheet. |
| Offset by ... rows | Specify a number to shift the starting write position: - Positive number: skips rows downward. - Negative number: moves rows upward (useful when appending data but preserving a pre-styled footer). |
| Begin by writing ... empty lines | Specify number of blank rows to insert before writing the actual data. |
| Omit Header | Select to exclude the header row from being written. |
| Table Fields: | |
| Name | Select the input field from the workflow stream to write to Excel. |
| Type | Select the data type of the field from the list. - Number - String - Date - Boolean - Integer - BigNumber - Binary - Timestamp - Internet Address |
| Format | Select the format of the selected field type from the list. For example, $#,##0.00, dd/MM/yyyy |
| Style from cell | Specify a cell reference (e.g., A1) to copy the styling (font, border, background) for this entire column. This is useful when using templates. |
| Field Title | Specify a custom name for the header/footer. If left blank, the step uses the workflow field Name. |
| Header/Footer style from cell | Specify a cell reference to copy the styling for the header or footer row of this column. |
| Field Contains Formula | Select Yes if the field data is an Excel formula (for example, SUM(A1:B1)). Do not include the leading sign (=) in the data. |
| Hyperlink | Select a field that contains the target URL or file path. Supported targets include HTTP, ftp, email addresses (mailto:), and local file paths. |
| Cell Comment / Cell Author | Select fields containing comment text and author names to attach comments to the cells in this column (Valid for .xlsx only). |
| Get Fields | Click to get the field details from the input stream. |
| Minimal Width | Sets a fixed column width for each field using metadata or the maximum length of data in the column. |