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:
- First row contains name of field in created structure
- Second row contains data element for this field
- 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 :>.
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:
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.
and we couldn't thank enough abap2xlsx author Ivan Femia, and all contributors... 🙂
I agree. I use it A LOT. 😉
Thanks Sandra Rossi for mentioning.
I'm so happy that abap2xlsx is still a great help for many 🙂
Thanks.
short feedback:
The program doesn't work properly, when some columns in the excell is blank.
Kind Regards
Bahman
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_kind( p_type_kind = lr_description->type_kind
p_length = lr_description->length
p_decimals = lr_description->decimals ) ) TO rt_components.
Can you help me ?