Skip to Content
Technical Articles

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 :>.

6 Comments
You must be Logged on to comment or reply to a post.
  • Thanks. I see that your tool is based on ALSM_EXCEL_TO_INTERNAL_TABLE, so I advise to make your tool even more powerful by using abap2xlsx instead.

    ALSM_EXCEL_TO_INTERNAL_TABLE has important restrictions as explained in the note 933420 – ALSM_EXCEL_TO_INTERNAL_TABLE:

    • The module is not released for customers
    • The module is only available in a SAP_APPL system
    • The output structure is limited to 9999 rows and columns
    • The output structure is limited to 50 characters per cell

    Also, it can’t run in background, and is rather slow (based on OLE).

    All of these drawbacks don’t exist abap2xlsx. The only drawback of abap2xlsx is that it works with xlsx only, but this format is used for a long time now (since 2007).

     

    • Hey Sandra,
      thank you for your insight, I will investigate abap2xlsx for sure.
      It’s just my small idea how to make it different and maybe easier to upload an excel as I have never seen such approach 🙂

  • It’s always nice to see a different way to do things.   It’s also great that abap2xlsx is highlighted again.  There is so much functionality in that tool.

    Thank you for sharing!  As always it generated some good comments already.  Sandra’s above.