SAP OLE excel导出多个sheet页

*整体思路：
*先打开excel，整理好数据
*1.拷贝模板，复制sheet页  PERFORM frm_add_excel_sheet .
*2.在遍历中打开sheet页，根据sheet页填充单元格 PERFORM frm_open_excel_sheet USING sy-tabix .
*3.

  CLEAR gv_filename.
*  获取下载文件路径
  PERFORM f_frm_download_excel_module.
  CHECK gv_filename IS NOT INITIAL.
*  CONDENSE l_filename NO-GAPS.
*  显示处理进度
  PERFORM f_process_indcator USING text-002 0 .           "初始化GUI

*  下载模板
  PERFORM f_download_excel_fromserver USING c_objid_header
                                            gv_filename.    "从服务器中下载EXCEL模板到L_FILENAME中
*  显示处理进度
  PERFORM f_process_indcator USING text-004 0 .      "初始化GUI"
*  初始化OLE
  PERFORM f_initialization USING g_excel
                                 g_wbook
                                 gv_filename
                                 g_sheet
                        CHANGING g_excel1.
  g_tempsheet = g_sheet.
  PERFORM frm_deal_data.
  PERFORM frm_process_data.
  PERFORM frm_add_excel_sheet .
  LOOP AT gt_item INTO gs_item.
    PERFORM frm_open_excel_sheet USING sy-tabix .
    PERFORM f_write_head_row USING gs_item.
    PERFORM f_write_mat_row USING gs_item .
    PERFORM f_write_sp_row USING gs_item.
  ENDLOOP.
  SET PROPERTY OF g_excel1 'CutCopyMode' = 0.
*  SET PROPERTY OF l_excel1 'Visible' = 1.
*  关闭退出OLE
  PERFORM f_close_file CHANGING g_excel1 sy-subrc.

*&---------------------------------------------------------------------*
*&      Form  F_FRM_DOWNLOAD_EXCEL_MODULE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form f_frm_download_excel_module .

  data: l_str type w3objid.

  "下载下来的文件名称
  if sy-ucomm = 'FC_OLE'."分厂汇总表
    l_str = c_str1.
  elseif sy-ucomm = 'MX_OLE'."明细表
    l_str = c_str2.
  elseif sy-ucomm = 'LHA_OLE'."炼化汇总表
    l_str = c_str.
  elseif sy-ucomm = 'EXPORT'."检修计划ole
    l_str = c_str.
  elseif sy-ucomm = 'EXPORT_B'."检修计划ole
    l_str = c_str3.
  endif.

  concatenate c_local_path l_str sy-datum sy-uzeit '.xlsx' into gv_filename.
  gv_file_name = gv_filename.
*  获取文件名称
  call method cl_gui_frontend_services=>file_save_dialog
    exporting
      window_title      = 'Excel file name'
      default_extension = 'xlsx'
      default_file_name = gv_file_name
    changing
      filename          = gv_file_name
      path              = gv_path
      fullpath          = gv_fullpath.
  if sy-subrc = 0.
    gv_filename = gv_fullpath.
  endif.
endform.

form f_process_indcator using pl_text       type string
                              pl_percentage type n.

* 显示程序处理进度
  call function 'SAPGUI_PROGRESS_INDICATOR'
    exporting
      percentage = pl_percentage            "进度
      text       = pl_text.                 "文本

endform.                    "FRM_PROCESS_DICATOR

form f_download_excel_fromserver using pl_objid type wwwdatatab-objid
                                       pl_dest  type rlgrap-filename.

  data: l_objdata like wwwdatatab,
        l_mime    like w3mime,
        l_objnam  type string,
        l_rc      like sy-subrc,
        l_errtxt  type string.

  concatenate pl_objid '.xlsx' into l_objnam.
  condense l_objnam no-gaps.

* 检查模板是否存在
  select single relid objid from wwwdata
    into (l_objdata-relid, l_objdata-objid)
    where srtf2    = 0                         "计数器
      and relid    = 'MI'                      "区域
      and objid    = pl_objid.                 "对象名

* 模板不存在
  if sy-subrc <> 0 or l_objdata-objid = space.
    message e002(zfico) with l_objnam.
  endif.

* 下载模板到指定路径
  call function 'DOWNLOAD_WEB_OBJECT'
    exporting
      key         = l_objdata                   "对象名
      destination = pl_dest                     "用于装载/卸载的局部文件
    importing
      rc          = l_rc.

