Upload data from an Excel file

Class ZCL_CONVERT_EXCEL_TO_ITAB

class ZCL_CONVERT_EXCEL_TO_ITAB definition
public
final
create public .

public section.

class-methods GET_ITAB
importing
!FILENAME type STRING
!WORKSHEET type STRING optional
!BEGIN_COL type I default 1
!BEGIN_ROW type I default 1
changing
!ITAB type ANY TABLE
exceptions
FILE_OPEN_ERROR
FILE_READ_ERROR
NO_BATCH
GUI_REFUSE_FILETRANSFER
INVALID_TYPE
NO_AUTHORITY
UNKNOWN_ERROR
BAD_DATA_FORMAT
HEADER_NOT_ALLOWED
SEPARATOR_NOT_ALLOWED
HEADER_TOO_LONG
UNKNOWN_DP_ERROR
ACCESS_DENIED
DP_OUT_OF_MEMORY
DISK_FULL
DP_TIMEOUT
OTHERS_EXC
CONV_BIN_TO_XSTRING_FAILED
SHEET_NOT_FOUND
LOAD_DOCUMENT_FAILED .
class-methods GET_FILENAME
returning
value(FILENAME) type STRING .
class-methods GET_FILE_TEXT
returning
value(FILENAME) type STRING .
class-methods GET_FILE_EXCEL
returning
value(FILENAME) type STRING .

private section.

types:
t_worksheet_names TYPE STANDARD TABLE OF string .

class-methods GET_ACTIVE_SHEET
importing
!XDOCUMENT type XSTRING
!WORKSHEETS type T_WORKSHEET_NAMES
returning
value(ACTIVESHEET) type STRING
exceptions
LOAD_DOCUMENT_FAILED .

GET_ITAB Static Method Public Transfer data from Excel worksheet to Internal Table
GET_FILENAME Static Method Public
GET_FILE_TEXT Static Method Public
GET_FILE_EXCEL Static Method Public
GET_ACTIVE_SHEET Static Method Private Get activesheet (opening sheet)

GET_ITAB

FILENAME Importing Type STRING
WORKSHEET Importing Type STRING
BEGIN_COL Importing Type I 1
BEGIN_ROW Importing Type I 1
ITAB Changing Type ANY TABLE

FILE_OPEN_ERROR
FILE_READ_ERROR
NO_BATCH
GUI_REFUSE_FILETRANSFER
INVALID_TYPE
NO_AUTHORITY
UNKNOWN_ERROR
BAD_DATA_FORMAT
HEADER_NOT_ALLOWED
SEPARATOR_NOT_ALLOWED
HEADER_TOO_LONG
UNKNOWN_DP_ERROR
ACCESS_DENIED
DP_OUT_OF_MEMORY
DISK_FULL
DP_TIMEOUT
OTHERS_EXC
CONV_BIN_TO_XSTRING_FAILED
SHEET_NOT_FOUND
LOAD_DOCUMENT_FAILED

METHOD get_itab.
DATA:
lt_data_tab     TYPE solix_tab,
lw_xdocument    TYPE xstring,
lw_filelength   TYPE i,
lo_itab         TYPE REF TO data,
lo_excel_ref    TYPE REF TO cl_fdt_xl_spreadsheet,
lo_table_descr  TYPE REF TO cl_abap_tabledescr,
lo_struct_descr TYPE REF TO cl_abap_structdescr,
lw_filename     TYPE char255,
lw_ext          TYPE char10.

FIELD-SYMBOLS:
<ft_data> TYPE STANDARD TABLE,
<fs_itab> TYPE any.

lw_filename = filename.

CALL FUNCTION ‘TRINT_FILE_GET_EXTENSION’
EXPORTING
filename = lw_filename
* UPPERCASE = ‘X’
IMPORTING
extension = lw_ext.

IF lw_ext = ‘XLS’.
MESSAGE ‘XLS format cannot be used for this function, please try converting to XLSX.’ TYPE ‘E’.
RETURN.
ENDIF.

