写点什么

SAP 实施项目中涉及到编程方式操作 Excel 的几种场景介绍

作者:Jerry Wang
  • 2022 年 7 月 19 日
  • 本文字数:5420 字

    阅读完需:约 18 分钟

SAP 实施项目中涉及到编程方式操作 Excel 的几种场景介绍

本文分享笔者在从事 SAP 项目开发工作中,涉及到的通过编程方式操作 Excel 的业务场景,希望起到抛砖引玉的作用。

使用 Excel 导入数据到 SAP Cloud for Customer 系统

假设要上传 Account 的数据到系统:点 download metadata:



自动 download 一个 zip 下来:



在 folder Templates 里维护要上传的 Account data:



把维护好的 excel 重新打成 zip, upload:



稍后在 monitor 里会观察到成功上传的 task:



看到 import 成功的消息:



UI 上能搜索出这条于 2017-11-14 日创建的 Account:


如何通过 Excel import 的方式导入测试数据到 SAP Commerce Cloud 服务器

登录 SAP Commerce Cloud Administration Cockpit,从下拉菜单里选择 Product Cockpit:



点击这个箭头,打开 product 视图:



点击这个 excel import 按钮:



选择好 excel 和 media.zip 之后,点击 import:



点击这个按钮查看 import 进度:




进到 Backoffice 页面,根据 excel 里的 product id 查看一个 product,确保导入成功:


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

先看这个 report 执行的效果:打开本地 1.xlsx excel 文件:



这个 Excel 内容如下:



使用 get_ranges_data 方法,成功读取 Excel 的数据如下:




本例完整代码如下:


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
复制代码

总结

本文通过三个实际的业务场景,分别介绍了如何通过 Excel 作为载体,导入数据到 SAP Cloud for Customer 和 SAP Commerce Cloud 系统,以及通过 ABAP 编程语言解析 Excel 文件内容的详细技术实现。

发布于: 刚刚阅读数: 4
用户头像

Jerry Wang

关注

🏆InfoQ写作平台-签约作者🏆 2017.12.03 加入

SAP成都研究院开发专家,SAP社区导师,SAP中国技术大使。2007 年从电子科技大学计算机专业硕士毕业后加入 SAP 成都研究院工作至今。工作中使用 ABAP, Java, JavaScript 和 TypeScript 进行开发。

评论

发布
暂无评论
SAP 实施项目中涉及到编程方式操作 Excel 的几种场景介绍_Excel_Jerry Wang_InfoQ写作社区