How to use the Excel Transformation Module in PilotFish

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.

Structured Data