OLE 方式的 Excel

*&---------------------------------------------------------------------*
*& Report  Z_BARRY_EXCEL_TEST                                          *
*&                                                                     *
*&---------------------------------------------------------------------*
*&  Excel OLE 测试                                                     *
*&---------------------------------------------------------------------*
REPORT z_barry_excel_test .

 

 

INCLUDE ole2incl.
DATA: excel_obj     TYPE ole2_object,
      workbook_obj  TYPE ole2_object,
      sheet_obj     TYPE ole2_object,
      sheet_obj2    TYPE ole2_object,
      cell_obj      TYPE ole2_object,
      column_obj    TYPE ole2_object,
      range_obj     TYPE ole2_object,
      borders_obj   TYPE ole2_object,
      button_obj    TYPE ole2_object,
      int_obj       TYPE ole2_object,
      font_obj      TYPE ole2_object,
      row_obj       TYPE ole2_object.
DATA: celldata(40) TYPE c .
DATA: BEGIN OF itab OCCURS 0 ,
        ff1(10) TYPE c,
        ff2(10) TYPE c,
      END OF itab.

START-OF-SELECTION.
  itab-ff1 = 'A1'.
  itab-ff2 = 'A2'.
  APPEND itab.
  itab-ff1 = 'B1'.
  itab-ff2 = 'B2'.
  APPEND itab.
  itab-ff1 = 'C1'.
  itab-ff2 = 'C2'.
  APPEND itab.
  itab-ff1 = 'D1'.
  itab-ff2 = 'D2'.
  APPEND itab.
  itab-ff1 = 'E1'.
  itab-ff2 = 'E2'.
  APPEND itab.
  PERFORM open_excel_file USING  excel_obj
                                 workbook_obj
                                 sheet_obj
                                 'C:\XLS_TMPLT.XLS'
                                 'TestSheet'
                                 '1' .
  PERFORM excel_range_write USING excel_obj
                                  'A1:B2'
                                  'abcd'.
  PERFORM excel_row_insert  USING sheet_obj
                                  '1'
                                  '2'.
  PERFORM excel_column_insert USING sheet_obj
                                    'A'
                                    '2'.
  PERFORM rowheight USING excel_obj
                          '1:6'
                          '30'.
  PERFORM columnwidth USING excel_obj
                            'A:C'
                            '20'.
  PERFORM excel_cell_read USING excel_obj
                                '1'
                                'A'
                                celldata .
  PERFORM excel_range_write USING excel_obj
                                  'C1:D2'
                                  celldata .
  PERFORM excel_sheet_add USING workbook_obj
                                sheet_obj2
                                'Test02'.
  PERFORM excel_range_write USING excel_obj
                                  'C1:D2'
                                  'aaaaa'.
  PERFORM borderrange USING excel_obj
                            'A1:C3'.
  PERFORM runmacro USING excel_obj
                         'Macro1'.
  PERFORM fill_sheet_itab USING 2 3.
  CALL METHOD OF sheet_obj 'Activate' .
  PERFORM fill_sheet_itab USING 2 3.

  PERFORM protect_sheet .
  GET PROPERTY OF excel_obj 'ActiveWorkbook' = workbook_obj.
  PERFORM excel_save USING excel_obj.
  CALL METHOD OF workbook_obj 'CLOSE'.
  CALL METHOD OF excel_obj 'QUIT'.
*&---------------------------------------------------------------------*
*& 创建Excel对象
*&---------------------------------------------------------------------*
FORM open_excel_file USING  lcobj_excel
                            lcobj_workbook
                            lcobj_sheet
                            lc_filename
                            lc_sheetname
                            lc_visible.
  CREATE OBJECT lcobj_excel 'Excel.Application'.
  IF sy-subrc NE 0.
    MESSAGE e796(f9) WITH '不能创建Excel对象'.
  ENDIF.
  CALL METHOD OF lcobj_excel 'Workbooks' = lcobj_workbook.
  CALL METHOD OF lcobj_workbook 'Open' = lcobj_workbook
    EXPORTING #1 = lc_filename.
  IF sy-subrc NE 0.
    MESSAGE e796(f9) WITH '打开文件错误'.
  ENDIF.
  SET PROPERTY OF lcobj_excel 'Visible' = lc_visible.
  CALL METHOD OF lcobj_workbook 'Sheets' = lcobj_sheet
    EXPORTING #1 = 1.
  SET PROPERTY OF lcobj_sheet 'Name' = lc_sheetname.
