Skip to Content
Author's profile photo Former Member

Converting Excel file data with multiple sheets to an internal table

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

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Alpesh Saparia
      Alpesh Saparia

      Nice work around to read multiple Excel Sheets 🙂

      Author's profile photo Prakash Sivagnanam
      Prakash Sivagnanam

      HI,

      Good Work.... 🙂 ,

       

      With Regards

      Prakash Sivagnanam.

      Author's profile photo Amiruddin Ayaz Notta
      Amiruddin Ayaz Notta

      Any workaround for xlsx format type? I have an excel file of ~45MB in xlsx format with 2 sheets. It has 4 columns though and i need only 2nd column.

      Author's profile photo Kiran K
      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.

      Author's profile photo Iván Redondo Rodríguez
      Iván Redondo Rodríguez

      One question,

      Where finish "the enddo" ??

      All start with: "Do Sheets times" ...  but Where it is finished?

      Thnak you.

       

      Regards.

      Author's profile photo Diogo Awaihara
      Diogo Awaihara

      Right after CALL METHOD cl_gui_frontend_services=>clipboard_export

      I’ve added “sheename” also so you can get only sheet name you want.

      Find bellow the code


      FUNCTION z_alsm_excel_2_int_table_sheet .
      *”———————————————————————-
      *”*”Interface local:
      *”  IMPORTING
      *”     VALUE(FILENAME) LIKE  RLGRAP-FILENAME
      *”     VALUE(I_BEGIN_COL) TYPE  I DEFAULT 1
      *”     VALUE(I_BEGIN_ROW) TYPE  I DEFAULT 1
      *”     VALUE(I_END_COL) TYPE  I DEFAULT 256
      *”     VALUE(I_END_ROW) TYPE  I DEFAULT 65536
      *”     VALUE(I_SHEETS) TYPE  I DEFAULT 100
      *”     VALUE(I_SHEETNAME) TYPE  CHAR10 OPTIONAL
      *”  TABLES
      *”      IT_DATA STRUCTURE  ZSTALSMEX_TABLINE
      *”  EXCEPTIONS
      *”      INCONSISTENT_PARAMETERS
      *”      UPLOAD_OLE
      *”———————————————————————-

      DATAexcel_tab     TYPE  ty_t_sender.
      DATAld_separator  TYPE  c.
      DATAapplication TYPE  ole2_object,
      workbook    TYPE  ole2_object,
      sheetno     TYPE  i,
      sheetname   TYPE  char10,
      range       TYPE  ole2_object,
      worksheet   TYPE  ole2_object.
      DATAh_cell  TYPE  ole2_object,
      h_cell1 TYPE  ole2_object.
      DATA:
      ld_rc             TYPE i.
      *   Rückgabewert der Methode “clipboard_export     “

      * Makro für Fehlerbehandlung der Methods
      DEFINE m_message.
      CASE sysubrc.
      WHEN 0.
      WHEN 1.
      MESSAGE ID symsgid TYPE symsgty NUMBER symsgno
      WITH symsgv1 symsgv2 symsgv3 symsgv4.
      WHEN OTHERSCALL METHOD OF application ‘QUIT’RAISE upload_ole.
      ENDCASE.
      END-OF-DEFINITION.

      * check parameters
      IF i_begin_row > i_end_rowRAISE inconsistent_parametersENDIF.
      IF i_begin_col > i_end_colRAISE inconsistent_parametersENDIF.

      * Get TAB-sign for separation of fields
      CLASS cl_abap_char_utilities DEFINITION LOAD.
      ld_separator cl_abap_char_utilities=>horizontal_tab.

      * open file in Excel
      IF applicationheader space OR applicationhandle 1.
      CREATE OBJECT application ‘Excel.Application’.
      m_message.
      ENDIF.
      CALL METHOD OF application ‘Workbooks’ workbook.
      m_message.
      CALL METHOD OF workbook ‘Open’ EXPORTING #1 filename.
      ***************adaptação para múltiplas abas – início ****************
      ***  m_message.
      ****  set property of application ‘Visible’ = 1.
      ****  m_message.
      ***  GET PROPERTY OF  application ‘ACTIVESHEET’ = worksheet.
      ***  m_message.
      REFRESH it_data.
      DO i_sheets TIMES.
      sheetno sheetno + 1.

      GET PROPERTY OF  application ‘ACTIVESHEET’ worksheet.
      m_message.

      CALL METHOD OF application ‘Worksheets’ worksheet
      EXPORTING
      #1 sheetno.
      IF sysubrc <> 0.
      IF sheetno 1.
      m_message.
      ELSE.
      EXIT.
      ENDIF.
      ENDIF.

      CALL METHOD OF worksheet ‘Activate’.
      m_message.

      GET PROPERTY OF worksheet ‘NAME’ sheetname.
      m_message.

      “Filtro por sheetname
      CHECK sheetname i_sheetname OR i_sheetname IS NOT SUPPLIED.

      GET PROPERTY OF application ‘activesheet’ worksheet.
      m_message.
      ***************adaptação para múltiplas abas – fim ****************

      * mark whole spread sheet
      CALL METHOD OF worksheet ‘Cells’ h_cell
      EXPORTING #1 i_begin_row #2 i_begin_col.
      m_message.
      CALL METHOD OF worksheet ‘Cells’ h_cell1
      EXPORTING #1 i_end_row #2 i_end_col.
      m_message.

      CALL METHOD  OF worksheet ‘RANGE’ range
      EXPORTING #1 h_cell #2 h_cell1.
      m_message.
      CALL METHOD OF range ‘SELECT’.
      m_message.

      * copy marked area (whole spread sheet) into Clippboard
      CALL METHOD OF range ‘COPY’.
      m_message.

      * read clipboard into ABAP
      CALL METHOD cl_gui_frontend_services=>clipboard_import
      IMPORTING
      data       excel_tab
      EXCEPTIONS
      cntl_error 1
      *       ERROR_NO_GUI         = 2
      *       NOT_SUPPORTED_BY_GUI = 3
      OTHERS     4.
      IF sysubrc <> 0.
      MESSAGE a037(alsmex).
      ENDIF.

      PERFORM separated_to_intern_convert_s TABLES excel_tab it_data
      USING  ld_separator
      sheetno
      sheetname.

      * clear clipboard
      REFRESH excel_tab.
      CALL METHOD cl_gui_frontend_services=>clipboard_export
      IMPORTING
      data       excel_tab
      CHANGING
      rc         ld_rc
      EXCEPTIONS
      cntl_error 1
      *       ERROR_NO_GUI         = 2
      *       NOT_SUPPORTED_BY_GUI = 3
      OTHERS     4.

      ENDDO“adaptação para múltiplas abas

      * quit Excel and free ABAP Object – unfortunately, this does not kill
      * the Excel process
      CALL METHOD OF application ‘QUIT’.
      m_message.

      * >>>>> Begin of change note 575877
      * to kill the Excel process it’s necessary to free all used objects
      FREE OBJECT h_cell.       m_message.
      FREE OBJECT h_cell1.      m_message.
      FREE OBJECT range.        m_message.
      FREE OBJECT worksheet.    m_message.
      FREE OBJECT workbook.     m_message.
      FREE OBJECT application.  m_message.
      * <<<<< End of change note 575877
      ENDFUNCTION.

       


      LZALSMEXF01


      *&———————————————————————*
      *&      Form  SEPARATED_TO_INTERN_CONVERT_S
      *&———————————————————————*
      *       text
      *———————————————————————-*
      *  –>  p1        text
      *  <–  p2        text
      *———————————————————————-*
      FORM separated_to_intern_convert_s TABLES i_tab       TYPE ty_t_sender
      i_intern    TYPE ty_t_itab_s
      USING  i_separator TYPE c
      i_sheetno   type i
      i_sheetname TYPE char10.
      DATAl_sic_tabix LIKE sytabix,
      l_sic_col   TYPE kcd_ex_col.
      DATAl_fdpos     LIKE syfdpos.

      LOOP AT i_tab.
      l_sic_tabix sytabix.
      l_sic_col 0.
      WHILE i_tab CA i_separator.
      l_fdpos syfdpos.
      l_sic_col l_sic_col + 1.
      PERFORM line_to_cell_separat_s TABLES i_intern
      USING  i_tab l_sic_tabix l_sic_col
      i_separator l_fdpos i_sheetno i_sheetname.
      ENDWHILE.
      IF i_tab <> space.
      CLEAR i_intern.
      i_internsheetno   i_sheetno.
      i_internsheetname i_sheetname.
      i_internp_row     l_sic_tabix.
      i_internp_col     l_sic_col + 1.
      i_internvalue     i_tab.
      APPEND i_intern.
      ENDIF.
      ENDLOOP.
      ENDFORM.                    ” SEPARATED_TO_INTERN_CONVERT_S
      *———————————————————————*
      FORM line_to_cell_separat_s TABLES i_intern    TYPE ty_t_itab_s
      USING  i_line
      i_row       LIKE sytabix
      ch_cell_col TYPE kcd_ex_col
      i_separator TYPE c
      i_fdpos     LIKE syfdpos
      i_sheetno   TYPE i
      i_sheetname TYPE char10.
      DATAl_string   TYPE ty_s_senderline.
      DATA  l_sic_int  TYPE i.

      CLEAR i_intern.
      l_sic_int i_fdpos.
      i_internsheetno i_sheetno.
      i_internsheetname i_sheetname.
      i_internp_row i_row.
      l_string i_line.
      i_internp_col ch_cell_col.
      * csv Dateien mit separator in Zelle: –> ;”abc;cd”;
      IF i_separator ‘;’ OR  i_separator ‘,’ AND
      l_string(1gc_esc.
      PERFORM line_to_cell_esc_sep USING l_string
      l_sic_int
      i_separator
      i_internvalue.
      ELSE.
      IF l_sic_int > 0.
      i_internvalue i_line(l_sic_int).
      ENDIF.
      ENDIF.
      IF l_sic_int > 0.
      APPEND i_intern.
      ENDIF.
      l_sic_int l_sic_int + 1.
      i_line i_line+l_sic_int.
      ENDFORM.

      ...


      Structure: ZSTALSMEX_TABLINE


      SHEETNO –> INT2
      SHEETNAME –> CHAR10
      P_ROW –> KCD_EX_ROW_N
      P_COL –> KCD_EX_COL_N
      VALUE –> CHAR50