SAP OLE excel匯出多個sheet頁

效果圖

第一個sheet頁：

第二個sheet頁：

*整體思路：
*先打開excel，整理好資料
*1.拷貝模板，復制sheet頁  PERFORM frm_add_excel_sheet .
*2.在遍歷中打開sheet頁，根據sheet頁填充單元格 PERFORM frm_open_excel_sheet USING sy-tabix .
*3.

主程式
定義變數

CONSTANTS:  c_local_path(10) TYPE  c           VALUE 'C:\ERP\',        " 檔案夾本地路徑
            c_str            TYPE  w3objid     VALUE '檢修計劃資訊匯出',       " EXCEL模板-申請表匯總
            c_objid_header   TYPE  w3objid     VALUE 'ZPMRPT004'.      " EXCEL模板-檢修計劃ole

DATA:  gv_filename    TYPE rlgrap-filename,     " 檔案本地路徑
       l_cell_content TYPE string.
DATA: gv_file_name  TYPE   string.               " 檔案本地路徑
DATA: gv_path TYPE string.
DATA: gv_fullpath TYPE string.                   "檔案全名

  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
以下均為主程式跳轉具體代碼，

獲取下載檔案路徑

*&---------------------------------------------------------------------*
*&      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

初始化OLE

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

添加sheet頁

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.

打開sheet頁

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.

關閉退出OLE

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