OLE 操作集合

*&---------------------------------------------------------------------*
*&  OLE方式EXCEL操作通用程序
*&---------------------------------------------------------------------*

******************************************************************************
*数据定义
******************************************************************************

INCLUDE ole2incl.

DEFINE m_message.
  case sy-subrc.
    when 0.
    when others.
      message e000(su)
              with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  endcase.
END-OF-DEFINITION.

DATA: application TYPE ole2_object,
      workbook        TYPE ole2_object,
      sheet           TYPE ole2_object,
      columns         TYPE ole2_object,
      w_comment       TYPE ole2_object,
      w_shape         TYPE ole2_object,
      rows            TYPE ole2_object,
      range           TYPE ole2_object,
      h_f             TYPE ole2_object,                         " FONT
      cells1          TYPE ole2_object,
*      ADD FOR CELLS COLOR
      selectiont      TYPE ole2_object,
      interior        TYPE ole2_object,
      borders         TYPE ole2_object,
*      END ADD
      cells           TYPE ole2_object,
      pag_break       TYPE ole2_object.

DATA: g_6_pageno TYPE i VALUE 0. "当前页码，用于分页
DATA: g_6_pageno_copy TYPE i VALUE 0. "


DATA: p_filename TYPE string,
      g_6_path     TYPE string.

DATA: g_6_row TYPE i , "默认一次粘贴1000行
      g_6_row_begin TYPE i ,                             "开始粘贴的行
      g_6_left TYPE string ,
      g_6_right TYPE string .

DATA: gi_line TYPE RANGE OF sy-tabix WITH HEADER LINE.

TYPES: BEGIN OF ty6_s_senderline ,
          line(4096)     TYPE c,
       END OF ty6_s_senderline.
DATA: i_excel_tab TYPE TABLE OF ty6_s_senderline.
DATA: wa_excel TYPE ty6_s_senderline .

DATA: g_6_separator TYPE c.

* ALV相关定义
TYPE-POOLS: slis.
DATA : i_fieldcat_lvc TYPE lvc_t_fcat.
DATA: wa_layout_lvc TYPE lvc_s_layo.
DATA : wa_layout TYPE slis_layout_alv.
DATA : i_events TYPE slis_t_event,
       i_list_comments       TYPE slis_t_listheader.
DATA : wa_events LIKE LINE OF i_events,
       wa_fieldcat_lvc           TYPE lvc_s_fcat,
       wa_list_comments      LIKE LINE OF i_list_comments.


DEFINE m_fill_fieldcat .
  clear wa_fieldcat_lvc.
  wa_fieldcat_lvc-fieldname =  &1.    "内表字段名称
  wa_fieldcat_lvc-scrtext_m =  &2.    " 字段输出名称
  wa_fieldcat_lvc-seltext =  &2.    " 字段输出名称
  wa_fieldcat_lvc-outputlen =  &3.    "输出长度
  wa_fieldcat_lvc-datatype  = '&4'.    "数据类型
  wa_fieldcat_lvc-no_zero   = 'X'.
  append wa_fieldcat_lvc to i_fieldcat_lvc.
END-OF-DEFINITION.



******************************************************************************
*FORM
******************************************************************************

*&---------------------------------------------------------------------*
*&      FORM  F_EXP_PREPARE
*&---------------------------------------------------------------------*
*       EXCEL数据导出前的准备，下载模板
*----------------------------------------------------------------------*
*      -->PV_OBJID     模板名称
*      -->PV_FILENAME  存放路径
*----------------------------------------------------------------------*
FORM f_exp_prepare USING pv_objid CHANGING pv_path.
  DATA: l_indcatortxt TYPE string.
  DATA l_filename TYPE filename.

  CONCATENATE sy-title '_' sy-datum '.XLS' INTO l_filename.
  REPLACE ALL OCCURRENCES OF REGEX '[\/\\\:]' IN l_filename WITH '_'.

  CLEAR p_filename.
  PERFORM f_get_save_excel_filename USING l_filename CHANGING p_filename.
  IF p_filename IS INITIAL.
    MESSAGE s000(su) WITH '用户取消操作'.
    RETURN.
  ENDIF.
  pv_path = p_filename.

