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. Using the IntelliScript Editor
  7. XMap
  8. Libraries
  9. Schema Object
  10. Command Line Interface
  11. Scripts
  12. Parsers
  13. Script Ports
  14. Document Processors
  15. Formats
  16. Data Holders
  17. Anchors
  18. Transformers
  19. Actions
  20. Serializers
  21. Mappers
  22. Locators, Keys, and Indexing
  23. Streamers
  24. Validators, Notifications, and Failure Handling
  25. Validation Rules
  26. Custom Script Components

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>


Updated June 14, 2019