Use the office integration class encapsulated by SAP ABAP to access local Excel files

Time:2022-4-24

Let’s first look at the effect of this report: open local 1 Xlsx excel file:

Use the office integration class encapsulated by SAP ABAP to access local Excel files

The excel content is as follows:
Use the office integration class encapsulated by SAP ABAP to access local Excel files

useget_ranges_dataMethod to successfully read Excel data as follows:

Use the office integration class encapsulated by SAP ABAP to access local Excel files

Use the office integration class encapsulated by SAP ABAP to access local Excel files

The complete code of this example is as follows:

*&---------------------------------------------------------------------*
*& Report  Z_IMPORT
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT  Z_IMPORT.
DATA:
oref_container   TYPE REF TO cl_gui_custom_container,
iref_control     TYPE REF TO i_oi_container_control,
iref_document    TYPE REF TO i_oi_document_proxy,
iref_spreadsheet TYPE REF TO i_oi_spreadsheet,
iref_error       TYPE REF TO i_oi_error.
DATA:
v_document_url TYPE c LENGTH 256,
i_sheets TYPE soi_sheets_table,
wa_sheets TYPE soi_sheets,
i_data        TYPE soi_generic_table,
wa_data       TYPE soi_generic_item,
i_ranges      TYPE soi_range_list,
i_total       TYPE i,
p_cols        TYPE i value 2.
PARAMETERS:
p_file  TYPE  localfile OBLIGATORY,
p_rows  TYPE i DEFAULT 100 OBLIGATORY,
p_mode TYPE c AS CHECKBOX.
INITIALIZATION.
CALL METHOD c_oi_container_control_creator=>get_container_control
IMPORTING
control = iref_control
error   = iref_error.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'E'.
ENDIF.
CREATE OBJECT oref_container
EXPORTING
*      parent                      =
container_name              = 'CONTsadasda'
*      style                       =
*      lifetime                    = lifetime_default
*      repid                       =
*      dynnr                       =
*      no_autodef_progid_dynnr     =
EXCEPTIONS
cntl_error                  = 1
cntl_system_error           = 2
create_error                = 3
lifetime_error              = 4
lifetime_dynpro_dynpro_link = 5
OTHERS                      = 6.
IF sy-subrc <> 0.
MESSAGE e001(00) WITH 'Error while creating container'.
ENDIF.
CALL METHOD iref_control->init_control
EXPORTING
*      dynpro_nr                = SY-DYNNR
*      gui_container            = ' '
inplace_enabled          = 'X'
*      inplace_mode             = 0
*      inplace_resize_documents = ' '
*      inplace_scroll_documents = ' '
*      inplace_show_toolbars    = 'X'
*      no_flush                 = ' '
*      parent_id                = cl_gui_cfw=>dynpro_0
r3_application_name      = 'EXCEL CONTAINER'
*      register_on_close_event  = ' '
*      register_on_custom_event = ' '
*      rep_id                   = SY-REPID
*      shell_style              = 1384185856
parent                   = oref_container
*      name                     =
*      autoalign                = 'x'
IMPORTING
error                    = iref_error
*      retcode                  =
EXCEPTIONS
javabeannotsupported     = 1
OTHERS                   = 2
.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'E'.
ENDIF.
CALL METHOD iref_control->get_document_proxy
EXPORTING
*     document_format    = 'NATIVE'
document_type      = soi_doctype_excel_sheet
*     no_flush           = ' '
*     register_container = ' '
IMPORTING
document_proxy     = iref_document
error              = iref_error
*    retcode            =
.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'E'.
ENDIF.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
* To provide F4 help for the file
PERFORM sub_file_f4.
START-OF-SELECTION.
CONCATENATE 'FILE://' p_file INTO v_document_url.
CALL METHOD iref_document->open_document
EXPORTING
document_title   = 'Excel'
document_url     = v_document_url
*     no_flush         = ' '
open_inplace     = 'X'
*     open_readonly    = ' '
*     protect_document = ' '
*     onsave_macro     = ' '
*     startup_macro    = ''
*     user_info        =
IMPORTING
error            = iref_error
*     retcode          =
.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'I'.
LEAVE LIST-PROCESSING.
ENDIF.
CALL METHOD iref_document->get_spreadsheet_interface
EXPORTING
no_flush        = ' '
IMPORTING
error           = iref_error
sheet_interface = iref_spreadsheet
*     retcode         =
.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'I'.
LEAVE LIST-PROCESSING.
ENDIF.
CALL METHOD iref_spreadsheet->get_sheets
EXPORTING
no_flush = ' '
*      updating = -1
IMPORTING
sheets   = i_sheets
error    = iref_error
*      retcode  =
.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'I'.
LEAVE LIST-PROCESSING.
ENDIF.
LOOP AT i_sheets INTO wa_sheets.
CALL METHOD iref_spreadsheet->select_sheet
EXPORTING
name     = wa_sheets-sheet_name
*         no_flush = ' '
IMPORTING
error    = iref_error
*         retcode  =
.
IF iref_error->has_failed = 'X'.
EXIT.
*      call method iref_error->raise_message
*        exporting
*          type = 'E'.
ENDIF.
CALL METHOD iref_spreadsheet->set_selection
EXPORTING
top     = 1
left    = 1
rows    = p_rows
columns = p_cols.
CALL METHOD iref_spreadsheet->insert_range
EXPORTING
name     = 'Test'
rows     = p_rows
columns  = p_cols
no_flush = ''
IMPORTING
error    = iref_error.
IF iref_error->has_failed = 'X'.
EXIT.
*      call method iref_error->raise_message
*        exporting
*          type = 'E'.
ENDIF.
REFRESH i_data.
CALL METHOD iref_spreadsheet->get_ranges_data
EXPORTING
*        no_flush  = ' '
all       = 'X'
*        updating  = -1
*        rangesdef =
IMPORTING
contents  = i_data
error     = iref_error
*        retcode   =
CHANGING
ranges    = i_ranges
.
DELETE i_data WHERE value IS INITIAL OR value = space.
ULINE.
WRITE:/1 wa_sheets-sheet_name COLOR 3.
ULINE.
*    LOOP AT i_data INTO wa_data.
*      WRITE:(50) wa_data-value.
*      AT END OF row.
*        NEW-LINE.
*      ENDAT.
*    ENDLOOP.
ENDLOOP.
CALL METHOD iref_document->close_document
*  EXPORTING
*    do_save     = ' '
*    no_flush    = ' '
IMPORTING
error       = iref_error
*    has_changed =
*    retcode     =
.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'I'.
LEAVE LIST-PROCESSING.
ENDIF.
CALL METHOD iref_document->release_document
*  EXPORTING
*    no_flush = ' '
IMPORTING
error    = iref_error
*    retcode  =
.
IF iref_error->has_failed = 'X'.
CALL METHOD iref_error->raise_message
EXPORTING
type = 'I'.
LEAVE LIST-PROCESSING.
ENDIF.
IF p_mode = 'X'.
*    DATA: ls_data TYPE ZREPORT,
*          lt_data TYPE STANDARD TABLE OF ZREPORT,
*          chaR_index TYPE string,
*          msg TYPE string.
*    LOOP AT i_data INTO wa_data.
*
*      IF sy-tabix MOD 2 = 1.
*         char_index = sy-tabix.
*         ls_data-report_name = wa_data-value.
*         CONCATENATE 'Index' char_index ls_data-report_name INTO msg SEPARATED BY SPACE.
*         CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
*           EXPORTING
*             TEXT = msg.
*      ELSE.
*         ls_data-description = wa_data-value.
*         APPEND ls_data TO lt_data.
*         CLEAR: wa_data,ls_data.
*      ENDIF.
*    ENDLOOP.
*    DELETE FROM zreport.
*    INSERT zreport FROM TABLE lt_data.
*    COMMIT WORK AND WAIT.
*    IF sy-subrc = 0.
*      i_total = lines( lt_data ).
*      WRITE:/ 'User Assign Row Number:' , p_rows.
*      WRITE:/ 'Actually Imported Row Numer:' , i_total.
*    ENDIF.
ENDIF.
*&---------------------------------------------------------------------*
*&      Form  SUB_FILE_F4
*&---------------------------------------------------------------------*
*       F4 help for file path
*----------------------------------------------------------------------*
FORM sub_file_f4 .
DATA:
l_desktop       TYPE string,
l_i_files       TYPE filetable,
l_wa_files      TYPE file_table,
l_rcode         TYPE int4.
* Finding desktop
CALL METHOD cl_gui_frontend_services=>get_desktop_directory
CHANGING
desktop_directory    = l_desktop
EXCEPTIONS
cntl_error           = 1
error_no_gui         = 2
not_supported_by_gui = 3
OTHERS               = 4.
IF sy-subrc <> 0.
MESSAGE e001(00) WITH
'Desktop not found'.
ENDIF.
* Update View
CALL METHOD cl_gui_cfw=>update_view
EXCEPTIONS
cntl_system_error = 1
cntl_error        = 2
OTHERS            = 3.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title            = 'Select Excel file'
default_extension       = '.xls'
*      default_filename        =
file_filter             = '.xls'
*      with_encoding           =
initial_directory       = l_desktop
*      multiselection          =
CHANGING
file_table              = l_i_files
rc                      = l_rcode
*     user_action             =
*     file_encoding           =
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 e001(00) WITH 'Error while opening file'.
ENDIF.
READ TABLE l_i_files INDEX 1 INTO l_wa_files.
IF sy-subrc = 0.
p_file = l_wa_files-filename.
ELSE.
MESSAGE e001(00) WITH 'Error while opening file'.
ENDIF.
ENDFORM.                    " SUB_FILE_F4