ENDFORM.                    "excel_initialization
*&---------------------------------------------------------------------*
*& 读取单元格的数据
*&---------------------------------------------------------------------*
FORM excel_cell_read USING lcobj_excel
                           lc_row
                           lc_col
                           lc_value.
  DATA: lc_cell TYPE ole2_object.
  GET PROPERTY OF lcobj_excel 'Cells' = lc_cell
  exporting #1 = lc_row
            #2 = lc_col.
  GET PROPERTY OF lc_cell 'Value' = lc_value.
ENDFORM.                    "excel_cell_read
*&---------------------------------------------------------------------*
*& 写数据至Range
*&---------------------------------------------------------------------*
FORM excel_range_write USING lcobj_excel
                             lc_range
                             lc_value.
  DATA: lc_cell TYPE ole2_object.
  CALL METHOD OF lcobj_excel 'RANGE' = lc_cell
    EXPORTING
    #1 = lc_range.
  PERFORM font USING lc_cell 1 '30'.
  SET PROPERTY OF lc_cell 'VALUE' = lc_value.
ENDFORM.                    "excel_cell_write
*&---------------------------------------------------------------------*
*& 插入N行
*&---------------------------------------------------------------------*
FORM excel_row_insert USING lcobj_sheet
                            lc_row
                            lc_count.
  DATA lc_range TYPE ole2_object.
  DO lc_count TIMES.
    CALL METHOD OF lcobj_sheet 'Rows' = lc_range
      EXPORTING #1 = lc_row.
    CALL METHOD OF lc_range 'Copy'.
    CALL METHOD OF lcobj_sheet 'Rows' = lc_range
      EXPORTING #1 = lc_row.
    CALL METHOD OF lc_range 'Insert'.
    CALL METHOD OF lc_range 'ClearContents'. "是否需要清空Cell
  ENDDO.
ENDFORM.                    "excel_row_insert
*&---------------------------------------------------------------------*
*& 插入N列
*&---------------------------------------------------------------------*
FORM excel_column_insert USING  lcobj_sheet
                                lc_col
                                lc_count.
  DATA lc_range TYPE ole2_object.
  CALL METHOD OF lcobj_sheet 'COLUMNS' = lc_range
    EXPORTING #1 = lc_col.
  CALL METHOD OF lc_range 'Copy'.
  CALL METHOD OF lcobj_sheet 'COLUMNS' = lc_range
    EXPORTING #1 = lc_col.
  DO lc_count TIMES.
    CALL METHOD OF lc_range 'Insert'.
  ENDDO.
  CALL METHOD OF lc_range 'ClearContents'.
ENDFORM.                    "excel_column_insert
*&---------------------------------------------------------------------*
*& 增加一个Sheet
*&---------------------------------------------------------------------*
FORM excel_sheet_add USING  lcobj_workbook
                            lcobj_sheet
                            lc_newsheetname.
  DATA lc_newsheet TYPE ole2_object.
  GET PROPERTY OF lcobj_workbook 'Sheets' = lcobj_sheet.
  CALL METHOD OF lcobj_sheet 'Add' = lc_newsheet.
  SET PROPERTY OF lc_newsheet 'Name' = lc_newsheetname.
ENDFORM.                    "excel_sheet_add
*&---------------------------------------------------------------------*
*& 增加一个Workbook
*&---------------------------------------------------------------------*
FORM excel_workbook_add USING lcobj_excel
                              lcobj_workbook.
  DATA lc_newworkbook TYPE ole2_object.
  GET PROPERTY OF lcobj_excel 'Workbooks' = lcobj_workbook.
  CALL METHOD OF lcobj_workbook 'Add' = lc_newworkbook.
ENDFORM.                    "excel_workbook_add
*&---------------------------------------------------------------------*
*& 设置Cell的字体
*&---------------------------------------------------------------------*
FORM font USING lcobj_cell
                bold
                size.
  DATA lc_font TYPE ole2_object.
  CALL METHOD OF lcobj_cell 'FONT' = lc_font.
  SET PROPERTY OF lc_font 'BOLD' = bold.
  SET PROPERTY OF lc_font 'SIZE' = size.
  FREE OBJECT lc_font.
