Log out?

Excel Transformation Module

The Excel transformation module is a bi-directional module that can convert Microsoft Excel files to XML and also convert XML files to Microsoft Excel. For inbound Excel data streams the module will assume the first row of the Excel sheet is contains the column headers and create an XML stream of the following format:

<?xml version="1.0" encoding="UTF-8"?>
<XCSExcelBook sheetCount="1">
	<XCSExcelSheet name="Sheet1" rowCount="2">
	<Columns count="2">
		<Column index="1" type="String">ColumnHeader1</Column>
		<Column index="2" type="String">ColumnHeader2</Column>
	</Columns>
	<XCSExcelRow index="1">
		<ColumnHeader1 index="1">Cell data C1</ColumnHeader1>
		<ColumnHeader2 index="2">Cell data C2</ColumnHeader2>
	</XCSExcelRow>
	</XCSExcelSheet>
</XCSExcelBook>

assuming the Excel document has the following format:

ColumnHeader1 ColumnHeader2
Cell data C1 Cell data C2

To convert XML data to Excel, the Excel Transformation Module can accept XML in the following format:

<XCSExcelBook sheetCount="1">
	<XCSExcelSheet name="Sheet1" rowCount="2">
	<XCSExcelRow index="1">
		<First index="1">Sam</First>
		<Last index="2">Thomas</Last>
		<BirthDate index="3">6/12/1964</BirthDate>
		<Age index="4">42</Age>
	</XCSExcelRow>
	</XCSExcelSheet>
</XCSExcelBook>

and convert it to an Excel document of the following format:

First Last BirthDate Age
Sam Thomas 6/12/1964 42

Note: Valid type attributes are string, numeric, boolean and date.

Note: For a list of valid date values see the documentation for Java’s java.text.SimpleDateFormat class https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html.

Configuration Properties:

Property Name Type Description
Sheet Index Integer The index of the sheet that will be read from the inbound data stream. For outbound data streams this value must remain at 0.(Default 0)
Add Header Boolean Check this checkbox to include the XML tag names as the column headers. This is only used on XML to Excel conversion.
Row Element Tag Name String If the first child of the root node does not have an element name of XCSExcelRow, you can specify the correct element name here. This is only used on XML to Excel conversion.
Error On Invalid DataType Boolean Check this checkbox to throw an exception when unknown cell types are included in the inbound Excel document. Supported cell types are String, Date and Numeric only.

    to post comments

    This is a unique website which will require a more modern browser to work! Please upgrade today!

    Thanks!

    Our editors are notified.

    Close