Converting Excel file data with multiple sheets to an internal table

[Former Member]

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

Go to SE80 and create a function group as shown below

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

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

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

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

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.

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.

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