Skip to Content

Recently one of the customer came up with a requirement to create an excel file as an output of the scenario. Scenario was Proxy-SAP PI 7.31 (Java Stack)-Third Party – output as Excel Sheet. First PoC of this requirement was to create a target file by just giving output.xls and see what gets populated. As expected output get populated as xml.

To populate target file as Excel file we need to do transformation somewhere between the XML received and target file generation. We have 2 options –

  1. Transformation at the time of Mapping
  2. Write Adapter Module in Java

At the time of mapping we can achieve excel output through XSLT mapping. For this solution we need to convert incoming XML file to MS Excel XML format.

For the conversion of XML to MS Excel XML format we need to use below code in xslt.

<?mso-application progid="Excel.Sheet"?>
<xsl:stylesheet version="1.0"
xmlns:html="http://www.w3.org/TR/REC-html40"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:o="urn:schemas-microsoft-com:office:office"
                xmlns:x="urn:schemas-microsoft-com:office:excel"
                xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

Explanation of this scenario can be found below.

Convert incoming XML to Excel or Excel XML – Part 1 – XSLT Way

Second option is to write an adapter module to convert incoming XML to CSV and then to Excel Sheet.

Pre-Requisite and Assumptions of the development:

  1. 1. Apache POI open Standard for excel Conversion
  2. 2. SAP PI specific Development libraries

  com.sap.aii.af.cpa.svc.api.jar

  com.sap.aii.af.lib.mod.jar

  com.sap.aii.af.ms.ifc_api.jar

  com.sap.aii.af.svc_api.jar

Complete explanation of this scenario can be found below.

Convert incoming XML to Excel Sheet Part 2 – Adapter Module way

I have seen lots of questions related to excel  output and I hope this blog will give a good solution to them.

To report this post you need to login first.

3 Comments

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

  1. Otto Gold

    Hi,

    is there a good reason to link to a third party site instead of posting the content here? Marketing your homepage on SCN? Not my cup of tea…

    cheers Otto

    (0) 
  2. Grzegorz Glowacki

    Hi Aashish,

    When writing such a complex and complete guide to a single topic, would you agree that a Document or a Wiki would be better than three separate blogs? This would also give you an opportunity to compare both approaches, and highlight their strong and weak sides. How do you feel about that?

    Regards,

    Greg

    (0) 

Leave a Reply