* 下载模板失败
  if l_rc <> 0.
    message e001(zfico) with l_objnam.
  endif.

endform.                    "FRM_DOWNLOAD_EXCEL_FROMSERVER

form f_process_indcator using pl_text       type string
                              pl_percentage type n.

* 显示程序处理进度
  call function 'SAPGUI_PROGRESS_INDICATOR'
    exporting
      percentage = pl_percentage            "进度
      text       = pl_text.                 "文本

endform.                    "FRM_PROCESS_DICATOR

form f_initialization using excel wbook filename tempsheet
                    changing p_excel1 type ole2_object.
*  创建OLE运用文件
  create object excel 'Excel.Application'.
  if sy-subrc <> 0.
    message e899(mm) with '创建EXCEL文件失败'.
  endif.
  p_excel1 = excel.
  call method of excel 'Workbooks' = wbook.
  call method of wbook 'Open' = wbook
    exporting
    #1 = filename.
  if sy-subrc <> 0.
    message e899(mm) with 'Can not OPEN EXCEL File:' filename.
  endif.

*  设置文件的可显示状态
  set property of excel 'Visible' = 1.
*  SET PROPERTY OF P_EXCEL1 'Visible' = 1.
  set property of excel  'screenupdating'  = 1.


*  设置模板
  call method of wbook 'Sheets' = tempsheet
    exporting
    #1 = 1.


endform.                               " F_INITIALIZATION

FORM frm_add_excel_sheet .


  DATA: l_str TYPE string.
  DATA: l_dx TYPE i.
  DATA: l_lines TYPE i.

  CLEAR: l_str.

  l_dx = 1.

  CLEAR: l_lines.
  DESCRIBE TABLE gt_item LINES l_lines.
  l_lines = l_lines - 1.

  DO  l_lines TIMES.
    "拷贝现有 sheet
    CALL METHOD OF g_sheet 'copy'
      EXPORTING
        #1 = g_sheet.
    "sheet 重命名

    CLEAR: l_str.
    l_dx = l_dx + 1.
    l_str = l_dx.
    CONDENSE l_str NO-GAPS.
    CONCATENATE '第' l_str INTO l_str.
    CONDENSE l_str NO-GAPS.
    CONCATENATE l_str '页' INTO l_str.
    CONDENSE l_str NO-GAPS.
    SET PROPERTY OF g_sheet 'name' = l_str.
  ENDDO.


*  " 创建sheet并添加到工作表
*  set property of g_excel 'SheetsInNewWorkbook' = l_lines.   "如需多个sheets,将1改成相应的值
*  call method of g_wbook 'ADD' = g_sheet.
*
  CALL METHOD OF g_sheet 'ACTIVATE'.


ENDFORM.

FORM frm_open_excel_sheet  USING    p_sy_tabix TYPE sytabix .

  DATA: l_dx TYPE i.
  g_tabix = p_sy_tabix.
  l_dx = g_tabix.

  CALL METHOD OF g_wbook 'SHEETS' = g_sheet
    EXPORTING
    #l_dx = l_dx.

  CALL METHOD OF g_sheet 'ACTIVATE'.


ENDFORM.

form f_write_cell using sheet
                         row
                         col
                         value.

*  DATA: CELL TYPE OLE2_OBJECT.

  call method of sheet 'Cells' = g_cell no flush
    exporting
    #1 = row                        "行
    #2 = col.                       "列
*  GET PROPERTY OF g_cell 'Font' = g_font.
*  set property of g_font 'Bold' = 0 .
  set property of g_cell 'Value' = value no flush.
*  set property of g_cell 'horizontalAlignment' = 3. "3表示在单元格中居中显示


endform.                               " F_WRITE_CELL

FORM f_write_mat_row USING gs_item TYPE ty_item .

  DATA: l_num TYPE i.
  DATA: l_num1 TYPE i.
  DATA: l_flag TYPE c.

  l_num = 6.

  REFRESH gt_item_tmp.
  gt_item_tmp = gt_item_mat_info.
  DELETE gt_item_tmp WHERE qmnum <> gs_item-qmnum OR serial <> gs_item-serial.

  LOOP AT gt_item_tmp INTO gs_item_mat_info." where qmnum = gs_item-qmnum and serial = gs_item-serial.

    CLEAR: l_num1,l_flag.

    l_num = l_num + 1.
    l_num1 = l_num + 1.

    AT LAST.
      l_flag = 'X'.
    ENDAT.

