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 –
- Transformation at the time of Mapping
- 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.
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. Apache POI open Standard for excel Conversion
- 2. SAP PI specific Development libraries
Complete explanation of this scenario can be found below.
I have seen lots of questions related to excel output and I hope this blog will give a good solution to them.