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 |
Production Note: The aliases for this format are divided into the reader and writer: xlsxr and xlsxw. See also PR 53850.
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
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.
This results in the formula appearing in the worksheet in the cell the attribute is written and is used to calculate the cell value.
Example values are:
=SUM(C2:C18)
SUM(C2:C18)
AVERAGE($A$1:$A$A5)
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
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.
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.
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.
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.
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.
The tables must be defined in the workspace before they can be read. In Microsoft Excel, tables can be either worksheets or named ranges. FME always signifies named ranges using the notation <sheetName>/<namedRangeName>, followed by the names and types of the attributes.
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.