Microsoft OOXML Excel 2007+
MapForce can read data from and write data to Microsoft Excel 2007+ workbooks, in the default Office Open XML (OOXML) format. This format was first introduced in Microsoft Office 2007 and, in case of Excel workbooks, is associated with the default .xlsx extension. In the MapForce interface, and in this documentation, Microsoft Excel 2007 and later files are generically referred to as "Excel 2007+" files.
Microsoft Office 2013 added support for Strict Open XML Spreadsheet format (ISO/IEC 29500 Strict). The Strict Open XML Spreadsheet format also has the .xlsx extension; however, technically this is a distinct format which adheres to stricter validation rules.
The following table illustrates how reading and writing data from/to Excel 2007+ workbooks is supported across MapForce transformation languages. Notice the differences between Office Open XML and Strict Open XML Spreadsheet formats.
Microsoft Excel Format | MapForce Language | Reading Support | Writing Support |
Office Open XML | BUILT-IN | Yes | Yes |
C# | Yes | Yes | |
Java | Yes | Yes | |
XSLT 2.0, XSLT 3.0 | Yes | No | |
Strict Open XML Spreadsheet | BUILT-IN | Yes | No |
C# | Yes | No | |
Java | Yes | No | |
XSLT 2.0. XSLT 3.0 | Yes | No |
If you need to convert any Office Open XML files generated by MapForce to Strict Open XML Spreadsheet format, open the workbook in Excel 2013, and then save it as Strict Open XML Spreadsheet.
Saving to Strict Open XML Spreadsheet format
As mapping components in MapForce, Excel 2007+ files have the following general behavior:
•You can map data from Excel 2007+ to any component supported in MapForce, and vice versa, including XBRL taxonomy files (see Excel to XBRL example).
•If Microsoft Excel 2007 or later is installed on your computer, you can preview the transformation output immediately in the Output pane of the mapping window, and you can save it to a file. If you don't have Excel 2007 or later, you can still map to or from Excel 2007+ files. In this case, you cannot preview the result in the Output pane, but you can still save it, by clicking Save Output File on the Output menu.
If you are using multiple high-DPI monitors, and if they have different DPI-awareness levels, it is not possible to preview the Excel 2007+ output directly in MapForce. Instead, a message similar to the following is displayed: "Embedding of Excel is not possible with the current system configuration". |
•You can choose from the component settings whether to create new Excel files from a mapping, or update existing Excel files.
•If you choose to create new Excel files, the generated Excel 2007+ output does not contain any formatting, only data.
•If you choose to update existing Excel files, the formatting information of your existing Excel file is preserved as it is. Note that updating cells that contain formulas is not supported. Instead, you can configure from the component settings to stop the mapping execution with an error when such cells are encountered, or ignore them (while still displaying warnings in the Messages window).
•As an alternative to generating and saving the output manually, you can compile the mapping design to a MapForce Server execution file, or deploy it to a FlowForce Server, and execute it as and when required through FlowForce Server jobs. For further information, see Compiling a MapForce mapping and Deploying a MapForce mapping, respectively.
•If supported by the mapping components, you can generate program code from a mapping (see the supported languages above) and execute it externally from your custom application. For more information, see Code Generator. Note that the Update existing Excel files feature is supported only when the transformation language is BUILT-IN.