Easy Excel upload to internal table – ZCL_EXCEL_UPLOADER

I’m sure you have faced a task of building an application with excel upload at some point. I have faced it dozen of times and every time it was quite a deal. Finally I decided to design something reusable and share it with you.

Class ZCL_EXCEL_UPLOADER

The class allows you to quickly upload your excel into internal table and display (if you want to :>).

You can download it from my -> github <-

There you will find class zcl_excel_uploader.abap with class itself, report z_excelupload.abap with sample application and excel_uploader_sample.xlsx.

How to use

Excel

In order to make everything easy structure of internal table is stored in excel:

1. First row contains name of field in created structure
2. Second row contains data element for this field
3. Third row contains description (just for user experience, haha )

Class

In constructor pass following parameters:

• iv_filename – path to Excel file – in example program I use method cl_gui_frontend_services=>file_open_dialog to get a path
• iv_last_column_number – number of columns in Excel (4 in example)
• iv_number_of_rows – number of ALL rows (including header) (5 in example)
• iv_first_data_row – number of first row with actual data (4 in example)
• iv_names_row – row number where fieldnames are stored (1 in example)
• iv_dataelements_row – row number where data element names are stored (2 in example)

As you can see you get some flexibility with regards to structure of your excel.

Example:

DATA(lo_excel_uploader) = NEW zcl_excel_uploader( iv_filename           = 'C:\excel.xlsx'
                                                  iv_last_column_number = 4
                                                  iv_number_of_rows     = 5
                                                  iv_first_data_row     = 4
                                                  iv_names_row          = 1
                                                  iv_dataelements_row   = 2 ).

Next, you will get access to public table mt_data. As you have noticed table is being generated dynamically, so standard way of accessing fields will not work. You need to use field-symbols:

FIELD-SYMBOLS: <lt_data> TYPE ANY TABLE,
               <ls_data> TYPE any.
               
ASSIGN lo_excel_uploader->mt_data->* TO <lt_data>.

Reading table:

DATA(lv_field_key) = 'ID'.
READ TABLE <lt_data> ASSIGNING <ls_data> WITH KEY (lv_field_key) = '1'.

Looping table:

LOOP AT <lt_data> ASSIGNING FIELD-SYMBOL(<ls_data>).
  ASSIGN COMPONENT 'ID' OF STRUCTURE <ls_data> TO FIELD-SYMBOL(<lv_id>).
ENDLOOP.

You can also use method display( ) to quickly check if everything is fine:

lo_excel_uploader->display( ).

Text for columns are derived from data elements dynamically.

Final word

For sure approach presented here is not perfect. In many cases you will want to build your structure and field catalog from scratch with your bare fingers. However I believe my solution will make at least some of the work easier. Don’t hesitate to modify the solution, in most cases it will serve as skeleton for further development probably :>.