TIP: Upload an Excel file into an internal table and display in ALV format

The following sample code can be used to upload an excel file in to an internal table and have the results displayed in a simple ALV List Display.

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

*& Report ZSAMPLE

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

*& AUTHOR: Kevin Wilson

* Sample Spreadsheet

* COL1 COL2 COL3

* ROW1: 10 Data in Row 1 colum 2 Data in Row 1 colum 3

* ROW2: 20 Data in Row 2 colum 2 Data in Row 2 colum 3

* ROW3: 30 Data in Row 3 colum 2 Data in Row 3 colum 3

* ROW4: 40 Data in Row 4 colum 2 Data in Row 4 colum 3

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

REPORT zsample_excel_upload.

*** Data Declarations ***

TYPE-POOLS: slis,

ole2.

* zalsmex_tabline is a structure with 3 fields:

* ROW         Types        KCD_EX_ROW_N

* COL         Types        KCD_EX_COL_N

* VALUE      Types        CHAR50

TYPES: ty_d_itabvalue TYPE zalsmex_tabline-value,

* internal table containing the excel data

ty_t_itab TYPE STANDARD TABLE OF zalsmex_tabline,

* line type of sender table

BEGIN OF ty_s_senderline,

line(4096) TYPE c,

END OF ty_s_senderline,

* sender table

ty_t_sender TYPE STANDARD TABLE OF ty_s_senderline.

CONSTANTS: gc_esc VALUE '"'.

TYPES: BEGIN OF lty_table,

col1 TYPE ztable-col1,

col2 TYPE ztable-col2,

col3 TYPE ztable-col3,

END OF lty_table.

DATA:

lt_fieldcatalog TYPE slis_t_fieldcat_alv WITH HEADER LINE,

ls_gd_layout TYPE slis_layout_alv,

lt_itab_table TYPE STANDARD TABLE OF lty_table,

ls_itab_table TYPE lty_table.

DATA:

excel_tab TYPE ty_t_sender,

intern TYPE STANDARD TABLE OF zalsmex_tabline,

ls_excel_tab TYPE ty_s_senderline,

ls_string TYPE string,

ld_separator TYPE c,

application TYPE ole2_object,

workbook TYPE ole2_object,

range TYPE ole2_object,

worksheet TYPE ole2_object,

h_cell TYPE ole2_object,

h_cell1 TYPE ole2_object,

ld_rc TYPE i,

lv_filename TYPE rlgrap-filename.

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.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDCASE.

END-OF-DEFINITION.

PARAMETERS:

p_file TYPE rlgrap-filename OBLIGATORY, "Filename

p_row_st TYPE i DEFAULT 1 OBLIGATORY, "1st Row

p_row_en TYPE i DEFAULT 100 OBLIGATORY, "Last Row

p_col_st TYPE i DEFAULT 1 OBLIGATORY, "1st Column

p_col_en TYPE i DEFAULT 3 OBLIGATORY. "Last Column

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

program_name = syst-repid

dynpro_number = syst-dynnr

CHANGING

file_name = p_file

EXCEPTIONS

mask_too_long = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE e999(b1) WITH 'Issue with getting filename:' sy-subrc.

ENDIF.

START-OF-SELECTION.

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

lv_filename = p_file.

CALL METHOD OF workbook 'Open' EXPORTING #1 = lv_filename.

m_message.

GET PROPERTY OF application 'ACTIVESHEET' = worksheet.

m_message.

* Get top left cell

CALL METHOD OF worksheet 'Cells' = h_cell

EXPORTING #1 = p_row_st #2 = p_col_st.

m_message.

* Get bottom right cell

CALL METHOD OF worksheet 'Cells' = h_cell1

EXPORTING #1 = p_row_en #2 = p_col_en.

m_message.

* Mark range from top left to bottom right

CALL METHOD OF worksheet 'RANGE' = range

EXPORTING #1 = h_cell #2 = h_cell1.

m_message.

* Select range

CALL METHOD OF range 'SELECT'.

m_message.

* copy range into Clipboard

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 e999(b1) WITH 'Issue importing clipboard:' sy-subrc.

ENDIF.

PERFORM convert_excel TABLES excel_tab intern

USING ld_separator.

IF excel_tab[] IS NOT INITIAL.

CLEAR: lt_itab_table[].

LOOP AT excel_tab INTO ls_excel_tab.

ls_string = ls_excel_tab.

SPLIT ls_string AT ld_separator

