In this post you will learn how to Read Excel File from Application Server. This solution covers reading the data from Excel File tabs as well.

Step 1 : Read the file from application server into xstring.

Important to note that the file should be read in binary mode.

DATA(lv_file) = 'path/file.xlsx'.
OPEN DATASET lv_file FOR INPUT IN BINARY MODE.
IF sy-subrc EQ 0.
  READ DATASET lv_file INTO lv_xls_xstr.
  IF sy-subrc NE 0.
    MESSAGE e002 WITH lv_file.
  ENDIF.
ELSE.
  MESSAGE e001 WITH lv_file.
ENDIF.
CLOSE DATASET lv_file.

Step 2 : Get the Excel Sheet names

CL_FDT_XL_SPREADSHEET class is used to get the sheet names and get the data from specific sheet.

"Create object for cl_fdt_xl_spreadsheet
DATA(lo_xls) = NEW cl_fdt_xl_spreadsheet( document_name = iv_file
                                          xdocument     = lv_xls_xstr ).

"Get work sheets
lo_xls->if_fdt_doc_spreadsheet~get_worksheet_names( 
  IMPORTING 
    worksheet_names = DATA(lt_sheets) 
).

Step 3 : Read the sheet data 

"Loop at sheets & get data using a method from the class
LOOP AT lt_sheets INTO DATA(ls_sheet).
  ir_data_ref = lo_xls->if_fdt_doc_spreadsheet~get_itab_from_worksheet( ls_sheet  ) .
  ASSIGN ir_data_ref->* TO FIELD-SYMBOL(<lfs_data_tab>).
  cl_demo_output=>display( <lfs_data_tab> ).
ENDLOOP.

This part of code will read each sheet and display with the demo output class.

It is important to note that

  • the column names from the table are A, B, C, D … and so on
  • the actual column header from the excel file are shown as the first line

This is a effective method which allows us to read Excel file. However, this comes at a risk. SAP suggests to use this class only within BRF+ framework and not outside. See the note below for the warning.

Note 2468709 – Usage of standard class CL_FDT_XL_SPREADSHEET

The solution to this issue is to use the ABAP2XLSX open source project. It can be found on github at below links.

https://github.com/abap2xlsx/abap2xlsx

Visit ABAP Code Sample : ABAP2XLSX project for tutorial on installing the project.

Visit ABAP Code Samples page for more code samples.

If you like the content, please subscribe…

Join 2,647 other followers