XML to Excel component

The XML to Excel component allows you to convert XML files into Excel documents.

Basic configuration

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

Include header

Specify whether to include a header row in the Excel output.

Options

• Yes (default)
• No

XML Input

<?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 Output - Yes

"name","price","description"
"food1","1","desc1"
"food2","2","desc2"
"food3","3","desc3"

Excel Output - No

"food1","1","desc1"
"food2","2","desc2"
"food3","3","desc3"

Include index column

Specify whether to include an index column in the Excel output.

Options

• Yes
• No (default)

using index

When enabled, the index column will always be the first column and line numbers will be unquoted.

XML Input

<?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 Output - No

"name","price","description"
"food1","1","desc1"
"food2","2","desc2"
"food3","3","desc3"

Excel Output - Yes

"line","name","price","description"
1,"food1","1","desc1"
2,"food2","2","desc2"
3,"food3","3","desc3"

Index column name

Specify the name of the index column. The default value is line.

note

Only available when Include index column is enabled.

Sort columns

Specify how the columns should be ordered in the Excel document (by name).

Options

• Original (default)
• Ascending
• Descending

XML Input

<?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 Output - Original

"name","price","description"
"food1","1","desc1"
"food2","2","desc2"
"food3","3","desc3"

Excel Output - Ascending

"description","name","price"
"desc1","food1","1"
"desc2","food2","2"
"desc3","food3","3"

Excel Output - Descending

"price","name","description"
"1","food1","desc1"
"2","food2","desc2"
"3","food3","desc3"

Excel format

Specify the format of the Excel output.

Options

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

Advanced configuration

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

Use custom worksheets

Specify whether to enable custom worksheets.

Options

• Yes
• No (default)

Custom worksheets

Define custom worksheets by specifying the following options for each sheet:

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

note

Only available when Use custom worksheets is enabled.

Using Expresssions

Each expression type has a dedicated page on the Academy. You can find them under Guides > References:

• Simple
• XPath
• JsonPath
• Groovy (external reference)

Using XML to Excel

The XML should follow this structure:

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

processing depth

Processing is supported up to the subSubElement level.

<?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 -->

Key considerations

• Only one type of element will be processed per worksheet, the first one that is encountered.
• Header names are based on the unique subElements and subSubElements.
  • SubSubElement names include their parent element, formatted as subElement_subSubElement.
• Multiple subElements or subSubElements with the same name inside the same parent are ignored.

modify XML

Use the XSLT component or manually modify the XML to conform to the desired structure if:

• Attributes need to be processed.
• Non-unique subElements or subSubElements within the same element need to be processed.

Last update on Feb 25, 2025