ENDFORM.                    "font
*&---------------------------------------------------------------------*
*& 行高
*&---------------------------------------------------------------------*
FORM rowheight USING lcobj_excel
                     row
                     height .
  DATA lc_row TYPE ole2_object .
  CALL METHOD OF lcobj_excel 'ROWS' = lc_row
    EXPORTING #1 = row.
  SET PROPERTY OF lc_row 'RowHeight' = height .
  FREE OBJECT lc_row.
ENDFORM .                    "rowheight
*&---------------------------------------------------------------------*
*& 列宽
*&---------------------------------------------------------------------*
FORM columnwidth USING lcobj_excel
                       column
                       width .
  DATA ls_col TYPE ole2_object .
  CALL METHOD OF lcobj_excel 'COLUMNS' = ls_col
    EXPORTING
    #1 = column.
  SET PROPERTY OF ls_col  'columnwidth' = width .
  FREE OBJECT ls_col.
ENDFORM .                    "columnwidth
*---------------------------------------------------------------------*
* 添加Range的边框                                                     *
*---------------------------------------------------------------------*
FORM borderrange USING lcobj_excel
                       range .
  DATA: lc_cell TYPE ole2_object ,
        lc_borders TYPE ole2_object .
  CALL METHOD OF lcobj_excel 'RANGE' = lc_cell
    EXPORTING
    #1 = range.
  DO 4 TIMES .
    CALL METHOD OF lc_cell 'BORDERS' = lc_borders
      EXPORTING #1 = sy-index.
    SET PROPERTY OF lc_borders 'LineStyle' = '1'.
    SET PROPERTY OF lc_borders 'WEIGHT' = 2.                "4=max
    SET PROPERTY OF lc_borders 'ColorIndex' = '3'.
  ENDDO.
  FREE OBJECT lc_borders.
  FREE OBJECT lc_cell.
ENDFORM.                    "borderrange

*---------------------------------------------------------------------*
* FORM fill_sheet_itab                                                *
*---------------------------------------------------------------------*
* 把内表依次放入Cell                                                  *
*---------------------------------------------------------------------*
FORM  fill_sheet_itab USING rowindex
                            colindex.
  DATA: row    TYPE  i ,
        col    TYPE  i .
  DATA: lcobj_cell TYPE ole2_object .
  FIELD-SYMBOLS:  .
  row = rowindex .
  LOOP AT itab .
    col = colindex .
    DO 20 TIMES.
      ASSIGN COMPONENT sy-index OF STRUCTURE itab TO .
      IF sy-subrc <> 0 .
        EXIT.
      ENDIF.
      CALL METHOD OF excel_obj 'Cells' = lcobj_cell
        EXPORTING #1 = row
        #2 = col.
      SET PROPERTY OF lcobj_cell 'Value' = .
      col = col + 1.
    ENDDO.
    row = row + 1.
  ENDLOOP.
ENDFORM.                    "fill_sheet_itab

 

*---------------------------------------------------------------------*
* 执行Excel的宏                                                       *
*---------------------------------------------------------------------*
FORM runmacro USING lcobj_excel
                    macroname.
  CALL METHOD OF lcobj_excel 'RUN'
    EXPORTING
    #1 = macroname.
ENDFORM.                    "runmacro

 

*&---------------------------------------------------------------------*
* 保护工作表
*----------------------------------------------------------------------*
FORM protect_sheet .
  CALL METHOD OF sheet_obj 'PROTECT'
      EXPORTING
        #1 = 'pass' " Password
        #2 = 1 " Protect Drawing Objects
        #3 = 1 " Protect Contents
        #4 = 1." Protect Scenarios.
ENDFORM.                    " protect_sheet

 

*&---------------------------------------------------------------------*
*& 保存Excel
*&---------------------------------------------------------------------*
FORM excel_save USING lcobj_excel.
  SET PROPERTY OF lcobj_excel 'DisplayAlerts' = 0.
  CALL METHOD OF lcobj_excel 'Save'.
*  CALL METHOD OF workbook_obj 'SAVEAS'  "另存
*    EXPORTING
*      #1 = 'C:\Test.xls'
*      #2 = 1.
ENDFORM.                    "excel_save