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.
46 views