Microsoft Excel Writer
Description:
Microsoft Excel Writer is a step in the Output Plugin for Process Studio Workflows. Microsoft Excel Writer step writes into an MS Excel file and supports both the xls and xlsx file formats.
Configurations:
| No. | Field Name | Description |
|---|---|---|
| File: | ||
| 1 | Filename | The name of the spreadsheet file you are writing to. |
| 2 | Extension | Select extension for selected file from a list. - Xls - xlsx Note: 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. |
| 3 | 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. |
| 4 | Include stepnr in filename? | Check the field to append a number (starting from _0, _1, _2, etc.) to each file name, which helps distinguish between files generated by different instances of the same step. Enable this option to ensure each file created by multiple instances of the step has a unique number appended to its name. Uncheck or disable this option to keep the file name the same for each instance of the step, which might lead to overwriting files as each instance tries to create a file with the same name. |
| 5 | Include date in filename? | Check to include the date in filename. |
| 6 | Include time in filename? | Check to include the time in filename. |
| 7 | Specify Date time format | Check to specify the date time format. |
| 8 | Date time format | Select the date time format from list. Note: Field is enabled only when the Specify Date time format is checked. |
| 19 | If output file exists | Select from the list if output file is already available: - Replace with new output file. - Use existing file for writing. |
| 5 | 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. |
| 6 | Add filename(s) to result | Check to have the filename added to the result filenames |
| 7 | File Password | Specify a password for the file. |
| Sheet | ||
| 1 | Sheet Name | The sheet name the step will write rows to. |
| 2 | Make this the active sheet | If checked the Excel file will by default open on the above sheet when opened in MS Excel. |
| 3 | 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. |
| 4 | 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: | 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 (bot 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. | |
| 1 | Use template when creating new files | Select the checkbox if you want to create new files using the template. Note: You must select the checkbox if you want add multiple templates to an existing file. |
| 2 | Template file | Select the template file from the folder path. Note: Click Browse to select the folder path. |
| 3 | Use template when creating new sheets | Select the checkbox if you want to add template details as a new sheet to the existing file. |
| 4 | Is template sheet present in target file? | Select the checkbox if the template sheet is already present in the target file. Selecting the checkbox ensures that the template is updated in the target file. |
| 5 | Template sheet | Specify the name of the sheet that will be added to the file. |
| 6 | Hide Template Sheet | Select the checkbox if you want to hide the template sheet added to the file. |
| Content Tab: | ||
| Content | ||
| 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. | |
| Ignore formula evaluation error | If checked, the step evaluates the formula entered by the user, and even if the formula is incorrect the error is ignored. Note: The output file displays the whole formula as a value if there is an error in the formula. | |
| 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. | |
| Sheet: | ||
| 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: | ||
| 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. | |
| 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 Process Studio 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 putting comments on cells. If you'd like to generate comments, you may specify fields holding the comment and author for a given column. |