Create fancy Excel Files with SAP PI


SAP PI Interview Questions and SAP PI Tutorials

SAP PI Interview Questions and SAP PI Tutorials

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 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:

SpreadSheetML Structure

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:

Duplicate Elements

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.

SAP PI Interview Questions and SAP PI Tutorials

SAP PI Interview Questions and SAP PI Tutorials

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.

 

 

 

SAP PI Interview Questions and SAP PI Tutorials

SAP PI Interview Questions and SAP PI Tutorials

Share this:
Share this page via Email Share this page via Stumble Upon Share this page via Digg this Share this page via Facebook Share this page via Twitter
en.pdf24.org    Send article as PDF   
This entry was posted in sap pi 7.1 tutorials and tagged , , , , , , , , , , , , , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *