Skip to Content

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

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

Update 10 May 2016: Add new optional parameters headerRow and onlyValidCharsInXMLName (courtesy of Jacob Frank Vandborg).

Update 7 Aug 2015: Add new parameter columnOffset.

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

Update 11 Feb 2015: Updated source code links to shared module repository on GitHub. Previous GitHub repository will be deleted.

Update 27 Jan 2015: Updated Source code section to indicate EAR deployment file released for download at GitHub repository.

Update 3 Nov 2014: Refactoring of source code to cater for SimpleExcel2XML and SimpleXML2Excel conversions.


There are already a handful of blogs on SCN dealing with Excel to XML conversion – so why another one??

Here is my wish list for a comprehensive solution:-

  • Able to read all kinds of Excel format (XLS and XLSX)
  • Behaves in a similar way as MessageTransformBean
  • Highly configurable – develop/deploy once, use multiple times
  • Able to handle XML special characters

In the reference section below are some of the more popular approaches, however below are some of the (non-exhaustive) limitations/drawbacks

  • XLSX files stores string contents in a separate sharedStrings.xml file in the zipped XLSX file
  • JExcel API does not support Excel 2007 XLSX formats
  • Limitations in handling formulas, formatting and special characters in cells

ExcelTransformBean (ETB) aims to provide a generic adapter module solution (a la MessageTransformBean) that is highly configurable and reusable. It is based on the Apache POI API. Utilizing the combined SS interface of the API, it uses a single logic to read all kinds of Excel files (XLS and XLSX).

This first part covers Excel to simple XML conversion, while the second part covers simple XML to Excel conversion.

Source Code

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

FormatConversionBean – One Bean to rule them all!

This converter is based on Apache POI 3.9 library. In order for the Java project to compile and build successfully, the following JAR files need to be referenced/imported into the project.

  • poi-3.9-20121203.jar
  • poi-ooxml-3.9-20121203.jar
  • poi-ooxml-schemas-3.9-20121203.jar
  • xmlbeans-2.3.0.jar
  • dom4j-1.6.1.jar

The library files can be downloaded from Apache’s website, direct link to the ZIP file is provided below.

Apache POI 3.9 ZIP file


Note: CPI version has been updated to Apache POI 3.17 – refer to FormatConversionBean arrives in CPI for details on library dependencies.


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 Excel to XML conversion. Certain parameters will automatically inherit the default values if it is not configured.

Parameter Name Allowed values Default value Remarks
converterClass PI –
CPI – com.equalize.converter.core.Excel2XMLConverter
Required field. Determines conversion class.
sheetName The name of the active Excel sheet to extract. Either sheetName or sheetIndex must be populated.
sheetIndex Integer values beginning from 0 The index of the active Excel sheet to extract (starts from 0). Either sheetName or sheetIndex must be populated.
skipEmptyRows Y, N Y Empty rows to be skipped or not
rowOffset Integer values beginning from 1 0 Starting row to begin extracting content from (i.e. 0 = start from first row, 1 = start from second row). If processFieldNames = ‘fromFile’ and rowOffset = 0, first line will be automatically be headerRow+1
headerRow Integer values beginning from 1 0 Available only when processFieldNames = ‘fromFile’. Determines which row to retrieve header column names from (0-based so 0 is first row, 1 is second row)
columnOffset Integer values beginning from 1 0 Starting column to begin extracting content from (i.e. 0 = start from first column, 1 = start from second column)
processFieldNames fromFile, fromConfiguration, notAvailable Required field. Determines the naming of each column of the rows, and the number of columns to extract:

  • fromFile = Column names and number of columns are determined from header line of the sheet
  • fromConfiguration = Column names and number of columns are determined from parameter fieldNames
  • notAvailable = Column names will be set as ColumnX, where X = 1,2,3,4. Number of columns will be determined from parameter columnCount
fieldNames Name of columns. Required field when processFieldNames = ‘fromConfiguration’
onlyValidCharsInXMLName Y, N N Available only when processFieldNames = ‘fromFile’. Removes invalid XML characters from column names retrieved from header row of sheet
columnCount Integer values beginning from 1 Number of columns for extraction. Required field when processFieldNames = ‘notAvailable’
recordName Record XML element name for row of record in output
documentName Required field. Document name of root element of XML output
documentNamespace Required field. Namespace of root element of XML output
formatting excel, raw excel Controls how the cell contents are formatted in XML output

  • excel = Cells are displayed the same way as Excel formatting of corresponding cell
  • raw = Raw value of cells are displayed
evaluateFormulas Y, N Y Controls how cell contents with formulas are displayed in XML output

  • YES = Cells are displayed with result of formula evalution
  • NO = Cells are displayed with actual formula
