TIP: Report to upload an Excel sheet to an internal table

I always find myself needing to upload an Excel in to internal table so here is a simple copy-and-paste for the next time that you need it...

REPORT zupload_excel_to_itab NO STANDARD PAGE HEADING.


*** Change the table type definition as needed

TYPES: BEGIN OF t_datatab ,

col1(25) TYPE c,

col2(30) TYPE c,

col3(30) TYPE c,

col4(30) TYPE c,

col5(30) TYPE c,

col6(30) TYPE c,

col7(30) TYPE c,

col8(30) TYPE c,

col9(30) TYPE c,

col10(30) TYPE c,

col11(30) TYPE c,

END OF t_datatab.


DATA: it_datatab TYPE STANDARD TABLE OF t_datatab INITIAL SIZE 0,

wa_datatab TYPE t_datatab,

gd_scol TYPE i VALUE '1',

gd_srow TYPE i VALUE '1',

gd_ecol TYPE i VALUE '256',

gd_erow TYPE i VALUE '65536',

it_tab TYPE filetable,

gd_subrc TYPE i.


*Selection screen definition

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

PARAMETERS: p_file LIKE rlgrap-filename

DEFAULT 'c:\test.xls' OBLIGATORY. " File Name

SELECTION-SCREEN END OF BLOCK b1.


***********************************************************************

* AT SELECTION-SCREEN

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

REFRESH: it_tab.

CALL METHOD cl_gui_frontend_services=>file_open_dialog

EXPORTING

window_title = 'Select File'

default_filename = '*.xls'

multiselection = ' '

CHANGING

file_table = it_tab

rc = gd_subrc.


LOOP AT it_tab INTO p_file.

* so_fpath-sign = 'I'.

* so_fpath-option = 'EQ'.

* append so_fpath.

ENDLOOP.


***********************************************************************

* START-OF-SELECTION.

START-OF-SELECTION.

PERFORM upload_excel_file TABLES it_datatab

USING p_file

gd_scol

gd_srow

gd_ecol

gd_erow.


***********************************************************************

* END-OF-SELECTION.

END-OF-SELECTION.

LOOP AT it_datatab INTO wa_datatab.

WRITE:/ wa_datatab-col1,

wa_datatab-col2,

wa_datatab-col3,

wa_datatab-col4,

wa_datatab-col5,

wa_datatab-col6,

wa_datatab-col7,

wa_datatab-col8,

wa_datatab-col9,

wa_datatab-col10,

wa_datatab-col11.

ENDLOOP.


*&--------------------------------------------------------------------*

*& Form UPLOAD_EXCEL_FILE

*&--------------------------------------------------------------------*

* upload excel spreadsheet into internal table

*---------------------------------------------------------------------*

* -->P_TABLE Table to return excel data into

* -->P_FILE file name and path

* -->P_SCOL start column

* -->P_SROW start row

* -->P_ECOL end column

* -->P_EROW end row

*---------------------------------------------------------------------*

FORM upload_excel_file TABLES p_table

USING p_file

p_scol

p_srow

p_ecol

p_erow.


DATA : lt_intern TYPE kcde_cells OCCURS 0 WITH HEADER LINE.


* Has the following format:

* Row number | Colum Number | Value

* ---------------------------------------

* i.e. 1 1 Name1

* 2 1 Joe

DATA : ld_index TYPE i.

FIELD-SYMBOLS : <fs>.


* Note: Alternative function module - 'ALSM_EXCEL_TO_INTERNAL_TABLE'

CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'

EXPORTING

filename = p_file

i_begin_col = p_scol

i_begin_row = p_srow

i_end_col = p_ecol

i_end_row = p_erow

TABLES

intern = lt_intern

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.


IF sy-subrc <> 0.

FORMAT COLOR COL_BACKGROUND INTENSIFIED.

WRITE:/ 'Error Uploading file'.

EXIT.

ENDIF.


IF lt_intern[] IS INITIAL.

FORMAT COLOR COL_BACKGROUND INTENSIFIED.

WRITE:/ 'No Data Uploaded'.

EXIT.

ELSE.

SORT lt_intern BY row col.

LOOP AT lt_intern.

MOVE lt_intern-col TO ld_index.

ASSIGN COMPONENT ld_index OF STRUCTURE p_table TO <fs>.

MOVE lt_intern-value TO <fs>.

AT END OF row.

APPEND p_table.

CLEAR p_table.

ENDAT.

ENDLOOP.

ENDIF.

ENDFORM. "UPLOAD_EXCEL_FILE

I use the class libraries to manipulate spreadsheets when I need to cater for multiple tabs. Ping me if you need an example on how to do that.
16 views

© 2020 by ERPGenie. All Rights Reserved