使用DOI保存多Sheet

*&---------------------------------------------------------------------*
*& Report Z3426EXCEL004
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT z3426excel004.

DATA:BEGIN OF gt_bukrs OCCURS 0,
       bukrs     TYPE t001-bukrs,
       butxt     TYPE t001-butxt,
       sheetname TYPE string,
     END OF gt_bukrs.
DATA: BEGIN OF gt_file OCCURS 0,        "下载到本地格式
        bukrs(4)   TYPE c,
        field1(22) TYPE c,
        field2(22) TYPE c,
        field3(22) TYPE c,
        field4(22) TYPE c,
        field5(22) TYPE c,
        field6(22) TYPE c,
      END OF gt_file.


gt_bukrs[] = VALUE #( ( bukrs = '1000' butxt = '测试公司0')
                    ( bukrs = '1001' butxt = '测试公司1')
                    ( bukrs = '1002' butxt = '测试公司2')
                    ( bukrs = '1003' butxt = '测试公司3')
                    ( bukrs = '1004' butxt = '测试公司4')
                    ( bukrs = '1005' butxt = '测试公司5') ).

gt_file[] = VALUE #( ( bukrs = '1000' field1 = '测试' field2 = '测试' field3 = '测试' )
                     ( bukrs = '1001' field1 = '测试' field2 = '测试' field3 = '测试' )
                     ( bukrs = '1002' field1 = '测试' field2 = '测试' field3 = '测试' )
                     ( bukrs = '1003' field1 = '测试' field2 = '测试' field3 = '测试' )
                     ( bukrs = '1004' field1 = '测试' field2 = '测试' field3 = '测试' )
                     ( bukrs = '1005' field1 = '测试' field2 = '测试' field3 = '测试' )
                     ).

DATA(filename) = 'C:\Users\XXXX\Desktop\Test\测试数据.xls'.
PERFORM download_file.

FORM download_file.
  INCLUDE ole2incl.
  DATA:lv_str TYPE string.
  DATA: excel     TYPE ole2_object,
        tempsheet TYPE ole2_object,
        sheet     TYPE ole2_object,
        cell      TYPE ole2_object,
        range     TYPE ole2_object,
        workbook  TYPE ole2_object,
        xlsname   TYPE string VALUE 'D:\测试数据.xls',
        line      TYPE i VALUE 0.

  CREATE OBJECT excel 'excel.application'.
  SET PROPERTY OF excel 'visible' = 0.
  CALL METHOD OF excel 'workbooks' = workbook.
  CALL METHOD OF workbook 'add'.
  CALL METHOD OF excel 'sheets' = sheet.
  CALL METHOD OF excel 'cells' = cell EXPORTING #1 = 1 #2 = 1.
  SET PROPERTY OF cell  'value' = '公司代码'.
  CALL METHOD OF excel 'cells' = cell EXPORTING #1 = 1 #2 = 2.
  SET PROPERTY OF cell  'value' = '公司代码描述'.

  LOOP AT gt_bukrs.
    line = sy-tabix + 1.
    CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 1.
    SET PROPERTY OF cell  'value' = gt_bukrs-bukrs.
    CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 2.
    SET PROPERTY OF cell  'value' = gt_bukrs-butxt.
  ENDLOOP.

  LOOP AT gt_bukrs.
    CALL METHOD OF excel 'sheets' = sheet.
    CALL METHOD OF sheet 'add'.
    CALL METHOD OF excel 'worksheets' = sheet EXPORTING #1 = gt_bukrs-sheetname.
    CALL METHOD OF sheet 'activate '.
    SET PROPERTY OF sheet 'name' = gt_bukrs-bukrs.
    CLEAR:line.

    line = line + 1.
    lv_str = '表头区域'.
    CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 1.
    SET PROPERTY OF cell  'value' = lv_str.
    CALL METHOD OF excel 'Range' = range
    EXPORTING
      #1 = 'A1'
      #2 = 'F1'.
    CALL METHOD OF range 'SELECT'.
    CALL METHOD OF range 'MERGE'.
    SET PROPERTY OF range  'MergeCells' = 1.
    SET PROPERTY OF range  'HorizontalAlignment' = 3.   "横向对齐
    SET PROPERTY OF range  'VerticalAlignment' = 2.

    line = line + 1.
    CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 1.
    SET PROPERTY OF cell  'value' = '编制单位'.

    CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 2.
    SET PROPERTY OF cell  'value' = gt_bukrs-butxt.

    CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 3.
    SET PROPERTY OF cell  'value' = `期间`.

    lv_str = sy-datum+0(4) && `年` && sy-datum+4(2) && `月`.
    CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 4.
    SET PROPERTY OF cell  'value' = lv_str.

    CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 5.
    SET PROPERTY OF cell  'value' = '单位'.

    CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 6.
    SET PROPERTY OF cell  'value' = '人民币元'.

    line = line + 1.
    CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 1.
    SET PROPERTY OF cell  'value' = '项目'.

    CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 2.
    SET PROPERTY OF cell  'value' = '行次'.

    CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 3.
    SET PROPERTY OF cell  'value' = '本月'.

    CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 4.
    SET PROPERTY OF cell  'value' = '本季'.

    CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 5.
    SET PROPERTY OF cell  'value' = '本年'.

    CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 6.
    SET PROPERTY OF cell  'value' = '上年'.

    LOOP AT gt_file WHERE bukrs = gt_bukrs-bukrs.
      line = line + 1.

      CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 1.
      SET PROPERTY OF cell  'value' = gt_file-field1.

      CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 2.
      SET PROPERTY OF cell  'value' = gt_file-field2.

      CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 3.
      SET PROPERTY OF cell  'value' = gt_file-field3.

      CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 4.
      SET PROPERTY OF cell  'value' = gt_file-field4.

      CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 5.
      SET PROPERTY OF cell  'value' = gt_file-field5.

      CALL METHOD OF excel 'cells' = cell EXPORTING #1 = line #2 = 6.
      SET PROPERTY OF cell  'value' = gt_file-field6.
    ENDLOOP.

  ENDLOOP.
  xlsname = filename.
  GET PROPERTY OF excel 'ActiveWorkbook ' = workbook.
  CALL METHOD OF workbook 'saveas' EXPORTING #1 = xlsname #2 = 1.
  CALL METHOD OF workbook 'close'.
  CALL METHOD OF excel 'quit'.
  FREE OBJECT workbook.
  FREE OBJECT excel.
  MESSAGE s001(00) WITH '文件已下载完成!'.
ENDFORM.