Menu

How to Read an Uploaded Excel File in SAP ABAP

2020-10-20

ABAP and the SAP NetWeaver Core has struggled for some time to offer modern functionality, such as interacting with MS Excel file formats. However, it is easily possible to read data from an Excel file in SAP ABAP. Here is how.

The key to reading Excel files in ABAP is the zif_excel_reader function in conjunction with the zcl_excel_reader_2007 class. Using these ABAP tools, you get access to a bunch of methods and functionality to easily read data from XLS and XLSX file formats - something that is very helpful when importing data into SAP from Excel or offering easy-to-use services to your SAP users.

ABAP program to read data from an Excel file

PARAMETERS pa_funcname TYPE file_table-filename OBLIGATORY.

* This selection screen event is fired when the F4 help for field pa_funcname is called. 
AT SELECTION-SCREEN ON VALUE-REQUEST FOR pa_funcname.
    DATA lv_return TYPE i.
    DATA lv_user_action TYPE i.
    DATA it_filetable TYPE filetable.

    * Clear file table. It might contain old entries from earlier calls.
    CLEAR it_filetable.

    * Call the "open file" dialog.
    TRY.
        cl_gui_frontend_services=>file_open_dialog(
            EXPORTING
                file_filter    = |xls (*.xls)\|*.xls\|{ cl_gui_frontend_services=>filetype_all }|
                multiselection = abap_false
            CHANGING
                file_table  = it_filetable
                rc          = lv_return
                user_action = lv_user_action
        ).

        IF lv_user_action EQ cl_gui_frontend_services=>action_ok.
            IF lines( it_filetable ) > 0.
                * Check the first entry
                pa_funcname = it_filetable[1]-filename.
            ENDIF.
        ENDIF.
    
    CATCH cx_root INTO DATA(e_error_message).
        MESSAGE e_error_message->get_text( ) TYPE 'I'.
    ENDTRY.

* When the report is submitted...
START-OF-SELECTION.
    TRY.  
        * Create a reader object
        DATA(lo_excel_reader) = CAST zif_excel_reader( NEW zcl_excel_reader_2007( ) ).
        DATA(lo_excel_file) = lo_excel_reader->load_file( pa_funcname ).

        * This reads the active worksheet from the excel file
        DATA(lo_active_sheet) = lo_excel_file->get_active_worksheet( ).

        * Output the content of the active sheet
        LOOP AT lo_active_sheet->sheet_content ASSIGNING FIELD-SYMBOL(<cell>) GROUP BY <cell>-cell_row ASSIGNING FIELD-SYMBOL(<row>).
            LOOP AT GROUP <row> ASSIGNING FIELD-SYMBOL(<cell_data>).
                * Here are some examples of the data you can get.
                WRITE: / <cell_data>-cell_coords,
                    <cell_data>-cell_value,
                    <cell_data>-cell_formula,
                    <cell_data>-data_type,
                    <cell_data>-cell_column,
                    <cell_data>-cell_row,
                    <cell_data>-cell_style.
            ENDLOOP.
        ENDLOOP.

    CATCH cx_root INTO DATA(e_error_message).
        WRITE: / e_error_message->get_text( ).
    ENDTRY.