使用OLE2对象（操使作EXCEL:把内表中的数据在EXCEL中显示出来）
来源：互联网 发布：js 混淆加密 编辑：程序博客网 时间：2024/06/11 08:59
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 SHEETCONSTANTS:* 前面一定要添加'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_GET2.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_FILE2.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_INSERT2.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_ITEMFORM 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_WRITE3. 拓展，多个Sheet3.1 在Excel中录制宏 宏名称：SheetCopy 宏代码： Sub SheetCopy()'' SheetCopy Macro Sheets("Sheet1").Select Sheets("Sheet1").Copy After:=Sheets(1)End Sub3.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
0 0
- 使用OLE2对象（操使作EXCEL:把内表中的数据在EXCEL中显示出来）
- 把Excel中的数据导入到数据库中并显示在页面
- 如何将从数据库中的数据（表的形式显示出来的）导入到Excel表格中？
- 把数据库中的数据读到EXCEL中
- 将Excel中的数据在JTable中显示
- VC6.0如何实现把excel中的数据读取后显示在listctrl列表上
- 把Excel表中的数据导入数据库
- VB把一个Excel中的部分数据Copy到另一个Excel表中
- 利用java操作Excel表格（把Excel中的数据导入数据库中）
- 将DataGridView中的数据导入Excel中,并显示Excel
- 把Excel中的数据导入gridView显示，再导入数据库
- 使用Excel中的插入对象功能在Excel中插入Word文档
- 把Excel表中数据导入数据库
- 使用POI把数据导出excel表
- 把excel中的数据批量导入到mysql数据中
- ABAP：把内表中的内容导出为excel文件
- 用Excel VBA连接SQL Server数据库并将表中的数据显示到Excel中
- 把Excel文件中的数据读入到DataGrid中
- ListView实现上拉加载更多
- HTML 简介及简单的html标签
- Labview+USB+FPGA数据采集系统之总结
- oracle 10g函数大全--分析函数
- nginx下配置FastDFS
- 使用OLE2对象（操使作EXCEL:把内表中的数据在EXCEL中显示出来）
- JAVA中最方便的Unicode转换方法
- ======两秒之内两次back就finish======
- php 数组排序
- 李刚java ee登陆报错
- 基于JavaEE&&移动平台的企业级房地产ERP采购系统
- 【Latex】Latex 支持中文
- tomcat7配置数据源
- sql之truncate 、delete与drop区别
