Writer Directives

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