*下载模板
  l_indcatortxt = '程序正在下载模板'.
  PERFORM f_process_indcator USING l_indcatortxt 0 .

  PERFORM f_download_excel_fromserver USING pv_objid p_filename.

  CHECK NOT p_filename IS INITIAL.

  l_indcatortxt = '程序正在初始化OLE控件'.
  PERFORM f_process_indcator USING l_indcatortxt 0 .

ENDFORM. " F_EXP_PREPARE
*&---------------------------------------------------------------------*
*&      FORM  F_DOWNLOAD_EXCEL
*&---------------------------------------------------------------------*
*       TEXT
*----------------------------------------------------------------------*
FORM f_download_excel USING pv_objid CHANGING pv_filename.
  DATA: l_indcatortxt TYPE string.
  DATA l_path TYPE string.
  CALL METHOD cl_gui_frontend_services=>get_sapgui_workdir
    CHANGING
      sapworkdir = l_path
    EXCEPTIONS
      cntl_error = 1.

  CONCATENATE l_path '\' pv_objid '-' '.XLS' INTO pv_filename.
* 下载模板
  l_indcatortxt = '程序正在下载模板'.
  PERFORM f_process_indcator USING l_indcatortxt 0 .
  PERFORM f_download_excel_fromserver USING pv_objid pv_filename.

  CHECK NOT p_filename IS INITIAL.
  l_indcatortxt = '程序正在初始化OLE控件'.
  PERFORM f_process_indcator USING l_indcatortxt 0 .

ENDFORM. " F_EXP_PREPARE



*&---------------------------------------------------------------------*
*&      FORM  F_EXPORT_TO_EXCEL
*&---------------------------------------------------------------------*
*       内表数据输出到EXCEL
*----------------------------------------------------------------------*
*      -->PV_ITAB    需要输出的内表
*----------------------------------------------------------------------*
FORM f_export_to_excel TABLES pv_itab.

  DATA: l_line TYPE i,             "内表行数
        l_count  TYPE i,           "插入EXCEL行数
        l_star   TYPE i,           "内表开始行
        l_end    TYPE i,           "内表结束行
        l_p TYPE p DECIMALS 2,     "中间运行结果
        l_n TYPE i.                 "循环粘贴次数
  DATA l_num TYPE i.                "取余数
  DATA l_paste_start TYPE i.        "开始粘贴的行
  DESCRIBE TABLE pv_itab LINES l_line.
  l_p = l_line / g_6_row.
  l_n = ceil( l_p ).
  l_num = l_line / g_6_row.
  IF l_line <= g_6_row.
    PERFORM f_fill_range  TABLES pv_itab USING 1 l_line g_6_row_begin.
  ELSE.
    l_star = 1.
    l_end =  g_6_row.
    l_paste_start = g_6_row_begin.
    DO l_n TIMES.
      PERFORM f_fill_range TABLES pv_itab USING l_star l_end l_paste_start.
      l_star = l_end + 1 .
      l_end = l_star + g_6_row - 1.
      l_paste_start = l_paste_start + g_6_row.
    ENDDO.
    IF l_num NE 0.
      l_end = l_star + l_num.
      l_paste_start = l_star + 6.
      PERFORM f_fill_range  TABLES pv_itab  USING l_star l_end l_paste_start.
    ENDIF.
  ENDIF.
ENDFORM. " F_EXPORT_TO_EXCEL

*&---------------------------------------------------------------------*
*&      FORM  F_EXPORT_TO_EXCEL_WITH_HEADER
*&---------------------------------------------------------------------*
*       内表数据输出到EXCEL
*----------------------------------------------------------------------*
*      -->PV_ITAB    需要输出的内表
*----------------------------------------------------------------------*
FORM f_export_to_excel_with_header TABLES pv_itab.

  DATA: l_line TYPE i,             "内表行数
        l_count  TYPE i,           "插入EXCEL行数
        l_star   TYPE i,           "内表开始行
        l_end    TYPE i,           "内表结束行
        l_p TYPE p DECIMALS 2,     "中间运行结果
        l_n TYPE i.                 "循环粘贴次数
  DATA l_num TYPE i.                "取余数
  DATA l_paste_start TYPE i.        "开始粘贴的行
  DESCRIBE TABLE pv_itab LINES l_line.
  ADD 1 TO l_line.
  l_p = l_line / g_6_row.
  l_n = ceil( l_p ).
  l_num = l_line / g_6_row.
  IF l_line <= g_6_row.
    PERFORM f_fill_range_with_header  TABLES pv_itab USING 1 l_line g_6_row_begin.
  ELSE.
    l_star = 1.
    l_end =  g_6_row.
    l_paste_start = g_6_row_begin.
    DO l_n TIMES.
      IF sy-index = 1.
        PERFORM f_fill_range_with_header TABLES pv_itab USING l_star l_end l_paste_start.
        ADD 1 TO l_paste_start.
      ELSE.
        PERFORM f_fill_range TABLES pv_itab USING l_star l_end l_paste_start.
      ENDIF.
      l_star = l_end + 1 .
      l_end = l_star + g_6_row - 1.
      l_paste_start = l_paste_start + g_6_row.
    ENDDO.
    IF l_num NE 0.
      l_end = l_star + l_num.
      l_paste_start = l_star + 6.
      PERFORM f_fill_range TABLES pv_itab  USING l_star l_end l_paste_start.
    ENDIF.
  ENDIF.
ENDFORM. " F_EXPORT_TO_EXCEL_WITH_HEADER

*&---------------------------------------------------------------------*
*&      FORM  F_EXPORT_TO_EXCEL_BREAK
*&---------------------------------------------------------------------*
*       TEXT
*----------------------------------------------------------------------*
*      -->PV_ITAB       输出的内表
*      -->PV_LINE       每一页包含的行数
*      -->PV_COUNT      每一页包含的列数
*      -->PV_DATA_LINE  每一页输出的数据条数
*----------------------------------------------------------------------*
FORM f_export_to_excel_break TABLES pv_itab USING pv_line pv_columns pv_data_line .

  DATA: l_line TYPE i,             "内表行数
        l_count  TYPE i,           "插入EXCEL行数
        l_star   TYPE i,           "内表开始行
        l_end    TYPE i,           "内表结束行
        l_p TYPE p DECIMALS 2,     "中间运行结果
        l_n TYPE i.                 "循环粘贴次数
  DATA l_num TYPE i.                "取余数
  DATA l_paste_start TYPE i.        "开始粘贴的行

  DATA: lb_n TYPE i,
        lb_p TYPE p DECIMALS 2,
        lb_data_line TYPE i.

  DATA:lb_star TYPE i,            "分页开始输出的行
       lb_end TYPE i.             "分页结束的行
  DATA:lb_row_begin TYPE i.
  DESCRIBE TABLE pv_itab LINES l_line.
* 判断是否要分页
  IF pv_data_line < l_line.
*   判断循环次数
    lb_p = l_line / pv_data_line.
    lb_n = ceil( lb_p ).
    lb_star = 1.
    lb_end = pv_data_line.
    lb_row_begin = g_6_row_begin.
    DO lb_n TIMES.
      PERFORM f_fill_range  TABLES pv_itab USING lb_star lb_end lb_row_begin.
      IF sy-index NE lb_n.
        PERFORM f_pagebreak USING pv_line pv_columns g_6_row_begin 0 0 pv_data_line.
      ENDIF.
      lb_star = lb_end + 1.
      lb_end = lb_end + pv_data_line.
      lb_row_begin = lb_row_begin + pv_line.
    ENDDO.
  ELSE.
    PERFORM f_fill_range  TABLES pv_itab USING 1 l_line g_6_row_begin.
  ENDIF.
ENDFORM. " F_EXPORT_TO_EXCEL



*&---------------------------------------------------------------------*
*&      FORM  F_EXPORT_TO_EXCEL_BREAK2
*&---------------------------------------------------------------------*
*       TEXT
*----------------------------------------------------------------------*
*      -->PV_ITAB       要输出的内表
*      -->PV_LINE       每一页的行数
*      -->PV_COLUMNS    每一页的列数
*      -->PV_DATA_LINE  每一页中需要从内表中输出的记录条数
*----------------------------------------------------------------------*
FORM f_export_to_excel_break2 TABLES pv_itab USING pv_line pv_columns pv_data_line .

  DATA: l_line TYPE i,             "内表行数
        l_count  TYPE i,           "插入EXCEL行数
        l_star   TYPE i,           "内表开始行
        l_end    TYPE i,           "内表结束行
        l_p TYPE p DECIMALS 2,     "中间运行结果
        l_n TYPE i.                 "循环粘贴次数
  DATA l_num TYPE i.                "取余数
  DATA l_paste_start TYPE i.        "开始粘贴的行

  DATA: lb_n TYPE i,
        lb_p TYPE p DECIMALS 2,
        lb_data_line TYPE i.

  DATA:lb_star TYPE i,            "分页开始输出的行
       lb_end TYPE i.             "分页结束的行
  DATA:lb_row_begin TYPE i.
  DESCRIBE TABLE pv_itab LINES l_line.
