The directives processed by the Microsoft Excel writer are listed below. The suffixes shown are prefixed by the current <WriterKeyword> in a mapping file. By default, the <WriterKeyword> for the Microsoft Excel writer is XLSXW.
These directives are exposed in Workbench as format settings through the Writer Parameters.
DATASET
Required/Optional: Required
This is the name of the destination file.
For example:
XLSXW_DATASET C:/data/states.xlsx
Workbench Parameter: Destination Microsoft Excel File
DEF
Required/Optional: Required
Each Microsoft Excel table must be defined before it can be written. The general form of a Microsoft Excel definition statement is:
XLSX <worksheetName> \
[xlsx_drop_sheet (yes|no)] \
[xlsx_trunc_sheet (yes|no)] \
[xlsx_sheet_order <sheetorder>] \
[xlsx_freeze_end_row <endrow>] \
[xlsx_field_names_out (yes|no)] \
[xlsx_names_are_positions (yes|no)] \
[xlsx_start_col <startCol>] \
[xlsx_start_row <startRow>] \
[xlsx_offset_col <offsetCol>] \
[xlsx_offset_row <offsetRow>] \
[xlsx_table_writer_mode
(Insert_New_Rows|Insert|Update|Delete)] \
[xlsx_row_id <keyColumn>] \
[<fieldName> <fieldType(attributeStyle)>] +
The table definition allows control of the table that will be created. <fieldType> will be used for worksheet creation, but will subsequently be ignored when writing values to the sheet.
The configuration parameters present on the definition line are described in the following table:
|
Field Type |
Contents |
|
worksheetName |
The name of the worksheet to be written. Named ranges can be specified by usage of a slash character (/); for example: <sheetName>/<namedRangeName> If xlsx_drop_sheet is set to YES, the worksheet or named range will be deleted prior to writing. If a template file or template sheet are specified as writer options, the option to drop existing sheets or named ranges is ignored as the existing sheet will always be replaced by the template sheet. If xlsx_trunc_sheet is set to YES then all the data will be deleted (formatting will remain). Worksheet names must be less than 31 characters in length. If a template file or template sheet are specified as writer options, the option to truncate existing sheets or named ranges is applied to the template sheet before it is copied to the destination file. |
| xlsx_table_writer_mode |
The default writer mode for the feature types being written. Valid values are Insert, Update, and Delete. The writer-level choice provides the initial value to all the feature type choices. Default: Insert |
|
xlsx_row_id |
This is the attribute whose value specifies which row is to be updated or deleted when the writer mode is Update, or Delete. It will also work with Insert mode, but it is not required. This attribute will not be written out. For example: xlsx_row_id ID would instruct the writer to modify the rows pointed to by the FME attribute ID. Row indices should be specified, starting from 1. |
| xlsx_field_names_out |
Specifies whether or not to write the headers of the attributes. |
| fieldName | The name of the field to be written. Valid values for field name include any character string devoid of invalid SQL characters and less than 255 characters in length. |
| fieldType |
Excel is not actually a database, so columns are not strictly typed. Therefore, for the schema, FME provides only very generic types (string, datetime, date, time, number, and bool). However, Excel does allow for formatting, which is captured in the attribute type parameter. Please note that if you are creating a worksheet, this format will be applied to the entire column. If you are writing to an existing worksheet, or writing a named range, then only those cells you write will have this format. This means that column width is ignored for any case except a brand new worksheet. |
| xlsx_start_row | A numeric value that tells FME which row to create a named range at (ignored for worksheets). Row indices should be specified starting from 1. |
| xlsx_start_col |
A numeric value which tells FME which column to create the named range at (ignored for worksheets). Column indices should be specified starting from 1. |
| xlsx_offset_row |
The start row (relative to the start of the worksheet or named range) to begin writing data. For sheets, this is from the beginning of the sheet, so a value of 1 for a sheet would write at the very top of the sheet. For a named range, this is relative to the first row of the named range, so a value of 2 for a named range beginning at row 4 would start writing at row 5. Column indices should be specified starting from 1. |
| xlsx_offset_col | This is the same concept as the xlsx_offset_row, but applied to columns instead of rows. See xlsx_offset_row for how this is affected by writing a worksheet versus a named range. |
| xlsx_names_are_positions |
This determines if the attribute names are to be used as Excel column designations. If set to Yes, then column names must be alphabetic and be within the maximum column size for Excel (IV for XLS and XFD for XLSX). xlsx_offset_col will be ignored. If set to No, then columns will be written out in the order specified by the feature type, starting at xlsx_offset_col. |
| xlsx_sheet_order |
The relative order of this feature type in the workbook. Lower values will be placed in front of feature types with higher values. If it is not specified, the feature type will be placed at the end of the workbook. If the feature type is a named range and the containing sheet feature type exists, the sheet position should be specified on the sheet instead. Sheet position indices are integers that should be specified starting from 1. Sheets with identical xlsx_sheet_order parameters will be ordered relative to each other based on incoming order. |
| xlsx_freeze_end_row | Specifies the last row to be frozen. Rows 1 to n will be frozen. This option only applies to writing to a sheet (not a named range). |
WRITER_MODE
Required/Optional: Optional
This directive informs the Microsoft Excel writer which write operations will be performed by default by this writer. This operation can be set to Insert, Delete, or Update. The writer-level value chosen in the settings box gets passed to the feature types (or worksheet) level. The corresponding feature type DEF parameter name is called xlsx_table_writer_mode. It has the same valid options as the writer level mode.
The operation can be set specifically for individual features as well, using the fme_db_operation attribute.
If the XLSXW_WRITER_MODE statement is not specified, then a value of Insert is given.
|
Field Type |
Contents |
| <writer_mode> |
The type of operation that should be performed by the writer. The valid list of values is below: Insert Update Delete |
Example:
XLSXW_WRITER_MODE Insert
Workbench Parameter: Writer Mode
OVERWRITE_FILE
If set to YES, deletes the existing Excel spreadsheet file before writing.
If a template file is specified, this parameter will be ignored and the destination file will automatically be overwritten.
Required/Optional
Required
Workbench Parameter
Overwrite Existing File
TEMPLATEFILE
If specified will make a copy of the template file in the location specified in the DATASET parameter. All operations will be performed on the copy. The template file specified remains unchanged. Note that this parameter overrides the OVERWRITE_FILE writer parameter and the Drop Existing Sheet/Named Range feature type parameter.
Required/Optional
Optional
Workbench Parameter
Template File
TEMPLATE_SHEET
If specified, will make and use a copy of the selected sheet when a new sheet is added. Any sheets already existing in the template file being used will not become a copy of the template sheet.
This parameter is only useful when a valid TEMPLATEFILE parameter is specified.
Note that this parameter overrides the Drop Existing Sheet/Named Range feature type parameter.
Required/Optional
Optional
Workbench Parameter
Template Sheet