Skip to Content

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.

Introduction

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

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 (converterClass = ‘com.equalize.xpi.af.modules.excel.Excel2XMLTransformer’). Certain parameters will automatically inherit the default values if it is not configured.

Parameter Name Allowed values Default value Remarks
conversionType SimpleExcel2XML Required field. Determines conversion type.
converterClass com.equalize.xpi.af.modules.excel.Excel2XMLTransformer 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
converterClass com.equalize.xpi.af.modules.excel.Excel2XMLTransformer
sheetName Sheet1
processFieldNames fromFile
documentName MT_Order
documentNamespace urn:equalize:com

Result

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
converterClass com.equalize.xpi.af.modules.excel.Excel2XMLTransformer
sheetIndex 0
processFieldNames fromConfiguration
fieldNames Order,Date,Material,Quantity
rowOffset 2
recordName Line
documentName MT_CustomOrder
documentNamespace urn:equalize:com
formatting raw

Result

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
converterClass com.equalize.xpi.af.modules.excel.Excel2XMLTransformer
sheetIndex 0
processFieldNames notAvailable
columnCount 5
rowOffset 1
documentName MT_CustomOrder
documentNamespace urn:equalize:com
skipEmptyRows N
evaluateFormulas N
emptyCellOutput defaultValue
emptyCellDefaultValue space

Result

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

Reference

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

To report this post you need to login first.

34 Comments

You must be Logged on to comment or reply to a post.

  1. Peter Wallner

    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,

    Peter

    (0) 
  2. siri sha

    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.

    http://scn.sap.com/thread/3763584

    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

    Siri

    (0) 
    1. Eng Swee Yeoh Post author

      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.

      Rgds

      Eng Swee

      (0) 
  3. Rajesh PS

    Hi Eng Swee Yeoh ,


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


    Implemented Scenario 1

    /wp-content/uploads/2015/07/hjtr_741689.jpg

    Parameter Name Parameter Value
    converterClass com.equalize.xpi.af.modules.excel.Excel2XMLTransformer
    sheetName StoreXL
    processFieldNames fromFile
    documentName MT_Order
    documentNamespace htttp://OrdersofOrders/xi/StoreConversion
    (0) 
        1. Alexey Dmitriev

          Donwload the EAR file. Its in the blog aabout FormatConversionBean.

          Then open NWDS and connect it to your system(Java As).

          Open Deployment perspectinve  – import EAR, and press start.

          If successfull pop up window appeared then you can use the module in adapter/

          (0) 
      1. Rajesh PS

        Hi Alexey,

        If you have the steps of procedure to work upon for FILE(EXCEL) to FILE(XML) scenario in PI7.0

        Please share. Many Thanks.

        (0) 
          1. Eng Swee Yeoh Post author

            Hi Alexey,

            I tried to reply to your earlier comment about the date formats but I think it was deleted before I managed to post the comment. Anyway here it is below if you still need it:-

            ——————-

            Thank you for the feedback. Glad to know that the modules were deployed successfully and works.

            Regarding the Excel format, to be honest I haven’t tested it extensively with different users and date formats.

            The code for it uses the Apache POI DataFormatter as shown below. So that is the default behaviour.

            com.equalize.xpi.af.modules/Excel2XMLTransformer.java at master · engswee/com.equalize.xpi.af.modules · GitHub

            DataFormatter formatter = new DataFormatter(true);

            cellContent = formatter.formatCellValue(cell);

            Unfortunately, I am quite tied up at the moment and do not have time to look into this. As the source code is available publicly on GitHub, may I suggest that you try to modify the module and test if there is some Apache POI method that can cater for it.

            You can build the module and perform standalone testing on it following my blogs below:-

            Recompile com.equalize.xpi.af.modules as EJB 2.1 modules in NWDS 7.1x

            Standalone testing of Adapter Module in NWDS

            Rgds

            Eng Swee

            (0) 
    1. Eng Swee Yeoh Post author

      Hi Rajesh

      Note that the module and EAR file are not compatible with PI 7.0. I’d suggest you search for other alternative solutions for that particular PI version.

      Rgds

      Eng Swee

      (0) 
  4. Manikandan Rajendran

    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

    Regards

    M

    (0) 
    1. Alexey Dmitriev

      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

      (0) 
      1. Eng Swee Yeoh Post author

        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.

        Rgds

        Eng Swee

        (0) 
        1. Abhishek Paul

          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 sequence..as in the screenshot.

          For the PayloadSwapBean the params are:

          swap.keyName     =Payload-Name

          swap.keyValue1

          ModuleSequence.JPG

          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.

          Payload.JPG

          Please help me with suggestion.

          (0) 
          1. Eng Swee Yeoh Post author

            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.

            Regards

            Eng Swee

            (0) 
  5. Miguel Jorge

    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

    FormatConversionBean.PNG


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

    Thanks,

    Miguel

    (0) 
    1. Eng Swee Yeoh Post author

      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.

      Rgds

      Eng Swee

      (0) 
      1. Miguel Jorge

        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.

        Thanks,

        Miguel

        (0) 
        1. Eng Swee Yeoh Post author

          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?

          /wp-content/uploads/2015/09/log_780945.png

          Rgds

          Eng Swee

          (0) 
          1. Miguel Jorge

            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.

            Thanks,

            Miguel

            (0) 
  6. Shradha Wanjari

    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.

    Thanks,

    Shradha.

    (0) 
    1. Eng Swee Yeoh Post author

      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.

      Regards

      Eng Swee

      (0) 
  7. Apu Das

    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 beansap.com/Excel2XMLTransformerEAR*xml|Excel2XMLTransformerEJB.jar*xml|Excel2XMLTransformer. Most probable reason: application sap.com/Excel2XMLTransformerEAR 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 –


    AbstractModuleConverter

    AuditLogHelper

    DynamicConfigurationHelper

    ParameterHelper

    4.  Build the EJB project

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

    Pic1.JPG

    6. My ejb-jar.xml value –

    Pic2.JPG

    7. Export as SAP EJB JAR File

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

    Pic3.JPG

    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.

    Regards,

    Apu

    (0) 
    1. Michael Appleby

      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.

      (0) 
        1. Michael Appleby

          Right click on Eng’s name link.  Copy the url.  Paste it into your Discussion.  Mouse over the link and click on the A popup, then select T for auto title. 

          That will bring it to Eng’s attention.

          Cheers, Mike (Moderator)

          SAP Technology RIG

          (0) 
  8. deva Gembali

    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

     

    (0) 

Leave a Reply