*  拷贝行
    IF l_flag IS INITIAL .
      PERFORM  f_paste_rows_mutl_sheet USING g_sheet  l_num l_num l_num1 l_num1.
    ENDIF.

    PERFORM f_write_cell USING g_sheet l_num 'A' gs_item_mat_info-xh.    "序号
    PERFORM f_write_cell USING g_sheet l_num 'B' gs_item_mat_info-matkl.    "序号
    PERFORM f_write_cell USING g_sheet l_num 'C' gs_item_mat_info-matklx.    "序号
    PERFORM f_write_cell USING g_sheet l_num 'E' gs_item_mat_info-matnr.    "序号
    PERFORM f_write_cell USING g_sheet l_num 'F' gs_item_mat_info-maktx.    "序号
    PERFORM f_write_cell USING g_sheet l_num 'I' gs_item_mat_info-meins.    "序号
    PERFORM f_write_cell USING g_sheet l_num 'J' gs_item_mat_info-jhsl.    "序号
    PERFORM f_write_cell USING g_sheet l_num 'K' gs_item_mat_info-verpr_yg.    "序号
    PERFORM f_write_cell USING g_sheet l_num 'L' gs_item_mat_info-jshj.    "序号
    PERFORM f_write_cell USING g_sheet l_num 'M' gs_item_mat_info-tbcj.    "序号
    PERFORM f_write_cell USING g_sheet l_num 'N' gs_item_mat_info-zbz.    "序号

  ENDLOOP.

  CLEAR: g_index.
  IF l_num1 IS INITIAL.
    l_num1 = 8.
  ELSE.
    g_index = l_num1 .
  ENDIF.


*  总计行:写入
  SORT gt_item_mat_sum BY qmnum serial.
  READ TABLE gt_item_mat_sum INTO gs_item_mat_sum WITH KEY qmnum = gs_item-qmnum
                                                            serial = gs_item-serial BINARY SEARCH.
  IF sy-subrc = 0.
    PERFORM f_write_cell USING g_sheet l_num1 'J' gs_item_mat_sum-jhsl.    "计划数量
    PERFORM f_write_cell USING g_sheet l_num1 'L' gs_item_mat_sum-jshj.    "价税合计
  ENDIF.



ENDFORM.

FORM f_paste_rows_mutl_sheet  USING i_application TYPE ole2_object
                        i_start_source_row TYPE i
                        i_end_source_row TYPE i
                        i_start_target_row TYPE i
                        i_end_target_row TYPE i.

  DATA:l_row_str1 TYPE string,
       l_row_str2 TYPE string,
       l_row_str  TYPE string.

  DATA:l_range TYPE ole2_object.

  DATA: l_tabix TYPE i.

  l_row_str1 = i_start_source_row.
  l_row_str2 = i_end_source_row.
  CONCATENATE l_row_str1 ':' l_row_str2 INTO l_row_str.
  CONDENSE l_row_str NO-GAPS.
*定位行
  l_tabix = g_tabix.
  CALL METHOD OF i_application 'rows' = l_range
    EXPORTING
    #l_tabix = l_row_str.
*选择行
  CALL METHOD OF l_range 'select'.
*复制行
  CALL METHOD OF l_range 'COPY'.

  l_row_str1 = i_start_target_row.
  l_row_str2 = i_end_target_row.
  CONCATENATE l_row_str1 ':' l_row_str2 INTO l_row_str.
  CONDENSE l_row_str NO-GAPS.
*定位行
  CALL METHOD OF i_application 'rows' = l_range
    EXPORTING
    #l_tabix = l_row_str.
*选择行
  CALL METHOD OF l_range 'select'.
*插入行
  CALL METHOD OF l_range 'insert'.
ENDFORM.

form f_close_file changing pl_application type ole2_object
                           pl_subrc       type sy-subrc.

  data l_workbook  type ole2_object.

* 得到当前活动的工作簿
  get property of pl_application 'ACTIVEWORKBOOK' = l_workbook.

* 保存工作簿
  call method of l_workbook 'SAVE'.

* 关闭工作簿
  call method of l_workbook 'CLOSE'.

* 退出Excel进程
  call method of pl_application 'QUIT'.

* 退出是否成功状态返回值
  pl_subrc = sy-subrc.

endform.                    "f_close_file