Microsoft Excel (XLSXW) Writer Parameters
Usage Notes
A hyperlink can be set for an attribute by setting a separate attribute named <attributename>.hyperlink.
For more information about hyperlinks in this format, see Usage Notes.
A comment can be set for an attribute by setting a separate attribute named <attributename>.comment.
A formula for calculating the value of an attribute can be set in a separate attribute named <attributename>.formula.
The formula appears in the worksheet, in the cell in which the attribute is written.
Note that in order for the formula to be applied to the cell, the writer must contain both attributename (which can be empty) and attributename.formula. For example:
An Excel file contains three columns, col1, col2, and col3. To fill col3 with a formula, you can create an attribute col3 (which can be empty) and an attribute col3.formula, which is filled with the formula. In the resulting Excel file, col3 will contain the correct formula.
Example values are:
=SUM(C2:C18)
SUM(C2:C18)
AVERAGE($A$1:$A$A5)
Note that these are not FME expressions (so you would not use the Arithmetic Editor) but you can use concatenation and FME attributes to build the formula. For example:
SUM(E@Value(_rowID):P@Value(_rowID))
would return the formula
SUM(E3:P3) when _rowID = 3
- Per-cell formatting can be set for an attribute by setting a separate attribute named <attributename>.formatting and setting the value with the same name-value pairs used in per-feature formatting.
- Per-feature (row) formatting is stored in the xlsx_row_formatting attribute per feature. The attribute value is a list of name-value pairs, separated by semicolons. For example: version;1;horizontal_alignment;right;row_height;37;background_color;0,0,1;font;Times New Roman,BOLD,UNDERLINE.
- Header row formatting can be set by enabling the Field Names Row Format parameter and setting the style parameters.
Note that both per-cell and per-feature formatting can be set using the ExcelStyler transformer. Per-column formatting can be set by clicking the Edit button under the Writer User Attributes tab (Formatting column), and setting the desired options. This does not apply to header row formatting.
Note
Formatting is applied with the order of precedence: template < column < row < cell. That is, cell formatting will overwrite row formatting, column formatting, and any formatting set on the template sheet. Row formatting will override formatting set by the column and formatting set on the template sheet. Column formatting will only override formatting set on the template sheet.
For example, in the image below, the background color is used to exhibit the order of precedence for formatting, the yellow background of the cell is overriding the blue background of the row (cell C3), which in turn is overriding the green background of the first column (cells A3,B3), which is overriding the purple green background of the template (cells A3,B3).
Similarly, the text color or font_color is used to exhibit how default values are used when higher precedence formatting is not specified. In the image below, the font_color on the row is not specified, so the default color from the template is used, as well as the font_color set in the second column (white in cell A3 and red in cell B3). The font_color of the cell is set, so it overwrites the column’s font_color (blue in cell C3).
Argument |
Description |
Possible Values |
---|---|---|
version | Version of the format string to be used | 1 |
number_format_string |
How numbers should be formatted |
A custom number format that obeys Excel’s rules for formatting custom number formats. If a number_format_string is used, the characters in the number format string must be escaped. |
font_color |
Font Color |
An RGB value in the form of a CSV string triplet where Red, Green and Blue are floating point numbers between 0 and 1, where 0 is fully transparent and 1 is fully opaque for each color channel. For example, 1,0,0 is red, 0,1,1 is turquoise. |
font |
The font and font style to use |
A string of the form: FONT_NAME,OPTIONS where:
Example: Times New Roman,BOLD,UNDERLINE,12 |
pattern_color |
Cell foreground Color |
An RGB value in the form of a CSV string triplet where Red, Green and Blue are floating point numbers between 0 and 1, where 0 is fully transparent and 1 is fully opaque for each color channel. Example: 1,0,0 is red; 0,1,1 is turquoise |
background_color |
Cell background color |
An RGB value in the form of a CSV string triplet where Red, Green and Blue are floating point numbers between 0 and 1, where 0 is fully transparent and 1 is fully opaque for each color channel. Example: 1,0,0 is red; 0,1,1 is turquoise |
pattern_style |
Cell fill pattern |
One of:
|
horizontal_alignment |
How text is aligned horizontally in the cell |
One of:
|
vertical_alignment
|
How text is aligned vertically in the cell |
One of:
|
indent |
How far to indent text in the cell |
A number between 0 and 15: Example: 7.8 |
text_orientation |
How the text should be oriented in the cell |
An angle in degrees from -90 to 90 |
text_control |
How text should be coerced to fit in the cell |
One of:
|
hide_cells |
Whether or not to hide the cells |
One of:
|
cell_border_color |
Cell border color |
An RGB value in the form of a CSV string triplet where Red, Green and Blue are floating point numbers between 0 and 1, where 0 is fully transparent and 1 is fully opaque for each color channel. Example: 1,0,0 is red; 0,1,1 is turquoise |
cell_border_style |
Cell border style |
One of:
|
row_height |
Height of the row |
A number between 0 and 409.50, which can only be set using per-feature (row) formatting. |
The Excel Writer accepts only English-formatted formulas. This is because formulas in the underlying XML files are stored in English.
Examples
-
If your Excel application is set to the Spanish locale, Excel will automatically translate the =SUM() formula to the Spanish equivalent of the word SUM, which is =SUMA().
-
If you input =SUMA() in FME for the attribute.formula value, the formula in the written XLSX file will display incorrectly as =@SUMA() when viewed in Excel. Even if you are using a Spanish locale of Excel, you will need to input the formula as =SUM() in FME.
Writer Parameters
- No (default) – Features will be written to the original file.
- Yes – Delete an existing file before writing out any features.
If you specify a template file, the writer will make a copy of the template file in the location specified in the Destination Microsoft Excel File parameter. All operations will be performed on the copy: the template file specified remains unchanged.
Template Sheet
When you specify a Template File, you can select one of the worksheets as the default template worksheet for each feature type. Click the browse button to view the list.
Named ranges that exist on the template sheet are not duplicated.
The writer feature type option to Drop Existing Sheet/Named Range is irrelevant if specifying a template sheet, since the existing sheet will always be replaced by the template sheet/named ranges. 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. To access writer feature type parameters, open the Feature Type dialog.
Remove Template Sheet if Unchanged
If the template sheet is not being modified, setting this parameter to Yes will remove the sheet from the output file. This sets the default for each feature type.
Default: No
Sheet/Named Range Parameters
One writer can have multiple worksheets and/or named ranges created in a new or existing spreadsheet, including multiple named ranges on a single sheet.
Drop Existing Sheets/Named Ranges
- 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.
Truncate Existing Sheets/Named Ranges
If a template file or template sheet are specified as writer options here, the option to truncate existing worksheets or named ranges on feature types 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 specified offset.
Output Field Names
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 worksheets or named ranges.
Field Names Row Format
This parameter sets the format of the field names or header row.
When this option is checked, the different format options are enabled. The formatting is set only when writing the header row (which is done once per sheet).
Default Feature Type Writer Mode
The writer mode can be specified at two unique levels: on the feature type, or on individual features.
At the writer level, the writer mode specified by this parameter is inherited as the starting writing mode for all of its feature types. This determines how each feature received is treated by default. Valid writer modes are listed below:
Mode | Description |
---|---|
Insert |
Appends features to the end of the sheet or writes them to an exact row and column position. Any data that already exists at that location will be overwritten. If a value on the feature is specified for the Row Number column, that row will be used instead. Note The Insert feature type writer mode will ignore any feature-level writer modes using fme_db_operation. If per-feature writer modes are desired, consider using Update at the feature type level.
|
Update |
Causes the row corresponding to the feature’s value specified in the Row Number Attribute to have its data changed to the values on the incoming feature. If an attribute is missing while in this mode, the existing cell value will not be overwritten. Note In FME versions before 2021.1, the Use Attribute Names as Column Positions option must be enabled for this to behave as expected.
|
Delete |
Deletes the values in the row corresponding to the feature’s value specified in the Row Number Attribute. Only a number of columns equal to the amount of columns in the schema will have their data and formatting removed. |
Check this option to protect worksheets in an Excel file. (It is not yet possible to encrypt the entire spreadsheet.)
Password
You can protect the worksheet with or without a password.
Protection Level
- Select-Only Permissions – Users can only select/copy from a cell.
- View-Only Permissions – Users can only view cell contents.
- Specific Permissions – Select this option to enable the Specific Permissions parameter, then click the browse button to open the list of standard Excel permissions. These selections allow all users to perform only the selected actions within the sheet/cell.
See the Microsoft Excel: Writer Feature Type Parameters for more details on sheet protection.