本文分享笔者在从事 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 文件内容的详细技术实现。