emptyCellOutput suppress, defaultValue suppress Controls how empty cells are displayed in XML output

  • suppress = Empty cells are not displayed (no corresponding XML tags for empty cells)
  • defaultValue = Empty cells will be displayed with default value
emptyCellDefaultValue <blank> If emptyCellOutput = ‘defaultValue’, all empty cells will be populated with value in this parameter
indentFactor Integer values beginning from 1 0 Determines the number of indentation spaces for each level in the XML output
debug Y, N N Displays contents in Audit Log of each cell extracted. WARNING: Use this only for debugging in non-productive systems

Example Scenarios

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

Scenario 1

Excel 2007 XSLX file format.

Extract Sheet1 with column names determined directly from header line of file.

Special character & automatically converted

Module parameters

Parameter Name Parameter Value
sheetName Sheet1
processFieldNames fromFile
documentName MT_Order
documentNamespace urn:equalize:com


Input /wp-content/uploads/2014/10/in1_567666.png
Output /wp-content/uploads/2014/10/out1_567688.png

Scenario 2

Excel binary XLS file format.

Extract sheet at index 0. Column names are provided from configuration.

Row offset provided to skip first two lines.

No formatting of cells, so raw values displayed.

Module parameters

Parameter Name Parameter Value
sheetIndex 0
processFieldNames fromConfiguration
fieldNames Order,Date,Material,Quantity
rowOffset 2
recordName Line
documentName MT_CustomOrder
documentNamespace urn:equalize:com
formatting raw


Input /wp-content/uploads/2014/10/in2_567687.png
Output /wp-content/uploads/2014/10/out2_567686.png

Scenario 3

Excel 2007 XSLX file format.

Extract sheet at index 0.

Column names are not available. Number of columns = 5.

Row offset provided to skip first line.

Empty rows are included.

Cells with formula are displayed with formula instead of result.

Empty cells are displayed with default value “space”.

Module parameters

Parameter Name Parameter Value
sheetIndex 0
processFieldNames notAvailable
columnCount 5
rowOffset 1
documentName MT_CustomOrder
documentNamespace urn:equalize:com
skipEmptyRows N
evaluateFormulas N
emptyCellOutput defaultValue
emptyCellDefaultValue space


Input /wp-content/uploads/2014/10/in3_567714.png
Output /wp-content/uploads/2014/10/out3_567715.png


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

This article does not cover the steps for creating a custom adapter module. This can be found easily via SCN search. It is also listed in the reference section of the article below regarding adapter module testing.

Standalone testing of Adapter Module in NWDS

Alternative methods for Excel conversion

PI/XI: Reading MS Excel’s XLSX and XLSM files with standard PI modules – easily…

Excel Files – How to handle them in SAP XI/PI (The Alternatives)

A Simple approach in Reading Excel File

