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>
<foods>
<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>
</foods>
</breakfast_menu>
"name","price","description"
"food1","1","desc1"
"food2","2","desc2"
"food3","3","desc3"
"food1","1","desc1"
"food2","2","desc2"
"food3","3","desc3"
Include index column
Options
yesno(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>
<foods>
<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>
</foods>
</breakfast_menu>
"name","price","description"
"food1","1","desc1"
"food2","2","desc2"
"food3","3","desc3"
"line","name","price","description"
1,"food1","1","desc1"
2,"food2","2","desc2"
3,"food3","3","desc3"
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)ascendingdescending
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>
<foods>
<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>
</foods>
</breakfast_menu>
"name","price","description"
"food1","1","desc1"
"food2","2","desc2"
"food3","3","desc3"
"description","name","price"
"desc1","food1","1"
"desc2","food2","2"
"desc3","food3","3"
"price","name","description"
"1","food1","desc1"
"2","food2","desc2"
"3","food3","desc3"
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
yesno(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.