*&---------------------------------------------------------------------*
*& 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