ABAP OLE

1、业务需求

用户在报表中点击导出按钮，将销售发票有关信息填充至模板中的SHEET1以及SHEET2中

2、代码实现

  DATA: gs_excel       TYPE ole2_object,
        gs_sheet1      TYPE ole2_object,
        gs_sheet2      TYPE ole2_object,
        gs_wbooklist   TYPE ole2_object,
        gs_wbook       TYPE ole2_object,
        gs_cell1       TYPE ole2_object,
        gs_cell2       TYPE ole2_object,
        gs_cells       TYPE ole2_object.

  DATA: lv_paste(8022) TYPE c,
        lt_paste       LIKE TABLE OF lv_paste.

  DATA gv_sheet_name(20) TYPE c .
  DATA gv_intex(2) TYPE c .
  DATA gv_line_cntr TYPE i .
  DATA : lv_row(5) TYPE c .
  DATA: lv_filename TYPE string,
        lv_path     TYPE string,
        lv_fullpath TYPE string,
        lv_path2    TYPE localfile.
  DATA(lv_fild_name) = '批量开票-导入开票模板' && sy-datum && sy-uzeit && '.xlsx' .
* 选择导出路径
  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      window_title         = 'SAVE'
      default_file_name    = lv_fild_name
      file_filter          = 'xlsx 文件|*.xlsx|'
    CHANGING
      filename             = lv_filename
      path                 = lv_path
      fullpath             = lv_fullpath
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.

  DATA: ls_wwwdata TYPE wwwdatatab,
        lv_message TYPE string.
  DATA: lv_subrc TYPE sy-subrc.
  lv_path2 = lv_fullpath .
  " 校验文件是否存在
  SELECT SINGLE
    relid
    objid
    FROM wwwdata
    INTO CORRESPONDING FIELDS OF ls_wwwdata
   WHERE srtf2 = 0
     AND relid = 'MI'
     AND objid = 'ZSDB001'.
  IF sy-subrc = 0.
    " 下载
    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
      EXPORTING
        key         = ls_wwwdata
        destination = lv_path2
      IMPORTING
        rc          = lv_subrc.
  ENDIF.

  CREATE OBJECT gs_excel 'EXCEL.APPLICATION'.             " 创建 XLSX
  SET PROPERTY OF gs_excel 'Visible' = 0 .                 "1前台运行。为0时表示为后台运行。
  GET PROPERTY OF gs_excel 'Workbooks' = gs_wbooklist .
  CALL METHOD  OF gs_wbooklist 'Open'  = gs_wbook
        EXPORTING #1 = lv_fullpath.
*  GET PROPERTY OF gs_excel 'ACTIVESHEET' = gs_activesheet.

  CALL METHOD OF gs_excel 'Worksheets' = gs_cells.
  CALL METHOD OF gs_cells 'Item' = gs_sheet1
  EXPORTING #1 = '1-发票基本信息' .

  CALL METHOD OF gs_excel 'Worksheets' = gs_cells.
  CALL METHOD OF gs_cells 'Item' = gs_sheet2
  EXPORTING #1 = '2-发票明细信息' .
*09.10.2024 17:41:58 :填充数据
  lv_row = '4' .
  LOOP AT gt_alv INTO gs_alv WHERE sel IS NOT INITIAL .
    CALL FUNCTION 'CONVERSION_EXIT_CUNIT_OUTPUT'
      EXPORTING
        input          = gs_alv-meins
        language       = sy-langu
      IMPORTING
        output         = gs_alv-meins
      EXCEPTIONS
        unit_not_found = 1
        OTHERS         = 2.

    CALL METHOD OF gs_sheet1 'Cells' = gs_cell1
      EXPORTING
      #1 = lv_row
      #2 = 1.
    SET PROPERTY OF gs_cell1 'Value' = gs_alv-vbeln .   "发票号
    SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .  "字体默认居中

    CALL METHOD OF gs_sheet1 'Cells' = gs_cell1
      EXPORTING
      #1 = lv_row
      #2 = 6.
    SET PROPERTY OF gs_cell1 'Value' = gs_alv-name .    "客户号
    SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .  "字体默认居中

    CALL METHOD OF gs_sheet1 'Cells' = gs_cell1
      EXPORTING
      #1 = lv_row
      #2 = 7.
    SET PROPERTY OF gs_cell1 'Value' = gs_alv-taxnum .   "客户税号
    SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .  "字体默认居中

    CALL METHOD OF gs_sheet2 'Cells' = gs_cell2
      EXPORTING
      #1 = lv_row
      #2 = 1.
    SET PROPERTY OF gs_cell2 'Value' = gs_alv-vbeln .   "发票号
    SET PROPERTY OF gs_cell2 'HorizontalAlignment' = -4108 .  "字体默认居中

    CALL METHOD OF gs_sheet2 'Cells' = gs_cell2
      EXPORTING
      #1 = lv_row
      #2 = 5.
    SET PROPERTY OF gs_cell2 'Value' = gs_alv-meins .   "单位
    SET PROPERTY OF gs_cell2 'HorizontalAlignment' = -4108 .  "字体默认居中

    CALL METHOD OF gs_sheet2 'Cells' = gs_cell2
      EXPORTING
      #1 = lv_row
      #2 = 6.
    SET PROPERTY OF gs_cell2 'Value' = gs_alv-lfimg .   "数量
    SET PROPERTY OF gs_cell2 'HorizontalAlignment' = -4108 .  "字体默认居中

    CALL METHOD OF gs_sheet2 'Cells' = gs_cell2
      EXPORTING
      #1 = lv_row
      #2 = 7.
    SET PROPERTY OF gs_cell2 'Value' = gs_alv-cmpre .   "单价
    SET PROPERTY OF gs_cell2 'HorizontalAlignment' = -4108 .  "字体默认居中

    CALL METHOD OF gs_sheet2 'Cells' = gs_cell2
      EXPORTING
      #1 = lv_row
      #2 = 8.
    SET PROPERTY OF gs_cell2 'Value' = gs_alv-zhszj .   "金额
    SET PROPERTY OF gs_cell2 'HorizontalAlignment' = -4108 .  "字体默认居中

    CALL METHOD OF gs_sheet2 'Cells' = gs_cell2
      EXPORTING
      #1 = lv_row
      #2 = 9.
    SET PROPERTY OF gs_cell2 'Value' = gs_alv-zsm .   "税率
    SET PROPERTY OF gs_cell2 'HorizontalAlignment' = -4108 .  "字体默认居中

    lv_row = lv_row + 1 .
    CLEAR : gs_alv .
  ENDLOOP .
  CLEAR : lv_row .
*-----------
  CALL METHOD OF gs_wbook 'SAVE'. "保存

  CALL METHOD OF gs_wbook 'CLOSE'."关闭

  CALL METHOD OF gs_excel 'QUIT'. "退出