OLE 导出类

class ZCL_EXCEL_DOWNLOAD definition
  public
  final
  create public .

public section.

  data G_FILEPATH type RLGRAP-FILENAME .

  methods CONSTRUCTOR
    importing
      value(I_DEFAULT_FILENAME) type STRING optional
      value(I_OBJID) type W3OBJID optional
      value(I_FILEPATH) type RLGRAP-FILENAME optional .
  class-methods DOWNLOAD_TEMPLATE
    importing
      value(I_DEFAULT_FILENAME) type STRING optional
      value(I_OBJID) type WWWPARAMS-OBJID optional
    exporting
      value(E_PATH) type RLGRAP-FILENAME .
  methods OPEN_EXCEL
    importing
      value(I_SHEET_NAME) type STRING optional .
  methods SET_SINGLE_CELL
    importing
      value(I_ROW) type I
      value(I_COL) type I
      value(I_VALUE) type ANY default 3
      value(I_HA) type I optional .
  methods SET_ROW_CELL
    importing
      !LT_ITAB type STANDARD TABLE
      value(I_ROW) type I
      value(I_COL) type I .
  methods SET_RANGE_CELL
    importing
      !LT_ITAB type STANDARD TABLE
      value(I_ROW) type I
      value(I_COL) type I .
  methods CLOSE_EXCEL
    importing
      !I_CLOSE type C default 'X' .
  methods INS_ROW
    importing
      value(COPY) type I
      value(ROW) type I
      value(COUNT) type I .
protected section.
private section.

  data G_EXCEL type OLE2_OBJECT .
  data G_WORKBOOKS type OLE2_OBJECT .
  data G_WORKBOOK type OLE2_OBJECT .
  data G_SHEET type OLE2_OBJECT .
ENDCLASS.



