ABAP EXCEL导入与导出（二）

需求场景：

上一篇博客记录按照EXCEL模板导入数据，这一片博客记录一下以EXCEL格式导出数据。
导出EXCEL的方式有多种，实现方式常见的有OLE,DOI和直接操作EXCEL文件（XML格式文件或者一般文本格式文件）；OLE和DOI使用方便，但是缺点是不能脱离GUI使用；直接操作Excel文件方式适用性更强，常用在web端接口或者邮件发送，其中代表性的是ABAP2XLSX。
本篇主要是记录一下以OLE方式导出EXCEL文件的实现过程。

实现过程

1. 选择保存路径
2. 下载模板
3. 打开EXCEL模板
4. 把内表数据填充到EXCEL
5. 保存EXCEL文件

关键代码

DATA: g_fullname TYPE string.

DATA:   go_excel  TYPE ole2_object,
        go_book   TYPE ole2_object,
        go_books  TYPE ole2_object,
        go_sheet  TYPE ole2_object,
        go_sheets TYPE ole2_object,
        go_cell   TYPE ole2_object,
        go_cells  TYPE ole2_object,
        go_cell1  TYPE ole2_object,
        go_cell2  TYPE ole2_object.

FORM frm_download_excel USING p_flag.
  DATA: l_err TYPE flag.
  DATA: l_objid LIKE wwwdata-objid."改为按模板下载数据，因此定义为全局变量

  l_objid = 'ZMMT001'.

  IF gt_output[] IS INITIAL.
    MESSAGE '没有数据' TYPE 'S' DISPLAY LIKE 'E'.
    EXIT.
  ENDIF.

  "选择保存路径
  PERFORM sub_get_save_file_path CHANGING l_err.
  CHECK l_err = space.  "check后面表达式为false时，跳出当前form

  "下载模板
  PERFORM sub_download_excel_template USING l_objid CHANGING l_err.
  IF l_err <> space.
    MESSAGE l_err TYPE 'S' DISPLAY LIKE 'E'.
    EXIT.
  ENDIF.

  "打开EXCEL模板
  PERFORM sub_open_excel_file.

  "把内表数据填充到EXCEL
  PERFORM sub_fill_data_to_excel USING p_flag.

  "保存EXCLE文件
  PERFORM sub_excel_save.
ENDFORM.                    " FRM_DOWNLOAD_EXCEL

"选择文件保存路径及文件名
FORM sub_get_save_file_path CHANGING l_err TYPE flag.
  DATA: l_filename TYPE string,
        l_path     TYPE string.

  CLEAR:l_filename, l_path, l_err ,g_fullname.

  l_filename = '国内对账单.xls'.

  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      default_extension    = 'XLSX'
      default_file_name    = l_filename
    CHANGING
      filename             = l_filename
      path                 = l_path
      fullpath             = g_fullname
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.
  IF sy-subrc <> 0.
    MESSAGE s001(00) DISPLAY LIKE 'E'.
  ENDIF.
  IF g_fullname = space.
    l_err = 'X'.
  ENDIF.
ENDFORM.                    " SUB_GET_SAVE_FILE_PATH

"下载EXCEL模板
FORM sub_download_excel_template USING p_objid  LIKE wwwdata-objid CHANGING p_error TYPE flag.
  DATA: ls_key        LIKE wwwdatatab,
        l_destination LIKE rlgrap-filename,
        l_rc          LIKE sy-subrc.
  DATA: l_exist TYPE flag.

  p_error = 'X'.

  CLEAR l_exist.
  PERFORM sub_check_template_exist USING p_objid CHANGING l_exist.
  CHECK l_exist = 'X'.

  CLEAR:p_error, ls_key,l_destination,l_rc.
  ls_key-relid = 'MI'.
  ls_key-objid = p_objid.
  l_destination = g_fullname.

  CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
    EXPORTING
      key         = ls_key
      destination = l_destination
    IMPORTING
      rc          = l_rc.

  IF l_rc = 0.
    CLEAR p_error.
  ENDIF.

ENDFORM.

FORM sub_open_excel_file .
  CREATE OBJECT go_excel 'EXCEL.APPLICATION' ."创建EXCEL对象
  SET PROPERTY OF go_excel 'VISIBLE' = 0."设置EXCEL属性可视
  CALL METHOD OF go_excel 'WORKBOOKS' = go_book. "激活工作簿

  CALL METHOD OF go_book 'OPEN'
    EXPORTING
      #1 = g_fullname. "打开指定路径EXCEL工作簿

  CALL METHOD OF go_excel 'WORKSHEETS' = go_sheet
    EXPORTING
    #1 = 1.

  CALL METHOD OF go_sheet 'SELECT'.

  CALL METHOD OF go_sheet 'ACTIVATE'. "sheet 激活
ENDFORM.                    " SUB_OPEN_EXCEL_FILE

