SAP ABAP EXCEL导出多个SHEET页签

ABAP导出数据到EXCEL指定页签 。

通过如下方法指定SHEET页签。

  CALL METHOD OF
      gv_book
      'Sheets' = gv_sheet
    EXPORTING
      #1       = 2." Sheet页签序号
*--设置excel可见
  CALL METHOD OF
      gv_excel
      'Worksheets' = gv_sheet
    EXPORTING
      #1           = 2.
  CALL METHOD OF
    gv_sheet
    'Activate'.

DATA: gv_excel   TYPE ole2_object,
      gv_sheet   TYPE ole2_object,
      gv_book    TYPE ole2_object,
      gv_rows    TYPE ole2_object,
      gv_range   TYPE ole2_object,       "range
      gv_borders TYPE ole2_object,      "borders
      gv_cell    TYPE ole2_object.


FORM frm_download_to_excel .
*选择文件。
  DATA: l_name     LIKE  wwwdatatab,
        l_mine     LIKE  w3mime OCCURS 10,
        l_title    TYPE  string,
        l_filename TYPE  string,
        l_path     TYPE  string,
        l_fullpath TYPE  string.

  l_name-relid    =   'MI'.
  l_name-objid    =   'ZPPR00060'.
  l_name-text     =   sy-title.
  l_title         = '员工批量报工.XLS'.     "另存为显示名称

  CALL FUNCTION 'WWWDATA_IMPORT'
    EXPORTING
      key               = l_name
    TABLES
      mime              = l_mine
    EXCEPTIONS
      wrong_object_type = 1
      import_error      = 2
      OTHERS            = 3.
  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      window_title         = l_title
      default_extension    = 'xls'
      default_file_name    = l_title
      file_filter          = '*.xls,*.xlsx' "另存为显示的文件扩展名
    CHANGING
      filename             = l_filename
      path                 = l_path
      fullpath             = l_fullpath
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.

  IF sy-subrc <> 0.
    STOP.
  ENDIF.

  IF l_filename = ''.
*    MESSAGE E000(ZP) WITH '操作取消'.
  ENDIF.
*模板下载
  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
      filename = l_fullpath
      filetype = 'BIN'
    TABLES
      data_tab = l_mine.
*  PERFORM frm_progress_indicator USING TEXT-002.
*--打开excel模板
  PERFORM frm_open_excel USING l_fullpath.
*--向excel写入人员信息
  PERFORM frm_write_excel_person_sheet."向EXCEL中写入数据
*写入订单工序信息
  PERFORM frm_write_excel_aufnr_sheet."向EXCEL中写入数据
*设置单元格边框。
  PERFORM frm_range_borders.
  SET PROPERTY OF gv_excel 'Visible' = 1.
*保存数据并释放对象。
  PERFORM frm_save_excel USING l_fullpath.
ENDFORM.

FORM frm_open_excel USING pa_path.
*创建应用程序对象
  CREATE OBJECT gv_excel 'Excel.Application'.
*创建工作簿
  CALL METHOD OF
    gv_excel
      'Workbooks' = gv_book.
*打开指定路径的工作簿。
  CALL METHOD OF
      gv_book
      'Open' = gv_book
    EXPORTING
      #1     = pa_path.

**获取指定SHEET页
**  CALL METHOD OF
**      gv_book
**      'Sheets' = gv_sheet
**    EXPORTING
**      #1       = 1.
**--设置excel可见
*  CALL METHOD OF
*      gv_excel
*      'Worksheets' = gv_sheet
*    EXPORTING
*      #1           = 1.
*  CALL METHOD OF
*    gv_sheet
*    'Activate'.

ENDFORM. " FRM_OPEN_EXCEL


FORM frm_write_excel_person_sheet.
  DATA:lw_person_tab LIKE typ_person.
*获取人员信息SHEET页签
  DATA:l_num TYPE int4.
  CALL METHOD OF
      gv_book
      'Sheets' = gv_sheet
    EXPORTING
      #1       = 2.
