锐客网

  1. 首页 > it技术 > >

使用|使用 SAP ABAP 封装的 Office Integration class 访问本地 Excel 文件

先看这个 report 执行的效果：打开本地 1.xlsx excel 文件：
【使用|使用 SAP ABAP 封装的 Office Integration class 访问本地 Excel 文件】使用|使用 SAP ABAP 封装的 Office Integration class 访问本地 Excel 文件
文章图片

这个 Excel 内容如下：
使用|使用 SAP ABAP 封装的 Office Integration class 访问本地 Excel 文件
文章图片

使用 get_ranges_data 方法，成功读取 Excel 的数据如下：
使用|使用 SAP ABAP 封装的 Office Integration class 访问本地 Excel 文件
文章图片

使用|使用 SAP ABAP 封装的 Office Integration class 访问本地 Excel 文件
文章图片

本例完整代码如下：

*&---------------------------------------------------------------------* *& ReportZ_IMPORT *& *&---------------------------------------------------------------------* *& *& *&---------------------------------------------------------------------*REPORTZ_IMPORT.DATA: oref_containerTYPE REF TO cl_gui_custom_container, iref_controlTYPE REF TO i_oi_container_control, iref_documentTYPE REF TO i_oi_document_proxy, iref_spreadsheet TYPE REF TO i_oi_spreadsheet, iref_errorTYPE 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_dataTYPE soi_generic_table, wa_dataTYPE soi_generic_item, i_rangesTYPE soi_range_list, i_totalTYPE i, p_colsTYPE i value 2.PARAMETERS: p_fileTYPElocalfile OBLIGATORY, p_rowsTYPE 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 = https://www.it610.com/article/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. *&---------------------------------------------------------------------* *&FormSUB_FILE_F4 *&---------------------------------------------------------------------* *F4 help for file path *----------------------------------------------------------------------* FORM sub_file_f4 . DATA: l_desktopTYPE string, l_i_filesTYPE filetable, l_wa_filesTYPE file_table, l_rcodeTYPE 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

    推荐阅读


    上一篇：JoJoGAN|JoJoGAN One-Shot Face Stylization（使用 StyleGAN 创建 JoJo风格人脸头像）

    下一篇：适合|适合 Kubernetes 初学者的一些实战练习(一)