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).
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
Nice work around to read multiple Excel Sheets 🙂
HI,
Good Work.... 🙂 ,
With Regards
Prakash Sivagnanam.
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.
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.
One question,
Where finish "the enddo" ??
All start with: "Do Sheets times" ... but Where it is finished?
Thnak you.
Regards.
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
*”———————————————————————-
DATA: excel_tab TYPE ty_t_sender.
DATA: ld_separator TYPE c.
DATA: application TYPE ole2_object,
workbook TYPE ole2_object,
sheetno TYPE i,
sheetname TYPE char10,
range TYPE ole2_object,
worksheet TYPE ole2_object.
DATA: h_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 sy–subrc.
WHEN 0.
WHEN 1.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4.
WHEN OTHERS. CALL METHOD OF application ‘QUIT’. RAISE upload_ole.
ENDCASE.
END-OF-DEFINITION.
* check parameters
IF i_begin_row > i_end_row. RAISE inconsistent_parameters. ENDIF.
IF i_begin_col > i_end_col. RAISE inconsistent_parameters. ENDIF.
* 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 application–header = space OR application–handle = –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 sy–subrc <> 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 sy–subrc <> 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.
DATA: l_sic_tabix LIKE sy–tabix,
l_sic_col TYPE kcd_ex_col.
DATA: l_fdpos LIKE sy–fdpos.
LOOP AT i_tab.
l_sic_tabix = sy–tabix.
l_sic_col = 0.
WHILE i_tab CA i_separator.
l_fdpos = sy–fdpos.
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_intern–sheetno = i_sheetno.
i_intern–sheetname = i_sheetname.
i_intern–p_row = l_sic_tabix.
i_intern–p_col = l_sic_col + 1.
i_intern–value = 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 sy–tabix
ch_cell_col TYPE kcd_ex_col
i_separator TYPE c
i_fdpos LIKE sy–fdpos
i_sheetno TYPE i
i_sheetname TYPE char10.
DATA: l_string TYPE ty_s_senderline.
DATA l_sic_int TYPE i.
CLEAR i_intern.
l_sic_int = i_fdpos.
i_intern–sheetno = i_sheetno.
i_intern–sheetname = i_sheetname.
i_intern–p_row = i_row.
l_string = i_line.
i_intern–p_col = ch_cell_col.
* csv Dateien mit separator in Zelle: –> ;”abc;cd”;
IF ( i_separator = ‘;’ OR i_separator = ‘,’ ) AND
l_string(1) = gc_esc.
PERFORM line_to_cell_esc_sep USING l_string
l_sic_int
i_separator
i_intern–value.
ELSE.
IF l_sic_int > 0.
i_intern–value = 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