CALL FUNCTION ‘GUI_UPLOAD’
EXPORTING
filename                = filename
filetype                = ‘BIN’
IMPORTING
filelength              = lw_filelength
header                  = lw_xdocument
TABLES
data_tab                = lt_data_tab
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
OTHERS                  = 17.
IF sy–subrc <> 0.
CASE sy–subrc.
WHEN 1.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING file_open_error.
WHEN 2.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING file_read_error.
WHEN 3.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING no_batch.
WHEN 4.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING gui_refuse_filetransfer.
WHEN 5.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING invalid_type.
WHEN 6.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING no_authority.
WHEN 7.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING unknown_error.
WHEN 8.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING bad_data_format.
WHEN 9.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING header_not_allowed.
WHEN 10.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING separator_not_allowed.
WHEN 11.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING header_too_long.
WHEN 12.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING unknown_dp_error.
WHEN 13.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING access_denied.
WHEN 14.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING dp_out_of_memory.
WHEN 15.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING disk_full.
WHEN 16.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING dp_timeout.
WHEN OTHERS.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING others_exc.
ENDCASE.
ENDIF.

“Convert binary data to xstring
CALL FUNCTION ‘SCMS_BINARY_TO_XSTRING’
EXPORTING
input_length = lw_filelength
IMPORTING
buffer       = lw_xdocument
TABLES
binary_tab   = lt_data_tab
EXCEPTIONS
failed       = 1
OTHERS       = 2.
IF sy–subrc <> 0.
CASE sy–subrc.
WHEN 1.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING conv_bin_to_xstring_failed.
WHEN OTHERS.
MESSAGE ID sy–msgid TYPE sy–msgty NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4
RAISING others_exc.
ENDCASE.
ENDIF.

lo_excel_ref = NEW cl_fdt_xl_spreadsheet(
document_name = filename
xdocument = lw_xdocument ) .

“Get List of Worksheets
lo_excel_ref->if_fdt_doc_spreadsheet~get_worksheet_names(
IMPORTING
worksheet_names = DATA(lt_worksheets) ).

IF NOT lt_worksheets IS INITIAL.
IF worksheet IS INITIAL.
“Get Active Tab
*        get_active_sheet(
*          EXPORTING
*            xdocument   = lw_xdocument
*            worksheets  = lt_worksheets
*          RECEIVING
*            activesheet = DATA(lw_woksheetname)
*          EXCEPTIONS
*            load_document_failed = 1
*            OTHERS               = 2
*        ).
*        IF sy-subrc <> 0.
*          MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
*                   WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
*          RAISING load_document_failed.
*        ENDIF.
*Replaced on 07.10.2021
DATA(lo_xml_document) = NEW cl_xml_document( ).
lo_xml_document->parse_xstring( lo_excel_ref->if_fdt_doc_pkg~get_file_as_xstring( ‘xl/workbook.xml’ ) ).      “load workbook xml
DATA(lw_activetab) = lo_xml_document->get_node_attribute( node = lo_xml_document->find_node( ‘workbookView’ ) “get node attribute
name = ‘activeTab’).
IF lw_activetab IS INITIAL.
DATA(lw_woksheetname) = VALUE #( lt_worksheets[ 1 ] OPTIONAL ).
ELSE.
ADD 1 TO lw_activetab.
lw_woksheetname = VALUE #( lt_worksheets[ lw_activetab ] OPTIONAL ).
ENDIF.
*Replacement end.
ELSE.
READ TABLE lt_worksheets INTO lw_woksheetname
WITH KEY table_line = worksheet.
IF sy–subrc <> 0.
MESSAGE ‘Sheet not found’ TYPE ‘E’
RAISING sheet_not_found.
ENDIF.
ENDIF.

IF lw_woksheetname IS NOT INITIAL.
DATA(lo_data_ref) = lo_excel_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet(
lw_woksheetname ).
“Now you have excel work sheet data in dyanmic internal table
ASSIGN lo_data_ref->* TO <ft_data>.
ELSE.
MESSAGE ‘Sheet not found’ TYPE ‘E’
RAISING sheet_not_found.
ENDIF.
ENDIF.

CREATE DATA lo_itab LIKE LINE OF itab.
ASSIGN lo_itab->* TO <fs_itab>.

“Transfer data from Excel work sheet to ITAB
IF <ft_data> IS ASSIGNED AND <fs_itab> IS ASSIGNED.
lo_table_descr ?= cl_abap_tabledescr=>describe_by_data( p_data = itab ).
lo_struct_descr ?= lo_table_descr->get_table_line_type( ).
DATA(lw_num_of_col) = lines( lo_struct_descr->components ).

