Table of Contents

Search

  1. Preface
  2. Introduction to Data Transformation
  3. Data Processor Transformation
  4. Wizard Input and Output Formats
  5. Relational Input and Output
  6. XMap
  7. Libraries
  8. Schema Object
  9. Command Line Interface
  10. Scripts
  11. Parsers
  12. Script Ports
  13. Document Processors
  14. Formats
  15. Data Holders
  16. Anchors
  17. Transformers
  18. Actions
  19. Serializers
  20. Mappers
  21. Locators, Keys, and Indexing
  22. Streamers
  23. Validators, Notifications, and Failure Handling
  24. Validation Rules
  25. Custom Script Components

Data Transformation User Guide

Data Transformation User Guide

XmlToXlsx

XmlToXlsx

The
XmlToXlsx
document processor converts XML documents to Microsoft Excel .xlsx format. The
XmlToXlsx
document processor can optionally use an .xlsx template to generate the .xlsx document.
The processor operates on an XML representation of an Excel workbook. The XML representation must be in the UTF-8 encoding and it must conform to the
ExcelToXml_03_07_10.xsd
schema. You can find the schema in the
doc
subdirectory of the installation directory. The schema file is provided for your information.
The processor reverses the operation of
ExcelToXml_03_07_10
. Use the
ExcelToXml_03_07_10
processor in a Data Processor transformation to transform an Excel workbook to XML. After you process the XML data, use the
XmlToXlsx
processor to transform the data back to an Excel workbook.
This component supports Excel version 2007 and higher. It writes its output directly, not through Microsoft Excel. You do not need to install Excel on the computer.
This component is implemented in Java and requires correct configuration of the Java Runtime Environment (JRE).
The following table describes the properties of the
XmlToXlsx
document processor:
Property
Description
param1
A variable that holds the name of the
*.xlsx
template file. The
param1
property has the property
template_file
that specifies the template file name.
param2
A variable that holds the location of the
*.xlsx
template file. The
param2
property has the property
template_location
that specifies the template file path.
Excel sheets that exist solely in the template are represented as in the template in the .xlsx output. Excel sheets that are defined in both the template and the XML receive cell styles from the template and cell values from the XML.
To apply a cell style from a different cell in the template, you can use the attribute
style_as
in the XML as part of the
cell
element.

Example

To use the
style_as
attribute to apply a cell style from a cell in the current sheet, set the
style_as
attribute equal to the cell number that contains the style. In the following example, the style from field
A1
in the current sheet applies to the cell defined by the XML context, namely row 3 and cell 2.
<row rownumber="3" firstcol="1" lastcol="12" height="405" zeroheight="false" > <cell number="2" type="string" style_index="3" font_index="3"
style_as="A1"
> <data>Informatica</data> </cell> </row>
To apply a cell style from a cell in a different sheet, set the
style_as
attribute equal to the sheet and cell number that contains the style. In the following example, the style is applied from the field
A1
in the workbook sheet named
Summary
.
<row rownumber="3" firstcol="1" lastcol="12" height="405" zeroheight="false" > <cell number="2" type="string" style_index="3" font_index="3"
style_as="Summary:A1"
> <data>Informatica</data> </cell> </row>