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

Function Module SAP_CONVERT_TO_XLS_FORMAT
Uses the Spreadsheet-Interface from SAP Office Integration (see below)
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
Not suitable for Background Processing

Interface I_OI_SPREADSHEET
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
Cannot be used for background processing

Examples:
• Basic Use: See Code of FM SAP_CONVERT_TO_XLS_FORMAT
• Advanced Example (Embedded Macros and Pictures): Link
• Advanced Example (Pivot Table): Link
• Demoprograms in Package SOFFICEINTEGRATION

Function Module MS_EXCEL_OLE_STANDARD_DAT
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 characterlike fields
Example: Link

Microsoft OLE
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.)
There are some major drawbacks:
• Knowledge of OLE specific Excel Commands necessary
• Poor performance
• Interference with user
• Limited error handling as execution of command is done outside SAP
• Not suitable for background processing

Examples: Link1 Link2 Link3

Method CL_BCS_CONVERT=>STRING_TO_SOLIX
Simple method to create Excel Files in Background Mode (which is already the main advantage)
Fields are written in a delimited text field and then converted to Excel with this method
There are no formatting options

PI: SpreadSheetML
SpreadSheetML is the XML description of an Excel File developed by Microsoft. It can be opened with Excel regulary. For a complete description of this technique, please refer to this blog.

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

ABAP: SpreadsheetML
PI is not the only option to create SpreadSheetML Files
A very inconvenient option is to directly create the xml in a text variable
Better is, to use the SAP built in XSLT-Prozessor that allows to transform DDIC structures into XML. However, this option also requires more manual effort then the PI solution
Examples: Link1 Link2 Link3

ABAP2XLSX
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
Class ABAP2XLSX is a convenient openSource Suite to generate Excel Files according to this new standard

The features of this suite allow producing high end excel files (drawings, graphs, conditional formatting, formulas etc)
The suite works in background, is performant, platform independent and is easy to understand
Information on ABAP2XLSX can be found on the ABAP2XLSX Homepage or in this nice blog.
This project can be imported quite easily as a .nugg file

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

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.

FM GUI_DOWNLOAD / Method CL_GUI_FRONTEND_SERVICES=>GUI_DOWNLOAD
Those do not really generate Excel-Files.
They only can generate WK1 and CSV files which can also opened with Excel

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
Various Converters can be found under http://www.processtext.com/

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 as a .nugg file

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

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 indivually defined (as needed e.g. type i for numberic 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

FunctionModule ALSM_EXCEL_TO_INTERNAL_TABLE
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.
 

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

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 Strcutures 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.

Function Module GUI_UPLOAD 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)

Alternative Considerations : 3rd Party approaches to integration

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 behaviour, 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 behaviour 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

  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.