Convert incoming XML to Excel Sheet

[aashish_sinha]

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.