SAP: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
2012-10-11 08:33:41 黄健
TYPE-POOLS: ole2 . 1. SMW0 上传Excel模板 2. 在程序中调用 变量声明： DATA: l_excel_obj TYPE ole2_object, l_workbook_obj TYPE ole2_object, l_sheet_obj TYPE ole2_object. DATA: l_fname LIKE rlgrap-filename, l_filename TYPE string, l_icount TYPE i, l_irow TYPE i. 2.2 取得模板文件和路径 PERFORM temp_excel_get CHANGING l_fname. FORM temp_excel_get CHANGING fname. 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 = 'ZP001MMR019'. "此处为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'. * 如果存在，调用DOWNLOAD_WEB_OBJECT 函数下载模板到路径下 CALL FUNCTION 'DOWNLOAD_WEB_OBJECT' EXPORTING key = l_objdata destination = l_destination IMPORTING rc = l_rc. IF l_rc NE 0. CONCATENATE '保函交接单模板文件：' l_destination '下载失败' INTO l_errtxt. MESSAGE l_errtxt TYPE 'E'. ENDIF. fname = l_destination. ENDFORM. " TEMP_EXCEL_GET 2.3 以模板生成文件 PERFORM open_excel_file USING l_excel_obj l_workbook_obj l_sheet_obj l_fname '1' . FORM open_excel_file USING l_excel l_workbook l_sheet l_filename l_visible. CREATE OBJECT l_excel 'Excel.Application'. IF sy-subrc NE 0. MESSAGE e796(f9) WITH '不能创建Excel对象'. ENDIF. CALL METHOD OF l_excel 'Workbooks' = l_workbook. CALL METHOD OF l_workbook 'Open' = l_workbook EXPORTING #1 = l_filename. IF sy-subrc NE 0. MESSAGE e796(f9) WITH '打开文件错误'. ENDIF. SET PROPERTY OF l_excel 'Visible' = l_visible. CALL METHOD OF l_workbook 'Sheets' = l_sheet EXPORTING #1 = 1. ENDFORM. " OPEN_EXCEL_FILE 2.4 插入表格行数 计算内表的行数 DESCRIBE TABLE gt_out LINES l_icount. l_icount = l_icount - 1. 复制空行数 PERFORM excel_row_insert USING l_sheet_obj '4' l_icount. FORM excel_row_insert USING l_sheet l_row l_count. DATA l_range TYPE ole2_object. DO l_count TIMES. CALL METHOD OF l_sheet 'Rows' = l_range EXPORTING #1 = l_row. CALL METHOD OF l_range 'Copy'. CALL METHOD OF l_sheet 'Rows' = l_range EXPORTING #1 = l_row. CALL METHOD OF l_range 'Insert'. CALL METHOD OF l_range 'ClearContents'. "是否需要清空Cell ENDDO. ENDFORM. " EXCEL_ROW_INSERT 2.5 写值到表格中 l_irow = 3. LOOP AT gt_out. l_irow = l_irow + 1. PERFORM write_item USING l_excel_obj l_irow. ENDLOOP. FORM write_item USING l_excel_obj l_row. DATA: l_count TYPE i. l_count = l_row - 3. PERFORM excel_cell_write USING l_excel_obj l_row 'A' l_count. PERFORM excel_cell_write USING l_excel_obj l_row 'B' gt_out-bhno. ENDFORM. " WRITE_ITEM FORM excel_cell_write USING l_excel l_row l_col l_value. DATA: l_cell TYPE ole2_object. CALL METHOD OF l_excel 'Cells' = l_cell EXPORTING #1 = l_row #2 = l_col. SET PROPERTY OF l_cell 'Value' = l_value. ENDFORM. " EXCEL_CELL_WRITE 3. 拓展，多个Sheet 3.1 在Excel中录制宏 宏名称：SheetCopy 宏代码： Sub SheetCopy() ' ' SheetCopy Macro Sheets("Sheet1").Select Sheets("Sheet1").Copy After:=Sheets(1) End Sub 3.2 创建Sheet个数 DATA: l_excel_obj TYPE ole2_object, l_workbook_obj TYPE ole2_object, l_sheet_obj TYPE ole2_object. DATA: l_sheet_name TYPE string, l_sheetno TYPE i, l_sheetcnt TYPE i. PERFORM create_sheets USING l_excel_obj l_sheetcnt. FORM create_sheets USING l_excel_obj l_hcount. DO l_hcount TIMES. CALL METHOD OF l_excel_obj 'RUN' EXPORTING #1 = 'SheetCopy'. ENDDO. ENDFORM. " CREATE_SHEETS 3.3 修改Sheet名称 PERFORM change_sheetname USING l_workbook_obj l_sheet_obj l_sheet_name l_sheetno. FORM change_sheetname USING l_workbook_obj l_sheet_obj l_sheet_name l_sheetno. CALL METHOD OF l_workbook_obj 'Sheets' = l_sheet_obj EXPORTING #1 = ls_sheetno. SET PROPERTY OF l_sheet_obj 'Name' = l_sheet_name. CALL METHOD OF l_sheet_obj 'Activate' . ENDFORM. " CHANGE_SHEETNAME