You must be Logged on to comment or reply to a post.
  • Hello Eng Swee Yeoh,

    thank you for this very interesting and useful adapter module. This will make life much easier dealing with EXCEL sheets without having to write adapter modules anymore.

    Best regards,


  • Hi Eng Swee Yeoh,

    Thanks for BLOG,

    I am little confused at using "sheetIndex" value. As I have a requirement to read Excel which has 4 Tabs in it and I need to read first 2 Tabs data and Map it back to RFC.

    And not clear on how to build Data types\Message Types for each Excel sheet fields.

    Is this requirement is possible using this Example Scenario 2.I have already raised SCN query on this. It will be helpful for me if you respond to my query.

    also please let me know where to keep these jar files in PI ..

    • poi-3.9-20121203.jar
    • poi-ooxml-3.9-20121203.jar
    • poi-ooxml-schemas-3.9-20121203.jar
    • xmlbeans-2.3.0.jar
    • dom4j-1.6.1.jar

    Thanks & Regards


    • Dear Siri

      Thank you for your interest in this adapter module.

      Unfortunately, the module as it is can only read 1 sheet in an Excel file. If you want to be able to read more, you can download the source code and further enhance it accordingly.

      Regarding the JAR files, these are bundled in the adapter module's EAR file and are automatically deployed into the PI system once the EAR file is deployed. You do not need to manually upload them into PI's file system.


      Eng Swee

      • Hi Eng

        I like this pose. But unfortunately I do not know how to use this. I downloaded the poi-bin-3.9-20121203 zip and gave it to our basis team to upload in the server. They said that it was uploaded successfully. But we cannot find the modules. Here below is the screen shot from Netweaver application module. Your help is greatly appreciated.


        netweaver application module




  • Hi Eng Swee Yeoh ,

    Facing error in comm channel on implementing above steps. Please help. Many Thanks....!!

    Implemented Scenario 1


    Parameter Name Parameter Value
    sheetName StoreXL
    processFieldNames fromFile
    documentName MT_Order
    documentNamespace htttp://OrdersofOrders/xi/StoreConversion
  • Hello Eng,

    Your module has made the life easy. Thanks for that. I tried your module for an excel file with FTP adapter. It worked without any issues. My requirement is to use with an sender email adapter, where xlsx file will come as an attachment. I used PayloadZipBean,  PayloadSwapBean,  FormatConversionBean, XIMailAdapterBean in order with appropriate parameters. It processed the file content using the parameters given in configuration(i could see in audit log with debug on and off) but it fails in intergration engine due to the fact the converted content is still in the mailattachment-1 and not in the main mail message payload. I tried using one more payload swap bean after format conversion bean but it still fails in the integration engine. I can see that there is no problem in your module processing but just want to ensure have you faced any issues with mail adapter. Any help would be appreciated. Thanks.

    Server: PI7.31 Dual Stack



    • Hi Manikandan Rajendran,

      why do u use PayloadZipBean? its not necessary, is your attach in not zipped.

      Please exclude from module chain and it should work.

      Use first - localejbs/AF_Modules/PayloadSwapBean with

      swap.keyName     payload-name
      swap.keyValue     MailAttachment-1

      Second - FormatConversionBean

      configure it according your requirement

      Third  - XIMailAdapterBean - standard one

      • Thanks Alexey for your assistance here.

        Manikandan, please follow Alexey's suggestions. You don't need to unzip, just need to swap the attachment to the main payload before using FormatConversionBean.


        Eng Swee

        • Hi Eng,

          Thanks for presenting the module.

          I have a interface Sender mail adapter containing .xslx file in attachment.I need to convert the .xslx file to XML and then use it.

          I am using the module in the below in the screenshot.

          For the PayloadSwapBean the params are:

          swap.keyName     =Payload-Name



          But the problem I am getting is the payload still is in .xslx format.

          This is why the mapping is failing.

          But astonisingly if I dowload the MailAttachment-1.xslx file and see that,it opens in notepad++, and that is the converted xml.

          So the TransformationBean works but my purpose is not solved.


          Please help me with suggestion.

          • Hi Abhishek

            Thanks for your interest in this development.

            As you are relatively new to SCN, I'd suggest that you post your query as a discussion thread. It is the appropriate place as the comments section of blogs are not suitable for detailed discussions to tackle specific issues.

            Just as a note, I suspect that the PayloadSwapBean might not be working correctly. Can you please provide screenshot of the full configuration parameters for all modules? Also provide screenshot of the audit/message log showing the execution of the modules.


            Eng Swee

  • Hi Eng,

    Thanks for this blog and assistance in using this module.

    I am able to successfully test this as a standalone module in NWDS.

    However after deploying it to our PI 7.4 system, I get an error:

    Error: java.lang.IllegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream


    Do you perhaps know how I would go about fixing this error?



    • Hi Miguel

      From the first line of your log, it seems there is already a conversion to XML. Is this done by some standard FCC or MTB?

      Please note that this module expects an Excel file in either binary XLS format or the newer XLSX 2007 format (refer Apache POI - the Java API for Microsoft Documents). The error you are getting is due to the input not being in either of these formats.


      Eng Swee

      • Hi Eng,

        Thanks for the reply.

        I have tried it with both formats and it fails.  I then tried the module with a stand-alone test on my local machine through the NWDS and the process worked.  This locally working file, is the file that I am trying to test through PI now and getting this error message.



        • Hi Miguel

          Can you explain what is happening in your channel that causes the file to be converted to XML as shown in the first line (highlighted below)? Are you using File Content Conversion in the channel? Can you share your channel's module configuration screenshot?



          Eng Swee

          • Hi Eng,

            Yes, I had originally configured the channel for FCC.  As soon as I removed the FCC, it worked perfectly.

            Thanks very much for your help.



  • Hi Eng,

    I have a scenario from MAIL to IDOC  where excel file comes as an attachment. This excel sheet contains two worksheets one has header and another has the item level details.

    Using the above i was able convert the excel to xml but only a single sheet at a time .Could you please let me know  how can i use this to read two worksheet from the excel at the same time.



    • Hi Shradha

      The design of this converter is to read one sheet only. If you want to read more than one sheet you can enhance it according to your needs. The source code is publicly available as described in the Source Code section above.


      Eng Swee

  • Hi Eng,

    I am trying to implement excel to xml conversion from your below blog -

    ExcelTransformBean Part 1: Convert various Excel ...

    But After deploying it in my java stack I am getting below error and its deployed with warning as well -

    Error while obtaining business methods info for*xml|Excel2XMLTransformerEJB.jar*xml|Excel2XMLTransformer. Most probable reason: application cannot be started. See logs available for this application.

    These are the steps I have followed. I think I have done something wrong. Can you please help me -

    1. Created EJB 3.0 project with name Excel2XMLTransformerEJB and added to EAR - Excel2XMLTransformerEAR

    2.  Added all required jars in ejb project build path.

    3. In EJB project created one session bean (EJB 3.x) with name Excel2XMLTransformer.

    Created normal class with below names -





    4.  Build the EJB project

    5. My ejb-j2ee-engine.xml value -


    6. My ejb-jar.xml value -


    7. Export as SAP EJB JAR File

    8. In EAR application-j2ee-engine.xml value -


    9.  Build and export as SAP EAR File.

    10. Finally Run on server. its deployed with warning.

    Can you please help me with the correct and exact steps. Its very urgent

    Thanks in advance for you help.



    • Unless you are asking for clarification/correction of some part of the Document, please create a new Discussion marked as a Question.  The Comments section of a Blog (or Document) is not the right vehicle for asking questions as the results are not easily searchable.  Once your issue is solved, a Discussion with the solution (and marked with Correct Answer) makes the results visible to others experiencing a similar problem.  If a blog or document is related, put in a link.  Read the Getting Started documents (link at the top right) including the Rules of Engagement. 

      NOTE: Getting the link is easy enough for both the author and Blog.  Simply MouseOver the item, Right Click, and select Copy Shortcut.  Paste it into your Discussion.  You can also click on the url after pasting.  Click on the A to expand the options and select T (on the right) to Auto-Title the url.

  • HI Eng,


    Kindly help to check the following error while reading excel file. Current version PI 7.4

    Error :

    Channel CC_XXXXXX: Sending file failed with javax.ejb.TransactionRolledbackLocalException: nested exception is: java.lang.RuntimeException: java.lang.NoClassDefFoundError: org/apache/poi/POIXMLDocument : cannot initialize class because prior initialization attempt failed; nested exception is: javax.ejb.EJBException: nested exception is: java.lang.RuntimeException: java.lang.NoClassDefFoundError: org/apache/poi/POIXMLDocument : cannot initialize class because prior initialization attempt failed; nested exception is: javax.ejb.EJBTransactionRolledbackException: nested exception is: java.lang.RuntimeException: java.lang.NoClassDefFoundError: org/apache/poi/POIXMLDocument : cannot initialize class because prior initialization attempt failed; nested exception is: javax.ejb.EJBException: nested exception is: java.lang.RuntimeException: java.lang.NoClassDefFoundError: org/apache/poi/POIXMLDocument : cannot initialize class because prior initialization attempt failed - continue processing


  • Hi Eng Swee,

    Good day!

    Thanks for sharing this really helpful blog!

    Just a question, is there a capability under this bean that will allow to split a single excel xslx file with 2 sheets into 2 separate xlsx/csv files? 😉 No transformation required, just need to split this single file. Do I just convert the xlsx file into an XML and apply the multi-mapping before converting it back into xls? 😀




  • Hello,

    Successfully deployed the EAR file.  Version of PI is 7.4 SP 8.

    I am getting the below error:



    Below are the configuration details in the Communication Channel.

    Hope you can help me.




    • I suggest you perform standalone testing in NWDS so that you can debug your error. You can check one of my other blogs which details more on that.

  • it seems like can't add xml.recordsetName & xml.<StructureName>. just like the standard FCC,since I got a deep structure with multiple nodes,is there any solution deal with this?



  • Hi Eng Swee Yeoh,

    thank you  good blog,


    We are given xls format in receiver communication channel but the output file data is asking like XML file format, how to default come us xls format can u have any idea

    please find below screenshots


    for those popups, we don't want how to directly come



  • Hi Eng Swee Yeoh,


    I have implemented this excel transformation bean. I would like to check is there anybody implemented the dynamic sheet name in this sheet name parameter.

    As per my requirement,  I would like to pass the APR-19(month name and two digit year). Any inputs on this requirements.



    • Hi Rajani


      Module parameters in PI are always static values - so you won't be able to pass dynamic values to sheetName parameter.



      Eng Swee

  • Hi Eng Swee Yeoh,

    Thanks to the ear file you have provided, i was able to deploy using NWDS.

    The version of PI is 7.5 and i am using the Excel to xml transformation,

    i configured the parameters in the sender CC as detailed below and moved on for testing, 


    i am getting the error as attached in the screen shot, can you please guide me to resolve this

    appreciate your response Thanks in advance

    Karthik G