*--设置excel可见
  CALL METHOD OF
      gv_excel
      'Worksheets' = gv_sheet
    EXPORTING
      #1           = 2.
  CALL METHOD OF
    gv_sheet
    'Activate'.

  l_num = 2.
  LOOP AT gt_person_tab INTO lw_person_tab.
*向EXCEL中写数据。
    PERFORM frm_write_cell USING l_num  1   lw_person_tab-pernr  .
    PERFORM frm_write_cell USING l_num  2   lw_person_tab-name  ."
    PERFORM frm_write_cell USING l_num  3   lw_person_tab-orang_desc .
    l_num = l_num + 1.
  ENDLOOP.
ENDFORM.

FORM frm_write_excel_aufnr_sheet .
  DATA:lw_person_tab LIKE typ_person.
  DATA:lw_aufnr_tab LIKE  typ_aufnr.
  DATA:lw_result_tab LIKE typ_aufnr.
*获取人员信息SHEET页签
  DATA:l_num TYPE int4.
  CALL METHOD OF
      gv_book
      'Sheets' = gv_sheet
    EXPORTING
      #1       = 3.
*--设置excel可见
  CALL METHOD OF
      gv_excel
      'Worksheets' = gv_sheet
    EXPORTING
      #1           = 3.
  CALL METHOD OF
    gv_sheet
    'Activate'.

  l_num = 2.
  LOOP AT gt_result_tab INTO lw_result_tab WHERE checkbox EQ 'X'.
    LOOP AT gt_aufnr_tab INTO lw_aufnr_tab WHERE aufnr EQ lw_result_tab-aufnr.
*    生产订单  开始日期  结束日期  订单数量  工序编号  工序描述
*向EXCEL中写数据。
      PERFORM frm_write_cell USING l_num  1   lw_aufnr_tab-aufnr  .
      PERFORM frm_write_cell USING l_num  2   lw_aufnr_tab-gstrp   ."
      PERFORM frm_write_cell USING l_num  3   lw_aufnr_tab-gltrp .
      PERFORM frm_write_cell USING l_num  4   lw_aufnr_tab-psmng .
      PERFORM frm_write_cell USING l_num  5   lw_aufnr_tab-charg .
      PERFORM frm_write_cell USING l_num  6   lw_aufnr_tab-vornr .
      PERFORM frm_write_cell USING l_num  7   lw_aufnr_tab-description .
      PERFORM frm_write_cell USING l_num  8   lw_aufnr_tab-matnr .
      PERFORM frm_write_cell USING l_num  9   lw_aufnr_tab-maktx .
      l_num =  l_num + 1.
    ENDLOOP.
  ENDLOOP.
ENDFORM.

FORM frm_save_excel USING pa_fullpath.
  SET PROPERTY OF gv_excel 'DisplayAlerts' = 0.
*  CALL METHOD OF
*    gv_book
*    'SAVE'
*    EXPORTING
*      #1 = pa_fullpath.
  CALL METHOD OF
    gv_book
    'SAVE'.
  CALL METHOD OF
    gv_book
      'Exit' = gv_book.
  CALL METHOD OF gv_book 'Close'
    EXPORTING
      #1 = 0.

*退出应用程序
  CALL METHOD OF gv_excel 'QUIT'.
*释放用到的对象
  FREE OBJECT gv_cell.
  FREE OBJECT gv_range.
  FREE OBJECT gv_borders.
  FREE OBJECT gv_rows. "not used
  FREE OBJECT gv_sheet.
  FREE OBJECT gv_book.
  FREE OBJECT gv_excel.
ENDFORM. " FRM_SAVE_EXCEL


FORM frm_write_cell USING pa_row
                               pa_col
                               pa_val.
  CALL METHOD OF
      gv_excel
      'Cells' = gv_cell
    EXPORTING
      #1      = pa_row
      #2      = pa_col.
  SET PROPERTY OF gv_cell 'Value' = pa_val.
ENDFORM. "FRM_WRITE_CELL