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

[Shabarish_Nair]

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.