【ABAP】OLE2的使用 操作EXCEL把内表中的数据在EXCEL中显示出来

*&---------------------------------------------------------------------*
*& Report ZTEST_OLE_EXCEL
*&
*&---------------------------------------------------------------------*
*& 测试OLE EXCEL
*&---------------------------------------------------------------------*
REPORT ZTEST_OLE_EXCEL
MESSAGE-ID ZTEST.
*&---------------------------------------------------------------------*
*& TYPE-POOLS
*&---------------------------------------------------------------------*
TYPE-POOLS:
OLE2. "或者：INCLUDE OLE2INCL
*&---------------------------------------------------------------------*
*& 定义数据
*&---------------------------------------------------------------------*
DATA:
TD_BKPF TYPE TABLE OF BKPF,
TH_BKPF TYPE BKPF.
DATA:
W_TMP_FILE(65) TYPE C.
* OLE2对象
DATA:
W_EXCEL TYPE OLE2_OBJECT, "EXCEL
W_BOOKS TYPE OLE2_OBJECT, "LIST OF WORKBOOKS
W_BOOK TYPE OLE2_OBJECT, "WORKBOOK
W_SHEET TYPE OLE2_OBJECT, "SHEET
W_CELL TYPE OLE2_OBJECT. "CELL OF SHEET
CONSTANTS:
* 前面一定要添加'MI'
CNS_DOWN_KEY TYPE WWWDATATAB VALUE 'MIZTSTOLE_EXCEL',
CNS_DOWN_PATH TYPE LOCALFILE VALUE 'C:\ZTST_TMP.XLT', "下载地址
* 部分字符串
CNS_VISIBLE TYPE CHAR32 VALUE 'VISIBLE',
CNS_WORKBOOKS TYPE CHAR32 VALUE 'WORKBOOKS',
CNS_OPEN TYPE CHAR32 VALUE 'OPEN',
CNS_WORKSHEETS TYPE CHAR32 VALUE 'WORKSHEETS',
CNS_ACTIVATE TYPE CHAR32 VALUE 'ACTIVATE',
CNS_RANGE TYPE CHAR32 VALUE 'RANGE',
CNS_VALUE TYPE CHAR32 VALUE 'VALUE'.
*&---------------------------------------------------------------------*
*& 选择屏幕
*&---------------------------------------------------------------------*
PARAMETERS:
P_BUKRS TYPE BKPF-BUKRS OBLIGATORY, "公司代码
P_GJAHR TYPE BKPF-GJAHR OBLIGATORY. "会计年度
*&---------------------------------------------------------------------*
*& START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.
* 取得数据
PERFORM FRM_GET_BKPF_DATA.
*-从SAP服务器上下载模板（要先通过SMWO上传）
PERFORM FRM_DOWN_TEMPLATE.
*-向EXCEL中填入数据，并显示出来
PERFORM FRM_EXPORT_DATA.
*&---------------------------------------------------------------------*
*& Form FRM_GET_BKPF_DATA
*&---------------------------------------------------------------------*
* 取得数据
*----------------------------------------------------------------------*
FORM FRM_GET_BKPF_DATA .
SELECT *
FROM BKPF
INTO TABLE TD_BKPF
WHERE BUKRS = P_BUKRS
AND GJAHR = P_GJAHR.
IF SY-SUBRC <> 0.
* MESSAGE S001 DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING.
ENDIF.
ENDFORM. " FRM_GET_BKPF_DATA
*&---------------------------------------------------------------------*
*& Form FRM_DOWN_TEMPLATE
*&---------------------------------------------------------------------*
* text
*-----------------------------------------

