OLE

DATA :go_excel_app TYPE  ole2_object, "APP,
      go_workbook  TYPE  ole2_object, "Workbook,
      go_sheet     TYPE  ole2_object, "Sheet,
      go_cell      TYPE  ole2_object, "Cell
      go_range     TYPE  ole2_object, "Range
      gv_file_path TYPE  string, " File path
      gv_link      TYPE  i,         "  Link
      gv_open_flag TYPE  char01. "  Open flag

CONSTANTS c_tab  TYPE  c VALUE cl_abap_char_utilities=>horizontal_tab.

TYPES : BEGIN OF ty_line,
          tline(1024) TYPE c,
        END OF ty_line.
TYPES tt_line TYPE STANDARD TABLE OF ty_line.

CREATE OBJECT go_excel_app 'Excel.Application'.
gv_file_path = iv_file_path.

DATA : iv_sheet_name   TYPE any,
       iv_sheet_number TYPE any,
       iv_hidden       TYPE flag.

IF gv_open_flag IS INITIAL.
  CALL METHOD OF
    go_excel_app
      'Workbooks' = go_workbook.

  IF gv_link IS INITIAL.
    CALL METHOD OF
        go_workbook
        'Open'      = go_workbook
      EXPORTING
        #1          = gv_file_path.
  ENDIF.

  IF sy-subrc <> 0.
    RAISE open_file_failed.
  ENDIF.
  gv_open_flag = 'X'.
ENDIF.

IF iv_sheet_name IS NOT INITIAL.

  IF gv_link IS INITIAL.
    CALL METHOD OF
        go_workbook
        'Sheets'    = go_sheet
      EXPORTING
        #1          = iv_sheet_name.
  ELSE.
    CALL METHOD OF
        go_excel_app
        'Sheets'     = go_sheet
      EXPORTING
        #1           = iv_sheet_name.
  ENDIF.

ENDIF.

IF iv_sheet_number IS NOT INITIAL.

  IF gv_link IS INITIAL.
    CALL METHOD OF
        go_workbook
        'Sheets'    = go_sheet
      EXPORTING
        #1          = iv_sheet_number.
  ELSE.
    CALL METHOD OF
        go_excel_app
        'Sheets'     = go_sheet
      EXPORTING
        #1           = iv_sheet_number.
  ENDIF.

ENDIF.

*如果2个参数都没有输入，则打开第一个sheet
IF iv_sheet_name IS INITIAL AND iv_sheet_number IS INITIAL.
  IF gv_link IS INITIAL.
    CALL METHOD OF
        go_workbook
        'Sheets'    = go_sheet
      EXPORTING
        #1          = 1.
  ELSE.
    CALL METHOD OF
        go_excel_app
        'Sheets'     = go_sheet
      EXPORTING
        #1           = 1.
  ENDIF.
ENDIF.

CALL METHOD OF
  go_sheet
  'Activate'.

IF iv_hidden IS NOT INITIAL.
  SET PROPERTY OF go_excel_app 'Visible' = 1.
ELSE.
  SET PROPERTY OF go_excel_app 'Visible' = 0.
ENDIF.

SET PROPERTY OF go_excel_app 'DisplayAlerts' = 0.

Insert Row

DATA :iv_row   TYPE i,
      iv_count TYPE i,
      iv_clear TYPE c.

DO iv_count TIMES.
  CALL METHOD OF go_sheet 'Rows' = go_range
    EXPORTING
      #1 = iv_row.

  CALL METHOD OF go_range 'Copy'.

  CALL METHOD OF go_range 'Insert'.

  IF iv_clear = 'X'.
    CALL METHOD OF go_range 'ClearContents'.
  ENDIF.
ENDDO.

SET_CELL_VALUE

DATA : iv_row   TYPE i,
       iv_col   TYPE i,
       iv_value TYPE any.

CALL METHOD OF go_excel_app 'CELLS' = go_cell
    EXPORTING
      #1 = iv_row
      #2 = iv_col.
SET PROPERTY OF go_cell 'value' =  iv_value.

COPY_TO_CLIPBOARD

DATA : ev_rc  TYPE i,
       it_tab TYPE STANDARD TABLE .

CALL METHOD cl_gui_frontend_services=>clipboard_export
  IMPORTING
    data                 = it_tab
  CHANGING
    rc                   = ev_rc
  EXCEPTIONS
    cntl_error           = 1
    error_no_gui         = 2
    not_supported_by_gui = 3
    no_authority         = 4
    OTHERS               = 5.
