Microsoft Excel Reader (XLSXR) and Writer (XLSXW)

The reader will match attribute names to column values on the header row, to allow for reordering of the schema in FME or reordering of the columns in the source files. However, renaming a column in the file (but not changing the workspace attributes) will cause that column to be ignored.

Formatting or style information consists of font, text color, background colors, numeric formatting, fill patterns, border styles, etc. and can be set in the Excel application at either the column, row, or individual cell level.

Formatting is supported by both the Excel reader and writer. Row and cell formatting is read and written, except for formatting from the header row, and will be preserved from the reader to the writer. Additionally, the Excel writer can write column formatting and separate header row formatting.

Note that both row and cell formatting in Excel files is read by the Excel reader as cell formatting.

• Per-cell formatting is stored per feature as a separate attribute named <attributename>.formatting and setting the attribute 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.
• 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.

Specifying formatting explicitly per column, row, or cell will be merged with formatting in a template sheet or existing sheet; otherwise, formatting within existing sheets or templates will be preserved if new formatting is not specified. Merging of formatting for a column, row, or cell is specific to each format setting. For example, setting Background Color for a cell does not override or invalidate existing formatting for Text Alignment or Custom Number formatting.

Note that the column type specified may also affect formatting. Specifically Date, Time, and DateTime column types will enforce some custom number formatting to structure the date, and .hyperlink fields will set the color and underline values in the font format.

This format supports formulas.

The reader can optionally read in formulas used to calculate the values of a cell and store the formula in <attributename>.formula.

For the writer, 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

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.

This format supports embedded hyperlinks. The reader can optionally read in embedded hyperlinks in a worksheet and store them in <attributename>.hyperlink.

For the writer, a hyperlink can be set for an attribute by setting a separate attribute named <attributename>.hyperlink:

In the output Excel spreadsheet, clicking the cell in which the attribute is written will open the hyperlinked URL. The value of the cell does not need to be same as the hyperlink. Note that if the attribute name is uppercase (for example, attribute.HYPERLINK), this functionality will not work.

Example values are:

https://www.safe.com

file://c:/documents/readme.txt

FME identifies named ranges and tables using the format <sheetName>/<namedRangeName>, followed by the names and types of the attributes.

FME currently does not support creating new Pivot tables, charts, or graphs, but can add data to them if they exist in template files or worksheets.

The writer can set protection levels for worksheets only. Encryption of the spreadsheet is not yet possible. The reader can read Excel files even if they have sheet (password) protection, but cannot read encrypted spreadsheets.

The Microsoft Excel writer allows the specification of a template file to use as a starting point for creating a new dataset. If a template file name is specified, the writer will make a copy of the template file in the location specified in the Dataset parameter. All write operations will be performed on the copy. The template file specified remains unchanged. If a template sheet is selected, in addition to the template file being copied, all new sheets being written will be written on a copy of the selected template sheet.

This is particularly useful when populating an area of the template with data such as an existing chart, graph, or pivot table or when creating many spreadsheets from a single template. If the destination dataset already exists, and a template file is specified, the destination dataset will always be overwritten, regardless of the specified choice in the Overwrite File: option. Likewise, the writer feature type option to Drop Existing Sheet/Named Range is irrelevant if a template file or template sheet is specified, as the existing sheet will always be replaced by the template sheet. However, the writer feature type option to Truncate Existing Sheet/Named Range is applied to template worksheets after being copied to the destination file.

FME automatically recognizes some common attribute names as potential x,y,z coordinates and sets their types. See Reader Parameters for additional information.

This data may not necessarily have a spatial component, but columns can be identified as x, y, or z coordinates to create point geometries. If a schema scan is performed and field labels contain variations of x/y, east/north, or easting/northing, FME will create the point geometry.

FME also detects latitude and longitude column names (for example, Latitude/Longitude or Lat/Long), and sets the source coordinate system to LL-WGS84.