OLE 复制excel模版sheet页

PERFORM frm_copy_sheet USING lv_total_page ls_input-fullpath 'sheet1' 'sheet'.
*&---------------------------------------------------------------------*
*& Form FRM_COPY_SHEET
*&---------------------------------------------------------------------*
*& pv_page 总页数
*& p_path 文件模版路径
*& p_src 复制sheet页名
*& pv_base 复制sheet页基础名
*&---------------------------------------------------------------------*
*&      --> LV_SHEET
*&---------------------------------------------------------------------*
FORM frm_copy_sheet  USING pv_page p_path p_src pv_base.
  TYPES:BEGIN OF ty_name,
          name TYPE string,
        END OF ty_name.
  DATA lv_temp_name  TYPE string.
  DATA  lv_new_name   TYPE string.
  DATA lt_names TYPE TABLE OF ty_name WITH DEFAULT KEY.
  DATA lv_temp_sheet TYPE ole2_object.
  DATA: excel          TYPE ole2_object,
        workbooks      TYPE ole2_object,
        workbook       TYPE ole2_object,
        lv_count       TYPE i,
        lv_sheet_count TYPE i,
        first_sheet    TYPE ole2_object,
        source_sheet   TYPE ole2_object,
        new_sheet      TYPE ole2_object.

  DATA(lv_times) = pv_page - 1.
* 1. 启动Excel并打开模板
  CREATE OBJECT gcl_excel 'Excel.Application'.
  SET PROPERTY OF gcl_excel 'Visible' = 0.

  GET PROPERTY OF gcl_excel 'Workbooks' = workbooks.
  CALL METHOD OF workbooks 'Open' = workbook EXPORTING #1 = p_path.

* 2. 获取源工作表
  CALL METHOD OF gcl_excel 'Worksheets' = source_sheet EXPORTING #1 = p_src.

* 3. 预加载所有现有工作表名称（提升性能）
  GET PROPERTY OF gcl_excel 'Sheets' = gcl_sheets.
  GET PROPERTY OF gcl_sheets 'Count' = lv_sheet_count.

  " 将现有名称存入内部表
  DO lv_sheet_count TIMES.
    CALL METHOD OF gcl_sheets 'Item' = lv_temp_sheet EXPORTING #1 = sy-index.
    GET PROPERTY OF lv_temp_sheet 'Name' = lv_temp_name.
    FREE OBJECT lv_temp_sheet.
  ENDDO.

* 4. 循环复制指定次数
  DO lv_times TIMES.
    DATA(lv_index) = sy-index.
    IF lv_index EQ 1.
      lv_new_name = |{ pv_base }{ pv_page }|.
      SET PROPERTY OF source_sheet 'Name' = lv_new_name.
      lt_names = VALUE #( BASE lt_names ( name = lv_new_name ) ).
    ENDIF.

    lv_new_name = |{ pv_base }{ pv_page - lv_index }|.

    " 检查名称是否存在（基于缓存）
    READ TABLE lt_names  INTO DATA(ls_temp) WITH KEY name = lv_new_name.
    IF sy-subrc EQ 0.
      DATA(lv_flag) = 'X'.
    ENDIF.
    WHILE lv_flag = 'X'.
      lv_count = lv_count + 1.
      lv_new_name = |{ pv_base }{ pv_page - lv_index }|.
      READ TABLE lt_names  INTO ls_temp WITH KEY name = lv_new_name.
      IF sy-subrc <> 0 OR lv_count > 20.
        lv_flag = ''.
      ENDIF.
    ENDWHILE.

    " 复制模板
    CALL METHOD OF gcl_sheets 'Item' = first_sheet EXPORTING #1 = 1.
    CALL METHOD OF source_sheet 'Copy' EXPORTING #2 = first_sheet.

    " 重命名新工作表
    CALL METHOD OF gcl_sheets 'Item' = new_sheet EXPORTING #1 = 1.
    SET PROPERTY OF new_sheet 'Name' = lv_new_name.
    lt_names = VALUE #( BASE lt_names ( name = lv_temp_name ) ).
*    " 更新总数并获取新工作表
    lv_sheet_count = lv_sheet_count + 1.
    FREE OBJECT:first_sheet, new_sheet.
  ENDDO.

* 5. 保存并清理
  CALL METHOD OF workbook 'Save'.          " 保存工作簿
  SET PROPERTY OF gcl_excel 'Visible' = 0.     " 关闭Excel界面
  FREE OBJECT: source_sheet, workbook, workbooks.
ENDFORM.