SAP_ABAP_OLE_EXCEL批导案例

SAP ABAP顾问能力模型梳理_企业数字化建设者的博客-CSDN博客SAP Abap顾问能力模型https://blog.csdn.net/java_zhong1990/article/details/132469977

一、OLE_EXCEL批导

1.1 下载按钮
1.2  选择EXCEL上传，解析EXCLE数据， Call屏幕。

1.3 实现效果

1.4 代码样例

*&---------------------------------------------------------------------*
*& Report ZMMRP010
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zmmrp007.*******************************************************
*程序名: zmmrp007
*程序描述: 采购订单审批
*----------------------------------------------------------------------
*创建日期     开发顾问     业务顾问
*2019.09.17. terryzhong 
*===============================================
*修改请求号 修改日期 修改人 修改描述
*xxxxxxxx xxxx.xx.xx xxxxxxxx xxxxxxxx
*******************************************************
INCLUDE zmmrp007_top.INCLUDE zmmrp007_sel.INCLUDE zmmrp007_frm.INCLUDE zmmrp007_9000.INCLUDE zmmrp007_status_9000pbo.INCLUDE zmmrp007_user_command_9000i01.INITIALIZATION."第一次打开屏幕或者点击执行代码已经执行完成重新显示屏幕的时候会运行
*（1）.只能用于报表程序
*（2）.在选择屏幕出现之前执行.
*（3）.通常的用法是在这里给选择屏幕中的字段赋值。sscrfields-functxt_02 = '下载模板'.AT SELECTION-SCREEN ON VALUE-REQUEST FOR pa_file.PERFORM get_excel.AT SELECTION-SCREEN."当在选择屏幕上有做操作的时候都会进入到这个事件IF sscrfields-ucomm = 'FC02'.PERFORM download_template.ENDIF.START-OF-SELECTION."当触发执行的时候屏幕IF p_rd2 EQ 'X'."批量修改"执行对EXCEL数据的解析PERFORM get_data.CLEAR gs_cdata.CALL SCREEN '9000'.ENDIF.END-OF-SELECTION.IF p_rd1 EQ 'X'.CALL TRANSACTION 'ZMMRP007A'."单个修改ENDIF.*&---------------------------------------------------------------------*
*& 包含 ZMMRP007_TOP
*&---------------------------------------------------------------------*TABLES: sscrfields.TYPES: BEGIN OF gy_cdata,check(2) TYPE c,ebeln TYPE ekko-ebeln, "采购订单号ebelp TYPE ekpo-ebelp, "采购订单项次zindex TYPE etens, "确认序号ebtyp TYPE ekes-ebtyp, "确认类型eindt TYPE ekes-eindt, "交货日期menge TYPE ekpo-menge, "数量* peinh TYPE ztmm_quotesi-peinh, "价格单位bstme TYPE bstme,
* bstme(10) type c,suppliercno TYPE xblnr_long, "参考exestatus(2) TYPE c,exedesc(255) TYPE c,END OF gy_cdata.
DATA gs_cdata TYPE gy_cdata.
DATA gt_cdata TYPE TABLE OF gy_cdata.
DATA gv_ok_code LIKE sy-ucomm.DATA gt_cdata_copy TYPE TABLE OF gy_cdata.
DATA gs_cdata_copy TYPE gy_cdata.*DATA gt_cdata_update TYPE TABLE OF gy_cdata.
*DATA gs_cdata_update TYPE gy_cdata.*&---------------------------------------------------------------------*
*& 包含 ZMMRP007_SEL
*&---------------------------------------------------------------------*SELECTION-SCREEN FUNCTION KEY 2.
SELECTION-SCREEN:BEGIN OF BLOCK bl1 WITH FRAME.
SELECTION-SCREEN SKIP 1.
PARAMETERS:p_rd1 RADIOBUTTON GROUP g1."单个采购订单确定
PARAMETERS:p_rd2 RADIOBUTTON GROUP g1."批导一采购订单确认
SELECTION-SCREEN SKIP 1.
PARAMETERS: pa_file TYPE rlgrap-filename."文件夹上传
SELECTION-SCREEN:END OF BLOCK bl1.*&---------------------------------------------------------------------*
*& 包含               ZMMRP007_FRM
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Form DOWNLOAD_TEMPLATE
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM download_template .DATA:lw_objdata     TYPE wwwdatatab,l_filename     TYPE string  VALUE '卡片BDC',    "默认下载名称l_fullpath     TYPE string  VALUE 'D:\' ,       "文件保存目录l_path         TYPE string  VALUE 'D:\' ,       "模板保存路径lw_destination LIKE rlgrap-filename,l_rc           LIKE sy-subrc,l_user_action  TYPE i.* 保存下载的模板CALL METHOD cl_gui_frontend_services=>file_save_dialogEXPORTINGdefault_extension    = 'xlsx'             "文件格式default_file_name    = '采购订单交期确认批导模板'            "默认名称CHANGINGfilename             = l_filename             "默认下载名称path                 = l_path                 "文件保存目录fullpath             = l_fullpath             "获取模板保存路径user_action          = l_user_actionEXCEPTIONScntl_error           = 1error_no_gui         = 2not_supported_by_gui = 3OTHERS               = 4.IF l_user_action NE 0.MESSAGE e208(00) WITH '取消操作'.ENDIF.lw_destination = l_fullpath.* 检查模板是否为空IF l_fullpath = '' OR l_path IS  NOT INITIAL.SELECT SINGLE *INTO CORRESPONDING FIELDS OF lw_objdataFROM wwwdata WHERE objid = 'ZMMRP007_TEMPLATE' .CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'EXPORTINGkey         = lw_objdatadestination = lw_destinationIMPORTINGrc          = l_rc.IF l_rc NE 0.MESSAGE  '模板下载失败'TYPE 'E'.
*   模板下载失败！RETURN.ENDIF.ENDIF.
ENDFORM.FORM get_excel .DATA : l_filetab TYPE filetable,l_waftab  LIKE LINE OF l_filetab,l_rc      TYPE i.CALL METHOD cl_gui_frontend_services=>file_open_dialogEXPORTINGwindow_title            = '打开文件'initial_directory       = 'C:/'CHANGINGfile_table              = l_filetabrc                      = l_rcEXCEPTIONSfile_open_dialog_failed = 1cntl_error              = 2error_no_gui            = 3not_supported_by_gui    = 4OTHERS                  = 5.IF sy-subrc <> 0.MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgnoWITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.EXIT.ELSE.READ TABLE l_filetab INTO l_waftab INDEX 1.pa_file = l_waftab-filename.CLEAR: l_filetab,l_waftab.ENDIF.
ENDFORM.FORM get_data .TYPES: BEGIN OF ly_excel_data,ebeln       TYPE ekko-ebeln, "采购订单号ebelp       TYPE ekpo-ebelp, "采购订单项次zindex      TYPE etens, "确认序号ebtyp       TYPE ekes-ebtyp, "确认类型eindt       TYPE ekes-eindt, "交货日期menge       TYPE ekpo-menge, "数量bstme       TYPE bstme, "单位
*           peinh       TYPE ztmm_quotesi-peinh, "价格单位suppliercno TYPE xblnr_long, "参考END OF ly_excel_data.DATA ls_excel_data TYPE ly_excel_data.DATA lt_excel TYPE TABLE OF alsmex_tabline WITH HEADER LINE.DATA l_index LIKE sy-tabix.DATA: lv_lines TYPE i.FIELD-SYMBOLS: <fs>.IF pa_file IS NOT INITIAL.CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'EXPORTINGfilename                = pa_filei_begin_col             = '1'i_begin_row             = '2'i_end_col               = '8'i_end_row               = '50000'TABLESintern                  = lt_excelEXCEPTIONSinconsistent_parameters = 1upload_ole              = 2OTHERS                  = 3.IF sy-subrc = 0.
*&& 将EXCEL格式中的数据导入到内表TAB_LOAD中LOOP AT lt_excel.ASSIGN COMPONENT lt_excel-col OF STRUCTURE ls_excel_data TO <fs>.<fs> = lt_excel-value.AT END OF row.MOVE-CORRESPONDING ls_excel_data TO gs_cdata."跟Excel的列一一对应的内表APPEND gs_cdata TO gt_cdata.CLEAR: ls_excel_data, gs_cdata.ENDAT.ENDLOOP.lv_lines = lines( gt_cdata ).IF lv_lines > 200.MESSAGE  '本次导入数据超过200，请拆分数据文件！' TYPE 'S'DISPLAY LIKE 'E'.STOP.ENDIF.ENDIF.ELSE.MESSAGE  '请输入文件路径！' TYPE 'S'DISPLAY LIKE 'E'.STOP.ENDIF.ENDFORM.