Skip to Content

PREREQUISITES

It is expected that the readers have the basic knowledge of Function modules and considerable experience in ABAP workbench.

Also Check that EXCEL is registered in R/3 via transaction SOLE.

INTRODUCTION

Reading data from files is a very common requirement in project implementations. Excel spread sheets are simpler and better readable to text files. The standard ABAP function module ALSM_EXCEL_TO_INTERNAL_TABLE reads data from Excel file into internal table of type ALSMEX_TABLINE. This function module ONLY reads data from the active worksheet i.e., if the excel file has multiple worksheets, the data can be read from any one worksheet. The following approaches can be used to read data from all the worksheets within a excel file into the internal table.

i)              Developing a custom FM (Copy of ALSM FM) along with additional parameter for sheets.

ii)             Using SAP classes and interfaces (cl_gui_custom_container, i_oi_container_control, i_oi_document_proxy, i_oi_spreadsheet).

iii)            Using the XML classes and interfaces and the assumption is that the source Excel file will be saved as XML Spreadsheet file.

This document deals with the first technique.

Custom Solution

As SAP standard fm (ALSM_EXCEL_TO_INTERNAL_TABLE) doesn’t support reading multiple sheets of an excel file, it has to be customized as below:

Custom solution:

Go to SE11 and create a custom structure (ZALSMEX_TABLINE) as below.

This is done because in standard structure (ALSMEX_TABLINE) we don’t have a parameter for sheet number (in order to distinguish the data in the internal table).

Capture.JPG

Go to SE80 and create a function group as shown below

Capture.JPG

Create a new FM, copy all source code from ALSM_EXCEL_TO_INTERNAL_TABLE, including parameters and other needed files to your new FM (eq  : ZALSM_EXCEL_TO_INTERNAL_TABLE).

Go to SE37 and click on the ‘COPY’ button as shown below

Capture.JPG

Provide Custom FM name (ZALSM_EXCEL_TO_INTERNAL_TABLE) and also corresponding Function group  (ZFGEXCEL_INT) as shown below:

Capture.JPG

– Add 1 new Import (parameter) as follow:  >> Sheets type I & 1 Tables parameter IT_DATA type ZALSMEX_TABLINE (custom structure)

Capture.JPG

Capture.JPG

·        – Open ZALSM_EXCEL_TO_INTERNAL_TABLE source code

– Find Sentence:

***********

set property of application ‘Visible’ = 1.

m_message.

  GET PROPERTY OF  application ‘ACTIVESHEET’ = worksheet.

  m_message.

***********

and replace with the following code:

Do Sheets times

Sheet no = sheet no +1.

set property of application ‘Visible’ = 1.

m_message.

GET PROPERTY OF  application ‘ACTIVESHEET’ = worksheet.

m_message.

CALL METHOD OF APPLICATION ‘Worksheets’ =

                  worksheet EXPORTING #1 = sheet no.

m_message.

CALL METHOD OF worksheet ‘Activate’.

m_message.

GET PROPERTY OF application ‘ACTIVESHEET’ = worksheet.

m_message.

Capture.JPG

Also Double click on the below perform statement  & Create a separate include (LZFGEXCEL_INTF01) for the subroutine as shown below and copy the code from standard FM in it.

PERFORM separated_to_intern_convert TABLES excel_tab intern
                                        USING  ld_separator
                                               sheetno.

Capture.JPG

Also write APPEND LINES OF intern TO it_data after the perform statement as shown below.

This is done in order to collect the data from all sheets into an internal table.

Capture.JPG

Then Go back to the main program and call the FM as below:

CALL FUNCTION ‘ZALSM_EXCEL_TO_INTERNAL_TABLE’
    EXPORTING
      filename                      = filename
      i_begin_col                   = 1
      i_begin_row                   = 1
      i_end_col                     = 50
      i_end_row                     = 11000
      sheets                        = 7
    TABLES
      it_data                       = i_intern
EXCEPTIONS
   INCONSISTENT_PARAMETERS       = 1
   UPLOAD_OLE                    = 2
   OTHERS                        = 3
            .

Limitations:

·         It can work for 99 sheets

·         Maximum  rows it can work –  65536

·         Maximum  columns it can work –  256

Testing :       Create XLS file with 10000 rows of data

Capture.JPG

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Kiran K

    Phani,

    Thanks for sharing this.

    Don’t we have any Standard Class/Method or FM to achieve this functionality,in the recent versions of SAP ?

    K.Kiran.

    (0) 

Leave a Reply