Microsoft Excel: Writer Feature Type Parameters

• Drop sheet: Worksheets are deleted and recreated after all existing worksheets. (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 worksheets/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 worksheet in the spreadsheet, with worksheets having a higher order placed after it. If a value is not specified, then the sheet will be placed after all worksheets with a specified sheet order. If two worksheets 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 worksheets in the form of [sheetName,sheetPosition]

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

the worksheets 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.

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 worksheets 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. Both password and non-password protection are supported. There is currently no way to protect the entire spreadsheet to prevent addition, removal, or rearranging of worksheets. This setting does not apply to named ranges.

Locked Versus Unlocked

When considering permissions in Excel, it is important to understand the distinction between locked and unlocked cells, and when cells are and are not locked.

By default, all cells are locked, which means that many permissions (specified below) might not behave as you expect. For example, the “Delete Columns” permission will only allow you to delete columns that were already marked as “Unlocked” when the sheet was protected. You can unprotect cells, or ranges of cells, rows, and columns, by right-clicking on them, then selecting Format Cells >Protection and unchecking Locked.

Scenarios are declared as “Protected” either on first creation, or toggled after by editing them from the Excel Scenario Manager. This protection acts the same as locking or unlocking a cell. FME does not unlock cells when writing them out.

Microsoft's website contains a comprehensive description of locking, unlocking, and permissions.

There are three permission settings.

• View Permissions: Users may only view the worksheets.
• Select Permissions: Users may only select cells.
• Specific Permissions: Custom permissions, selecting permissions from the “Specific Permissions” box allows a user those permissions.

Specific Permissions

• Select locked cells
• Select unlocked cells
• Format cells: Allows users to format locked or unlocked cells, and by extension, allows users to format entire columns or rows
• Format columns: Allows users to format entire columns, but not individual cells
• Format rows: Allows users to format entire rows, but not individual cells
• Insert rows
• Insert columns
• Add hyperlinks to unlocked cells
• Delete unlocked columns
• Delete unlocked rows
• Sort unlocked rows
• User Autofilter on unlocked cells
• Use PivotTable & PivotChart on unlocked cells: Allows users to manipulate pivot tables or charts already created for cells on the given sheet
• Edit unlocked objects: Allows users to edit unlocked cells and graphic objects, such as images, charts, shapes or text boxes, and add comments
• Edit unprotected scenarios: Allows users to add scenarios, and remove or edit scenarios that are not protected

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 spreadsheet. 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 number identifier to be used for per-feature Update and Delete operations.

The default row number attribute is the xlsx_row_id attribute produced by the Excel reader to hold the row number, but it can be changed to any attribute.

Note: Unlike databases, where row IDs may not be ordered, the row number identifier corresponds to the number of rows from the top of the sheet (similar to the row number in a CSV file).