IF sy-subrc <> 0.
  ev_rc = sy-subrc.
ENDIF.

GET_RANGES

DATA :iv_row  TYPE i,
      iv_col  TYPE i,
      iv_row1	TYPE i,
      iv_col1	TYPE i,
      iv_sel  TYPE flag,
      ev_rang	TYPE ole2_object.

DATA : lv_cell1 TYPE ole2_object,
       lv_cell2 TYPE ole2_object.

CALL METHOD OF go_excel_app 'Cells' = lv_cell1
  EXPORTING
  #1 = iv_row
  #2 = iv_col.

CALL METHOD OF go_excel_app 'Cells' = lv_cell2
 EXPORTING
 #1 = iv_row1
 #2 = iv_col1.

CALL METHOD OF go_excel_app 'Range' = ev_rang
      EXPORTING
      #1 = lv_cell1
      #2 = lv_cell2.

IF iv_sel = 'X'.
  CALL METHOD OF ev_rang 'Select' .
ENDIF.

CHANGE_TABLE_TO_LINETABLE

DATA :itab TYPE STANDARD TABLE,
      line TYPE STANDARD TABLE.

DATA : lt_line TYPE STANDARD TABLE OF string,
       lv_line TYPE i.

FIELD-SYMBOLS : <dyn_line>  TYPE any,
                <dyn_field> TYPE any.

LOOP AT itab ASSIGNING <dyn_line>.
  APPEND INITIAL LINE TO lt_line ASSIGNING FIELD-SYMBOL(<fs_line>).

  DO.
    ASSIGN COMPONENT sy-index OF STRUCTURE <dyn_line> TO <dyn_field>.
    IF sy-subrc <> 0.
      EXIT.
    ELSE.
      IF <fs_line> IS INITIAL.
        <fs_line> = <dyn_field>.
      ELSE.
        <fs_line> = <fs_line> && c_tab && <dyn_field>.
      ENDIF.
    ENDIF.
  ENDDO.
ENDLOOP.

line[] = lt_line[].

COPY_TO_EXCEL

DATA:iv_cell1_row  TYPE i,
     iv_cell1_col TYPE i,
     iv_cell2_row TYPE i,  "DEFAULT 0
     iv_cell2_col TYPE i,  "DEFAULT 0
     iv_copy	    TYPE char01,  "DEFAULT SPACE
     iv_clear     TYPE char01,  "DEFAULT SPACE
     it_tab       TYPE STANDARD TABLE.

DATA: lt_lines TYPE tt_line,
      lv_row   TYPE i,
      lv_col   TYPE i.

IF iv_cell2_row IS INITIAL.
  lv_row = iv_cell1_row.
ELSE.
  lv_row = iv_cell2_row.
ENDIF.

IF iv_cell2_col IS INITIAL.
  lv_col = iv_cell1_col.
ELSE.
  lv_col = iv_cell2_col.
ENDIF.

CALL METHOD me->get_range
  EXPORTING
    iv_row  = iv_cell1_row
    iv_col  = iv_cell1_col
    iv_row1 = lv_row
    iv_col1 = lv_col
    iv_sel  = 'X'
  IMPORTING
    ev_rang = go_range.

IF iv_clear IS NOT INITIAL.
  CALL METHOD OF
    go_range
    'ClearContents'.
ENDIF.

IF iv_copy IS NOT INITIAL.
  CHECK it_tab[] IS NOT INITIAL.
  CALL METHOD me->change_table_to_linetable
    EXPORTING
      itab = it_tab
    IMPORTING
      line = lt_lines.

  CALL METHOD me->copy_to_clipboard
    CHANGING
      it_tab = lt_lines.
  IF sy-subrc <> 0.

  ENDIF.

  CALL METHOD OF
    go_sheet
    'Paste'.
ENDIF.

SAV

DATA : iv_quit  TYPE c. "  DEFAULT SPACE

CALL METHOD OF go_excel_app 'save'.

IF iv_quit = 'X'.
  CALL METHOD OF go_excel_app 'quit'.

  CALL METHOD me->free_object.
ENDIF.

FREE OBJECT.

FREE : go_cell,
       go_range,
       go_sheet,
       go_workbook,
       go_excel_app.

CLEAR:gv_link,
      gv_open_flag,
      gv_file_path.