Skip to Content

Life would have been easy if XI/PI provided us with the option of handling data of MS Excel files in a standard way. Unfortunately it doesnt !

So how can Excel files be handled in XI/PI?

There are actually quite a few options available for us;

1. Use Conversion Agent – How to Handle Excel Documents with SAP PI

2. Use XSLT Mapping – The specified item was not found.

3. Use JAVA

Its the third option that we will be looking into in this blog.

There are free APIs available to help us read or create an excel file. One such API is the JExcelAPI

I found it to be a very simple API to use and code. The jar files for building any project using the JExcel API can be downloaded from here

The solution that I assume is the best would be to code a module. The module can be coded in such a way so that you can make it generic across scenarios as per your landscape and requirements.

Lets now look at the design of two scenarios.

a. Read an Excel File

You can use the package jxl to your advantage in this case. The method getWorkbook(java.io.InputStream is) can be used in the module to read the workbook. There are various interfaces like sheet and cell that provides you various methods to access the data.

Once you access the data you can write out the source XML directly from the module so that it can be used for transformation. If you are lazy, and dont want to build the XML from your module itself, then make sure you write out a flat file format from your module and leave the XML conversion to the MessageTransformBean (Plain2XML), that will be next in chain after your custom module.

b. Writing an Excel File

Comparing reading to writing excel files, reading is relatively an easy task you can perform with the API. For writing out an excel file, the API provides you numerous interfaces and methods that can help you also do the following if required in a scenario;

1. Formating – Format cells in terms of font, font colour, number and date formatting

2. You can also edit the cells in terms of assigning background colours, borders etc.

3. Even formulas can be applied to the excel file

There are two ways you can code your module. The first would be to take the target XML itself as the input, parse the XML and then convert it to the required XML format. This would mean you implement a SAX or DOM parser to help you read data from the XML. The other option is to use the MessageTransformBean (XML2Plain) first in the chain to create a flat file format. You can then using String functions in the module to play around with the payload. I personally prefer this since its quite easy to manipulate Strings 🙂

To create/write Excel files, the package jxl.write should be used. You can use the method createWorkbook(java.io.OutputStream os) of the Workbook class  in the module. As mentioned earlier, it is also possible to set font, font colour, background colour, borders etc for the cells.

I have written a sample class which tries to create an excel output that you can base as a reference while building your custom module. The code also documents the formatting of cells, just in case your client expects some ‘delicacies’ of that kind 🙂

Find the code here – LINK

Note:

There are also other APIs other than JExcel that can be used. Another widely used API is POI-HSSF and POI-XSSF, part of the Apache POI project. You can also explore that option, but on a personal note I found the JExcel API much easier to use.

To report this post you need to login first.

15 Comments

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

    1. Shabarish Vijayakumar Post author
      thanks Chandra …
      it does seem i missed that one 🙂

      Felix got to that earlier and i never knew …

      Lets hope something new is absorbed from this blog of mine …

      Thanks again !!!

      (0) 
  1. Farooq Farooqui
    Hi,

    If I am not wrong there is a Blog for reading and writing Excel sheet using API’s (in Adapter module) is already available on SDN:)

    This blogs is very specific to reading/writing Excel sheet in Java.

    Regards,
    Farooq

    (0) 
    1. Shabarish Vijayakumar Post author
      yes there is as it was rightly pointed out by Chandravadhana earlier 🙂

      as i mentioned before … i hope this blog captures more details and will give the reader a different perspective on the topic …

      (0) 
  2. ganga naresh

    Hi Vijay ,

    I follow your blogs quite often …Good sharings …Well i have come across the same requirement of read a text file and write into an excel into an http://FTP... i have created the data types , message types , inbound outbound interfaces .Followed the same api and mapping class . and the test in the repository is showing java mapping successful .

    Start of test 

    •   Call method execute of the application Java mapping com.pi.xi.TextMapping
    •   *** START APPLICATION TRACE ***
    •   Processing message
    •   Processing completed successfully
    •   *** END APPLICATION TRACE ***
    •   Java mapping com/pi/xi/TextMapping completed. (execute() of com.pi.xi.TextMapping

    Executed successfully

    And the routing rules are also defined . However in the receiver channel is throwing an error of  illegal argument as below

    Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: The parameter “argument” has the value “remote:///test/*.xls”, so it contains the character “remote:///test/*.xls” which is not allowed: com.sap.exception.standard.SAPIllegalArgumentException: The parameter “argument” has the value “remote:///test/*.xls”, so it contains the character “remote:///test/*.xls” which is not allowed

    Am i missing something

    Thanks in advance

    (0) 
    1. Shabarish Vijayakumar Post author

      is the excel file being created in the java mapping or a module? if it is in the java mapping, then check you adapter CC settings. It might have nothing to do with the java excel api.

      (0) 
      1. ganga naresh

        Hi Vijay ,

        Thanks for the quick reply …probably i didnt put my description correctly. i have followed your blog exactly in using the java excel api . And yes as you said i belive this is sumting to be checked in adapter settings .should any file content conversion be used in the sender cc or receiver cc( file adapter ). Input file is *.txt and output file is *.xls

        Thanks

        Naresh

        (0) 
  3. Gourahari Sendha

    Hi Guys,

    I am trying to read the xls and xlsx file to convert into Xml file  using Apache POI jars.

    I am facing error in OM like:

    workbookjava.io.FileInputStream@36f4f861

    *** END APPLICATION TRACE ***

    LinkageError during appliction Java mapping com/incture/ExcelToXmlByPOI

    java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException at com.incture.ExcelToXmlByPOI.transform(ExcelToXmlByPOI.java:80)

    It clearly seems like xmlbeans jar is not reflecting in pi .

    But Same code is running fine in NWDS without causing any error.

    In ESR ,i imported all relevant jars.

    Any Idea how to Solve this issue.Please help me…

    Thanks

    Gourav

    (0) 

Leave a Reply