LOOP AT <ft_data> ASSIGNING FIELD–SYMBOL(<fs_data>) FROM begin_row.
DATA(lw_index_itab) = 1.

DO lw_num_of_col TIMES.
IF sy–index < begin_col.
CONTINUE.
ENDIF.

ASSIGN COMPONENT sy–index OF STRUCTURE <fs_data> TO FIELD–SYMBOL(<fw_data>).
ASSIGN COMPONENT lw_index_itab OF STRUCTURE <fs_itab> TO FIELD–SYMBOL(<fw_itab>).
IF <fw_data> IS ASSIGNED AND
<fw_itab> IS ASSIGNED.
<fw_itab> = <fw_data>.

UNASSIGN:
<fw_data>,
<fw_itab>.
ENDIF.

ADD 1 TO lw_index_itab.
ENDDO .

INSERT <fs_itab> INTO TABLE itab.
CLEAR <fs_itab>.
ENDLOOP.
ELSE.
MESSAGE ‘There is an error in converting the excel file’ TYPE ‘E’
RAISING sheet_not_found.
ENDIF.
ENDMETHOD.

GET_FILENAME

METHOD get_filename.
DATA __t_filetab    TYPE filetable.
DATA __rc           TYPE i.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title            = ‘Select File’
“default_filename        =
file_filter             = ‘(*.xlsx)|*.xlsx|(*.txt)|*.txt’
multiselection          = ‘ ‘
CHANGING
file_table              = __t_filetab
rc                      = __rc
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error              = 2
error_no_gui            = 3
not_supported_by_gui    = 4
OTHERS                  = 5.
IF  sy–subrc NE 0.
MESSAGE ID sy–msgid TYPE ‘E’ NUMBER sy–msgno DISPLAY LIKE ‘S’.
ENDIF.READ TABLE __t_filetab INTO DATA(__file) INDEX 1.
IF  sy–subrc EQ 0.
filename = __file–filename.
ENDIF.
ENDMETHOD.

GET_FILE_TEXT

METHOD get_file_text.
DATA __t_filetab    TYPE filetable.
DATA __rc           TYPE i.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title            = ‘Select File’
“default_filename        =
file_filter             = ‘(*.txt)|*.txt’
multiselection          = ‘ ‘
CHANGING
file_table              = __t_filetab
rc                      = __rc
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error              = 2
error_no_gui            = 3
not_supported_by_gui    = 4
OTHERS                  = 5.
IF  sy–subrc NE 0.
MESSAGE ID sy–msgid TYPE ‘E’ NUMBER sy–msgno DISPLAY LIKE ‘S’.
ENDIF.READ TABLE __t_filetab INTO DATA(__file) INDEX 1.
IF  sy–subrc EQ 0.
filename = __file–filename.
ENDIF.
ENDMETHOD.

GET_FILE_EXCEL

method GET_FILE_EXCEL.
DATA __t_filetab    TYPE filetable.
DATA __rc           TYPE i.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title            = ‘Select File’
“default_filename        =
file_filter             = ‘(*.xlsx)|*.xlsx’
multiselection          = ‘ ‘
CHANGING
file_table              = __t_filetab
rc                      = __rc
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error              = 2
error_no_gui            = 3
not_supported_by_gui    = 4
OTHERS                  = 5.
IF  sy–subrc NE 0.
MESSAGE ID sy–msgid TYPE ‘E’ NUMBER sy–msgno DISPLAY LIKE ‘S’.
ENDIF.READ TABLE __t_filetab INTO DATA(__file) INDEX 1.
IF  sy–subrc EQ 0.
filename = __file–filename.
ENDIF.
endmethod.

GET_ACTIVE_SHEET

