OLE-Excel基本操作

1.create object  obj

  CREATE OBJECT EXCEL 'Excel.Application'.

这个obj通常指能够支持的应用程序

应用程序首先要满足OLE2的标准

同时还要注册到R/3系统

维护的程序信息在table: tole中可以看到

tole有个视图v_tole  可以使用tcode : sole来编辑

2.Workbook

  CALL METHOD OF EXCEL 'WORKBOOKS' = BOOKS.

a.读取现有的excel

  CALL METHOD OF BOOKS 'Open' EXPORTING #1 = p_file.

b.新建excel

  CALL METHOD OF BOOKS 'Add' .

3.Sheets

a.设置sheets的数量

  SET PROPERTY OF excel 'SheetsInNewWorkbook' = 3.

b.切换到某个sheet

  CALL METHOD OF excel 'Worksheets' = sheet EXPORTING #1 = 1.
  CALL METHOD OF sheet 'Activate'.

这个active的方法一定要调用 才能确定使用某一个sheet

c.设定名称

  SET PROPERTY OF excel 'Name' = p_sheetname.

4.合并单元格

a.确定范围

  CALL METHOD OF excel 'RANGE' = range
       EXPORTING #1 = 'A2'
                 #2 = 'E3' .

b.范围选中

  CALL METHOD OF range 'SELECT' .

c.合并单元格

  SET PROPERTY OF range 'MERGE' = 1 .

5.删除行

  CALL METHOD OF excel 'ROWS' = row
                         Exporting #1 = 2.

  CALL METHOD OF row 'DELETE'.

6.单元格着色

CALL METHOD OF excel 'cells' = cell
        EXPORTING
          #1 = 1
          #2 = 3.
GET PROPERTY OF cell 'Interior' = color.
SET PROPERTY OF color 'ColorIndex' = 35.

 

PS:

1.  GET PROPERTY OF EXCEL 'ACTIVESHEET' = SHEET.

  GET PROPERTY OF EXCEL 'ACTIVEWORKBOOK' = BOOKS.
退出时要加上这两句

否则导不出excel

 

2.关于范围range 

使用后要free掉

才能使用下一个范围

 

3.关于quit 和 free

quit是给OLE Server的

即相当于告诉excel程序退出

free是abap程序里面的object

 

4.关于使用文件操作等

例如需要指定下载到桌面

和控件相关的automation queue

注意调用cl_gui_cfw=>flush

 

5.OLE的操作也可以指定no flush

节省server 和 client 的交互

一次性传输所有的OLE操作

 

附代码:

report z_excel_demo.
type-pools:  OLE2 .

DATA: excel TYPE ole2_object, " Excel object
    books TYPE ole2_object, " list of workbooks
    sheet TYPE ole2_object, " workbook
    cell  TYPE ole2_object, " cell
    row  TYPE ole2_object, " cell
    borders  TYPE ole2_object, " cell
    color  TYPE ole2_object, " cell
    range  TYPE ole2_object. " cell
data: l_desktop type string.

  CREATE OBJECT EXCEL 'Excel.Application'.

  SET PROPERTY OF excel 'Visible' = 0.
  SET PROPERTY OF excel 'SheetsInNewWorkbook' = 3.


  CALL METHOD OF EXCEL 'WORKBOOKS' = BOOKS.



  CALL METHOD OF BOOKS 'Add' .
*   CALL METHOD OF BOOKS 'Open' EXPORTING #1 = p_file.

  CALL METHOD OF excel 'Worksheets' = sheet EXPORTING #1 = 1.
  CALL METHOD OF sheet 'Activate'.
  perform filling.
  perform addition.

  CALL METHOD OF excel 'Worksheets' = sheet EXPORTING #1 = 2.
  CALL METHOD OF sheet 'Activate'.
  perform filling.


  GET PROPERTY OF EXCEL 'ACTIVESHEET' = SHEET.
  GET PROPERTY OF EXCEL 'ACTIVEWORKBOOK' = BOOKS.

*  "默认导出到桌面

  perform get_desktop using l_desktop.



  CONCATENATE l_desktop '\EXCEL_DEMO'   '.xls' INTO l_desktop.

      call method of BOOKS 'SAVEAS'
          EXPORTING
            #1 = l_desktop
           #2 = 1.

      call method of books 'CLOSE'.
      call method of excel 'QUIT'.


       FREE OBJECT EXCEL.
       message s888(sapaapdocu) with '成功导出模板:' l_desktop.

form filling.
    PERFORM fill_cell USING :
                      1 1 'Field1',
                      1 2 'Field2'.

endform.

form addition.
  "合并单元格
  CALL METHOD OF excel 'RANGE' = range

       EXPORTING #1 = 'A2'

                 #2 = 'E3' .

*  删除行
*  CALL METHOD OF excel 'ROWS' = row
*                         Exporting #1 = 2.
*  CALL METHOD OF row 'DELETE'.


  CALL METHOD OF range 'SELECT' .

  SET PROPERTY OF range 'MERGE' = 1 .

  free range.


  CALL METHOD OF excel 'RANGE' = range

       EXPORTING #1 = 'A5'

                  #2 = 'M5'.


  CALL METHOD OF range 'Borders' = borders

       EXPORTING #1 = 8.

  SET PROPERTY OF borders 'LineStyle' = 1.


  CALL METHOD OF excel 'cells' = cell

          EXPORTING

            #1 = 1

            #2 = 3.

GET PROPERTY OF cell 'Interior' = color.

SET PROPERTY OF color 'ColorIndex' = 35.

SET PROPERTY OF cell 'ColumnWidth' = 20.
endform.
*-------------------------------------------------------*
*  默认导出模板到桌面
*-------------------------------------------------------*
form get_desktop using p_desktop type string .
  CALL METHOD CL_GUI_FRONTEND_SERVICES=>GET_DESKTOP_DIRECTORY
      changing
        DESKTOP_DIRECTORY = p_desktop .

  CALL METHOD CL_GUI_CFW=>UPDATE_VIEW.
endform.


FORM fill_cell USING  p_i p_j p_val.

  CALL METHOD OF excel 'CELLS' = cell EXPORTING #1 = p_i #2 = p_j.
  SET PROPERTY OF cell 'VALUE' = p_val.

ENDFORM.