Create fancy Excel Files with SAP PI
There are several blogs how to create Excel Files in SAP.
Some of them handle the SpreadsheetML standard which is a good approach to create enhanced Excel files with formatting, multiple sheets etc. Compared to OLE, issues like performance or no background processing do not occur here.
A short overview over Spreadsheet ML and its advantages:
SpreadsheetML is a XML Standard defined by Microsoft and is available from Office 2003 on. Each Excel File can be converted to SpreadsheetML. Such Files can be named either .xls or .xml and are automatically opened with Microsoft Excel. Users normally will notice that they are opening a XML file.
A big advantage compared to OLE is that developers do not need to understand the meaning of each element in the XML structure. Instead, the Excel file that needs to be produced is converted to SpreadSheetML and from that we know which fields need to be filled with which value. Therefore, there is not too much try and error until we get the desired result and knowledge of OLE specific commands is not required.
The Schema provided by Microsoft is quite complicated, but on the other hand only a few elements are mandatory to create an Excel File with formatting:
Existing Blogs and Examples in the SDN either create the XML manually in a character field using concatenate (not very convenient) and others suggest using Transformations to convert ABAP Structures into the SpreadSheetML Format.
Now, let’s come to the advantage of using PI.
As we have a schema from Microsoft and SpreadSheetML is a XML standard, the idea came to me that I could import the schema in the PI Enterprise Service Repository, create a Service Proxy from the XSD.
And what advantages do we get from that?
- The complete structure of the XML is created automatically as DDIC structure
- Developers just need to look at the converted SpreadSheetML and fill the same fields and internal tables in ABAP
- The conversion from ABAP to XML is done automatically by the Proxy Runtime
- We can use the built-in functionality of PI to send the Excel File by email, ftp etc.
Normally, I would not describe how to import a XSD files and create Proxy from it, but as there are some traps during this procedure, I will note them here
Step 1) Make sure some OSS notes are installed
During Proxy Generation I came on several bugs in the SAP proxy runtime which SAP has fixed with the following notes:
0001472956 xsd:sequence with minOccurs=0 is ignored
0001487627 Syntax error in Stylesheet
Step 2) Get the XSD-Package from Microsoft
You can get the original files from here.
However, certain xsd language element are not supported by SAP. I therefore did some minor modification to the XSD.
You can download the modified files from here.
Step 3) Import the XSD in the Enterprise Service Builder
Place all 13 xsd from the package in one directory, use the “Import External Definition” Wizard to import the file “excelss.xsd”.
Make sure to keep “Import References” checked.
Once done, all 13 xsd should be imported automatically in one step.
Step 4) Create an asyncronous outbound interface
As message Type select “Workbook” from the excelss.xsd external Definition.
Step 5) Create the Proxy in SPROXY
Before activating, you need to change one structure because SAP generates duplicate field names.
Open the external view and naviage to the Table Element under Worksheet. You will find 4 duplicates. Just change the ABAP Name of the duplicates to any other name.
The location of those duplicates is shown in below screenshot:
After that you should be able to activate the proxy.
In below box, there is a sample report that will generate a simple Excel File with a little bit formatting.
In order to run this report in your system you need to change the prefix that you have choose during Proxy generation from ZTMP to the value that you have choosen as prefix. Additionally, the name of the service interface need to be changed (here: ZTMPCO_SI3_GENERIC_EXCEL_OUTB / SI3_GENERIC_EXCEL_OUTB_ASYNC)
Step 6) XSL-Mapping (optional)
At the beginning of the SpreadSheetML file, you will see following processing instruction:
This instruction ensures, that the file can be saved as .xml but is still shown as Excel File under Windows and is not opened with a XML Editor.
Without this processing instruction, you also can save the file as .xls and it can be opened as regular Excel file.
If you want to include this processing instruction, you should create a simple XSLT-Mapping in PI that will add this processing instruction.
Useful links and blogs: