SAP EXCEL OLE常用方法和属性

1.创建application:

CREATE OBJECT excel 'EXCEL.APPLICATION'.

2.设置显示模式，为1前台运行，为0时表示为后台运行。　　

SET PROPERTY OF excel 'VISIBLE' = 0.

3.设置为不弹消息框（在删除sheet时，可以不用确认）

SET PROPERTY OF excel 'DisplayAlerts' = 0.

4.创建工作薄：

CALL METHOD OF excel 'WORKBOOKS' = workbook .

5.工作簿打开本地文件：

CALL METHOD OF workbook 'Open'
    EXPORTING
      #1 = p_path.  “文件路径

6.新建一个sheet（用第9点命名）

CALL METHOD OF excel 'WORKSHEETS' = sheet.
CALL METHOD OF sheet 'Add' .

7.设置活动sheet

CALL METHOD OF excel 'WORKSHEETS' = sheet
  EXPORTING
   #1 = 'sheet1'.

 CALL METHOD OF sheet 'ACTIVATE'.

8.删除sheet（当前活动sheet）

CALL METHOD OF sheet 'DELETE'.

9. sheet重命名（当前活动sheet）

CALL METHOD OF sheet 'NAME' = 'sheet3'.

10.单元格赋值

"创建单元格对象，指定行列
CALL METHOD OF excel 'CELLS' = cell
  EXPORTING
  #1 = 2
  #2 = 2.

"赋值
SET PROPERTY OF cell 'value'=  xxxx.

11.单元格内部属性的操作

CALL METHOD OF cell 'INTERIOR' = int.
SET PROPERTY OF int 'ColorIndex' = color.  "颜色(参数为vba颜色代码)
SET PROPERTY OF int 'Pattern' = pattern.

12.单元格内字体的操作

CALL METHOD OF cell 'FONT' = font.
SET PROPERTY OF font 'BOLD' = bold.
SET PROPERTY OF font 'SIZE' = size.

13.单元格文本格式

SET PROPERTY OF cell 'NumberFormatLocal' ='@'.  "设置为文本格式,可以解决输入数据如111111111111111111，显示为1E+17的问题
SET PROPERTY OF cell 'NumberFormatLocal' ='#,##0'.  "财务用格式

14.指定操作的单元格的范围

CALL METHOD OF sheet 'range' = range
  EXPORTING
  #1 = 'a1'
  #2 = 'c3'.

15.合并范围内单元格

CALL METHOD OF range 'select '. "操作范围时，必须激活范围
SET PROPERTY OF range 'MergeCells' = 0 .  "合并单元格，0时不合并，1则合并。

16.范围的边框

 GET PROPERTY OF range 'borders' =  borders. "加边框

 SET PROPERTY OF borders 'weight'  = '2' .   "边框宽

 SET PROPERTY OF borders  'linestyle' = '1'. "框线格式

 

17. 执行宏

CALL METHOD OF EXCEL 'RUN' EXPORTING #1 ='XXX' "XXX宏名称

18.保存到本地（必须先设置为活动工作薄）

"设置为活动工作簿
GET PROPERTY OF excel 'ActiveWorkbook' = workbook.

"保存不带带调整文件
CALL METHOD OF workbook 'SAVEAS'
  EXPORTING
    #1 = p_path    "保存路径
    #2 = 1.

19.关闭退出

"关闭工作簿
CALL METHOD OF workbook 'CLOSE'.

"退出处理线程
CALL METHOD OF excel 'quit'.

 类：ole2_object

Create object obj_name ‘app’.创建APP应用类的一个对象obj_name实例

Set property of obj_name ‘XXX’ = f .设置对象OBJ_NAME属性xxx为值f

Get property of obj_name ‘XXX’ = f .将obj_name的属性xxx的值获取赋给f

Call method of obj_name ‘XXX’ = f
　　Exporting #1 = f1 ....#n = fn. 调用Obj_name的方法xxx 传入参数f1、、、fn,由f来接收返回值；

Free object obj_name. 释放obj_name.

操作过程中主要是很多方法和属性不知道，怎么办呢？
1、可以通过Tole表知道应用对象信息然后对应到OLELOAD表中查找相关的属性；
2、在word excel中录制宏，然后编辑宏，查看生成德VB代码，找到对应的属性和方法；