好久不写代码了，真的是手生了，翻了下之前的笔记，顺便回忆一下，之前查到的一些内容，重新复习一遍吧！留在以后查查也好，呵呵~
1.ole中如何保存和退出。
call method of sheetname 'saveas' exporting #1 = filepath #2 =1.
call method of applicationname 'quit'.
2.给sheet重命名。
call method of sheetname 'name' = 'sheetname'.
3.创建application.
call method of XXX 'excel.application'.
4.设置XXX的显示模式。
set property of XXX 'visible' = 1. 前台运行。为0时表示为后台运行。
5.创建workbook.
CALL METHOD OF EXCEL 'WORKBOOKS' = WORKBOOK .
call method of workbook 'ADD'.
6.在一个workbook中添加一个worksheet.
CALL METHOD OF applicationname 'sheets' = worksheet.
call method of worksheet 'Add' .
7.给单元格赋值。
CaLL METHOD OF EXCEL 'CELLS' = CELL
EXPORTING
#1 = 2
#2 = 2.
Set PROPERTY OF CELL 'value' = xxxx.
8.指定要被操作的sheet.
CALL METHOD OF applicationname 'Worksheets' = SHEET
EXPORTING
#1 = 'sheet3'. 这里sheet3为要操作的sheet的名字。
call method of sheet 'Activate '.
9.指定操作的单元格的范围。
CALL METHOD OF applicationname 'Range' = range
EXPORTING
#1 = 'B2'
#2 = 'c2'.
10. 如何操作范围内的单元格。
call method of range 'select '. range也为已经定义好的ole2_object.
Set PROPERTY OF range 'MergeCells' = 0 . 合并单元格，0时不合并，1则合并。与前面一起使用
Set PROPERTY OF range 'HorizontalAlignment' = 10 .
Set PROPERTY OF range 'VerticalAlignment' = -4108 .
11.单元格内部属性的操作.
call method of cell 'INTERIOR' = int.
set property of int 'ColorIndex' = color. 颜色
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.边框的操作.
call method of CELL 'BORDERS' = BORDERS
EXPORTING
#1 = '1'. 1-left 2-right 3-top 4-bottom
set property of borders 'Linestyle' = plinestyle .
set property of borders 'Weight' = pweight .
free object borders.
在此之前应该指定range.
14.复制与粘贴.
call method of sheet 'copy'.
call method of sheet 'paste'.
15.一点注意。
在操作sheet时，默认为上次操作的sheet.如果想更换，参考8。
16。一个问题。有时输入数据如111111111111111111，会显示为1E+17。
解决办法：
CALL METHOD OF h_excel 'COLUMNS' = columnObj
EXPORTING
#1 = 6. "the column number
SET PROPERTY OF columnObj 'ColumnWidth' = 10.
SET PROPERTY OF columnObj 'NumberFormat' = '@'.
17。打开一个workbook.
call METHOD OF workbook 'Open' EXPORTING #1 = filename+path.
18. 所有的操作方法都可以在sell----表 oleload 中 查询到。
19. 执行宏。
CALL METHOD OF EXCEL 'RUN' EXPORTING #1 = 'ZMACRO2'.
20。清除range 内容
CALL METHOD OF EXCEL 'Range' = RANGE
EXPORTING
#1 = tar_cell
#2 = tar_cell.
call METHOD of RANGE 'ClearContents'.
21 合并单元格
CALL METHOD OF EXCEL 'Range' = RANGE
EXPORTING
#1 = sor_cell
#2 = tar_cell.
SET PROPERTY OF RANGE 'MergeCells' = 1.
22。 缩小字体填充
SET PROPERTY OF range 'ShrinkToFit' = 0 .
note: 为0时取消缩小字体填充，为1时设置缩小字体填充。
note:
1.与ole相关的关键字存储在表oleload中。
2.如果有些ole方法不熟悉，可以利用vba录制宏来寻找相关的关键字。
顺便把我的模板代码贴出来，俺一般都是copy改改的~~~~~~~
REPORT ztest_ole.
****************************************************
*&create by zhanghz 2011.09.22 augus is me
*&测试ole的用法，把内表的数据导入到excel表中
*模板文件是从服务器中下载下来的
****************************************************
***********类型池********************************
TYPE-POOLS: ole2.
**********表声明********************************
TABLES: marc,mara.
*****************数据定义************************
DATA: excel TYPE ole2_object,
books TYPE ole2_object,
sheet TYPE ole2_object,
cell TYPE ole2_object.
DATA: BEGIN OF itab OCCURS 0,
matnr LIKE mara-matnr, "物料号
matkl LIKE mara-matkl,"物料组
tragr LIKE mara-tragr,"运输组
werks LIKE marc-werks,"工厂
maabc LIKE marc-maabc,"abc标识
lgpro LIKE marc-lgpro,"库存地点
END OF itab.
DATA: p_fname(120) TYPE c.
DATA: l_exist.
******选择屏幕************************************
SELECT-OPTIONS: zmatnr FOR mara-matnr.
PARAMETERS: zwerks LIKE marc-werks.
*PARAMETERS: p_fname(60) DEFAULT 'E:\BOM.XLS'.
*************start-of-selection********************
START-OF-SELECTION.
PERFORM temp_excel_get.
PERFORM check_file.
PERFORM get_data.
PERFORM display_data.
*&---------------------------------------------------------------------*
*& Form check_file
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM check_file.
CALL FUNCTION 'TMP_GUI_GET_FILE_EXIST' "判断本地文件是否存在
EXPORTING
fname = p_fname
IMPORTING
exist = l_exist
* ISDIR =
* FILESIZE =
EXCEPTIONS
fileinfo_error = 1
OTHERS = 2 .
IF sy-subrc <> 0 OR l_exist <> 'X'.
MESSAGE i398(00) WITH '打开模版文件' p_fname '时出错！'.
EXIT.
ENDIF.
ENDFORM. "check_file
*&---------------------------------------------------------------------*
*& Form get_data
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM get_data.
SELECT * INTO CORRESPONDING FIELDS OF TABLE itab FROM mara INNER JOIN marc ON mara~matnr = marc~matnr
WHERE mara~matnr IN zmatnr AND marc~werks = zwerks.
ENDFORM. "get_data
*&---------------------------------------------------------------------*
*& Form display_form
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM display_data.
CREATE OBJECT excel 'EXCEL.APPLICATION'."创建excel对象
CALL METHOD OF excel 'WORKBOOKS' = books."创建工作区
CALL METHOD OF books 'OPEN'
EXPORTING
#1 = p_fname.
"'E:\BOM.XLS'.
* DATA: title(100) TYPE c,
* text1(200) TYPE c,
* text2(100) TYPE c.
*
* CONCATENATE '打印者： ' space sy-uname INTO text1.
* CONCATENATE '打印日期 : '
* sy-datum+0(4) '年' sy-datum+4(2) '月' sy-datum+6(2) '日' INTO text2.
*
* PERFORM fill_cell USING 1 6 title.
* PERFORM fill_cell USING 2 1 text1.
* PERFORM fill_cell USING 3 1 text2.
*************************增加sheet操作
CALL METHOD OF excel 'WORKSHEETS' = sheet
EXPORTING
#1 = 1.
CALL METHOD OF sheet 'ACTIVATE'.
CALL METHOD OF excel 'CELLS' = cell
EXPORTING
#1 = 1
#2 = 1.
SET PROPERTY OF excel 'Visible' = 1. "调用EXCEL
DATA: row TYPE i.
* DATA: text1(200) TYPE c.
* DATA: text2(200) TYPE c.
* DATA: text3(200) TYPE c.
* DATA: text4(200) TYPE c.
* DATA: text5(200) TYPE c.
* DATA: text6(200) TYPE c.
* text1 = '物料号'.
* text2 = '物料组'.
* text3 = '运输组'.
* text4 = '工厂'.
* text5 = '标志'.
* text6 = '库存地点'.
** CONCATENATE '物料编码(成品/半成品)：' itab-matnr INTO text3.
** CONCATENATE '物料描述：' itab-maktx INTO text4.
** CONCATENATE '生效日期 : ' space vl_datuv INTO text5.
*******excel抬头名称
* PERFORM fill_cell USING 2 2 text1.
* PERFORM fill_cell USING 2 3 text2.
* PERFORM fill_cell USING 2 4 text3.
* PERFORM fill_cell USING 2 5 text4.
* PERFORM fill_cell USING 2 6 text5.
* PERFORM fill_cell USING 2 7 text6.
row = 3.
*******填充单元格数据
LOOP AT itab.
PERFORM fill_cell USING row 2 itab-matnr.
PERFORM fill_cell USING row 3 itab-matkl.
PERFORM fill_cell USING row 4 itab-tragr.
PERFORM fill_cell USING row 5 itab-werks.
PERFORM fill_cell USING row 6 itab-maabc.
PERFORM fill_cell USING row 7 itab-lgpro.
row = row + 1.
CLEAR : itab.
ENDLOOP.
**********第二个人sheet
* CALL METHOD OF excel 'WORKSHEETS' = sheet
* EXPORTING
* #1 = 2.
* CALL METHOD OF sheet 'ACTIVATE'.
* CALL METHOD OF excel 'CELLS' = cell
* EXPORTING
* #1 = 1
* #2 = 1.
* SET PROPERTY OF excel 'Visible' = 1. "调用EXCEL
* PERFORM fill_cell USING row 2 'sheet2 test'."处理第二个sheeet
****关闭WORKSHEET****
CALL METHOD OF sheet 'CLOSE'.
****关闭WORKBOOK****
CALL METHOD OF books 'CLOSE'.
****关闭EXCEL对象****
CALL METHOD OF excel 'QUIT'.
****释放对象****
FREE OBJECT cell.
FREE OBJECT sheet.
FREE OBJECT books.
FREE OBJECT excel.
ENDFORM. "display_form
*&---------------------------------------------------------------------*
*& Form fill_cell
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_I text
* -->P_J text
* -->P_VAL text
*----------------------------------------------------------------------*
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. "fill_cell
*&---------------------------------------------------------------------*
*& Form temp_excel_get
*&---------------------------------------------------------------------*
**2.在程序中下载模板到本地,模板的格式可以自己控制的
*下载EXCEL模板FORM
*----------------------------------------------------------------------*
FORM temp_excel_get.
DATA: lo_objdata LIKE wwwdatatab,
lo_mime LIKE w3mime,
lc_filename TYPE string VALUE'test01' ,"下载模板的默认文件名称
lc_fullpath TYPE string VALUE'D:\test\' ,"模板路径
lc_path TYPE string VALUE'D:\test\' ,"模板路径
ls_destination LIKE rlgrap-filename,
ls_objnam TYPE string,
li_rc LIKE sy-subrc,
ls_errtxt TYPE string.
DATA:p_objid TYPE wwwdatatab-objid,
p_dest LIKE sapb-sappfad.
p_objid = 'ZBOM.XLS'. "此处为EXCEL模板名称,使用事务代码smw0上传excel模板，二进制数据
CALL METHOD cl_gui_frontend_services=>file_save_dialog "调用保存对话框
EXPORTING
default_extension = 'XLS'
default_file_name = lc_filename
CHANGING
filename = lc_filename
path = lc_path
fullpath = lc_fullpath
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
IF lc_fullpath = ''.
MESSAGE '不能打开excel' TYPE 'E'.
ENDIF.
IF sy-subrc = 0.
p_dest = lc_fullpath.
* concatenate p_objid '.XLS' into ls_objnam.
CONDENSE ls_objnam NO-GAPS.
SELECT SINGLE relid objid FROM wwwdata INTO CORRESPONDING FIELDS OF lo_objdata
WHERE srtf2 = 0 AND relid = 'MI' AND objid = p_objid.
*检查表wwwdata中是否存在所指定的模板文件
IF sy-subrc NE 0 OR lo_objdata-objid EQ space."如果不存在，则给出错误提示
CONCATENATE '模板文件' ls_objnam '不存在' INTO ls_errtxt.
MESSAGE ls_errtxt TYPE 'I'.
ENDIF.
ls_destination = p_dest. "保存路径
*如果存在，调用DOWNLOAD_WEB_OBJECT 函数下载模板到路径下
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
key = lo_objdata
destination = ls_destination
IMPORTING
rc = li_rc.
IF li_rc NE 0.
CONCATENATE '模板文件：' ls_objnam '下载失败' INTO ls_errtxt.
MESSAGE ls_errtxt TYPE 'E'.
ENDIF.
p_fname = ls_destination.
ENDIF.
ENDFORM. "fm_excel