top of page

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

Writer's picture: ERPGenieERPGenie

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.

2,924 views0 comments

Recent Posts

See All

Comments


bottom of page