How to Read an Uploaded Excel File in SAP ABAP
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.
Recommended Now
All-new Echo Dot Smart speaker with Alexa
This bundle includes Echo Dot (3rd Gen) Charcoal and Philips Hue White A19 Medium Lumen Smart Bulb, 1100 Lumens. Built in Smart Home hub. Ask Alexa to control Zigbee-compatible devices. No additional Philips Hue hub required. Two choices for easy smart lighting - Start setting the mood with Hue Smart bulbs and your Echo device, supporting up to 5 Hue Bluetooth & Zigbee smart bulbs. Add the Hue Hub for whole-home smart lighting (up to 50 light points) and bonus features.
Check it out on amazon.com →
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_returnuser_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.All Tags
ABAP ALV Authorization Business Function Classes Classification Control Framework CPM Email Field Symbols File Management HANA HANA Cloud Platform Learn SAP Netweaver Patterns PDF River RDE SAP Demo SAP ERP SAP IMG SAPUI5 Tables Transport System User Exit User Parameters User Status
SAP ERP Dictionary
SAP Transactions
SAP Customer Exits
SAP Tables
SAP Authorization Objects
SAP Authorization Object Classes
SAP Programs
SAP Function Groups
SAP Function Modules
SAP Message Classes
SAP Packages
SAP Search Helps
SAP Master Data
Access Control List
Catalog
Characteristic
Class
Delivery
Material Document
Notification
Purchasing Document
Sales Order
Selected Set
WBS Element
