Skip to main content
Version: 4.15.1

XML to Excel component

The XML to Excel component provides a direct conversion from the XML format to an Excel document.

Basic configuration​

The XML to Excel component has the following basic configuration options:

Include header​

Options

  • yes (default)
  • no

Description

This option allows you to determine whether to include a header in the Excel output.

Example

Notice the difference in the first line in the Excel. When yes is selected, a header line is added. When no is selected, no header line is added. Furthermore, note that the Excel output has the foods worksheet opened.

<?xml version="1.0" encoding="UTF-8"?>
<breakfast_menu>
    <food>
        <name>food1</name>
        <price>1</price>
        <description>desc1</description>
    </food>
    <food>
        <name>food2</name>
        <price>2</price>
        <description>desc2</description>
    </food>
    <food>
        <name>food3</name>
        <price>3</price>
        <description>desc3</description>
    </food>
</breakfast_menu>

Include index column​

Options

  • yes
  • no (default)

Description

This option allows you to determine whether to include an index column in the Excel output.

Remark

When this option is set to yes, the index column will always be the first column, and the line numbers will always be without quotes.

Example

Notice the difference in the first column in the Excel. When no is selected, no index column is added. When yes is selected, an index column is added. Furthermore, note that the Excel output has the foods worksheet opened.

<?xml version="1.0" encoding="UTF-8"?>
<breakfast_menu>
    <food>
        <name>food1</name>
        <price>1</price>
        <description>desc1</description>
    </food>
    <food>
        <name>food2</name>
        <price>2</price>
        <description>desc2</description>
    </food>
    <food>
        <name>food3</name>
        <price>3</price>
        <description>desc3</description>
    </food>
</breakfast_menu>

Index column name​

Prerequesites

To enable this option, the Include index column must be set to yes.

Description

This option allows you to specify the name of the index column that is to be added in the Excel output. The default value for this option is line.

Sort columns​

Options

  • original (default)
  • ascending
  • descending

Description

This option allows you to determine how the columns must be sorted in the Excel output.

Example

Note that the Excel output has the foods worksheet opened.

<?xml version="1.0" encoding="UTF-8"?>
<breakfast_menu>
    <food>
        <name>food1</name>
        <price>1</price>
        <description>desc1</description>
    </food>
    <food>
        <name>food2</name>
        <price>2</price>
        <description>desc2</description>
    </food>
    <food>
        <name>food3</name>
        <price>3</price>
        <description>desc3</description>
    </food>
</breakfast_menu>

Excel format​

Options

  • XLSX (Excel 2007+) (default)
  • XLS (Excel 97-2007)

Description

This option allows you to determine the Excel format of the output.

Advanced configuration​

The XML to Excel component has the following advanced configuration options:

Use custom worksheets​

Options

  • yes
  • no (default)

Description

This option allows you to determine whether to use the custom worksheets option.

Custom worksheets​

Prerequesites

To enable this option, the Use custom worksheets must be set to yes.

Description

This option allows you to determine custom worksheets by providing the following options for every row:

  • Worksheet (name)
  • XPath expression (determines the worksheet content)

Remarks​

The XML should conform to the following XML structure: (note that the processing is supported up to the subSubElement level)


<root>
    <worksheet>
        <element>
            <subElement>
                <subSubElement></subSubElement>
            </subElement>
        </element>
    </worksheet>
</root>

Example


<?xml version="1.0" encoding="UTF-8"?>      <!-- structure tags: -->
<root>                                      <!-- root -->
    <items>                                 <!-- worksheet -->
        <item>                              <!-- element -->
            <firstName>Joe</firstName>      <!-- subElement -->
            <lastName>Foo</lastName>        <!-- subElement -->
            <age>21</age>                   <!-- subElement -->
            <description>                   <!-- subElement -->
                <title>Title</title>        <!-- subSubElement -->
                <body>Body</body>           <!-- subSubElement -->
            </description>                  <!-- subElement -->
        </item>                             <!-- element -->
        <item>                              <!-- element -->
            <firstName>John</firstName>     <!-- subElement -->
            <lastName>Doe</lastName>        <!-- subElement -->
            <age>30</age>                   <!-- subElement -->
            <description>                   <!-- subElement -->
                <title>Title</title>        <!-- subSubElement -->
                <body>Body</body>           <!-- subSubElement -->
            </description>                  <!-- subElement -->
        </item>                             <!-- element -->
    </items>                                <!-- worksheet -->
</root>                                     <!-- root -->

Furthermore, it is important to be aware of the following remarks:

  • Only one type of element will be processed per worksheet (the first one that is encountered).
  • Header names are chosen based on the unique subElements and subSubElements. The name of the subSubElements will contain the subElement name at the beginning like so: subElement_subSubElement (separated by an underscore).
  • Multiple subElements or subSubElements with the same name inside the same element are ignored outside the first one. (Thus, the only exception is when the subElements and subSubElements with the same name exist on different levels.)
  • In the following cases the XML should be manually adjusted or the XSLT component should be used to make the XML usable by the XML to Excel component:
    • When attributes need to be processed.
    • When non-unique subElements or subSubElements inside the same element need to be processed.