Licensing options for this format begin with FME Professional Edition.
FME provides read and write access to Microsoft Excel spreadsheets (also known as workbooks). Excel spreadsheets may contain multiple worksheets and each worksheet may optionally contain specifically named cell ranges called Named Ranges.
| Format Abbreviations |
|---|
|
Reader: XLSXR |
|
Writer: XLSXW |
Versions
This format supports:
- Excel 97-2003 binary formats (.xls)
- Excel 2007-2013 xml formats (.xlsx/.xlsm). .xlsm files are .xlsx files with macros enabled, and use the same file format.
The Excel reader/writer does not support Binary Workbooks (.xlsb), Macro-enabled Template (.xltm), or Excel Add-in (.xlam).
This reader/writer can only process a single file type per reader or writer.
Differences Between XLS and XLSX
Starting in Office 2007, Microsoft changed the default Excel file format from .xls to .xlsx.
While .xls is a proprietary binary format, the newer .xlsx version is an XML-based spreadsheet, defined in the Office Open XML (OOXML) specification.
How FME Works with Excel Data
To get the most value from an Excel spreadsheet, you also have to organize, analyze, and filter its data. Large datasets can easily exceed the limits of the Excel format: FME can read and analyze large amounts of data, filter it, organize it into sheets, and then output the results into a manageable spreadsheet. FME can also integrate spreadsheets with data from other formats (for example, demographic databases, GIS, CAD drawings, web services, and orthophotos).
You can use FME to perform simple operations, like merging or filtering spreadsheet data, but you can also use FME to perform more complex tasks like spatial operations. For example, if your data contains addresses, coordinate pairs, street names or asset codes, FME recognizes common names for geometry columns (like latitude/longitude), and then automatically converts the rows to geometry. By integrating geocoding services like ArGIS Online or Google, you can map Excel data and then view it in the FME Data Inspector.
For more information on using FME to automate Excel tasks, see FME and Excel.
Terminology in this Chapter
| Term | Definition or FME Representation |
|---|---|
| spreadsheet and workbook | spreadsheet and workbook |
| worksheet | worksheet or named range |
| column in a worksheet or named range | attribute |
| worksheet or named range | feature type |
| row | feature |
Usage Notes
Attribute Names and Column Values
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
Formatting (font, background colors, etc.) are supported on the writer only, and so will not be preserved from the reader to the writer.
Specifying formatting explicitly per column will be merged with formatting in a template sheet or existing sheet; otherwise, formatting within existing sheets or templates will be preserved if column formatting is not specified. Merging of formatting for a column 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.
Formulas
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.
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
Hyperlinks
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.
Example values are:
http://www.safe.com
file://c:/documents/readme.txt
Named Ranges
Tables must be defined in the workspace before they can be read. In Microsoft Excel, tables can be either worksheets or named ranges.
FME identifies named ranges using the format <sheetName>/<namedRangeName>, followed by the names and types of the attributes.
Pivot Tables, Charts, Graphs
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 sheets.
Template File
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.
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 sheets after being copied to the destination file.
x, y, z coordinates
This data may not necessarily have a spatial component to it, but columns can be identified as x, y, or z coordinates to create point geometries.
FME automatically recognizes some common attribute names as potential x,y,z coordinates and sets their types. See Reader Parameters for additional information.
About the Reader (XLSXR)
The XLSX reader supports reading multiple worksheets and named ranges from the same file. Because of this, multiple files are not supported, so you will need to create a separate reader for each Microsoft Excel spreadsheet.
About the Writer (XLSXW)
The Microsoft Excel writer writes attribute records into a spreadsheet. The writer provides the following capabilities:
- Table Creation
- Images
- Multiple Worksheets and/or Named Ranges
- Writer Mode Specification: The writer allows the user to specify a writer mode, which determines the operation to perform for each feature received.