EXCLE下载并填充数据

Lisson 3

已于 2023-10-09 15:48:59 修改

阅读量160

点赞数

分类专栏： ABAP 文章标签： ABAP

于 2023-06-28 10:23:24 首次发布

本文链接：https://blog.csdn.net/qq_40223617/article/details/131431330

版权

ABAP 专栏收录该内容

39 篇文章 3 订阅

订阅专栏

该文章详细介绍了如何在SAP系统中通过OLE接口操作Excel模板，实现数据的下载、打开和写入。具体步骤包括上传模板，使用DOWNLOAD_WEB_OBJECT下载，然后通过创建OLE2_OBJECT对象并调用其方法进行Excel操作，最后将数据写入指定单元格。代码示例展示了从选择文件到写入数据的完整流程。

摘要由CSDN通过智能技术生成

自定义EXCEL模板，通过OLE的方式写入数据

实现原理1：
主要用到以下三步，通过SMW0上传EXCEL模板，调用函数DOWNLOAD_WEB_OBJECT下载模板；定义对象参考OLE2_OBJECT，调用其中’Open’的方法打开EXCEL，最后通过定位单元格将数据写进去.(示例代码2会显示excle的读写过程，在读写过程中鼠标不能移动否则会出现数据丢失的情况，建议使用实例代码1后台读写)。

实例代码1：

REPORT ZZJX_TEST08 MESSAGE-ID ZZJXMSG.

DATA:C_EXPORT_FILENAME_XLS   TYPE STRING VALUE 'ZJX_TEST.XLSX', "导出模板默认文件名 '数据导入模板'
           C_OBJID_XLS                       TYPE WWWDATATAB-OBJID VALUE 'ZZJX_TEST08'.   "存放模板的对象id

DATA:  LO_OBJDATA                       LIKE WWWDATATAB,                                "Excel模板对象
             LS_DESTINATION             LIKE RLGRAP-FILENAME                           ,"下载保存的目标路径
             LC_PATH                    TYPE STRING,        "存储路径
             LC_FULLPATH                TYPE STRING,       "文件完整路径
             LI_RC                       LIKE SY-SUBRC.   "返回值

DATA: EXCEL_OBJ                         TYPE OLE2_OBJECT,
           BOOK_OBJ                     TYPE OLE2_OBJECT,
           SHEET_OBJ                    TYPE OLE2_OBJECT,
           CELL_OBJ                     TYPE OLE2_OBJECT.



LOAD-OF-PROGRAM.


  PARAMETERS: P_FILE TYPE RLGRAP-FILENAME OBLIGATORY.

INITIALIZATION.


AT SELECTION-SCREEN OUTPUT.


AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.
  CALL METHOD CL_GUI_FRONTEND_SERVICES=>DIRECTORY_BROWSE
    EXPORTING
      WINDOW_TITLE         = 'File Directory'
      INITIAL_FOLDER       =  'C:\Users\BRIGHT-SH-002\Desktop'
    CHANGING
      SELECTED_FOLDER      = LC_PATH
*  EXCEPTIONS
*    CNTL_ERROR           = 1
*    ERROR_NO_GUI         = 2
*    NOT_SUPPORTED_BY_GUI = 3
*    OTHERS               = 4
          .
  P_FILE = LC_PATH.

START-OF-SELECTION.
*第一步: 下载Excel到本地
  PERFORM DOWNLOAD_XLS_TEMPLATE.

*第二步：打开Excel文档
  PERFORM OPEN_EXCEL.

*第三步:   写入数据
  PERFORM WRITE_EXCEL.

END-OF-SELECTION.
*&---------------------------------------------------------------------*
*&      Form  DOWNLOAD_XLS_TEMPLATE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM DOWNLOAD_XLS_TEMPLATE .
* 获取保存路径
*  LC_PATH = P_FILE.

*  CALL METHOD CL_GUI_FRONTEND_SERVICES=>GET_DESKTOP_DIRECTORY
*    CHANGING
*      DESKTOP_DIRECTORY    = LC_PATH
**  EXCEPTIONS
**    CNTL_ERROR           = 1
**    ERROR_NO_GUI         = 2
**    NOT_SUPPORTED_BY_GUI = 3
**    OTHERS               = 4
*          .
*  IF LC_PATH IS INITIAL.
*    LC_PATH = 'C:\Users\BRIGHT-SH-002\Desktop'.
*  ENDIF.

  CONCATENATE LC_PATH '\' C_EXPORT_FILENAME_XLS INTO LC_FULLPATH.
