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.