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 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.
The Schema provided by Microsoft is quite complicated, but on the other hand only a view 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 Proxies from it, but as there are some traps during this procedure, I will note them here
Step-by-Step Guide
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
0001485074 WSDL error: attribute .. defined more than once
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_ASY
***************************************************
*&---------------------------------------------------------------------*
*& Report ZPROXY_TEST_EXCEL
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT ZPROXY_TEST_EXCEL.
DATA: ref_app TYPE REF TO cx_ai_application_fault,
ref_proxy TYPE REF TO ZTMPCO_SI3_GENERIC_EXCEL_OUTB,
ref_sys TYPE REF TO cx_ai_system_fault,
l_ack_request TYPE PRX_ACK_REQUEST_DETAILS,
l_async_messaging TYPE REF TO if_wsprotocol_async_messaging.
data: ls_workbook type ZTMPWORKBOOK.
data: ls_worksheet type ZTMPWORKSHEET_TYPE.
TRY.
CREATE OBJECT ref_proxy.
CATCH cx_ai_system_fault INTO ref_sys.
* Hier Ausnahmebehandlung
ENDTRY.
* ask for transport ack
TRY.
l_async_messaging ?= ref_proxy->get_protocol( if_wsprotocol=>async_messaging ).
l_ack_request = if_wsprotocol_async_messaging=>co_transport_acknowledgment.
l_async_messaging->set_acknowledgment_requested( l_ack_request ).
CATCH cx_ai_system_fault INTO ref_sys.
ENDTRY.
************** START ***************** FILL EXCEL STRUCTURE***********************************
perform generate_stlyes changing ls_workbook-styles-style.
perform fill_row_tab changing ls_worksheet-table-row.
ls_worksheet-name = 'myWorksheet'.
append ls_worksheet to ls_workbook-worksheet.
ls_worksheet-name = 'anotherWorksheet'.
append ls_worksheet to ls_workbook-worksheet.
************** END ***************** FILL EXCEL STRUCTURE***********************************
* Execute Proxy
TRY.
CALL METHOD ref_proxy->SI3_GENERIC_EXCEL_OUTB_ASYNC
EXPORTING
output = ls_workbook.
COMMIT WORK AND WAIT.
CATCH cx_ai_system_fault INTO ref_sys.
* Exception Handling
* ev_errmsg = ref_sys->errortext.
CATCH cx_ai_application_fault INTO ref_app.
ENDTRY.
*&---------------------------------------------------------------------*
*& Form FILL_ROW_TAB
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* <--P_LS_WORKSHEET_TABLE_ROW text
*----------------------------------------------------------------------*
FORM FILL_ROW_TAB CHANGING P_ROW_TAB type ZTMPSI3_GENERIC_EXCEL_OUT_TAB4.
data: ls_row type ZTMPSI3_GENERIC_EXCEL_OUTB_A16.
data: ls_cell type ZTMPCELL.
data: lt_cell_tab type ZTMPCELL_TAB.
* empty rows
append ls_row to p_row_tab.
append ls_row to p_row_tab.
append ls_row to p_row_tab.
* header row
ls_cell-Merge_Across = '1'.
ls_cell-Style_ID = 'rahmen'.
ls_cell-data-type = 'String'.
ls_cell-data-content = 'Bestandsübersicht'.
append ls_cell to lt_cell_tab.
ls_row-cell = lt_cell_tab.
append ls_row to p_row_tab.
clear: ls_cell, lt_cell_tab.
* data row
ls_cell-data-type = 'String'.
ls_cell-data-content = 'Früchte'.
append ls_cell to lt_cell_tab.
ls_cell-data-content = 'Anzahl'.
ls_cell-data-type = 'String'.
append ls_cell to lt_cell_tab.
ls_row-cell = lt_cell_tab.
append ls_row to p_row_tab.
clear lt_cell_tab.
ls_cell-data-type = 'String'.
ls_cell-data-content = 'Äpfel'.
append ls_cell to lt_cell_tab.
ls_cell-data-content = '7'.
ls_cell-data-type = 'Number'.
append ls_cell to lt_cell_tab.
ls_row-cell = lt_cell_tab.
append ls_row to p_row_tab.
clear lt_cell_tab.
ls_cell-data-type = 'String'.
ls_cell-data-content = 'Birnen'.
append ls_cell to lt_cell_tab.
ls_cell-data-content = '9'.
ls_cell-data-type = 'Number'.
append ls_cell to lt_cell_tab.
ls_row-cell = lt_cell_tab.
append ls_row to p_row_tab.
clear lt_cell_tab.
* summary row
ls_cell-Style_ID = 'gruen_fett'.
ls_cell-data-type = 'String'.
ls_cell-data-content = 'Summe'.
append ls_cell to lt_cell_tab.
ls_cell-data-content = '16'.
ls_cell-data-type = 'Number'.
append ls_cell to lt_cell_tab.
ls_row-cell = lt_cell_tab.
append ls_row to p_row_tab.
clear lt_cell_tab.
ENDFORM. " FILL_ROW_TAB
*&---------------------------------------------------------------------*
*& Form GENERATE_STLYES
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* <--P_LS_WORKSHEET_STYLES_STYLE text
*----------------------------------------------------------------------*
FORM GENERATE_STLYES CHANGING P_style_tab type ZTMPSTYLE_TYPE_TAB.
data: ls_style type ZTMPSTYLE_TYPE,
ls_border type ZTMPBORDER_TYPE.
* Style 1: Border around cell
ls_style-ID = 'rahmen'.
ls_border-Position = 'Bottom'.
ls_border-Line_Style = 'Continuous'.
ls_border-Weight = '2'.
append ls_border to ls_style-borders-border.
ls_border-Position = 'Top'.
append ls_border to ls_style-borders-border.
ls_border-Position = 'Left'.
append ls_border to ls_style-borders-border.
ls_border-Position = 'Right'.
append ls_border to ls_style-borders-border.
append ls_style to p_style_tab.
clear ls_style.
* Style 2: Bold Text with green background
ls_style-ID = 'gruen_fett'.
ls_style-Font-Family = 'Swiss'.
ls_style-Font-Bold = '1'.
ls_style-Interior-Color = '#339966'.
ls_style-Interior-Pattern = 'Solid'.
append ls_style to p_style_tab.
ENDFORM. " GENERATE_STLYES
***************************************************
Step 6) XSL-Mapping (optional)
At the beginning of the SpreadSheetML file, you will see following processing instruction:
<?mso-application progid="Excel.Sheet"?>
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.
