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.