CLASS ZCL_EXCEL_DOWNLOAD IMPLEMENTATION.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_EXCEL_DOWNLOAD->CLOSE_EXCEL
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_CLOSE                        TYPE        C (default ='X')
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD close_excel.
    SET PROPERTY OF g_excel 'DisplayAlerts' = 'false'.  "关闭保存提示。
    CALL METHOD OF
      g_workbook
      'SAVEAS'
      EXPORTING
        #1 = g_filepath. " #2 = 1.
    IF i_close = 'X'.
      CALL METHOD OF g_workbook 'CLOSE'. "关闭工作区
      CALL METHOD OF g_excel 'QUIT'."退出excel
    ENDIF .
    FREE OBJECT g_excel .
    FREE OBJECT g_workbooks .
    FREE OBJECT g_workbook .
    FREE OBJECT g_sheet .
    CLEAR:g_filepath .
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_EXCEL_DOWNLOAD->CONSTRUCTOR
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_DEFAULT_FILENAME             TYPE        STRING(optional)
* | [--->] I_OBJID                        TYPE        W3OBJID(optional)
* | [--->] I_FILEPATH                     TYPE        RLGRAP-FILENAME(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD constructor.
    DATA:
      l_filename  TYPE string,     " 文件名
      l_extension TYPE string ,   "文件后缀
      l_path      TYPE string,     " 路径
      l_fullpath  TYPE string.     " 全路径
    DATA:lv_title    TYPE string,
         lv_filename TYPE string.
    DATA:ls_template TYPE wwwdatatab."WEB资源库文件信息
    DATA:lv_subrc TYPE sysubrc .
    DATA:lv_file_filter TYPE string .
    IF i_filepath IS NOT INITIAL .
      g_filepath = i_filepath .
      RETURN .
    ENDIF .

    CHECK i_objid IS NOT INITIAL .

    CALL METHOD download_template
      EXPORTING
        i_default_filename = i_default_filename
        i_objid            = i_objid
      IMPORTING
        e_path             = g_filepath.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_EXCEL_DOWNLOAD=>DOWNLOAD_TEMPLATE
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_DEFAULT_FILENAME             TYPE        STRING(optional)
* | [--->] I_OBJID                        TYPE        WWWPARAMS-OBJID(optional)
* | [<---] E_PATH                         TYPE        RLGRAP-FILENAME
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD download_template.
    DATA:
      l_filename  TYPE string,     " 文件名
      l_extension TYPE string ,   "文件后缀
      l_path      TYPE string,     " 路径
      l_fullpath  TYPE string.     " 全路径
    DATA:lv_title    TYPE string,
         lv_filename TYPE string.
    DATA:ls_template TYPE wwwdatatab."WEB资源库文件信息
    DATA:lv_subrc TYPE sysubrc .
    DATA:lv_file_filter TYPE string .

    DATA: lv_directory TYPE string,
          lv_result    TYPE abap_bool.
    DATA:lv_extension(64).
    CALL FUNCTION 'WWWPARAMS_READ'
      EXPORTING
        relid            = 'MI'
        objid            = i_objid
        name             = 'fileextension'
      IMPORTING
        value            = lv_extension
      EXCEPTIONS
        entry_not_exists = 1
        OTHERS           = 2.
    IF sy-subrc <> 0.
*      实例不存在！
      MESSAGE s139(zmsg) DISPLAY LIKE 'E'.
      RETURN.
    ENDIF.


    IF lv_extension = '.xlsx' OR lv_extension = '.XLSX'.
      lv_file_filter = '*.xlsx|*.xlsx|*.xls|*.xls' .
    ELSEIF lv_extension = '.xls' OR   lv_extension = '.XLS' .
      lv_file_filter = '*.xls|*.xls|*.xlsx|*.xlsx' .
    ENDIF .
    l_extension = lv_extension.

    lv_directory =   'D:\' .
*    call method cl_gui_frontend_services=>directory_exist
*      exporting
*        directory            = lv_directory
*      receiving
*        result               = lv_result
*      exceptions
*        cntl_error           = 1
*        error_no_gui         = 2
*        wrong_parameter      = 3
*        not_supported_by_gui = 4
*        others               = 5.
*    if lv_result = abap_false.
*      clear:lv_directory.
*    endif.

    CALL METHOD cl_gui_frontend_services=>file_save_dialog   "保存文件路径对话框
      EXPORTING
        window_title         = 'Download Template'
        default_extension    = l_extension
        default_file_name    = i_default_filename
        file_filter          = lv_file_filter
        initial_directory    = lv_directory
      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.

    CHECK l_fullpath IS NOT INITIAL.

    CLEAR ls_template.
    ls_template-relid = 'MI'.
    ls_template-objid = i_objid.
    DATA:lv_filepath TYPE rlgrap-filename.
    e_path = lv_filepath = l_fullpath .

    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
      EXPORTING
        key         = ls_template
        destination = lv_filepath
      IMPORTING
        rc          = lv_subrc.

    IF lv_subrc NE  0 .
*      下载模板失败！
      MESSAGE s140(zmsg) DISPLAY LIKE 'E'.
    ENDIF .
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_EXCEL_DOWNLOAD->INS_ROW
* +-------------------------------------------------------------------------------------------------+
* | [--->] COPY                           TYPE        I
* | [--->] ROW                            TYPE        I
* | [--->] COUNT                          TYPE        I
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD ins_row.
    DATA: lc_range TYPE ole2_object.

    DO count TIMES.
      CALL METHOD OF g_sheet 'Rows' = lc_range
       EXPORTING #1 = copy.

      CALL METHOD OF lc_range 'Copy'.

      CALL METHOD OF g_sheet 'Rows' = lc_range
       EXPORTING #1 = row.

      CALL METHOD OF lc_range 'Insert'.

      CALL METHOD OF lc_range 'ClearContents'.
    ENDDO.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_EXCEL_DOWNLOAD->OPEN_EXCEL
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_SHEET_NAME                   TYPE        STRING(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD open_excel.
    CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
      EXPORTING
*       PERCENTAGE = 0
        text = 'Data Loading.....'.
    CREATE OBJECT g_excel 'EXCEL.APPLICATION'.
    SET PROPERTY OF g_excel 'VISIBLE' = 0.

    CALL METHOD OF
      g_excel
        'WORKBOOKS' = g_workbooks.
    CALL METHOD OF
        g_workbooks
        'OPEN'      = g_workbook
      EXPORTING
        #1          = g_filepath.

    "有多个sheet页时。
    IF i_sheet_name IS NOT INITIAL .
      CALL METHOD OF
          g_workbook
          'WORKSHEETS' = g_sheet
        EXPORTING
          #1           = i_sheet_name.
      CALL METHOD OF
        g_sheet
        'Activate'.
    ELSE.
      CALL METHOD OF
        g_workbook
          'ActiveSheet' = g_sheet.
    ENDIF .
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_EXCEL_DOWNLOAD->SET_RANGE_CELL
* +-------------------------------------------------------------------------------------------------+
* | [--->] LT_ITAB                        TYPE        STANDARD TABLE
* | [--->] I_ROW                          TYPE        I
* | [--->] I_COL                          TYPE        I
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD set_range_cell.
    CONSTANTS:
   lv_seperator TYPE c VALUE cl_abap_char_utilities=>horizontal_tab. " 值：#
*接口数据表相关
    DATA:wa_itab      TYPE REF TO data,                 "接口内表工作区,
         descr_wa     TYPE REF TO cl_abap_structdescr,  "接口内表工作区结构信息
         field_no     TYPE i,                           "接口内表工作区组件数量
         field_string TYPE string.                      "行结构组件的值

    FIELD-SYMBOLS:
      <wa_itab> TYPE any,  "接口内表工作区
      <field>   TYPE any.  "接口内表工作区中的组件
*粘贴内表
    DATA: BEGIN OF ls_data ,
            record(4096) TYPE c,
          END OF ls_data.

    DATA: lt_data LIKE TABLE OF ls_data,
          l_rc    TYPE i.
*EXCEL 对象
    DATA: excel_cell TYPE ole2_object.

    CHECK lt_itab[]   IS NOT INITIAL.
*创建接口数据表工作区
    CREATE DATA wa_itab LIKE LINE OF lt_itab.
    ASSIGN wa_itab->* TO <wa_itab>.
*获取内表工作区的组件数量
    descr_wa ?= cl_abap_typedescr=>describe_by_data( <wa_itab> ).
    DESCRIBE TABLE descr_wa->components LINES field_no.

*将接口表数据转存到粘贴内表中
    LOOP AT lt_itab INTO <wa_itab> .
      CLEAR:ls_data.
      IF field_no = 1.
*      ls_data-record = <wa_itab>.
        ASSIGN COMPONENT 1 OF STRUCTURE <wa_itab> TO <field>.
        field_string = <field>.
        ls_data-record = field_string.
      ELSE.
        DO field_no TIMES.
          ASSIGN COMPONENT sy-index OF STRUCTURE <wa_itab> TO <field>.
*        IF sy-subrc <> 0.
*          RAISE field_not_assigned.
*        ENDIF.
          field_string = <field>.
          IF sy-index = 1.
            ls_data-record = field_string.
          ELSE.
            CONCATENATE ls_data-record field_string  INTO ls_data-record SEPARATED BY lv_seperator.
          ENDIF.
        ENDDO.
      ENDIF.
      APPEND ls_data TO  lt_data.
    ENDLOOP.


    cl_gui_frontend_services=>clipboard_export( IMPORTING data = lt_data
                              CHANGING rc = l_rc ).
*设定单元格
    CALL METHOD OF
        g_sheet
        'Cells'         = excel_cell
      EXPORTING
        #1              = i_row
        #2              = i_col.
*选中单元格操作
    CALL METHOD OF
      excel_cell
      'Select'.
*粘贴操作
    CALL METHOD OF
      g_sheet
      'Paste'.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_EXCEL_DOWNLOAD->SET_ROW_CELL
* +-------------------------------------------------------------------------------------------------+
* | [--->] LT_ITAB                        TYPE        STANDARD TABLE
* | [--->] I_ROW                          TYPE        I
* | [--->] I_COL                          TYPE        I
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD set_row_cell.
    CONSTANTS:
   lv_seperator TYPE c VALUE cl_abap_char_utilities=>horizontal_tab. " 值：#
*接口数据表相关
    DATA:wa_itab      TYPE REF TO data,                 "接口内表工作区,
         descr_wa     TYPE REF TO cl_abap_structdescr,  "接口内表工作区结构信息
         field_no     TYPE i,                           "接口内表工作区组件数量
         field_string TYPE string.                      "行结构组件的值

    FIELD-SYMBOLS:
      <wa_itab> TYPE any,  "接口内表工作区
      <field>   TYPE any.  "接口内表工作区中的组件
*粘贴内表
    DATA: BEGIN OF ls_data ,
            record(4096) TYPE c,
          END OF ls_data.

    DATA: lt_data LIKE TABLE OF ls_data,
          l_rc    TYPE i.
*EXCEL 对象
    DATA: excel_cell TYPE ole2_object.

    CHECK lt_itab[]   IS NOT INITIAL.
*创建接口数据表工作区
    CREATE DATA wa_itab LIKE LINE OF lt_itab.
    ASSIGN wa_itab->* TO <wa_itab>.

*将接口表数据转存到粘贴内表中
    LOOP AT lt_itab INTO <wa_itab> .
      IF sy-tabix = 1 .
        ls_data-record = <wa_itab>.
      ELSE.
        field_string = <wa_itab>.
        CONCATENATE ls_data-record field_string  INTO ls_data-record SEPARATED BY lv_seperator.
      ENDIF.
    ENDLOOP.

    APPEND ls_data TO  lt_data.
    cl_gui_frontend_services=>clipboard_export( IMPORTING data = lt_data
                              CHANGING rc = l_rc ).
*设定单元格
    CALL METHOD OF
        g_sheet
        'Cells' = excel_cell
      EXPORTING
        #1      = i_row
        #2      = i_col.
*选中单元格操作
    CALL METHOD OF
      excel_cell
      'Select'.
*粘贴操作
    CALL METHOD OF
      g_sheet
      'Paste'.
  ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_EXCEL_DOWNLOAD->SET_SINGLE_CELL
* +-------------------------------------------------------------------------------------------------+
* | [--->] I_ROW                          TYPE        I
* | [--->] I_COL                          TYPE        I
* | [--->] I_VALUE                        TYPE        ANY (default =3)
* | [--->] I_HA                           TYPE        I(optional)
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD set_single_cell.
    DATA:lo_cell TYPE ole2_object,
         lo_font TYPE ole2_object.
    CHECK i_value IS NOT INITIAL.

    CALL METHOD OF
        g_excel
        'CELLS' = lo_cell
      EXPORTING
        #1      = i_row
        #2      = i_col.

    SET PROPERTY OF lo_cell 'VALUE' = i_value.

* 设置列宽
*  SET PROPERTY OF lo_cell 'COLUMNWIDTH' = 0.
* 对齐方式  3为居中
*    SET PROPERTY OF lo_cell 'HORIZONTALALIGNMENT' = i_ha.
** 水平对齐方式
*    SET PROPERTY OF lo_cell 'VERTICALALIGNMENT' = 2.
*
*    SET PROPERTY OF lo_cell 'WRAPTEXT' = 0.
*
*    GET PROPERTY OF lo_cell 'FONT' = lo_font.
** 字体加粗
*    SET PROPERTY OF lo_font 'BOLD' = 0.
**字体大小
*    SET PROPERTY OF lo_font 'SIZE' = 11.

    FREE lo_font.
    FREE lo_cell.
  ENDMETHOD.
ENDCLASS.