Skip to Content
Technical Articles
Author's profile photo Former Member

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

Assigned Tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sandra Rossi
      Sandra Rossi

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

       

      Author's profile photo Former Member
      Former Member

      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 🙂

      Author's profile photo Michelle Crapo
      Michelle Crapo

      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.

      Author's profile photo Sandra Rossi
      Sandra Rossi

      and we couldn't thank enough abap2xlsx author Ivan Femia, and all contributors... 🙂

      Author's profile photo Michelle Crapo
      Michelle Crapo

      I agree.  I use it A LOT.  😉

      Author's profile photo Ivan Femia
      Ivan Femia

      Thanks Sandra Rossi for mentioning.

      I'm so happy that abap2xlsx is still a great help for many 🙂

      Author's profile photo Former Member
      Former Member

      Thanks.

      short feedback:

      The program doesn't work properly, when some columns in the excell is blank.

      Kind Regards

      Bahman

      Author's profile photo Philippe BERINGER
      Philippe BERINGER

      Hello Andrzej ,

      I tried to implement the Class in our SAP system.

      The method get_by_kind does not exist in standard class cl_abap_elemdescr ?

      type cl_abap_elemdescr=>get_by_kindp_type_kind lr_description->type_kind
      p_length    lr_description->length
      p_decimals  lr_description->decimals TO rt_components.

       

      Can you help me ?