FORM sub_fill_data_to_excel USING p_flag.
  DATA: l_row  TYPE i,
        l_row2 TYPE i,
        l_no   TYPE i.
  DATA: l_val TYPE char30.
  DATA: l_mseh6 LIKE t006a-mseh6.
  DATA: l_jyxm TYPE char200.
  DATA:lv_dzqj     TYPE string,
       lv_line(8)  TYPE n,
       lv_text(20).

  DATA:BEGIN OF ls_slhj,
         zfretax LIKE zmmtydanf-zfretax,
         zfreje  LIKE zmmtydanf-zfreje,
       END OF ls_slhj,
       lt_slhj LIKE TABLE OF ls_slhj.

  CONCATENATE gv_date_begin+0(4) '年' gv_date_begin+4(2) '月' gv_date_begin+6(2) '日' '-' gv_date_end+0(4) '年' gv_date_end+4(2) '月' gv_date_end+6(2) '日' INTO lv_dzqj.
  "填充抬头
  PERFORM sub_fill_range USING 3 2 gv_name1.
  PERFORM sub_fill_range USING 4 2 gv_lifadr.
  PERFORM sub_fill_range USING 3 13 gv_butxt.
  PERFORM sub_fill_range USING 4 13 gv_tyfadr.
  PERFORM sub_fill_range USING 5 2 gv_vtext.
  PERFORM sub_fill_range USING 5 5 gv_fktj.
  PERFORM sub_fill_range USING 5 8 lv_dzqj.
  "填充项目
  l_row = 7.
  LOOP AT gt_output INTO DATA(ls_output).
    IF l_row > 14. "如果超过8行数据，自动插入新行处理
      PERFORM excel_row_insert USING l_row.
    ENDIF.

    PERFORM sub_fill_range USING l_row 1 ls_output-ztydanno.
    PERFORM sub_fill_range USING l_row 2 ls_output-wadat.
    PERFORM sub_fill_range USING l_row 3 ls_output-shdhs.
    PERFORM sub_fill_range USING l_row 4 ls_output-sortls.
    PERFORM sub_fill_range USING l_row 5 ls_output-zfrename.
    IF p_flag = '1'.
      PERFORM sub_fill_range USING l_row 11 ls_output-zjz.
      PERFORM sub_fill_range USING l_row 12 ls_output-zfresl.
      PERFORM sub_fill_range USING l_row 13 ls_output-zfredj.
      PERFORM sub_fill_range USING l_row 14 ls_output-zfretax.
      PERFORM sub_fill_range USING l_row 15 ls_output-zfreje.
    ENDIF.
    PERFORM sub_fill_range USING l_row 16 ls_output-remrk.
    PERFORM sub_fill_range USING l_row 6 ls_output-zwzc_t.
    PERFORM sub_fill_range USING l_row 7 ls_output-zwxp_t.
    PERFORM sub_fill_range USING l_row 8 ls_output-zarea_t.
    PERFORM sub_fill_range USING l_row 9 ls_output-zcity_t.
    PERFORM sub_fill_range USING l_row 10 ls_output-zcountry_t.

    ADD 1 TO l_row.
    CLEAR:ls_output.
  ENDLOOP.
  "填充合计
  IF p_flag = '1'.
    REFRESH:lt_slhj.

    LOOP AT gt_output INTO ls_output.
      MOVE-CORRESPONDING ls_output TO ls_slhj.
      COLLECT ls_slhj INTO lt_slhj.
      CLEAR:ls_output.
    ENDLOOP.

    CLEAR:lv_line,l_row2.
    lv_line = lines( gt_output ).
    IF lv_line > 8.
      l_row2 = 15 + lv_line - 8.
    ELSE.
      l_row2 = 15.
    ENDIF.

    l_row = l_row2.
    LOOP AT lt_slhj INTO ls_slhj.
      IF l_row > l_row2.
        PERFORM excel_row_insert USING l_row.
      ENDIF.
      CLEAR:lv_text.
      CONCATENATE '本月合计（税率 ' ls_slhj-zfretax '%)' INTO lv_text.
      PERFORM sub_fill_range USING l_row 1 lv_text.
      PERFORM sub_fill_range USING l_row 3 ls_slhj-zfreje.
      ADD 1 TO l_row.
      CLEAR:ls_slhj.
    ENDLOOP.
  ENDIF.
ENDFORM.                    " SUB_FILL_DATA_TO_EXCEL

FORM sub_fill_range USING p_row p_col p_value.
  DATA:l_row TYPE i,
       l_col TYPE i.
  l_row = p_row.
  l_col = p_col.

  CALL METHOD OF go_excel 'CELLS' = go_cell
    EXPORTING
    #1 = l_row
    #2 = l_col.

  CALL METHOD OF go_cell 'Select'.

  SET PROPERTY OF go_cell 'VALUE' = p_value.
ENDFORM.

FORM excel_row_insert  USING p_row TYPE i.
  DATA lo_range TYPE ole2_object.
  DATA: l_row TYPE i.

  l_row = p_row - 1.
  CALL METHOD OF go_sheet 'Rows' = lo_range    "call method
    EXPORTING #1 = l_row.

  CALL METHOD OF lo_range 'Copy'.

  ADD 1 TO l_row.
  CALL METHOD OF go_sheet 'Rows' = lo_range
    EXPORTING #1 = l_row.

  CALL METHOD OF lo_range 'Insert'.

  CALL METHOD OF go_sheet 'Rows' = lo_range
     EXPORTING #1 = l_row.

  CALL METHOD OF lo_range 'ClearContents'. "清空Cell
ENDFORM.                    " EXCEL_ROW_INSERT

FORM sub_excel_save .
*  CALL METHOD OF go_excel 'Run'
*    EXPORTING
*      #1 = 'setApp2'.

  GET PROPERTY OF go_excel 'ActiveSheet' = go_sheet. "获取活动SHEET
  GET PROPERTY OF go_excel 'ActiveWorkbook' = go_book.
  CALL METHOD OF go_book 'SAVE'.

  SET PROPERTY OF go_excel 'Visible' = 1.  "是否显示EXCEL 此处显示不退出

  FREE OBJECT go_sheet.
  FREE OBJECT go_book.
  FREE OBJECT go_excel.
ENDFORM.                    " SUB_EXCEL_SAVE

遇到的问题

因为公司内部在推行WPS，但是使用OLE这种方式导出的话可能会存在空白情况。