INTO ls_itab_table-col1 ls_itab_table-col2 ls_itab_table-col3.

"Hard-coded for easy reading - Make dynamic

APPEND ls_itab_table TO lt_itab_table.

CLEAR: ls_itab_table.

ENDLOOP.

ENDIF.

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

END-OF-SELECTION.

*** Main program

IF lt_itab_table IS NOT INITIAL.

REFRESH lt_fieldcatalog.

CLEAR lt_fieldcatalog.

lt_fieldcatalog-fieldname = 'Col1'.

lt_fieldcatalog-seltext_m = 'Column 1'.

lt_fieldcatalog-col_pos = 1.

APPEND lt_fieldcatalog.

CLEAR lt_fieldcatalog.

lt_fieldcatalog-fieldname = 'Col2'.

lt_fieldcatalog-seltext_m = 'Column 2'.

lt_fieldcatalog-col_pos = 2.

APPEND lt_fieldcatalog.

CLEAR lt_fieldcatalog.

lt_fieldcatalog-fieldname = 'Col3'.

lt_fieldcatalog-seltext_m = 'Column 3'.

lt_fieldcatalog-col_pos = 3.

APPEND lt_fieldcatalog.

CLEAR lt_fieldcatalog.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = sy-repid

is_layout = ls_gd_layout

it_fieldcat = lt_fieldcatalog[]

TABLES

t_outtab = lt_itab_table

EXCEPTIONS

program_error = 1

OTHERS = 2.

IF sy-subrc NE 0.

WRITE:/ sy-subrc.

ENDIF.

* quit excel and free ABAP Object - unfortunately, this does not kill the excel process

CALL METHOD OF application 'QUIT'.

m_message.

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

ENDIF.

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

*& Form CONVERT_EXCEL

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

FORM convert_excel TABLES i_tab TYPE ty_t_sender

i_intern TYPE ty_t_itab

USING i_separator TYPE c.

DATA:

l_sic_tabix LIKE sy-tabix,

l_sic_col TYPE kcd_ex_col,

l_fdpos LIKE sy-fdpos.

REFRESH i_intern.

LOOP AT i_tab.

l_sic_tabix = sy-tabix.

l_sic_col = 0.

WHILE i_tab CA i_separator. "#EC CI_NESTED

l_fdpos = sy-fdpos.

l_sic_col = l_sic_col + 1.

PERFORM line_to_cell_separat TABLES i_intern

USING i_tab l_sic_tabix l_sic_col

i_separator l_fdpos.

ENDWHILE.

IF i_tab <> space.

CLEAR i_intern.

i_intern-row = l_sic_tabix.

i_intern-col = l_sic_col + 1.

i_intern-value = i_tab.

APPEND i_intern.

ENDIF.

ENDLOOP.

ENDFORM.

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

FORM line_to_cell_separat TABLES i_intern TYPE ty_t_itab

USING i_line

i_row LIKE sy-tabix

ch_cell_col TYPE kcd_ex_col

i_separator TYPE c

i_fdpos LIKE sy-fdpos.

DATA: l_string TYPE ty_s_senderline,

l_sic_int TYPE i.

CLEAR i_intern.

l_sic_int = i_fdpos.

i_intern-row = i_row.

l_string = i_line.

i_intern-col = ch_cell_col.

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.

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

FORM line_to_cell_esc_sep USING i_string

i_sic_int TYPE i

i_separator TYPE c

i_intern_value TYPE ty_d_itabvalue.

DATA:

l_int TYPE i,

l_cell_end(2).

FIELD-SYMBOLS: <l_cell>.

l_cell_end = gc_esc.

l_cell_end+1 = i_separator .

IF i_string CS gc_esc.

i_string = i_string+1.

IF i_string CS l_cell_end.

l_int = sy-fdpos.

ASSIGN i_string(l_int) TO <l_cell>.

i_intern_value = <l_cell>.

l_int = l_int + 2.

i_sic_int = l_int.

i_string = i_string+l_int.

ELSEIF i_string CS gc_esc.

l_int = sy-fdpos.

ASSIGN i_string(l_int) TO <l_cell>.

i_intern_value = <l_cell>.

l_int = l_int + 1.

i_sic_int = l_int.

i_string = i_string+l_int.

l_int = strlen( i_string ).

IF l_int > 0 .

MESSAGE x001(zapo) .

ENDIF.

ELSE.

MESSAGE x001(zapo) .

ENDIF.

ENDIF.

ENDFORM.