SAP数据按格式保存到EXCEL(OLE)
来源：互联网 发布：数据编是不是得英语好 编辑：程序博客网 时间：2024/06/11 04:55
REPORT ZZBGS700 MESSAGE-ID Z1.
*----------------------------------------------------------------------*
* Topic: Example: Calling up Microsoft Excel. *
* *
* Description: Example program that is calling up EXCEL, transfer *
* an internal table and returning to R/3. *
* *
* Implementing The program uses OLE. *
* In R/3 release 3.0x, the command set of ABAP/4 ontepreter has been *
* increased with keywords that allow access to external objects through*
* OLE2. In SAP the new keywords for OLE2 are: *
* *
* 1. Create Object *
* 2. Set Property *
* 3. Get Property *
* 4. Call Method *
* 5. Free Object *
* *
* *
* Authoriz. None. *
* *
* Submitting: Run by SA38, SE38. *
* *
* Parametre: Excel file path and filename. *
* *
* Output: Excel file with data. *
* *
* Customizing: Check that EXCEL is registered in R/3 via transaction *
* SOLE. *
* *
* Change of You only need to do the syntax check at releasechange. *
* release: *
* *
* R/3 Release: Developed and tested in R/3 Release: *
* 3.0C *
* *
* Programmer: Benny G. Soerensen *
* Date: July 1997. *
* *
*------------------------------ Questions -----------------------------*
* Version 1
*-------------------------------Corrections----------------------------*
* Date Userid Correction Text *
* ::.::.:::: :::::::: :::::::::::::: ::::::::::::::::::::::::::::: *
*----------------------------------------------------------------------*
PARAMETERS: XLSFILE(64) TYPE C DEFAULT
’C:/BGS/SAP/MODUL-BC/OLE/ZZBGS700.XLS’.
TABLES USR03.
DATA: USERS LIKE USR03 OCCURS 100 WITH HEADER LINE
,ITEMS TYPE I.
CONSTANTS: OK TYPE I VALUE 0.
INCLUDE OLE2INCL.
DATA: EXCEL TYPE OLE2_OBJECT,
WORKBOOK TYPE OLE2_OBJECT,
SHEET TYPE OLE2_OBJECT,
CELL TYPE OLE2_OBJECT,
ROW TYPE OLE2_OBJECT.
*----------------------------------------------------------------------*
* You find SAP OLE programs under development Class ’SOLE’ *
* *
* MSTAPPL Table Maintenance APPL *
* RSOLEDOC Document list *
* RSOLEIN0 OLE Load Type Information *
* RSOLEINT Type Info Loaded *
* RSOLETI0 OLE Object Browser *
* RSOLETI1 OLE Object Browser *
* RSOLETI2 OLE Object Browser *
* RSOLETI3 F4 Help For OLE Objects *
* RSOLETT1 OLE 2.0 Automation Demo Program *
* *
* Transactions: *
* SOLE *
* SOLO - List of OLE applcations with loaded type info *
* *
* *
* You will find the decription of possible objects and methods in the *
* windows help file for excel. *
*----------------------------------------------------------------------*
* Create an Excel object and start Excel.
CREATE OBJECT EXCEL ’EXCEL.APPLICATION’.
IF SY-SUBRC NE OK.
MESSAGE I000 WITH SY-MSGLI.
ENDIF.
* Create an Excel workbook Object.
CALL METHOD OF EXCEL ’WORKBOOKS’ = WORKBOOK .
* Put Excel in background
SET PROPERTY OF EXCEL ’VISIBLE’ = 0 .
* Collect the data to the transfer.
select * FROM USR03 INTO TABLE USERS.
DESCRIBE TABLE USERS LINES ITEMS.
CHECK ITEMS > 0.
* Put Excel in front.
SET PROPERTY OF EXCEL ’VISIBLE’ = 1 .
* Transfer the header line to Excel.
CALL METHOD OF WORKBOOK ’OPEN’ EXPORTING #1 = XLSFILE.
CALL METHOD OF EXCEL ’RANGE’ = CELL EXPORTING #1 = ’A1’.
SET PROPERTY OF CELL ’VALUE’ = ’BNAME’ .
CALL METHOD OF EXCEL ’RANGE’ = CELL EXPORTING #1 = ’B1’.
SET PROPERTY OF CELL ’VALUE’ = ’NAME1’ .
* Transfer the internal table values to Excel
LOOP AT USERS.
CALL METHOD OF EXCEL ’ROWS’ = ROW EXPORTING #1 = ’2’ .
CALL METHOD OF ROW ’__insert’ NO FLUSH.
CALL METHOD OF EXCEL ’RANGE’ = CELL NO FLUSH EXPORTING #1 = ’A2’ .
SET PROPERTY OF CELL ’VALUE’ = USERS-BNAME NO FLUSH.
CALL METHOD OF EXCEL ’RANGE’ = CELL NO FLUSH EXPORTING #1 = ’B2’ .
SET PROPERTY OF CELL ’VALUE’ = USERS-NAME1 NO FLUSH.
ENDLOOP.
* release and exit Excel.
CALL METHOD OF EXCEL ’SAVE’.
CALL METHOD OF EXCEL ’QUIT’.
* Free all objects
FREE OBJECT CELL.
FREE OBJECT WORKBOOK.
FREE OBJECT EXCEL.
EXCEL-HANDLE = -1.
FREE OBJECT ROW.
*********************************************到此结束
下面是多个workbook多worksheet的例子,一个workbook多个sheet自己搞定吧..
PROGRAM ZS00_EXCEL_OLE_TEST .
parameters: p_fname like RLGRAP-FILENAME
default ’C:/test.xls’.
data: fname like p_fname,
kn like sy-repid.
data: cnt type i value 0.
data: sheetname(10) value ’TEST ’,c_row type i,
scnt type i,
val(20), wb(2).
CONSTANTS: OK TYPE I value 0.
INCLUDE OLE2INCL.
DATA: EXCEL TYPE OLE2_OBJECT,
WORKBOOK TYPE OLE2_OBJECT,
worksheet TYPE OLE2_OBJECT,
SHEET TYPE OLE2_OBJECT,
SHEET2 TYPE OLE2_OBJECT, "the 2th sheets
CELL TYPE OLE2_OBJECT,
CELL1 TYPE OLE2_OBJECT,
COLUMN TYPE OLE2_OBJECT,
RANGE TYPE OLE2_OBJECT,
BORDERS TYPE OLE2_OBJECT,
button TYPE OLE2_OBJECT,
int TYPE OLE2_OBJECT,
FONT TYPE OLE2_OBJECT,
ROW TYPE OLE2_OBJECT.
data: application type ole2_object,
book type ole2_object,
books type ole2_object.
data: ole_book TYPE ole2_object.
perform EXCELTEST.
*---------------------------------------------------------------------*
* form create_excel *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
form EXCELTEST.
data :sheetnum type c value ’1’ ,
sheetname(10) type c .
CREATE OBJECT EXCEL ’EXCEL.APPLICATION’.
if sy-subrc ne 0.
write: / ’No EXCEL creation possible’.
stop.
endif.
set property of EXCEL ’DisplayAlerts’ = 0.
CALL METHOD OF EXCEL ’WORKBOOKS’ = WORKBOOK .
* Put Excel in background
SET PROPERTY OF EXCEL ’VISIBLE’ = 1.
* Create worksheet
set property of excel ’SheetsInNewWorkbook’ = 1.
call method of workbook ’ADD’.
** adding 2 excel sheets .
do 2 times.
CALL METHOD OF excel ’sheets’ = worksheet .
call method of worksheet ’Add’ .
enddo.
CALL METHOD OF EXCEL ’Worksheets’ = SHEET
EXPORTING #1 = 1.
call method of sheet ’Activate ’.
CaLL METHOD OF EXCEL ’CELLS’ = CELL EXPORTING #1 = 2 #2 = 2 .
Set PROPERTY OF CELL ’value’ =
’--操作多个excel的破程序 by stone.fu on 2004/12/13---’ .
CALL METHOD OF EXCEL ’Range’ = range EXPORTING #1 = ’B2’ #2 =
’G2’.
call method of range ’select ’.
Set PROPERTY OF range ’MergeCells’ = 1 .
Set PROPERTY OF range ’HorizontalAlignment’ = -4108 .
Set PROPERTY OF range ’VerticalAlignment’ = -4108 .
perform fill_cell using ’20’ ’1’.
perform font using 1 ’16’.
CALL METHOD OF EXCEL ’RANGE’ = cell EXPORTING #1 = ’B2’ #2 =
’G2’.
perform border using ’1’ ’2’.
sheetnum = 1 .
do 2 times.
**** Copy Data
CALL METHOD OF excel ’Worksheets’ = SHEET
EXPORTING #1 = 1.
CALL METHOD OF sheet ’Range’ = range EXPORTING #1 = ’B2’ #2 =
’G2’.
call method of range ’select ’.
call method of sheet ’copy’.
sheetnum = sheetnum + 1 .
condense sheetnum .
*** Paste data
CALL METHOD OF excel ’Worksheets’ = SHEET
EXPORTING #1 = sheetnum .
CALL METHOD OF SHEET ’RANGE’ = range EXPORTING #1 = ’B2’ .
call method of range ’select ’.
call method of sheet ’PASTE ’.
enddo.
* Free sheet.
* free range .
*
endform.
*---------------------------------------------------------------------*
* form fill_cell *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
* --> COLOR *
* --> PATTERN *
*---------------------------------------------------------------------*
form fill_cell using color pattern.
call method of cell ’INTERIOR’ = int.
set property of int ’ColorIndex’ = color.
set property of int ’Pattern’ = pattern.
free object int.
endform.
*---------------------------------------------------------------------*
* form font *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
* --> BOLD *
* --> SIZE *
*---------------------------------------------------------------------*
form font using bold size.
call method of CELL ’FONT’ = font.
set property of font ’BOLD’ = bold.
set property of font ’SIZE’ = size.
free object font.
endform.
*---------------------------------------------------------------------*
* form border *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
* --> PLINEstyle *
* --> PWEIGHT *
*---------------------------------------------------------------------*
form border using plinestyle pweight .
*left
call method of CELL ’BORDERS’ = BORDERS exporting #1 = ’1’.
set property of borders ’Linestyle’ = plinestyle .
set property of borders ’Weight’ = pweight .
free object borders.
** right
call method of CELL ’BORDERS’ = BORDERS exporting #1 = ’2’.
set property of borders ’Linestyle’ = plinestyle .
set property of borders ’Weight’ = pweight .
free object borders.
* top
call method of CELL ’BORDERS’ = BORDERS exporting #1 = ’3’.
set property of borders ’Linestyle’ = plinestyle .
set property of borders ’Weight’ = pweight .
free object borders.
* bottom
call method of CELL ’BORDERS’ = BORDERS exporting #1 = ’4’.
set property of borders ’Linestyle’ = plinestyle .
set property of borders ’Weight’ = pweight .
free object borders.
endform.
**********************************************************
**************************************************************************
先在 Excel中录制宏，然后去看code , 再在ABAP中对着写就可以，VB使用OLE特别方便几乎将 VBAcopy过来就可以使用，但是ABAP中就要麻烦点。
只要注意CaLL METHOD OF(方法) 和set property of (属性),方法and 属性是OOD最基本的概念.
一句话,只要不怕麻烦,ABAP是可完全操纵excel就象你直接使用excel 一样.下面code是你所说的将一些cell合并居中加框字体的程序.
PROGRAM ZS00_EXCEL_OLE_TEST .*
parameters: p_fname like RLGRAP-FILENAME
default ’C:/test.xls’.
data: fname like p_fname,
kn like sy-repid.
data: cnt type i value 0.
data: sheetname(10) value ’TEST ’,c_row type i,
scnt type i,
val(20), wb(2).
parameters: p_exvis as checkbox default ’X’,
p_workbk(2) type p default ’01’,
p_wsheet(2) type p default ’01’.
CONSTANTS: OK TYPE I value 0.
INCLUDE OLE2INCL.
DATA: EXCEL TYPE OLE2_OBJECT,
WORKBOOK TYPE OLE2_OBJECT,
SHEET TYPE OLE2_OBJECT,
CELL TYPE OLE2_OBJECT,
CELL1 TYPE OLE2_OBJECT,
COLUMN TYPE OLE2_OBJECT,
RANGE TYPE OLE2_OBJECT,
BORDERS TYPE OLE2_OBJECT,
button TYPE OLE2_OBJECT,
int TYPE OLE2_OBJECT,
FONT TYPE OLE2_OBJECT,
ROW TYPE OLE2_OBJECT.
data: application type ole2_object,
book type ole2_object,
books type ole2_object.
data: ole_book TYPE ole2_object.
perform EXCELTEST.
*---------------------------------------------------------------------*
* form create_excel *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
form EXCELTEST.
CREATE OBJECT EXCEL ’EXCEL.APPLICATION’.
if sy-subrc ne 0.
write: / ’No EXCEL creation possible’.
stop.
endif.
set property of EXCEL ’DisplayAlerts’ = 0.
CALL METHOD OF EXCEL ’WORKBOOKS’ = WORKBOOK .
* Put Excel in background
if p_exvis eq ’X’.
SET PROPERTY OF EXCEL ’VISIBLE’ = 1.
else.
SET PROPERTY OF EXCEL ’VISIBLE’ = 0.
endif.
* Create worksheet
set property of excel ’SheetsInNewWorkbook’ = 1.
call method of workbook ’ADD’.
CALL METHOD OF EXCEL ’Worksheets’ = SHEET
EXPORTING #1 = 1.
call method of sheet ’ACTIVATE’.
SET PROPERTY OF SHEET ’NAME’ = ’EXCELTEST’.
CaLL METHOD OF EXCEL ’CELLS’ = CELL EXPORTING #1 = 2 #2 = 2 .
Set PROPERTY OF CELL ’value’ = ’大宗物料资金计划’ .
CALL METHOD OF EXCEL ’Range’ = range EXPORTING #1 = ’B2’ #2 =
’G2’.
call method of range ’select ’.
Set PROPERTY OF range ’MergeCells’ = 1 .
Set PROPERTY OF range ’HorizontalAlignment’ = -4108 .
Set PROPERTY OF range ’VerticalAlignment’ = -4108 .
perform fill_cell using ’20’ ’1’.
perform font using 1 ’16’.
CALL METHOD OF EXCEL ’RANGE’ = cell EXPORTING #1 = ’B2’ #2 =
’G2’.
perform border using ’1’ ’2’.
* Free sheet.
* free range .
*
endform.
form fill_cell using color pattern.
call method of cell ’INTERIOR’ = int.
set property of int ’ColorIndex’ = color.
set property of int ’Pattern’ = pattern.
free object int.
endform.
form font using bold size.
call method of CELL ’FONT’ = font.
set property of font ’BOLD’ = bold.
set property of font ’SIZE’ = size.
free object font.
endform.
form border using plinestyle pweight .
*left
call method of CELL ’BORDERS’ = BORDERS exporting #1 = ’1’.
set property of borders ’Linestyle’ = plinestyle .
set property of borders ’Weight’ = pweight .
free object borders.
** right
call method of CELL ’BORDERS’ = BORDERS exporting #1 = ’2’.
set property of borders ’Linestyle’ = plinestyle .
set property of borders ’Weight’ = pweight .
free object borders.
* top
call method of CELL ’BORDERS’ = BORDERS exporting #1 = ’3’.
set property of borders ’Linestyle’ = plinestyle .
set property of borders ’Weight’ = pweight .
free object borders.
* bottom
call method of CELL ’BORDERS’ = BORDERS exporting #1 = ’4’.
set property of borders ’Linestyle’ = plinestyle .
set property of borders ’Weight’ = pweight .
free object borders.
endform.
****************************合并单元格的做法
TYPE-POOLS ole2.
DATA excel TYPE ole2_object.
DATA workbook TYPE ole2_object.
DATA sheet TYPE ole2_object.
DATA: cell TYPE ole2_object,
range TYPE ole2_object.
DATA: font TYPE ole2_object.
CREATE OBJECT excel ’Excel.Application’.
SET PROPERTY OF excel ’Visible’ = 1.
CALL METHOD OF excel ’Workbooks’ = workbook.
CALL METHOD OF workbook ’Open’ EXPORTING #1 = ’C:/abc.XLS’.
CALL METHOD OF excel ’Worksheets’ = sheet EXPORTING #1 = ’Sheet1’.
* 合并单元格 B1 , B2
CALL METHOD OF sheet ’Range’ = range
EXPORTING #1 = ’B1’ #2 = ’B2’.
SET PROPERTY OF range ’MergeCells’ = 1.
* 控制字体的大小
CALL METHOD OF sheet ’CELLS’ = cell
EXPORTING #1 = 2 #2 = 3. "2行3列, 即 C2
CALL METHOD OF cell ’FONT’ = font.
SET PROPERTY OF font
’Size’ = ’24’.
CALL METHOD OF excel ’QUIT’.
FREE OBJECT font.
FREE OBJECT range.
FREE OBJECT cell.
FREE OBJECT sheet.
FREE OBJECT workbook.
FREE OBJECT excel.
- SAP数据按格式保存到EXCEL(OLE)
- [转帖]ABAP实践学习--ABAP中将SAP数据按格式保存到EXCEL(OLE)
- [转帖]ABAP实践学习--ABAP中将SAP数据按格式保存到EXCEL(OLE)
- SAP ABAP OLE 输出数据到 Excel 无法自动保存的解决
- SAP ABAP OLE 输出数据到 Excel 无法自动保存的解决
- 內表数据导出到EXCEL-OLE示例（带格式导出）
- 数据保存到Excel
- Perl OLE Excel Date格式的数据操作
- 保存DataGridView数据到excel
- Unity3D -- 保存数据到excel
- 通过OLE将ListView数据导出到Excel
- 使用OLE技术将数据下载到EXCEL
- 从数据库中导出数据以excel格式保存到本地
- SAP EXCEL OLE常用方法和属性
- SAP EXCEL OLE常用方法和属性
- SAP ABAP OLE 操作EXCEL小结
- SAP ABAP OLE 操作EXCEL小结
- 把dataset数据保存到excel,GridView导出到excel
- 转帖：面向程序员的数据库访问性能优化法则特别说明：
- DLL+ ActiveX控件+WEB页面调用例子
- 交友类网站设计
- 如何使用DataTable.Select选出来的Rows生成新的DataTable?
- 三层
- SAP数据按格式保存到EXCEL(OLE)
- Linux Shell 作业控制命令
- 程序员成长之双向循环链表
- 二进制数转换为十进制数
- 使用STL容器构成一棵树
- SWFObject参数
- C# Thread类
- QT 设置环境变量
- MS SQL 锁的理解
