A Simple approach in Reading Excel File

[former_member188961]

            Hi folks! Working with Adapter-modules has always been challenging and great to use for typical business requirements dealing with Excel files. Excel files carry most of the financial data in current business landscapes. Being one among those who faced such challenge, here I would like to share one of my recent works on Adapter module that deals with Excel files.

This blog explains the way an adapter module can be designed in a simple way and this is unique in the manner that it is generic in nature converting .xls and .xlsx files into XML for SAP PI to process.

  Here we go!  All you need to do is, just follow the below steps.

1.     Create the Data Type for the excel sheet that you want to read.

2.    Techie Buds, please follow the below link to know basics of creating an Adapter module in SAP PI. 

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/c0b39e65-981e-2b10-1c9c-fc3f8e674...

3.          Add the below Jars to your module

           poi-3.9.jar

          poi-ooxml-3.9.jar

          xmlbeans-2.3.0.jar

4.    Add the below attached java code in your project

5.     Configure the Sender channel parameters as per your data structure.

UDF : In my business scenario I created the below  UDF ,which maps the first occurrence of the field in XML (Cell Data) to Field 1 (Project Id) on the target side ,2^nd occurrence(Cell Data[2]) to Field 2 (Project Engineer). This way all the data in the XML file will be mapped to the actual fields on the target side for further processing. Isn’t it a simple one?

Testing:

Let’s test a file of .xls and .xlsx by placing in the FTP server for PI to pick.

Output XML file equivalent to EXCEL file read by the module , made available in Target FTP.

XML File :

  How did this approach help me?

• By using this module I converted my Actual Excel file to an intermediate XML file.
• The XML file is just like any other XML that we use in File based scenarios.
• Excel file based scenario is now turned to XML file based scenario using this scenario.
• The Adapter module has become a reusable one, as most of the financial data will have similar format the same can be used.