Microsoft Excel Reader (XLSXR) and Writer (XLSXW)
许可选项出于此格式,以FME Professional Edition开头。
FME提供对Microsoft Excel电子表格的读写访问(也称为工作簿)。Excel电子表格可能包含多个工作表,每个工作表都可以可选地包含名为Cell Ranges的特定命名Named Ranges。
Format Abbreviations |
---|
读者:XLSXR. |
作者:XLSXW |
版本
此格式支持:
- Excel 97-2003 binary formats (.xls)
- Excel 2007-2013 XML格式(.xlsx / .xlsm)。.xlsm文件与启用宏的.xlsx文件,并使用相同的文件格式。
The Excel reader/writer does not support Binary Workbooks (.xlsb), Macro-enabled Template (.xltm), or Excel Add-in (.xlam).
此读取器/写入器只能处理每个读取器或编写器的单个文件类型。
Differences Between XLS and XLSX
Starting in Office 2007, Microsoft changed the default Excel file format from.xls.到.xlsx.。
While.xls.is a proprietary binary format, the newer.xlsx.version is an XML-based spreadsheet, defined in the办公室打开XML(OOXML)规范。
FME如何与Excel数据一起使用
要从Excel电子表格获取最大值,您还必须组织,分析和过滤其数据。大型数据集可以容易地超出Excel格式的限制:FME可以读取和分析大量数据,过滤它,将其组织成纸张,然后将结果输出到可管理的电子表格中。FME还可以将电子表格与来自其他格式的数据集成(例如,人口统计数据库,GIS,CAD图纸,Web服务和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.
有关使用FME自动化Excel任务的详细信息,请参阅FME and Excel。
本章中的术语
Term | Definition or FME Representation |
---|---|
spreadsheet and workbook | spreadsheet and workbook |
worksheet | worksheet or named range |
column in a worksheet or named range | 属性 |
worksheet or named range | 特征type |
row | 特征 |
使用说明
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.
格式(字体、背景颜色等)正在吃晚饭ported 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背景颜色对于单元格,不会覆盖或使文本对齐或自定义编号格式无效的现有格式。
请注意,指定的列类型也可能影响格式化。具体来说Date那Time那and约会时间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.
此格式支持公式。读卡器可以可选地读取用于计算单元格的值并将公式存储在内
对于Writer,可以在命名的单独属性中设置用于计算属性值的公式
Example values are:
= SUM(C2:C18)
总和(C2:C18)
平均($ 1 $ 1:$ A5)
请注意,这些不是FME表达式(因此您不使用算术编辑器),但您可以使用连接和FME属性来构建公式。例如:
SUM(e @值(_rowid):p值(_rowid))
would return the formula
总和(e3:p3)_rowid = 3时
此格式支持嵌入的超链接。读者可以选择在工作表中的嵌入式超链接中读取并将其存储在
For the writer, a hyperlink can be set for an attribute by setting a separate attribute named
Example values are:
http://www.亚搏在线safe.com.
file://c:/documents/readme.txt
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
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.
Microsoft Excel Writer允许将模板文件的规范用作创建新数据集的起点。如果指定了模板文件名,则编写器将在指定的位置中制作模板文件的副本数据集parameter. All write operations will be performed on the copy. The template file specified remains unchanged.
当使用诸如现有图表,图形或枢轴表的数据填充模板的区域或从单个模板创建许多电子表格时,这尤其有用。如果目标数据集已存在,并且指定了模板文件,则无论指定的选择如何,都将始终覆盖目标数据集覆盖文件:option. Likewise, the writer feature type option toDrop Existing Sheet/Named Range如果指定了模板文件或模板表,则无关,因为现有表将始终由模板表替换。但是,Writer功能类型选项Truncate Existing Sheet/Named Rangeis applied to template sheets after being copied to the destination file.
该数据可能不一定具有对其的空间组件,但是列可以被识别为x,y或z坐标以创建点几何形状。
FME自动将一些常见的属性名称识别为潜在x,y,z坐标并设置其类型。看读者参数for additional information.
关于读者(XLSXR)
XLSX读取器支持从同一文件中读取多个工作表并命名范围。因此,不支持多个文件,因此您需要为每个Microsoft Excel电子表格创建一个单独的阅读器。
About the Writer (XLSXW)
The Microsoft Excel writer writes attribute records into a spreadsheet. The writer provides the following capabilities:
- Table Creation
- 图片
- Multiple Worksheets and/or Named Ranges
- Writer模式规范:Writer允许用户指定写入器模式,该方法确定为接收的每个功能执行的操作。