METHOD get_active_sheet.
DATA:
lo_xlsx_document TYPE REF TO cl_xlsx_document.TRY.
lo_xlsx_document = cl_xlsx_document=>load_document( iv_data = xdocument ).
CATCH cx_root INTO DATA(msg).
MESSAGE msg->get_text( ) TYPE ‘E’
RAISING load_document_failed.
ENDTRY.DATA(lo_zip) = NEW cl_abap_zip( ).
lo_zip->load( xdocument ).DATA(ls_files) = VALUE #( lo_zip->files[ name = ‘xl/workbook.xml’ ] OPTIONAL ).
IF ls_files IS NOT INITIAL.
lo_zip->get( EXPORTING name = ls_files–name
IMPORTING content = DATA(ls_workbook_xml) ).DATA(lo_xml_document) = NEW cl_xml_document( ).
lo_xml_document->parse_xstring( stream = ls_workbook_xml ).
DATA(lw_workbookview) = lo_xml_document->find_node( ‘workbookView’ ).
DATA(lw_activetab) = lo_xml_document->get_node_attribute(
node = lw_workbookview
name = ‘activeTab’
).IF lw_activetab IS INITIAL.
activesheet = VALUE #( worksheets[ 1 ] OPTIONAL ).
ELSE.
ADD 1 TO lw_activetab.
activesheet = VALUE #( worksheets[ lw_activetab ] OPTIONAL ).
ENDIF.
ENDIF.
ENDMETHOD.

Usage:

DATA:
BEGIN OF gt_excel_data OCCURS 0,
col01 TYPE char100,
col02 TYPE char100,
col03 TYPE char100,
col04 TYPE char100,
col05 TYPE char100,
col06 TYPE char100,
col07 TYPE char100,
col08 TYPE char100,
col09 TYPE char100,
col10 TYPE char100,
col11 TYPE char100,
col12 TYPE char100,
col13 TYPE char100,
col14 TYPE char100,
col15 TYPE char100,
col16 TYPE char100,
col17 TYPE char100,
col18 TYPE char100,
col19 TYPE char100,
col20 TYPE char100,
col21 TYPE char100,
col22 TYPE char100,
col23 TYPE char100,
col24 TYPE char100,
col25 TYPE char100,
col26 TYPE char100,
col27 TYPE char100,
col28 TYPE char100,
col29 TYPE char100,
col30 TYPE char100,
col31 TYPE char100,
col32 TYPE char100,
col33 TYPE char100,
col34 TYPE char100,
col35 TYPE char100,
col36 TYPE char100,
col37 TYPE char100,
col38 TYPE char100,
col39 TYPE char100,
col40 TYPE char100,
col41 TYPE char100,
col42 TYPE char100,
col43 TYPE char100,
col44 TYPE char100,
col45 TYPE char100,
col46 TYPE char100,
col47 TYPE char100,
col48 TYPE char100,
col49 TYPE char100,
col50 TYPE char100,
col51 TYPE char100,
col52 TYPE char100,
col53 TYPE char100,
col54 TYPE char100,
col55 TYPE char100,
col56 TYPE char100,
col57 TYPE char100,
col58 TYPE char100,
col59 TYPE char100,
col60 TYPE char100,
col61 TYPE char100,
col62 TYPE char100,
col63 TYPE char100,
col64 TYPE char100,
col65 TYPE char100,
col66 TYPE char100,
col67 TYPE char100,
col68 TYPE char100,
col69 TYPE char100,
col70 TYPE char100,
END OF gt_excel_data,TYPES: gty_excel_data LIKE gt_excel_data.
DATA gt_excel_data_tmp TYPE STANDARD TABLE OF gty_excel_data.DATA:
gv_sheet_name     TYPE soi_field_name.CLEAR: gv_sheet_name.CASE ‘X’.
WHEN p_store.
gv_sheet_name = ‘Plant’.
WHEN p_char.
gv_sheet_name = ‘Characteristic’.
WHEN OTHERS.
ENDCASE.CONCATENATE ‘FILE://’ p_file INTO gw_document_url.
DATA:
lv_inplace  TYPE c VALUE ‘X’,
lv_readonly,
lv_file     TYPE string,
lv_sheet    TYPE string.

lv_file   = p_file.
lv_sheet  = gv_sheet_name.
zcl_convert_excel_to_itab=>get_itab(
EXPORTING
filename                   = lv_file             ” Local file for upload/download
worksheet                  = lv_sheet          ” Worksheet (Blank = First sheet)
*      begin_col                  = 1
*      begin_row                  = 1
CHANGING
itab                       = gt_excel_data_tmp        ” Data from XLSX
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
others_exc                 = 17
conv_bin_to_xstring_failed = 18
sheet_not_found            = 19
load_document_failed       = 20
OTHERS                     = 21
).
IF sy–subrc <> 0.
MESSAGE ID sy–msgid TYPE ‘S’ NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4 DISPLAY LIKE ‘E’.
LEAVE LIST-PROCESSING.
ENDIF.