* 判断是否要分页
  IF pv_data_line < l_line.
*      判断循环次数
    lb_p = l_line / pv_data_line.
    lb_n = ceil( lb_p ).
    lb_star = 1.
    lb_end = pv_data_line.
    lb_row_begin = g_6_row_begin.
    DO lb_n TIMES.
      PERFORM f_fill_range  TABLES pv_itab USING lb_star lb_end lb_row_begin.
      lb_star = lb_end + 1.
      lb_end = lb_end + pv_data_line.
      lb_row_begin = lb_row_begin + pv_line.
    ENDDO.
  ELSE.
    PERFORM f_fill_range  TABLES pv_itab USING 1 l_line g_6_row_begin.
  ENDIF.
ENDFORM. " F_EXPORT_TO_EXCEL
*&---------------------------------------------------------------------*
*&      FORM  F_EXPORT_TO_EXCEL_BREAK_FIELD
*&---------------------------------------------------------------------*
*       TEXT
*----------------------------------------------------------------------*
*      -->PV_ITAB       TEXT
*      -->FIELDNAME     关键字段，作为排序的唯一关键字段
*      -->PV_LINE       TEXT
*      -->PV_COLUMNS    TEXT
*      -->PV_DATA_LINE  TEXT
*----------------------------------------------------------------------*
FORM f_export_to_excel_break_field TABLES pv_itab
  USING fieldname pv_line pv_columns  pv_data_line p_sort.

  DATA: l_line TYPE i,             "内表行数
        l_count  TYPE i,           "插入EXCEL行数
        l_star   TYPE i,           "内表开始行
        l_end    TYPE i,           "内表结束行
        l_p TYPE p DECIMALS 2,     "中间运行结果
        l_n TYPE i.                 "循环粘贴次数
  DATA l_num TYPE i.                "取余数
  DATA l_paste_start TYPE i.        "开始粘贴的行
  DATA: lb_n TYPE i,
        lb_p TYPE p DECIMALS 2,
        lb_data_line TYPE i.
  DATA:lb_star TYPE i,            "分页开始输出的行
       lb_end TYPE i.             "分页结束的行
  DATA:lb_row_begin TYPE i.
  DATA:l_tabix TYPE sy-tabix.                   "循环次数
  DATA:l_lines_br TYPE i.
  DATA l_line2.
  DATA:l_num_new TYPE i.
  DATA:lf_beg,
       lf_end.
  FIELD-SYMBOLS <detail_tab>    TYPE STANDARD TABLE.
  FIELD-SYMBOLS <detail_wa>     TYPE any.

  DATA detail_tab_ref           TYPE REF TO data.
  DATA detail_wa_ref            TYPE REF TO data.

  FIELD-SYMBOLS <data_tab_wa> TYPE any.
  FIELD-SYMBOLS  <dyn_field> TYPE any.
  DATA data_tab_ref             TYPE REF TO data.
  DATA: l_bumen_i TYPE i,                     "输出分页时的单位名称位置
        l_bumen_str TYPE string.              "输出
  DATA: l_short TYPE string,
        l_c TYPE i.

*  定义内表，保存字段的开始行，结束行
  DATA:BEGIN OF li_break OCCURS 0,
    fieldname TYPE lvc_fname,                       "字段名
    value TYPE string,                              "字段值
    field_beg TYPE i,                               "字段开始行
    field_end TYPE i,                               "字段结束行
    END OF li_break.
  DATA:li_tabix TYPE i.
  CREATE DATA data_tab_ref LIKE LINE OF pv_itab.
  ASSIGN data_tab_ref->* TO <data_tab_wa>.

  CALL METHOD cl_alv_table_create=>create_dynamic_table
    EXPORTING
      it_fieldcatalog = i_fieldcat_lvc
    IMPORTING
      ep_table        = detail_tab_ref.

  ASSIGN detail_tab_ref->* TO <detail_tab>.
  CREATE DATA detail_wa_ref LIKE LINE OF <detail_tab>.
  ASSIGN detail_wa_ref->* TO <detail_wa>.

  LOOP AT pv_itab ASSIGNING <data_tab_wa>.
    MOVE-CORRESPONDING <data_tab_wa> TO <detail_wa>.
