XLSX Workbench for SAP(ABAP) - tool for exporting data to Excel

[former_member191807]

PROJECT LINK:

• XLSX Workbench for SAP

RELATED PAGES:

• Export ALV-Tree to Excel with XLSX Workbench
• Export internal table to Excel with XLSX Workbench
• Create a Formatted Excel in a Background Job

YOUTUBE CHANNEL:

• XLSX Workbench (for SAP) - Simple Excel form from scratch - YouTube
  
• XLSX Workbench (for SAP) - Export Grid to Excel - YouTube
• XLSX Workbench (for SAP) - Export CL SALV TREE to Excel - YouTube
• XLSX Workbench (for SAP) - Export CL_GUI_ALV_TREE to Excel - YouTube

                           

IDEA

For a long time only OLE/DOI-approach was available for creating spreadsheets. As you know, OLE/DOI-approach is quite flexible, but it has some restrictions. For example, it requires installed MS Excel application (this makes impossible to use background job for generate spreadsheet). Moreover, OLE-approach has low performance (this problem can be partially solved using VBA, but this requires manually changing of security settings MS-Excel application on each front-end computer).

Since, ZIP-folder processing tools became available in the SAP environment, we are able to generate spreadsheets with new Open XML Format (XLSX). This approach does not have lacks of OLE.

But, in my opinion, the flaw of all existing solutions for generate spreadsheets from ABAP- is many lines of ABAP code required directly for creating and formatting spreadsheet layout. As result, the same Excel forms are quite differently implemented by the different developers. That approach heavily complicates a support of these developments.

My idea is creating the design-time environment for visual form editing (like a SMARTFORMS tool, for example).

A unified approach to development is realized because no need to write the code for the Excel form formatting - you only care about source data for the form.

Runtime printing form is being generated with the new Open XML Format (XLSX), without using of Microsoft Excel application (it makes possible to get result in the background jobs SAP).

DESIGN-TIME AND RUNTIME

Where are two modes of XLSX Workbench tool.

• Design-time mode starts with a call transaction ZXLWB_WORKBENCH and provides completely visual interface, which allows you to quickly create or change the form layout even you don't have ABAP skills:
  
  
  

• Runtime mode occurs each time the printing programm is being called.
  The printing program prepares the source data (and fill the context) and finally calls the functional module 'ZXLWB_CALLFORM', which compiles and displays the form:
  
  
  Developer decides how to use the runtime form:
  • display on the front-end computer via MS Excel application;
  • save on the front-end computer as file (.XLSX) with a specified path;
  • return into the printing program as rawdata.

WHAT DOES DEVELOPMENT PROCESS LOOK LIKE ?

You need to develop three components:

• CONTEXT.  As a Context, you can use any suitable Table type or Structure or Data element from the ABAP-dictionary. Or just create your own Context via transaction SE11.
  
  
• FORM. Call transaction ZXLWB_WORKBENCH and build the form structure in the visual editor. Forms are stored in the SAP WEB-repository (transaction SMW0, binary data for WebRFC-applications), and represented as XLSX-files. All data about form's structure is inside the file.

• PRINTING PROGRAM. It must contain next steps:

  • declare a context (just variable which refers to ABAP-dictionary type);

  • fill context with data;

• call function module 'ZXLWB_CALLFORM'.