Skip to Content
Author's profile photo Shabarish V Nair

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

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


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.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Chandra G
      Chandra G
      Author's profile photo Shabarish Vijayakumar
      Shabarish Vijayakumar
      Blog 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 !!!

      Author's profile photo Former Member
      Former Member

      Hi Shabarish,

      Please help me on below thread...

      Excel(.xlsx) to .csv converison in SAP PI XI | SCN

      Author's profile photo chandra shekar dasari
      chandra shekar dasari
      Nice blog buddy,Keep blogging

      chandra dasari(x collegue T-Systems)

      Author's profile photo Former Member
      Former Member
      Nice Blog!

      But I am begginer in SAP PI/XI and I would like to know how can I implements modules !!

      Thanks in advance

      Author's profile photo Shabarish Vijayakumar
      Shabarish Vijayakumar
      Blog Post Author
      Author's profile photo Former Member
      Former Member

      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.


      Author's profile photo Shabarish Vijayakumar
      Shabarish Vijayakumar
      Blog 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 ...

      Author's profile photo Former Member
      Former Member
      Hi Shabz,

      Is it necessary for us to custom a new adapter module or is there a standard Adapter Module for reading of Excel Files.

      Author's profile photo SUNIL SINGH
      Way Back I had the same requirement which was basically reading multiple Excel sheet in the mail attachment and converting them into xml.

      For this I had written a Adapter Module

      Sunil Singh

      Author's profile photo Former Member
      Former Member

      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 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
      •   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: The parameter "argument" has the value "remote:///test/*.xls", so it contains the character "remote:///test/*.xls" which is not allowed: 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

      Author's profile photo Shabarish Vijayakumar
      Shabarish Vijayakumar
      Blog 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.

      Author's profile photo Former Member
      Former Member

      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



      Author's profile photo Former Member
      Former Member

      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:


      LinkageError during appliction Java mapping com/incture/ExcelToXmlByPOI

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

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



      Author's profile photo Former Member
      Former Member

      Nice Blog. As a beginner in SAP PI, your blogs are very informative and helpful to me. Thanks. Keep Blogging!!