ExcelTransformBean Part 2: Convert simple XML to various Excel formats easily

[engswee]

Update 5 Nov 2019: Add new parameter trim to trim XML text nodes

Update 2 Nov 2018: Default parsing in CPI will be using SAX-based XmlSlurper

Update 2 Oct 2018: Now available in CPI as well.

Update 25 Mar 2015: ExcelTransformBean has been refactored to be part of FormatConversionBean. Parameter conversionType replaced with converterClass.

Introduction

This is a continuation of the ExcelTransformBean adapter module. The first part ExcelTransformBean Part 1: Convert various Excel formats to simple XML easily covers Excel to simple XML conversion.

This second part focuses on simple XML to Excel conversion. Similar to the first part, this uses the combined SS interface of Apache POI's API, therefore it is able to write both XLS and XLSX files.

Source Code

Refer to following blog on location of source code and/or EAR deployment file.

FormatConversionBean - One Bean to rule them all!

Usage of Module in Communication Channel

Module Processing Sequence

Number	Module Name	Type	Module Key
<Depending on position of module in chain> 1) Asynchronous scenario, Normally before the last module in channels 2) Synchronous scenario, Before last module to convert request payload After last module to convert response payload	Custom_AF_Modules/FormatConversionBean	Local Enterprise Bean	<Any Arbitrary Value>

Module Parameter Reference

Below is a list of the parameters for configuration of the module for XML to Excel conversion. Certain parameters will automatically inherit the default values if it is not configured.

Note: All fields in the simple XML structure must exists. If any field does not exist, this will cause the alignment of the columns to be incorrect. This is similar to the behavior of FCC or MTB's SimpleXML2Plain conversion. To ensure proper column alignments, use MapWithDefault when mapping the fields of the target structure.

Parameter Name	Allowed values	Default value	Remarks
converterClass	PI - com.equalize.xpi.af.modules.excel.XML2ExcelTransformer CPI - com.equalize.converter.core.XML2ExcelConverter		Required field. Determines conversion class
excelFormat	xls, xlsx	xlsx	Determines output file format
sheetName		Sheet1	Name of sheet in output Excel file
addHeaderLine	none, fromXML, fromConfiguration	none	Determines if there will be a header line in the output file, and how it should be populated: none = No header line fromXML = Column names are determined from XML field names fromConfiguration = Column names are determined from parameter fieldNames
fieldNames			Name of columns. Required field when processFieldNames = 'fromConfiguration'
useDOM	Y, N	N	Available only in CPI Parser used in parsing input XML N = SAX-based XmlSlurper Y = Document Object Model (DOM) based parser
trim	Y, N	N	Available only in CPI Trim whitespace in XML text nodes during parsing

Example Scenarios

Here are some example scenarios of the behavior of the conversion based on different configuration options.

Scenario 1

Default values for all optional parameters.

Excel 2007 XSLX file format with default Sheet1.

No header line.

Module parameters

Parameter Name	Parameter Value
converterClass	com.equalize.xpi.af.modules.excel.XML2ExcelTransformer

Result

Input
Output

Scenario 2

Excel binary XLS file format.

Active sheet name provided.

Header line added with details from input XML fields.

Escape sequence & automatically converted to special character &.

Module parameters

Parameter Name	Parameter Value
converterClass	com.equalize.xpi.af.modules.excel.XML2ExcelTransformer
excelFormat	xls
sheetName	MySheet1
addHeaderLine	fromXML

Result

Input
Output

Scenario 3

Excel 2007 XSLX file format with default Sheet1.

Header line added. Column names are provided from configuration.

Certain fields are populated with blank values.

Module parameters

Parameter Name	Parameter Value
converterClass	com.equalize.xpi.af.modules.excel.XML2ExcelTransformer
addHeaderLine	fromConfiguration
fieldNames	Field1,Date,Field2,Quantity

Result

Input
Output