Upload excel / xlsx to internal table in ABAP

Tags: ABAP, SAP, Excel, SAP GUI

Business users often ask for applocations which start with an excel sheet upload as first step, due they can't or do not want to get rid working in excel. Such interfacing demands a lot of development effort, due all conversion exits must be implemented one by one manually, for each column of the excel, with exception handling. The excel formats do not play any role here, since excel users often do not put a right format on the cells, so you cannot expect a number is stored as a number in right format etc. OK, let's do it, put some logic in a local helper class lcl_helper within an include you create and use in your report in advance. 

Prerequisites: ABAP 7.4 SP8+, due LOOP AT GROUP on internal table. This you can replace on lower NW versions to a standard loop with some criteriion.

Step - 0 - Define and implement your initial local class

After you created an empty inlcude successfully, add the below lines of local class definition. Later add this include in your program, before the selection screen definition.

CLASS lcl_helper DEFINITION.
PUBLIC SECTION.
ENDCLASS.

CLASS lcl_helper IMPLEMENTATION.
ENDCLASS.

Step 1 - Let user browse a file on the client computer

This is most probably a well known part for you. You listen to the value help event of your selection screen parameter for the file path:

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
p_file = lcl_helper=>browse_file( ).

Definition

 "! Browse frontend for a file
"! @parameter r_file_path | Client file path
CLASS-METHODS browse_file
RETURNING VALUE(r_file_path) TYPE string.

Implementation

METHOD browse_file.
DATA:
rc TYPE i,
file_table TYPE filetable.

cl_gui_frontend_services=>file_open_dialog(
EXPORTING
default_extension = '.xlsx'
file_filter = '*.xlsx'
multiselection = abap_false
CHANGING
file_table = file_table
rc = rc "Return Code, Number of Files or -1 If Error Occurred
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
not_supported_by_gui = 4
OTHERS = 5
).
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

IF ( rc <> 1 ).
RETURN.
ENDIF.

r_file_path = file_table[ 1 ]-filename.
ENDMETHOD.

Step 2 - Uplaod the file from the client to the ABAP server memory

Having the client file path of the excel document in hand, the journey continues. We need to have an xstring to be able to use the ABAP2XLSX library afterwards.

Definition

 "! Upload binary file from client
"! @parameter i_path | Client file path
"! @parameter e_xlsx | Excel file
"! @parameter e_failed | Upload error
"! @parameter e_messages | Message Table
CLASS-METHODS load_xlsx_from_client
IMPORTING
!i_path TYPE csequence
EXPORTING
!e_xlsx TYPE xstring
!e_failed TYPE abap_bool.

Implementation

METHOD load_xlsx_from_client.
DATA:
data_table TYPE STANDARD TABLE OF x255.

CLEAR: e_xlsx, e_failed.

cl_gui_frontend_services=>gui_upload(
EXPORTING
filename = CONV #( i_path )
filetype = 'BIN'
IMPORTING
filelength = DATA(file_length)
CHANGING
data_tab = data_table
EXCEPTIONS
file_open_error = 1
file_read_error = 2
no_batch = 3
gui_refuse_filetransfer = 4
invalid_type = 5
no_authority = 6
unknown_error = 7
bad_data_format = 8
header_not_allowed = 9
separator_not_allowed = 10
header_too_long = 11
unknown_dp_error = 12
access_denied = 13
dp_out_of_memory = 14
disk_full = 15
dp_timeout = 16
not_supported_by_gui = 17
error_no_gui = 18
OTHERS = 19
).
IF sy-subrc <> 0.
e_failed = abap_true.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
RETURN.
ENDIF.

CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
EXPORTING
input_length = file_length
IMPORTING
buffer = e_xlsx
TABLES
binary_tab = data_table
EXCEPTIONS
failed = 1
OTHERS = 2.

IF sy-subrc <> 0.
e_failed = abap_true.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
RETURN.
ENDIF.

ENDMETHOD.

Step 3 - Convert xstring to internal table using ABAP2XLSX

The last step is to convert the lines of the first worksheet of the excel document. For this we use the ABAP2XLSX library. 

Definition

 "! Extract xlsx worksheet records and convert to DDIC type internal table
" @parameter i_xlsx | excel document
"! @parameter e_items | return table
"! @parameter e_failed | conversion error
"! @parameter e_messages | messages
CLASS-METHODS convert_xlsx_to_itab
IMPORTING
!i_xlsx TYPE xstring
EXPORTING
!e_items TYPE YOUR_INTERNAL_TABLE_TYPE
!e_failed TYPE abap_bool
!e_messages TYPE esp1_message_tab_type.

Implementation

METHOD convert_xlsx_to_itab.
DATA:
item TYPE YOUR_DDIC_STRUCTURE.

CLEAR:
e_items, e_failed, e_messages.

DATA(xlsx_reader) = NEW zcl_excel_reader_2007( ).

TRY.
DATA(excel) = xlsx_reader->zif_excel_reader~load( i_excel2007 = i_xlsx ).
CATCH zcx_excel.
e_failed = abap_true.
APPEND VALUE #(
msgid = 'YOUR_MESSAGE_CLASS'
msgty = 'E'
msgno = 'YOUR_MESSAGE_NO'
) TO e_messages.
RETURN.
ENDTRY.

"Get first worksheet of the document
DATA(items_worksheet) = CAST zcl_excel_worksheet( excel->get_worksheets_iterator( )->get_next( ) ).

"Iterate over the cell series, and build internal table lines
LOOP AT items_worksheet->sheet_content INTO DATA(cell) GROUP BY cell-cell_row ASSIGNING FIELD-SYMBOL(<row>).
LOOP AT GROUP <row> INTO DATA(cell_data).
IF cell_data-cell_row = 1. "You need this in case the first row on the worksheet is the column header
EXIT.
ENDIF.
IF cell_data-cell_column = 1 AND cell_data-cell_value IS INITIAL. "Reached an empty line (assumed the first column is alwasys filled in the excel)
"or get last row using the abap2xlsx library method, or check on more cells
EXIT.
ENDIF.
CASE cell_data-cell_column.
WHEN 1. "Your first column is for example a character like field
item-first_field = CONV your_first_ddic_structure_field_type( cell_data-cell_value ).
WHEN 2. "Your second column, for example an amount field
TRY.
item-second_field = cell_data-cell_value.
CATCH cx_root INTO DATA(exception).
DATA(row_num) = |{ cell_data-cell_row }|.
CONDENSE row_num NO-GAPS.
APPEND VALUE #(
msgid = 'YOUR_MESSAGE_CLASS'
msgno = 'YOUR_MESSAGE_NO'
msgty = 'E'
msgv1 = row_num
msgv2 = cell_data-cell_value
msgv3 = exception->get_text( )
lineno = row_num
) TO e_messages ASSIGNING FIELD-SYMBOL(<message>).
e_failed = abap_true.
RETURN.
ENDTRY.
WHEN LAST_COLUMN_ID. "Last column reached
item-last_column = cell_data-cell_value.
"Add line to result set
APPEND item TO e_items.
ENDCASE.
ENDLOOP.
ENDLOOP.
ENDMETHOD.