SAPEXCELOLE常用方法和属性.docx
- 文档编号:7382865
- 上传时间:2023-01-23
- 格式:DOCX
- 页数:16
- 大小:18.99KB
SAPEXCELOLE常用方法和属性.docx
《SAPEXCELOLE常用方法和属性.docx》由会员分享,可在线阅读,更多相关《SAPEXCELOLE常用方法和属性.docx(16页珍藏版)》请在冰豆网上搜索。
SAPEXCELOLE常用方法和属性
SAPEXCELOLE常用方法和属性附加网上找到的比较好的源代码示例一份
1.ole中如何保存和退出。
callmethodofsheetname'saveas'
exporting
#1=filepath
#2=1.
callmethodofapplicationname'quit'.
-------------------------------------------------------------------------
2.给sheet重命名。
callmethodofsheetname'name'='sheetname'.
-------------------------------------------------------------------------
3.创建application.
callmethodofXXX'excel.application'.
-------------------------------------------------------------------------
4.设置XXX的显示模式。
setpropertyofXXX'visible'=1.前台运行。
为0时表示为后台运行。
-------------------------------------------------------------------------
5.创建workbook.
CALLMETHODOFEXCEL'WORKBOOKS'=WORKBOOK.
callmethodofworkbook'ADD'.
-------------------------------------------------------------------------
6.在一个workbook中添加一个worksheet.
CALLMETHODOFapplicationname'sheets'=worksheet.
callmethodofworksheet'Add'.
-------------------------------------------------------------------------
7.给单元格赋值。
CaLLMETHODOFEXCEL'CELLS'=CELL
EXPORTING
#1=2
#2=2.
SetPROPERTYOFCELL'value'= xxxx.
-------------------------------------------------------------------------
8.指定要被操作的sheet.
CALLMETHODOFapplicationname'Worksheets'=SHEET
EXPORTING
#1='sheet3'. 这里sheet3为要操作的sheet的名字。
callmethodofsheet'Activate'.
-------------------------------------------------------------------------
9.指定操作的单元格的范围。
CALLMETHODOFapplicationname'Range'=range
EXPORTING
#1='B2'
#2='c2'.
-------------------------------------------------------------------------
10. 如何操作范围内的单元格。
callmethodofrange'select'. range也为已经定义好的ole2_object.
SetPROPERTYOFrange 'MergeCells'=0. 合并单元格,0时不合并,1则合并。
与前面一起使用
SetPROPERTYOFrange 'HorizontalAlignment'=10.
SetPROPERTYOFrange 'VerticalAlignment'=-4108 .
-------------------------------------------------------------------------
11.单元格内部属性的操作.
callmethodofcell'INTERIOR'=int.
setpropertyofint'ColorIndex'=color. 颜色
setpropertyofint'Pattern'=pattern.
-------------------------------------------------------------------------
12.单元格内字体的操作.
callmethodofCELL'FONT'=font.
setpropertyoffont'BOLD'=bold.
setpropertyoffont'SIZE'=size.
-------------------------------------------------------------------------
13.边框的操作.
callmethodofCELL'BORDERS'=BORDERS
EXPORTING
#1='1'.1-left 2-right3-top4-bottom
setpropertyofborders'Linestyle'=plinestyle.
setpropertyofborders'Weight'=pweight.
freeobjectborders.
在此之前应该指定range.
-------------------------------------------------------------------------
14.复制与粘贴.
callmethodofsheet'copy'.
callmethodofsheet'paste'.
-------------------------------------------------------------------------
15.一点注意。
在操作sheet时,默认为上次操作的sheet.如果想更换,参考8。
-------------------------------------------------------------------------
16。
一个问题。
有时输入数据如111111*********111,会显示为1E+17。
解决办法:
CALLMETHODOFh_excel'COLUMNS'=columnObj
EXPORTING
#1=6. "thecolumnnumber
SETPROPERTYOFcolumnObj'ColumnWidth'=10.
SETPROPERTYOFcolumnObj'NumberFormat'='@'.
-------------------------------------------------------------------------
17。
打开一个workbook.
callMETHODOFworkbook'Open'EXPORTING#1=filename+path.
-------------------------------------------------------------------------
18. 所有的操作方法都可以在sell----表 oleload 中 查询到。
-------------------------------------------------------------------------
19. 执行宏。
CALLMETHODOFEXCEL'RUN'EXPORTING#1='ZMACRO2'.
-------------------------------------------------------------------------
20。
清除range 内容
CALLMETHODOFEXCEL'Range'=RANGE
EXPORTING
#1=tar_cell
#2=tar_cell.
callMETHODofRANGE'ClearContents'.
-------------------------------------------------------------------------
21 合并单元格
CALLMETHODOFEXCEL'Range'=RANGE
EXPORTING
#1=sor_cell
#2=tar_cell.
SETPROPERTYOFRANGE'MergeCells'=1.
-------------------------------------------------------------------------
22。
缩小字体填充
SETPROPERTYOFrange'ShrinkToFit'=0.
note:
为0时取消缩小字体填充,为1时设置缩小字体填充。
-------------------------------------------------------------------------
note:
1.与ole相关的关键字存储在表oleload中。
2.如果有些ole方法不熟悉,可以利用vba录制宏来寻找相关的关键字。
-------------------------------------------------------------------------
-------------------------------------------------------------------------
OLE示例:
可直接运行
*----------------------------------------------------------------------*
* Program Name:
Z_KEVIN_OLE
* Project :
N/A
* Author :
Kevin.Zhang
* Date :
2007.1.1
* Module :
N/A
* Description :
Template of Program
*
*
*
* Special features:
N/A
*
*
*----------------------------------------------------------------------*
* Modifications:
* Author Date Commented as Description
*----------- -------- ----------- ----------------------------------*
*
*----------------------------------------------------------------------*
REPORT Z_KEVIN_OLE.
*$*$----------------------------------------------------------------$*$*
*$*$ Global Types, Data Statements, Ranges, Constants $*$*
*$*$----------------------------------------------------------------$*$*
*-----------------------------------------------------------------------
* Type Pools
*-----------------------------------------------------------------------
*--Include for OLE-enabling definitions
INCLUDE OLE2INCL .
*-----------------------------------------------------------------------
* Global GUI Controls Variables / Structures
*-----------------------------------------------------------------------
*--Data Definition of OLE
DATA:
GS_EXCEL TYPE OLE2_OBJECT ,
GS_WBOOKLIST TYPE OLE2_OBJECT ,
GS_APPLICATION TYPE OLE2_OBJECT ,
GS_WBOOK TYPE OLE2_OBJECT ,
GS_ACTIVESHEET TYPE OLE2_OBJECT ,
GS_SHEETS TYPE OLE2_OBJECT ,
GS_NEWSHEET TYPE OLE2_OBJECT ,
GS_CELL1 TYPE OLE2_OBJECT ,
GS_CELL2 TYPE OLE2_OBJECT ,
GS_CELLS TYPE OLE2_OBJECT ,
GS_RANGE TYPE OLE2_OBJECT ,
GS_FONT TYPE OLE2_OBJECT ,
GS_INTERIOR TYPE OLE2_OBJECT ,
GS_COLUMNS TYPE OLE2_OBJECT ,
GS_CHARTS TYPE OLE2_OBJECT ,
GS_CHART TYPE OLE2_OBJECT ,
GS_CHARTTITLE TYPE OLE2_OBJECT ,
GS_CHARTTITLECHAR TYPE OLE2_OBJECT ,
GS_CHARTOBJECTS TYPE OLE2_OBJECT .
DATA GV_SHEET_NAME(20) TYPE C .
DATA GV_OUTER_INDEX LIKE SY-INDEX .
DATA GV_INTEX
(2) TYPE C .
DATA GV_LINE_CNTR TYPE I . "line counter
DATA GV_LINNO TYPE I . "line number
DATA GV_COLNO TYPE I . "column number
DATA GV_VALUE TYPE I . "data
*$*$----------------------------------------------------------------$*$*
*$*$ Selection Screen $*$*
*$*$----------------------------------------------------------------$*$*
*-----------------------------------------------------------------------
* Selection Screen
*-----------------------------------------------------------------------
PARAMETERS:
P_SHEETS TYPE I .
*$*$----------------------------------------------------------------$*$*
*$*$ Main Program $*$*
*$*$----------------------------------------------------------------$*$*
*--------- START-OF-SELECTION ----------
START-OF-SELECTION .
DO P_SHEETS TIMES .
*--Forming sheet name
GV_INTEX = SY-INDEX .
GV_OUTER_INDEX = SY-INDEX .
CONCATENATE 'Excel Sheet #' GV_INTEX INTO GV_SHEET_NAME .
*--For the first loop, Excel is initiated and one new sheet is added
IF SY-INDEX = 1 .
CREATE OBJECT GS_EXCEL 'EXCEL.APPLICATION' .
SET PROPERTY OF GS_EXCEL 'Visible' = 1 .
GET PROPERTY OF GS_EXCEL 'Workbooks' = GS_WBOOKLIST .
GET PROPERTY OF GS_WBOOKLIST 'Application' = GS_APPLICATION .
SET PROPERTY OF GS_APPLICATION 'SheetsInNewWorkbook' = 1 .
CALL METHOD OF GS_WBOOKLIST 'Add' = GS_WBOOK .
GET PROPERTY OF GS_APPLICATION 'ActiveSheet' = GS_ACTIVESHEET .
SET PROPERTY OF GS_ACTIVESHEET 'Name' = GV_SHEET_NAME .
*--For the rest of loops, other sheets are added
ELSE .
GET PROPERTY OF GS_WBOOK 'Sheets' = GS_SHEETS .
CALL METHOD OF GS_SHEETS 'Add' = GS_NEWSHEET .
SET PROPERTY OF GS_NEWSHEET 'Name' = GV_SHEET_NAME .
ENDIF .
GV_LINE_CNTR = 1 . "line counter
*--Title
*--Selecting cell area to be merged.
CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1
EXPORTING
#1 = 1
#2 = 1.
CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL2
EXPORTING
#1 = 1
#2 = 4.
CALL METHOD OF GS_EXCEL 'Range' = GS_CELLS
EXPORTING
#1 = GS_CELL1
#2 = GS_CELL2.
CALL METHOD OF GS_CELLS 'Select' .
*--Merging
CALL METHOD OF GS_CELLS 'Merge' .
*--Setting title data
CALL METHOD OF GS_EXCEL 'Cells' = GS_CELL1
EXPORTING
#1 = GV_LINE_CNTR
#2 = 1.
SET PROPERTY OF GS_CELL1 'Value' = 'TITLE' .
*--Formatting the title
GET PROPERTY OF GS_CELL1 'Font' = GS_FONT .
SET PROPERTY OF GS_FONT 'Underline' = 2 .
SET PROPERTY OF GS_FONT 'Bold' = 1 .
SET PROPERTY OF GS_CELL1 'HorizontalAlignment' = -4108 .
GET PROPERTY OF GS_CELL1 'Interior' = GS_INTERIOR .
SET PROPERTY OF GS_INTERIOR 'ColorIndex' = 15 .
SET PROPERTY OF GS_INTERIOR 'Pattern' = -4124 .
SET PROPERTY OF GS_INTERIOR 'PatternColorIndex' =
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SAPEXCELOLE 常用 方法 属性