*  检查模板是否存在
  SELECT SINGLE RELID OBJID FROM WWWDATA INTO CORRESPONDING FIELDS OF LO_OBJDATA
 WHERE SRTF2 = 0 AND RELID = 'MI' AND OBJID = C_OBJID_XLS.
  IF SY-SUBRC NE 0 OR LO_OBJDATA-OBJID EQ SPACE.
    MESSAGE E000(ZZJXMSG) WITH C_EXPORT_FILENAME_XLS.
  ENDIF.

*  下载模板
  LS_DESTINATION = LC_FULLPATH .
  CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
    EXPORTING
      KEY         = LO_OBJDATA
      DESTINATION = LS_DESTINATION
    IMPORTING
      RC          = LI_RC
* CHANGING
*   TEMP              =
  .
  IF LI_RC NE 0.
    MESSAGE E001(ZZJXMSG) WITH C_EXPORT_FILENAME_XLS.
  ENDIF.

ENDFORM.                    " DOWNLOAD_XLS_TEMPLATE
*&---------------------------------------------------------------------*
*&      Form  OPEN_EXCEL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM OPEN_EXCEL .
  CREATE OBJECT EXCEL_OBJ 'EXCEL.APPLICATION'.
  IF SY-SUBRC NE 0.
    MESSAGE 'EXCEL创建错误' TYPE 'S' DISPLAY LIKE 'E'.
    STOP.
  ENDIF.

  CALL METHOD OF
      EXCEL_OBJ
      'WORKBOOKS' = BOOK_OBJ.
  SET PROPERTY OF EXCEL_OBJ 'VISIBLE' = 0.
*  SET PROPERTY OF EXCEL_OBJ 'SheetInNewWorkbook' = 1.

* 打开excel文件，（新建使用：call method of book_obj 'Add' = sheet_obj）
  CALL METHOD OF
      BOOK_OBJ
      'Open'   = SHEET_OBJ
    EXPORTING
      #1       = LS_DESTINATION.

  CALL METHOD OF
      EXCEL_OBJ
      'Sheets'  = SHEET_OBJ"切换sheets
    EXPORTING
      #1        = 1.

  CALL METHOD OF
      SHEET_OBJ
      'Select'.

*  FREE OBJECT SHEET_OBJ."ok
  CALL METHOD OF SHEET_OBJ 'ACTIVATE'."激活
  SET PROPERTY OF SHEET_OBJ 'NAME' = '库存物料表'.
ENDFORM.                    " OPEN_EXCEL
*&---------------------------------------------------------------------*
*&      Form  WRITE_EXCEL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM WRITE_EXCEL .
  DATA:BEGIN OF IT_DATA OCCURS 10,
       MANDT TYPE MANDT,
       MATNR TYPE MATNR,
       MAKTX TYPE MAKT-MAKTX,
       WERKS TYPE WERKS_D,
       NAME1 TYPE NAME1,
       LGORT TYPE LGORT_D,
       LGOBE TYPE T001L-LGOBE,
       LABST TYPE LABST,
       MEINS TYPE MEINS,
    END OF IT_DATA.
  DATA LV_ROWS TYPE I.
  RANGES: LR_MATNR FOR MARD-MATNR.
  CLEAR LR_MATNR.
  LR_MATNR-SIGN = 'I'.
  LR_MATNR-OPTION = 'BT'.
  LR_MATNR-LOW = '000000001000000030'.
  LR_MATNR-HIGH = '000000001000000197'.
  APPEND LR_MATNR.

  SELECT A~MATNR B~MAKTX A~WERKS C~NAME1 A~LGORT D~LGOBE A~LABST E~MEINS
    INTO CORRESPONDING FIELDS OF TABLE IT_DATA
    FROM MARD AS A
    INNER JOIN MAKT  AS B ON B~MATNR = A~MATNR AND B~SPRAS = 'JA'
    INNER JOIN T001W AS C ON C~WERKS = A~WERKS
    INNER JOIN T001L AS D ON D~LGORT = A~LGORT
    INNER JOIN MARA  AS E ON E~MATNR = A~MATNR
    UP TO 5 ROWS
    WHERE A~MATNR IN LR_MATNR.


  FIELD-SYMBOLS <FS1> LIKE LINE OF IT_DATA.
  LOOP AT IT_DATA ASSIGNING <FS1> .
    <FS1>-MANDT = SY-MANDT.
  ENDLOOP.