*    顺序号已经错乱，
    ASSIGN COMPONENT 'SEQUN' OF STRUCTURE <detail_wa> TO  <dyn_field>.
    CLEAR <dyn_field>.
    APPEND <detail_wa> TO <detail_tab>.
  ENDLOOP.
  DELETE i_fieldcat_lvc WHERE fieldname = fieldname.
* OSP排序后，不再需要排序
  IF p_sort = 'X'.
    SORT <detail_tab> BY (fieldname).
  ENDIF.
  CLEAR: li_tabix.

  CLEAR: <detail_wa>.
*  获得分页表，某个字段A行到B行连续，B行后分页
  l_num_new = 1.
  LOOP AT <detail_tab> INTO <detail_wa>.
    CLEAR li_break-field_end.
    ASSIGN COMPONENT fieldname  OF STRUCTURE <detail_wa> TO  <dyn_field>.     "得到排序字段数值
    li_break-fieldname = fieldname.
    li_break-value = <dyn_field>.
    AT NEW <dyn_field>.
      li_break-field_beg = sy-tabix.
    ENDAT.
    AT END OF <dyn_field>.
      li_break-field_end = sy-tabix.
    ENDAT.
    IF NOT li_break-field_end IS INITIAL.
      APPEND li_break TO li_break.
    ENDIF.
  ENDLOOP.

*  重新编号
  CLEAR: <detail_wa> ,l_short,l_c.
  UNASSIGN <dyn_field>.
  LOOP AT <detail_tab> INTO <detail_wa>.
    ASSIGN COMPONENT fieldname OF STRUCTURE <detail_wa> TO <dyn_field>.

    IF l_short NE <dyn_field>.
      l_c = 0.
      l_short = <dyn_field>.
    ENDIF.
    l_c = l_c + 1.

    ASSIGN COMPONENT 'SEQUN' OF STRUCTURE <detail_wa> TO <dyn_field>.
    <dyn_field> = l_c.
    MODIFY <detail_tab> FROM <detail_wa>.

  ENDLOOP.

*  分部分进行粘贴
  CLEAR li_break.
  lb_star = 1.
  lb_end = pv_data_line.
  lb_row_begin = g_6_row_begin.
  DESCRIBE TABLE li_break LINES l_lines_br.
  LOOP AT li_break.
    l_tabix = sy-tabix.
*   重新定位
    IF l_tabix NE 1.
      lb_star = li_break-field_beg.
      lb_end = lb_star + pv_data_line - 1.
    ENDIF.

    l_line = li_break-field_end - li_break-field_beg + 1.                   "某一类别的数据行数
    IF pv_data_line < l_line.
*     判断循环次数
      lb_p = l_line / pv_data_line.
      lb_n = ceil( lb_p ).
      DO lb_n TIMES.
        PERFORM f_fill_range  TABLES <detail_tab> USING lb_star lb_end lb_row_begin.
*       单位描述
        l_bumen_i = lb_row_begin - 2.
        CONCATENATE '单位：' li_break-value INTO l_bumen_str.
        PERFORM f_fill_cell2 USING l_bumen_i 1 0 2 l_bumen_str.
        PERFORM f_merge_cells USING 1 l_bumen_i 6 l_bumen_i.
        IF sy-index NE lb_n.
          PERFORM f_pagebreak USING pv_line pv_columns g_6_row_begin 0 0 pv_data_line.
          l_paste_start = g_6_pageno * pv_line + 1.
          PERFORM f_insert_pagebreak USING l_paste_start.
        ENDIF.
        lb_star = lb_end + 1.
        lb_end = lb_end + pv_data_line.
        IF lb_end > li_break-field_end.
          lb_end = li_break-field_end.
        ENDIF.
        lb_row_begin = lb_row_begin + pv_line.
      ENDDO.
    ELSE.
      IF li_break-field_end < lb_end.
        lb_end = li_break-field_end.
      ENDIF.
      PERFORM f_fill_range  TABLES <detail_tab> USING lb_star lb_end lb_row_begin.
*     单位描述
      l_bumen_i = lb_row_begin - 2.
      CONCATENATE '单位：' li_break-value INTO l_bumen_str.
      PERFORM f_fill_cell2 USING l_bumen_i 1 0 2 l_bumen_str.
      PERFORM f_merge_