*&---------------------------------------------------------------------* *& Report ZCESHI_22 *& *&---------------------------------------------------------------------* *& *& *&---------------------------------------------------------------------* REPORT ZCESHI_22. *report ZLUPING05 * no standard page heading line-size 255. *&---------------------------------------------------------------------* *& 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 WITH HEADER LINE, 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'. DATA: l_fname LIKE rlgrap-filename. *&---------------------------------------------------------------------* *& 选择屏幕 *&---------------------------------------------------------------------* PARAMETERS: P_BUKRS TYPE BKPF-BUKRS , "公司代码 P_GJAHR TYPE BKPF-GJAHR . "会计年度 *&---------------------------------------------------------------------* *& START-OF-SELECTION *&---------------------------------------------------------------------* START-OF-SELECTION. * 取得数据 PERFORM FRM_GET_BKPF_DATA. *-从SAP服务器上下载模板（要先通过SMWO上传） PERFORM FRM_DOWN_TEMPLATE CHANGING l_fname. *-向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. Td_BKPF-bukrs = '1000'. Td_BKPF-belnr = '188888888'. APPEND TD_BKPF. ENDFORM. " FRM_GET_BKPF_DATA *&---------------------------------------------------------------------* *& Form FRM_DOWN_TEMPLATE *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* FORM FRM_DOWN_TEMPLATE CHANGING fname. DATA: LW_SUBRC TYPE SY-SUBRC. * 显示进度 CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING PERCENTAGE = 0 TEXT = 'Download the template!'. DATA: l_objdata LIKE wwwdatatab, l_destination LIKE rlgrap-filename, l_rc LIKE sy-subrc, l_errtxt TYPE string. SELECT SINGLE relid objid FROM wwwdata INTO CORRESPONDING FIELDS OF l_objdata WHERE srtf2 = 0 AND relid = 'MI' AND objid = 'ZZCFZB02'."'ZHJ88'.'ZZCFZB01' "此处为EXCEL模板名称 * 检查表wwwdata中是否存在所指定的模板文件 IF sy-subrc NE 0 OR l_objdata-objid = space."如果不存在，则给出错误提示 CONCATENATE '保函交接模板文件' '不存在' INTO l_errtxt. MESSAGE l_errtxt TYPE 'E'. ENDIF. l_destination = 'C:\保函交接单.XLS'. ** 下载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. DATA: l_fname LIKE rlgrap-filename, l_filename TYPE string, l_icount TYPE i, l_irow TYPE i. CALL FUNCTION 'DOWNLOAD_WEB_OBJECT' EXPORTING key = l_objdata destination = l_destination * KEY = CNS_DOWN_KEY * DESTINATION = CNS_DOWN_PATH IMPORTING rc = l_rc. IF l_rc NE 0. CONCATENATE '保函交接单模板文件：' l_destination '下载失败' INTO l_errtxt. MESSAGE l_errtxt TYPE 'E'. ENDIF. IF LW_SUBRC <> 0. * MESSAGE 'DOWNLOAD TEMPLATE FALL!' TYPE 'S' DISPLAY LIKE 'E'. LEAVE LIST-PROCESSING. ENDIF. fname = l_destination. 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 = l_fname."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 = '6'. LOOP AT TD_BKPF. "INTO TH_BKPF. LW_H = 'B'. PERFORM FRM_FILL_CELL USING LW_H LW_S Td_BKPF-BUKRS. LW_H = 'C'. PERFORM FRM_FILL_CELL USING LW_H LW_S Td_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