*  PERFORM EXCEL_ROW_INSERT USING SHEET_OBJ 3 1.
* 输出Excel表头,自定义格式的列等
  PERFORM FILL_CELL USING 3 1 1 'MANDT'.
  PERFORM FILL_CELL USING 3 2 1 'MATNR'.
  PERFORM FILL_CELL USING 3 3 1 'MAKTX'.
  PERFORM FILL_CELL USING 3 4 1 'WERKS'.
  PERFORM FILL_CELL USING 3 5 1 'NAME1'.
  PERFORM FILL_CELL USING 3 6 1 'LGORT'.
  PERFORM FILL_CELL USING 3 7 1 'LGOBE'.
  PERFORM FILL_CELL USING 3 8 1 'LABST'.
  PERFORM FILL_CELL USING 3 9 1 'MEINS'.
  CLEAR LV_ROWS.
  LOOP AT IT_DATA.
    LV_ROWS = SY-TABIX + 3.
*    PERFORM EXCEL_ROW_INSERT USING SHEET_OBJ LV_ROWS 1.
    PERFORM FILL_CELL USING: LV_ROWS 1 0 IT_DATA-MANDT,
                             LV_ROWS 2 1 IT_DATA-MATNR,
                             LV_ROWS 3 0 IT_DATA-MAKTX,
                             LV_ROWS 4 0 IT_DATA-WERKS,
                             LV_ROWS 5 0 IT_DATA-NAME1,
                             LV_ROWS 6 0 IT_DATA-LGORT,
                             LV_ROWS 7 0 IT_DATA-LGOBE,
                             LV_ROWS 8 0 IT_DATA-LABST,
                             LV_ROWS 9 0 IT_DATA-MEINS.
  ENDLOOP.

  FREE OBJECT CELL_OBJ.
  GET PROPERTY OF EXCEL_OBJ 'ActiveSheet' = SHEET_OBJ. "获取活动SHEET
  FREE OBJECT SHEET_OBJ.
  GET PROPERTY OF EXCEL_OBJ 'ACTIVEWORKBOOK' = BOOK_OBJ."激活工作区
  "save
*  CALL METHOD OF
*      BOOK_OBJ
*      'SAVEAS'
*    EXPORTING
*      #1       = LC_FULLPATH
*      #2       = 1.
  CALL METHOD OF
      BOOK_OBJ
      'SAVE'.
*   SET PROPERTY OF excel 'Visible' = 1.  "是否显示EXCEL 此处显示不退出
  CALL METHOD OF
      BOOK_OBJ
      'CLOSE'.
  CALL METHOD OF
      EXCEL_OBJ
      'QUIT'.

  FREE OBJECT BOOK_OBJ.
  FREE OBJECT EXCEL_OBJ.
ENDFORM.                    " WRITE_EXCEL
*&---------------------------------------------------------------------*
*&      Form  filL_cell
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->row  行
*      -->col  列
*      -->bold 加粗
*      -->val  填充值
*----------------------------------------------------------------------*
FORM FILL_CELL  USING   VALUE(ROW)
                        VALUE(COL)
                        VALUE(BOLD)
                        VALUE(VAL).
  CALL METHOD OF
      EXCEL_OBJ
      'CELLS'   = CELL_OBJ
    EXPORTING
      #1        = ROW
      #2        = COL.
*  SET PROPERTY OF CELL_OBJ 'BOLD' = BOLD.
  SET PROPERTY OF CELL_OBJ 'VALUE' = VAL.
*  FREE OBJECT CELL_OBJ.
ENDFORM.                    " FIL_CELL
*&---------------------------------------------------------------------*
*&      Form  EXCEL_ROW_INSERT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_SHEET  text
*      -->P_TAB  text
*      -->P_1      text
*----------------------------------------------------------------------*
FORM EXCEL_ROW_INSERT  USING    LCOBJ_SHEET
                                LC_ROW
                                LC_COUNT.
  DATA LC_RANGE TYPE OLE2_OBJECT.
  DATA H_BORDERS  TYPE OLE2_OBJECT.
  DO LC_COUNT TIMES.
    CALL METHOD OF
        LCOBJ_SHEET
        'Rows'      = LC_RANGE
      EXPORTING
        #1          = 1.
    CALL METHOD OF LC_RANGE 'Copy'.  "COPY第6行插入一个新行
    CALL METHOD OF
        LCOBJ_SHEET
        'Rows'      = LC_RANGE
      EXPORTING
        #1          = LC_ROW.
    CALL METHOD OF
        LC_RANGE
        'Insert'.
    CALL METHOD OF LC_RANGE 'ClearContents'. "是否需要清空Cell
  ENDDO.