-----------------------------*
FORM FRM_DOWN_TEMPLATE .
DATA:
LW_SUBRC TYPE SY-SUBRC.
* 显示进度
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
PERCENTAGE = 0
TEXT = 'Download the template!'.
* 下载sap服务器上的RFC模板（html）
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
EXPORTING
KEY = CNS_DOWN_KEY
DESTINATION = CNS_DOWN_PATH
IMPORTING
RC = LW_SUBRC
CHANGING
TEMP = W_TMP_FILE.
IF LW_SUBRC <> 0.
* MESSAGE 'DOWNLOAD TEMPLATE FALL!' TYPE 'S' DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING.
ENDIF.
ENDFORM. " FRM_DOWN_TEMPLATE
*&---------------------------------------------------------------------*
*& Form FRM_EXPORT_DATA
*&---------------------------------------------------------------------*
* 向EXCEL中填入数据，并显示出来
*----------------------------------------------------------------------*
FORM FRM_EXPORT_DATA .
DATA:
LW_H TYPE CHAR2,
LW_S TYPE CHAR2.
* 创建EXCEL
CREATE OBJECT W_EXCEL 'EXCEL.APPLICATION'.
SET PROPERTY OF W_EXCEL CNS_VISIBLE = 0.
* 设置可见
* SET PROPERTY OF W_EXCEL CNS_VISIBLE = 1 NO FLUSH.
* 创建EXCEL的WORKSBOOKS
CALL METHOD OF W_EXCEL CNS_WORKBOOKS = W_BOOKS.
* 创建BOOK FOR WORKSBOOKS
CALL METHOD OF W_BOOKS CNS_OPEN = W_BOOK
EXPORTING
#1 = CNS_DOWN_PATH.
CALL METHOD OF W_BOOK CNS_WORKSHEETS = W_SHEET
EXPORTING
#1 = 'Sheet1'.
CALL METHOD OF W_SHEET CNS_ACTIVATE.
* 输出到EXCEL
LW_S = '4'.
LOOP AT TD_BKPF INTO TH_BKPF.
LW_H = 'A'.
PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-BUKRS.
LW_H = 'B'.
PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-BELNR.
LW_H = 'C'.
PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-GJAHR.
LW_H = 'D'.
PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-BLART.
LW_H = 'E'.
PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-BLDAT.
LW_H = 'F'.
PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-BUDAT.
LW_H = 'G'.
PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-MONAT.
LW_H = 'H'.
PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-CPUDT.
LW_H = 'I'.
PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-XBLNR.
LW_H = 'J'.
PERFORM FRM_FILL_CELL USING LW_H LW_S TH_BKPF-BKTXT.
LW_S = LW_S + 1.
ENDLOOP.
* 设置可见
SET PROPERTY OF W_EXCEL CNS_VISIBLE = 1 NO FLUSH.
FREE OBJECT:
W_EXCEL,
W_BOOKS,
W_BOOK,
W_SHEET,
W_CELL.
ENDFORM. " FRM_EXPORT_DATA
*&---------------------------------------------------------------------*
*& Form FRM_FILL_CELL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->i_H text
* -->i_S text
* -->i_value text
*---------------------------

-------------------------------------------*
FORM FRM_FILL_CELL USING VALUE(I_H)
VALUE(I_S)
VALUE(I_VALUE).
DATA:
LW_CELL TYPE CHAR4.
* 填充单元格（定位）
CONCATENATE I_H I_S INTO LW_CELL.
CALL METHOD OF W_SHEET CNS_RANGE = W_CELL
EXPORTING
#1 = LW_CELL.
* 插入值
SET PROPERTY OF W_CELL CNS_VALUE = I_VALUE.
ENDFORM. " FRM_FILL_CELL


