SAP 实施项目中涉及到编程方式操作 Excel 的几种场景介绍
- 2022 年 7 月 19 日
本文字数:5420 字
阅读完需:约 18 分钟
本文分享笔者在从事 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 文件内容的详细技术实现。
版权声明: 本文为 InfoQ 作者【Jerry Wang】的原创文章。
原文链接:【http://xie.infoq.cn/article/96970aaa4a588932f16ac16ab】。文章转载请联系作者。
Jerry Wang
🏆InfoQ写作平台-签约作者🏆 2017.12.03 加入
SAP成都研究院开发专家,SAP社区导师,SAP中国技术大使。2007 年从电子科技大学计算机专业硕士毕业后加入 SAP 成都研究院工作至今。工作中使用 ABAP, Java, JavaScript 和 TypeScript 进行开发。
评论