ENDFORM.                    " EXCEL_ROW_INSERT

实例代码2：

INCLUDE zfi_zcsz_top.
INCLUDE zfi_zcsz_scr.
INCLUDE zfi_zcsz_pai.
INCLUDE zfi_zcsz_frm.

INITIALIZATION.
  PERFORM frm_init.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  PERFORM frm_get_path.

AT SELECTION-SCREEN.
  CASE sscrfields-ucomm.
    WHEN 'FC01'.
      PERFORM form_fco1.

  ENDCASE.


START-OF-SELECTION.
  PERFORM frm_get_excle.

TOP定义和屏幕

*-----------------------OLE--------------------------------------*
*创建对象实例，用于打开Excel程序
data:  xl_appl           type ole2_object,
       xl_sheet          type ole2_object,
       xl_init_sheet     type ole2_object,
       xl_template_sheet type ole2_object,
       xl_wkbks          type ole2_object,
       xl_cell           type ole2_object,
       xl_row            type ole2_object,
       blankrow          type i value 0,
       currentrow        type i value 0.
"选择屏幕
SELECTION-SCREEN FUNCTION KEY 1.

PARAMETERS: p_file  LIKE rlgrap-filename .  "选择文件路径

PAI

FORM frm_init .
  sscrfields-functxt_01 = '下载导入模板'.
ENDFORM.

FORM frm_get_path .
  CALL FUNCTION 'WS_FILENAME_GET'
    EXPORTING
      mode             = 'O'
      title            = '导入模板'
    IMPORTING
      filename         = p_file
    EXCEPTIONS
      inv_winsys       = 1
      no_batch         = 2
      selection_cancel = 3
      selection_error  = 4
      OTHERS           = 5.

ENDFORM.

FORM form_fco1 .
  DATA: objdata  TYPE wwwdatatab,
        lv_path  TYPE rlgrap-filename,
        lv_subrc TYPE sy-subrc.
  DATA:lv_xlsid TYPE w3objid.

  lv_xlsid = 'ZFI_ZCSZ'.
  " 下载
  CLEAR: lv_path, objdata.
  CALL FUNCTION 'WS_FILENAME_GET'
    EXPORTING
      def_path         = 'E:\'
      mask             = ',Excel file,*.xls;*.xlsx;' " 限制为Excel文件 --
      mode             = 'S'
      title            = '下载Excel模版……'
    IMPORTING
      filename         = lv_path     " 返回的文件名 --
    EXCEPTIONS
      inv_winsys       = 1
      no_batch         = 2
      selection_cancel = 3
      selection_error  = 4
      OTHERS           = 5.
  CLEAR lv_subrc.
  lv_subrc = sy-subrc.
  IF lv_subrc = 0.
    " 检查文件是否存在
    SELECT SINGLE relid objid INTO CORRESPONDING FIELDS OF objdata
      FROM wwwdata WHERE srtf2 EQ 0 AND relid EQ 'MI' AND objid EQ lv_xlsid.
    IF ( sy-subrc NE 0 ) OR ( objdata-objid IS INITIAL ).
      MESSAGE '模板不存在' TYPE  'E'.
      EXIT.
    ENDIF.
    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
      EXPORTING
        key         = objdata
        destination = lv_path
      IMPORTING
        rc          = lv_subrc.
    IF lv_subrc NE 0.
      MESSAGE '模板下载失败'  TYPE  'E'.
    ELSE.
      MESSAGE '模板下载成功'  TYPE  'S'.
    ENDIF.
  ENDIF.

  "根据项目号在在资产赋值表查对应的WBS.
  CHECK p_pspid IS NOT INITIAL .
  "打开excle
  PERFORM frm_open_excle USING lv_path.

  "写入数据
  PERFORM frm_write_excle.


ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  FRM_OPEN_EXCLE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM frm_open_excle  USING u1.

  DATA: column  TYPE ole2_object.
  CREATE OBJECT xl_appl 'EXCEL.APPLICATION'.
  SET PROPERTY OF xl_appl 'Visible' = 1.
  CALL METHOD OF
    xl_appl
      'Workbooks' = xl_wkbks.
  CALL METHOD OF
    xl_wkbks
    'Open'
    EXPORTING
      #1 = u1
      #2 = 0
      #3 = 0.             "只读参数
  IF sy-subrc <> 0.
    PERFORM free_object.
    MESSAGE '打开模板文件失败,请检查参数是否正确!' TYPE 'S' DISPLAY LIKE 'E'.
    LEAVE LIST-PROCESSING.
  ENDIF.
  CALL METHOD OF
      xl_appl
      'Worksheets' = xl_sheet
    EXPORTING
      #1           = 'sheet1'.
  GET PROPERTY OF xl_appl 'ActiveWorkbook' =  xl_wkbks. "必须加上,否则无法保存excel.

ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  FRM_WRITE_EXCLE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM frm_write_excle .
  DATA: lv_index TYPE i. "行号

