Skip to Content

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

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.


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

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


Input /wp-content/uploads/2014/11/in1_576442.png
Output /wp-content/uploads/2014/11/out1_576473.png

Scenario 2

Excel binary XLS file format.

Active sheet name provided.

Header line added with details from input XML fields.

Escape sequence &amp; automatically converted to special character &.

Module parameters

Parameter Name Parameter Value
excelFormat xls
sheetName MySheet1
addHeaderLine fromXML


Input /wp-content/uploads/2014/11/in2_576495.png
Output /wp-content/uploads/2014/11/out2_576496.png

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
addHeaderLine fromConfiguration
fieldNames Field1,Date,Field2,Quantity


Input /wp-content/uploads/2014/11/in3_576475.png
Output /wp-content/uploads/2014/11/out3_576480.png
You must be Logged on to comment or reply to a post.
  • Hi Eng,

    Its nice blog, I think, I may need to use it in one of my requirements.

    I am planning to practice this blog for my future requirement.

    I do not have much idea on Java, new to it.

    I have installed NWDS and collected the required jar files for Adapter Module Development.

    My SAP PI System version is 7.1, can you please mentor me on building it manually and compile it for SAP PI 7.1.


  • Hi Eng,

    I always love your blog, something new everytime. Thank you.

    I need your help in 1 of my scenario.

    It is outbound soap sync to inbound proxy sync.

    The xml data I receive through proxy will be mapped to target binary field(Base64Binary field)

    I’m trying to apply the 2nd scenario & wondering even though I use this payload bean how shall I map the binary output to the base 64

    basically its like

    Proxy xml data -> SOAP xml data + Binary(Base64)

    I need to populate the data per request in real time either xml data is requested or excel file is requested.

    This excel file I’m attaching as soap response.

    I hope it’s not confusing.

    COuld you please help me with this scenario.

    Basically soap is requesting Document data with document reference number-> PI forwards the data to ECC via proxy-> From internal tables proxy is retrieving the data & sending as response to proxy. PI is mapping this data to SOAP response.

    I’m planning to add soapsync -> proxy sync /async with File & use this module in file channel.

    Please advice.

  • Hello Eng Swee,

    This is one of most required and amazing blog I have read on scn.

    First of all thank you so much for putting in such great efforts to help others!!

    Secondly, I need your guidance in one of my scenario wherein I have to convert the xml format to Excel format.

    My requirement details are in following link:

  • Hello Eng Swee,

    Could you please guide me on how to proceed on developing an adapter module for converting xml to xsl?


    Indu Khurana.

  • Hi Eng,

    I have a ASYNC to SYNC scenario. Where the SOAP Synchronous response has an excel file(binary) in its body. I want to dump this excel file into an FTP. How do i dynamically extract the excel and push it as a file using my FTP adaptor ?  Key requirement is, this interface should handle any excel file and should not be specific to one excel sheet and it columns.