Excel With

 



Excel is an electronic spreadsheet program that is used for storing, organizing and manipulating data. Data is stored in individual cells that are usually organized in a series of columns and rows in a worksheet; this collection of columns and rows is referred to as a table. Lifewire / Adrian Mangel. Excel with Business is an online course platform with over 1,000,000 learners worldwide. Learn Microsoft Office skills, Finance, a Mini MBA and more. Excel with Business is an online course platform with over 1,000,000 learners worldwide. Learn Microsoft Office skills, Finance, a Mini MBA and more.

Excel uses any worksheets stored between the starting and ending names of the reference. For example, =SUM(Sheet2:Sheet13!B5) adds all the values contained in cell B5 on all the worksheets between and including Sheet 2 and Sheet 13. Works with Excel Open, edit, and save Microsoft Excel files with the Chrome extension or app. Convert Excel files to Google Sheets and vice versa.

If you're an Excel beginner, this is the perfect place to start.

Microsoft Excel is one of the most used software applications of all time. Hundreds of millions of people around the world use Microsoft Excel. You can use Excel to enter all sorts of data and perform financial, mathematical or statistical calculations.

1 Range: A range in Excel is a collection of two or more cells. This chapter gives an overview of some very important range operations.

2 Formulas and Functions: A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel.

Skip to end of metadataGo 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.



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.

Excel With C#

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

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

Excel With

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:

  • Various Converters can be found under http://www.processtext.com/

Microsoft Excel Versions

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

Versions

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

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.

Excel With Pivot Tables

NB: the method supersedes the function module GUI_DOWNLOAD.

Excel With Macros

Method CL_GUI_FRONTEND_SERVICES=>GUI_UPLOAD

Excel With $

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

Data Analytics With Excel Pdf

NB: the method supersedes the function module GUI_UPLOAD.