Skip to end of metadata
Go to start of metadata

Microsoft Excel is not always a favourable format to exchange data with customers, suppliers and their IT-systems
However, there are requests were the Excel format is mandatory.

In the beginning, SAP only had a very limited support but in the meantime the integration of office in SAP is very good.

There are a lot of specific articles on the SDN about the Excel topic. The purpose of this blog is to give a short overview over various options to work with Excel Files in SAP and provide links with examples for the described options.

There is no „best" way to work with Excel files - each option has its advantages and disadvantages and usage depends on several factors.



Writing Excel Files with SAP

ABAP2XLSX

ABAP2XLSX is a convenient openSource Suite to generate Excel Files according to format XLSX (since Office 2007).

Information on ABAP2XLSX can be found on the ABAP2XLSX Homepage or in this nice blog.

This project can be imported by first installing abapGit.

The features of this suite allow producing high end excel files (drawings, graphs, conditional formatting, formulas; etc)

The suite works in background, is performing, platform-independent and is easy to understand.

Several demo programs are available after importing the source code. Their names are all starting with ZDEMO_EXCEL.

NB: although SpreadsheetML Files are still compatible with newer Excel Versions, Microsoft changed to the OpenXML Standard with Office 2007. Word/Excel-Files consist now internally of several xml-files.

Function Module SAP_CONVERT_TO_XLS_FORMAT

Cannot be used in background processing.

Has a very simple interface (internal table, filename).

Creates native binary Excel.

Certain ABAP Types may not be converted correctly to Excel Format (e.g. Date).

Not every type of table can be used.

No changes to layout or formatting of the file possible.

No Support of multiple Sheets.

Interface I_OI_SPREADSHEET

Cannot be used in background processing.

Belongs to the SAP Office Integration / Control Framework.

Allows to create more sophisticated Excel Files (Colors, Individual Formatting, Multiple Sheets, Zoom, Hide Rows, etc.)

Although a lot of formatting options exist, the usage is quite simple because the methods of the interfaces are self-explanatory. 

Internally also uses OLE, but no OLE specific knowledge is required.

Poor performance if "mass methods" are not used.

Examples:

  • Basic Use: see code of function module SAP_CONVERT_TO_XLS_FORMAT
  • Advanced Example (Embedded Macros and Pictures): Link
  • Advanced Example (Pivot Table): Link
  • Demo programs in Package SOFFICEINTEGRATION

Function Module MS_EXCEL_OLE_STANDARD_DAT

Cannot be used in background processing.

Has a similar interface as SAP_CONVERT_TO_XLS_FORMAT but different options and directly uses OLE

Excel is opened after execution (cannot be suppressed)

Supports PivotTable, Header and Sheet Names but no further formatting options

Can only be used for character-like fields

Example: Link

Microsoft OLE

Drawbacks:

  • Cannot be used in background processing.
  • Use of Excel macro recorder + manual conversion from OLE specific Excel Commands to ABAP are required
  • Poor performance
  • Interaction with user
  • Limited error handling as execution of command is done outside SAP

The technique used for writing and reading the Excel file is the same (only difference is that the workbook must first be opened)

Examples can either be found in the previous section of writing with OLE or by looking into the simple code of the FM ALSM_EXCEL_TO_INTERNAL_TABLE

Compared to the previous methods, direct use of OLE offers the most options to create advanced excel files (e.g. print layout, header/footer, etc.) 

Examples: Link1 Link2 Link3

PI: SpreadSheetML

SpreadSheetML is an old format Office XML 2003. For a complete description of this technique, please refer to this blog.

Advantages:

  • Very performing
  • Suitable for Background Processing
  • Almost all Excel Options supported
  • Easy to understand
  • Built-in Connectivity (i.e. send file by email etc)

ABAP: SpreadsheetML

Excel file in the old format Office XML 2003

A very inconvenient option is to directly create the xml in a text variable

Better is, to use the SAP built in XSLT Processor that transforms DDIC structures into XML. However, this option also requires more manual effort then the PI solution

Examples: Link1Link2Link3

PI: JavaExcel API

Java supplies an API to create and read Excel Files in Java

This API can be included in PI and could also be used to create Native Excel Files

Similar to the SpreadsheetML Solution, Data structures that represent the Excel Content would be filled in SAP and PI would then hand over this data to the Excel API

Information how to integrate the API into PI, can be found in the section on how to read Excel File with this API.

The API reference can be found here.

3rd Party Converter

Small tools are available to convert csv or wk1 into a native Excel file from command line.

Those programs can be called from an ABAP Report.

Given the other options above, this solution is listed but seems not be the perfect choice

For example:

Reading/Importing Excel into SAP

ABAP2XLSX

Using ABAP2XLSX project it is possible to read office 2007/2010 file.

Information on ABAP2XLSX can be found on the ABAP2XLSX Homepage or in this nice blog.

This project can be imported quite easily by installing abapGit first.

Several Demo-Programs are available after importing the source code, all starting with ZDEMO_EXCEL* 

Microsoft OLE

The technique used for writing and reading the Excel file is the same (only difference is that the workbook must first be opened)

Examples can either be found in the previous section of writing with OLE or by looking into the simple code of the FM ALSM_EXCEL_TO_INTERNAL_TABLE

