Excel (XLSXW) Writer: Format Parameters

• Drop sheet: Worksheets are deleted and recreated after all existing sheets. (The sheet order may change.)
• Drop named range: Formatting and data in the extents of the existing named range are lost, but formatting in cells that exist outside the named range is unaffected. Original extents of the named range are lost. A new named range is created using the numbers specified in the Feature Type parameters Named Range Start Column and Named Range Start Row.

• This parameter is ignored if a template file is used, since the existing sheet/named range will always be replaced by the template sheet/named range. However, the writer feature type option to Truncate Existing Sheet/Named Range is applied to template sheets/named ranges before being copied to the destination file.

If a template file or template sheet is specified as a writer parameter, the option to truncate the existing sheet or named range is applied to the template sheet before it is copied to the destination file. Inserted items such as charts and pictures are not deleted.

• Truncate sheet: Formatting such as cell color and column widths that exist in cells outside the data extents remain.
• Truncate named range: Data and formatting in the extents of the existing named range is lost, but formatting in cells outside this existing named range is unaffected.

The Feature Type parameters Named Range Start Column and Named Range Start Row are ignored.

Data written to the named range use the original start positions and begin writing at the offset specified by Start Column and Start Row.

This parameter specifies a relative order of the feature type. Valid values are all positive integers (starting from 1).

The sheet with the lowest relative order will be the first sheet in the workbook, with sheets having a higher order placed after it. If a value is not specified, then the sheet will be placed after all sheets with a specified sheet order. If two sheets have identical sheet order values, then the two will be ordered according to their incoming order.

If the sheet feature type has no specified order, but it contains a named range feature type which does, then the order on the named range will be used. If the sheet with no order contains two named ranges with different sheet orders, then one of those values will be selected arbitrarily. Therefore, to avoid ambiguity, it is always best to specify the order directly on the sheet feature type.

Example

If you write 7 sheets in the form of [sheetName,sheetPosition]

{(A, ),(B,9),(C, ),(D,4),(E,4),(F, ),(G,2)}

the sheets are written in the order

G,D/E,B,A/C/F

where the order of (A/C/F) and (D/E) are dependent on their incoming order to the writer.

Using Feature Type Fanout will pass the sheet order of the template feature type to all its fanned out feature types. (See the topic Setting Feature Type Fanout in FME Workbench help.)

This parameter specifies whether or not to write the attribute names when inserting data.

When a template file is specified, this parameter is always in effect, when inserting data.

If no template file is used, this parameter has an effect only if the sheet or named range is being created or has been dropped or truncated prior to insertion. Otherwise, attribute names will not be added to existing sheets or named ranges.

This parameter allows the users to freeze the first n rows, where n is the last row to be frozen. For example,

• 1: the first row is frozen
• 2: the first two rows are frozen
• 5: the first five rows are frozen

In Excel this feature is called Freeze Panes, and it allows you to freeze a portion of the sheet so that certain rows like column headers remain visible while scrolling.

This option has no effect on named ranges.

Set to Yes to treat attribute names, such as A, B, or C as the column name in which to write the data. If set to No (the default), the columns will be written in the order on the schema.

If writing a Named Range, as determined by the feature type name, this parameter specifies the starting column in the sheet where the Named Range will be defined if it does not exist.

This parameter is ignored if the Named Range name supplied already exists and has a defined range.

If writing a Named Range, as determined by the feature type name, this parameter specifies the starting row in the sheet where the Named Range will be defined if it does not exist. This parameter is ignored if the Named Range name supplied already exists and has a defined range.

This parameter specifies a starting column location for writing data in the sheet identified by the feature type name. This index applies to cell-based data but does not apply to raster or other non-cell-based data.

Row indexes are valid values between 1 and the maximum number of columns allowable in the sheet.

This parameter specifies a starting row location for writing data in the sheet identified by the feature type name. This index applies to cell-based data but does not apply to raster or other non-cell-based data.

Row indexes are valid values between 1 and the maximum number of rows allowable in the sheet.

Specifies whether to enable sheet protection on the current sheet corresponding to this feature type. Sheet protection is useful to prevent someone from accidentally or deliberately changing, moving, or deleting important data. In Excel, both password and non-password protection are supported, but only non-password protection is currently supported in FME. A similar setting exists for cell protection per column; however, there currently is no way to protect the entire workbook to prevent addition, removal, or rearranging of sheets. This setting does not apply to named ranges.

You can embed a map or raster image from another source directly into a spreadsheet. This parameter determines which raster format will be used for rasters written to the workbook. There are three supported options

• PNG
• JPEG
• BMP

When sending a raster feature to the writer, there are a few attributes you can add to modify the result.

The writer mode can be specified on the feature type or on individual features.

At the feature type level, the writer mode is specified by this parameter. It may be superseded on individual features by the fme_db_operation format attribute.

Valid feature type writer modes are listed below:

The writer allows you to specify an input attribute to contain the value of the row identifier to be used for per-feature Update and Delete operations.