网上还有两个例子，粘在这里
样例一、
REPORT ZSAPLINK_INSTALLER_TEMPLATE.
TABLES sscrfields.
TYPE-POOLS ole2.
DATA: excel TYPE ole2_object,
word TYPE ole2_object,
book TYPE ole2_object,
rc TYPE c LENGTH 8.
SELECTION-SCREEN:
BEGIN OF SCREEN 100 AS WINDOW TITLE title,
BEGIN OF LINE,
PUSHBUTTON 2(12) button_1
USER-COMMAND word_start,
PUSHBUTTON 20(12) button_2
USER-COMMAND excel_start,
END OF LINE,
BEGIN OF LINE,
PUSHBUTTON 2(12) button_3
USER-COMMAND word_stop,
PUSHBUTTON 20(12) button_4
USER-COMMAND excel_stop,
END OF LINE,
END OF SCREEN 100.
START-OF-SELECTION.
button_1 = 'Start Word'.
button_2 = 'Start Excel'.
button_3 = 'Stop Word'.
button_4 = 'Stop Excel'.
CALL SELECTION-SCREEN 100 STARTING AT 10 10.
AT SELECTION-SCREEN.
CASE sscrfields-ucomm.
WHEN 'WORD_START'.
CHECK word-handle <> -1.
CHECK word-header = space.
CREATE OBJECT word 'Word.Basic'.
CALL METHOD OF word 'AppShow'.
WHEN 'EXCEL_START'.
CHECK excel-handle = 0.
CHECK excel-header = space.
CREATE OBJECT excel 'Excel.Application'.
SET PROPERTY OF excel 'Visible' = 1.
GET PROPERTY OF excel 'Workbooks' = book.
CALL METHOD OF book 'Open' = rc
EXPORTING #1 = 'C:\temp\Table.xls'.
WHEN 'WORD_STOP'.
CALL METHOD OF word 'AppClose'.
FREE OBJECT word.
CLEAR: word-handle, word-header.
WHEN 'EXCEL_STOP'.
CALL METHOD OF excel 'Quit'.
FREE OBJECT excel.
CLEAR: excel-handle, excel-header.
WHEN OTHERS.
LEAVE PROGRAM.
ENDCASE.
样例二
REPORT ZEXCEL NO STANDARD PAGE HEADING.
INCLUDE OLE2INCL.
DATA: EXCEL_OBJECT TYPE OLE2_OBJECT, “ EXCEL OBJECT
WORKBOOK_LIST TYPE OLE2_OBJECT, “ LIST OF WORKBOOKS
WORKBOOK TYPE OLE2_OBJECT, “ WORKBOOK
CELL TYPE OLE2_OBJECT, “ CELL
FONT TYPE OLE2_OBJECT. “ FONT
TABLES: PA0002.
DATA ROW_NUMBER TYPE I.
DATA: IT_PA0002 LIKE PA0002 OCCURS 10 WITH HEADER LINE.
START-OF-SELECTION.
SELECT * FROM PA0002 INTO TABLE IT_PA0002 UP TO 10 ROWS.
CREATE OBJECT EXCEL_OBJECT ‘EXCEL.APPLICATION’.
SET PROPERTY OF EXCEL_OBJECT ‘VISIBLE’ = 1.
CALL METHOD OF EXCEL_OBJECT ‘WORKBOOKS’ = WORKBOOK_LIST.
CALL METHOD OF WORKBOOK_LIST ‘ADD’ = WORKBOOK.
* FILL THE ACTIVE SHEET WITH COLUMN HEADINGS
PERFORM FILL_CELL_WITH_DATA USING 1 1 1 ‘Employ

ee Number’.
PERFORM FILL_CELL_WITH_DATA USING 1 2 1 ‘First Name’.
PERFORM FILL_CELL_WITH_DATA USING 1 3 1 ‘Last Name’.
PERFORM FILL_CELL_WITH_DATA USING 1 4 1 ‘Date of Birth’.
PERFORM FILL_CELL_WITH_DATA USING 1 5 1 ‘Place of Birth’.
* FILL EMPLOYEE DATA TO ACTIVE EXCEL SHEET
LOOP AT IT_PA0002.
ROW_NUMBER = SY-TABIX + 1.
PERFORM FILL_CELL_WITH_DATA USING ROW_NUMBER 1 0 IT_PA0002-PERNR.
PERFORM FILL_CELL_WITH_DATA USING ROW_NUMBER 2 0 IT_PA0002-VORNA.
PERFORM FILL_CELL_WITH_DATA USING ROW_NUMBER 3 0 IT_PA0002-NACHN.
PERFORM FILL_CELL_WITH_DATA USING ROW_NUMBER 4 0 IT_PA0002-GBDAT.
PERFORM FILL_CELL_WITH_DATA USING ROW_NUMBER 5 0 IT_PA0002-GBORT.
ENDLOOP.
FREE OBJECT EXCEL_OBJECT.
FORM FILL_CELL_WITH_DATA USING ROW COLUMN BOLD VAL.
CALL METHOD OF EXCEL_OBJECT
‘CELLS’ = CELL
EXPORTING #1 = ROW
#2 = COLUMN.
SET PROPERTY OF CELL ‘VALUE’ = VAL.
GET PROPERTY OF CELL ‘FONT’ = FONT.
SET PROPERTY OF FONT ‘BOLD’ = BOLD.
ENDFORM.
使用OLE的程序框架模板
…………………………
INCLUDE OLE2INCL.
DATA: EXCEL_OBJECT TYPE OLE2_OBJECT,
WORKBOOK_LIST TYPE OLE2_OBJECT.
…………………………
CREATE OBJECT EXCEL_OBJECT ‘EXCEL.APPLICATION’.
CALL METHOD OF EXCEL_OBJECT ‘WORKBOOKS’ = WORKBOOK_LIST.
CALL METHOD OF WORKBOOK_LIST ‘ADD’ = WORKBOOK.
…………………………
…………………………
SET PROPERTY OF CELL ‘VALUE’ = VAL.
…………………………
…………………………
FREE OBJECT EXCEL_OBJECT.