Compared to the previous methods, direct use of OLE offers the most options to create advanced excel files (e.g. print layout, header/footer, etc.) 

Function Module TEXT_CONVERT_XLS_TO_SAP

Has a simple interface (filename)

Uses interface i_oi_spreadsheet from Office Integration

Target DDIC structures can be individually defined (as needed e.g. type i for numeric columns)

Limited error handling

No Support of multiple Sheets (Only the active sheet is processed)

Not suitable for Background Processing

Example can be found here

*Interface i_oi_spreadsheet („Office Integration")*

To have more flexibility and a better error handling, a direct use of the I_OI_SPREADSHEET Interface is recommended

An example how to read Excel Files with multiple sheets can be found here

Function module ALSM_EXCEL_TO_INTERNAL_TABLE

Only provided with SAP ERP software (R/3, ECC, S/4).

Uses Microsoft OLE

The interface of this function module is also very simple but the result is quite different from TEXT_CONVERT_XLS_TO_SAP:

  • the generated internal table can not be freely defined
  • the generated internal table contains row/column of the imported cell
  • data area (start row, end row, start column, end column) must be defined
  • cell contents can only be up to 50 characters long

Because of the generic internal table that is used here, this method might be handy when the layout of the excel format tends to change and you need to find a dynamic approach in what cells you find the desired data.

JavaExcel API (via PI) 

The JavaExcel API integrated in the SAG PI System can convert native Excel Files into XML.

By default it uses the column names of the Excel File as XML tags.

XML Structures can then be automatically converted to DDIC Structures by the ABAP ProxyRuntime.

The advantage of the PI solution is that it also solves the problem of how to get the Excel File into the SAP System (e.g. by FTP, email, from a WebPage etc)

Refer to this article how to integrate the JExcel API into PI.

3rd Party Converter

It is also possible to use 3rd party applications to integrate Microsoft Excel with SAP ERP. The methods used by these are less about ABAP coding and more about using existing existing SAP APIs like BAPIs and remote enabled Function Modules or BDC's. Many of these third party applications connect to SAP via RFC using users' own credentials; this is especially common when calling transactions or using BDC over RFC. Most if not all of the third party applications are SAP Certified and the vendors can be found on http://EcoHub.SAP.COM

Considerations:

  • Many of these 3rd party products are developed in .NET with very tight integration with the Microsoft stack accordingly alignment is often closer to Excel functionality
  • Often there is little or no ABAP involved in the development of technical objects for integration
  • Since many of these approaches rely on BDC or BAPI logic there is a heavy dependence on understanding DYNPRO behavior, navigation and the underlying logic in the SAP APIs
  • Network latency over the RFC can result in poor performance
  • The process is relatively uncontrollable if the users that make the connections are not moderated and monitored because they appear as regular dialog users
  • Error handling is bound up in the behavior of the BDC or BAPI
  • BAPIs and RFMs do not exist for all equivalent transaction functions
  • Most of these approaches don't support background processing so scheduling is done locally by the user

Obsolete tools

Method CL_GUI_FRONTEND_SERVICES=>GUI_DOWNLOAD 

Those do not generate Excel-Files. 

They only can generate WK1 and CSV files which can also opened with Excel, but these options are obsolete.

NB: the method supersedes the function module GUI_DOWNLOAD. 

Method CL_GUI_FRONTEND_SERVICES=>GUI_UPLOAD

Similar to GUI_DOWNLOAD the functionality is very limited as it can not process native Excel files (only Text Formatted files)

NB: the method supersedes the function module GUI_UPLOAD.


10 Comments

  1. There's also a convenient way using this method:

    http://abapblog.com/articles/tricks/33-create-xlsx-mhtml-file-from-internal-table-in-background

    That is basically using cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform, which is also what CL_GUI_ALV_GRID->EXPORT_TO_SPREAD_SHEET (standard ALV export to spreadsheet) does.

    The only trouble I found so far is that if_salv_bs_xml=>c_type_xlsx is missing.
    Yet saving as if_salv_bs_xml=>c_type_excel_xml (.xml) opens fine in excel.

  2. if_salv_bs_xml=>c_type_xlsx is not available in all releases, If I remember good then it's from 710 or 720


  3. Do all of these suggestions require a coding change in the ABAP? Is there a way to implement for Ad Hoc or SAP Query?

    Thanks!

    1. You can use the mentioned 3rd party solutions to avoid coding.

      Try winshuttle query or BCC EasyInput.

  4. it will be fine to have a handy breakdown table that will show features that are (un)supported by each method: Pivot Tables, charts, multisheet, headers etc.

  5. Sandra Rossi

    Also, other modules with Excel read ability are missing from the overview:

    FILE_READ_AND_CONVERT_SAP_DATA

    IMPORT_FROM_SPREADSHEET

    KCD_EXCEL_OLE_TO_INT_CONVERT

    UPLOAD_XLS_FILE_2_ITAB

    A very good sample prog is available that demonstrates the use/limitations of each module and that is probably worth adding here

  6. ABAP: SpreadsheetML

    Examples: Link1Link2Link3

    the first actual link is https://blogs.sap.com/2009/02/10/abap-and-excel-create-formatted-excel-using-xml, two others are dead and non-existent anymore