Skip to Content
Technical Articles
Author's profile photo Eng Swee Yeoh

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

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.


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


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

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      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.


      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Hi Akhila

      Thank you for your interest in this module.

      There are plenty of resources on SCN on how to develop/build/compile the adapter module. You can find some of these in the Reference section of the following blog.

      Standalone testing of Adapter Module in NWDS

      Once you have created the EAR/EJB projects in NWDS, you can download the source code and reference libraries and add them to the project before building and deploying the module.

      I'll try to blog about the steps involved to use this in build and deploy this in 7.1x but it will take some time.


      Eng Swee

      Author's profile photo Former Member
      Former Member

      Thank you Eng.

      Will try as per suggestion.



      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Hi Akhila

      As requested, here is the guide.

      Recompile as EJB 2.1 modules in NWDS 7.1x


      Eng Swee

      Author's profile photo Former Member
      Former Member

      Hi Eng,

      Thanks a lot, it will help lot of consultants like me.



      Author's profile photo Harry Saj
      Harry Saj

      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.

      Author's profile photo Former Member
      Former Member

      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:

      Author's profile photo Ambrish Mishra
      Ambrish Mishra

      Hi Eng Swee,

      Nice blog and you seem to have covered various options associated with excel creation.



      Author's profile photo Former Member
      Former Member

      Hello Eng Swee,

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


      Indu Khurana.

      Author's profile photo Former Member
      Former Member

      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.


      Author's profile photo Tim Van Den Berghe
      Tim Van Den Berghe

      Hi Eng,

      Is it possible to transform numbers from XML into XLSX cells?
      Currently a field, e.g. <Quantity>10</Quantity>, will result in a TEXT-value in the output Excel.

      Thank you in advance,

      Kind regards, Tim.

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Hi Tim,


      Currently the logic in the module always creates the cells using String/Text type.

      Are you using this for PI or CPI?

      If PI, then I don't actively maintain the project anymore, so I'd suggest that you fork the repository and make the changes to suit your requirements.

      If CPI, then you can create an issue in the GitHub repo, and mark it as an enhancement request. I will have to see if this is something that can be whipped up quite easily or not.



      Eng Swee


      Author's profile photo Shreyashri Kar
      Shreyashri Kar

      Hello Eng Swee Yeoh,

      Thanks for such a great explanatory post.

      I have tried using the XmltoExcel conversion feature in CPI following both of your blogs.
      I have downloaded the required jar files from gitHub and uploaded into the CPI Iflow. I am using the same groovy script as shown here.
      It produces some sort of a distorted output although there is no error in CPI.

      I have attached the screenshots of my content modifer, resources & output over here.
      Can you please help me in understanding the anomaly? Am I missing any step?

      Author's profile photo Shiva Prasad Narahari
      Shiva Prasad Narahari

      Hi Eng swee Yeoh,

      Using this bean can we create two sheets in one excel in I Flow run ?


      Thanks in advance


      Author's profile photo Joe Girard
      Joe Girard

      Hi Shiva,

      I am also in need of being able to output multiple sheets in one xls file.

      Were you able to get this to work?