*获取资产赋值表的数据
  SELECT
    posid,
    xref3,
    zzcqd
    INTO TABLE @DATA(lt_zc)
    FROM zfi_zcfz
    WHERE pspid = @p_pspid.

  "公司代码
  SELECT SINGLE vbukr INTO @DATA(lv_vbukr)  FROM proj WHERE pspid = @p_pspid.

  "使用年限&期间
  SELECT
    anlkl,
    afapl,
    afabe,
    bdatu,
    ndjar,
    ndper
    INTO TABLE @DATA(lt_ankb)
    FROM ankb
    WHERE anlkl = '00110000'
    AND   afabe IN ( 01,20,30  ).


  CLEAR gt_table.
  LOOP AT lt_zc INTO DATA(ls_zc).
    MOVE-CORRESPONDING ls_zc TO gs_table.
    gs_table-bukrs = lv_vbukr.
    gs_table-anlkl = '110000'.
    gs_table-txt50 = gs_table-zzcqd.
    gs_table-anlhtxt = gs_table-zzcqd.
    gs_table-meins = 'EA'.

    READ TABLE lt_ankb INTO DATA(ls_ankb) INDEX 1.
    IF sy-subrc = 0.

    ENDIF.

    CLEAR ls_zc.
  ENDLOOP.

* 复制数据到Excel,针对固定格式
  CLEAR lv_index.
  LOOP AT gt_table INTO gs_table.

    CALL FUNCTION 'CONVERSION_EXIT_ABPSN_OUTPUT'
      EXPORTING
        input  = gs_table-posid
      IMPORTING
        output = gs_table-posid.

    lv_index = sy-tabix + 1.
    PERFORM fill_cell USING lv_index 1  gs_table-posid.
    PERFORM fill_cell USING lv_index 2  gs_table-xref3.
    PERFORM fill_cell USING lv_index 3  gs_table-zzcqd.
    PERFORM fill_cell USING lv_index 4  gs_table-bukrs.
    PERFORM fill_cell USING lv_index 5  gs_table-anlkl.
    PERFORM fill_cell USING lv_index 6  gs_table-zzcqd.
    PERFORM fill_cell USING lv_index 7  gs_table-zzcqd.
    PERFORM fill_cell USING lv_index 8  gs_table-meins.
    PERFORM fill_cell USING lv_index 9  gs_table-ndjar1.
    PERFORM fill_cell USING lv_index 10  gs_table-ndper1.
    PERFORM fill_cell USING lv_index 11  gs_table-ndper2.
    PERFORM fill_cell USING lv_index 12  gs_table-ndper2.
    PERFORM fill_cell USING lv_index 13  gs_table-ndper3.
    PERFORM fill_cell USING lv_index 14  gs_table-ndper3.

    CLEAR gs_table.
  ENDLOOP.

ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  FILL_CELL
*&---------------------------------------------------------------------*
*   row:  行号,
*   col:  列号,
*   bold: 字体是否加粗,0,否,1是.
*   val:  填充值
*----------------------------------------------------------------------*
FORM fill_cell  USING row col  val.
  DATA: cell TYPE ole2_object.

  CALL METHOD OF  xl_appl  'CELLS'   = cell
    EXPORTING
       #1        = row
       #2        = col.
  SET PROPERTY OF cell 'VALUE' = val.
ENDFORM.                    "FILL_CELL


FORM free_object .
  FREE OBJECT xl_row.
  FREE OBJECT xl_cell.
  FREE OBJECT xl_sheet.
  FREE OBJECT xl_init_sheet.
  FREE OBJECT xl_template_sheet.
  FREE OBJECT xl_wkbks.
  FREE OBJECT xl_appl.
ENDFORM.