使用Excel模板进行报表的开发.
今年搞的Excel比较多,总结了一下,相信常用的操作包含的差不多了。
可以首先定义一个无内容的Excel报表模板文件.
通过Tcode SMW0 上传至SAP数据库中备用.(注: Web对象应该选择’WebRFC 应用程序的二进制数据’)
开发程序: 在程序中需要首先导入下面两项. INCLUDE ole2incl. “ 此项根据需要导入不同的对象类 INCLUDE officeintegrationinclude.
在INITIALIZATION 中建立文件对象与链接服务器. 参考http://help.sap.com/saphelp_40b/helpdata/en/e9/0be7ed408e11d1893b0000e8323c4f/content.htm 选择需要的报表数据. 检查目标文件是否已经存在,若存在将其删除. 使用FUNCTION 'SAP_OI_LOAD_MIME_DATA' 从SAP数据库中得到报表模板数据. 使用METHOD factory->get_document_proxy创建文档实例. 使用METHOD document->play_document_from_table 用报表模板数据填入文档内容. 使用METHOD document->save_copy_as 将创建的文档另存为本地文件. 关闭释放文档对象: METHOD document->is_destroyed METHOD document->close_document METHOD document->release_document FREE document. 关闭释放连接服务器: METHOD link_server->stop_link_server FREE link_server. 关闭释放代理对象: METHOD factory->stop_factory FREE factory. 下面开始处理保存到本地的报表模板,向其填写具体数据内容. CREATE OBJECT h_excel 'EXCEL.APPLICATION'. “ 新建OLE对象 SET PROPERTY OF h_excel 'Visible' = 0. “ 定义其不可见 CALL METHOD OF h_excel 'Workbooks' = h_mapl. “ 得到活动excel对象 CALL METHOD OF h_mapl 'Open' “ 打开此活动excel EXPORTING #1 = p_file. “ 本地模板文件路径 CALL METHOD OF h_excel 'WORKSHEETS' = H_SHEET. “ 得到活动的worksheet 如果需要生成多张报表则需要建立多个Sheet . 首先判断相应名字的sheet是否已经存在: GET PROPERTY OF WORKSHEETS 'COUNT' = SHEETCOUNT. “ 得到sheet数量 DO SHEETCOUNT TIMES. “ 循环判断sheetname是否已经存在,若已经存在则不再创建 I = I + 1. CALL METHOD OF EXCEL 'WORKSHEETS' = TMPSHEET EXPORTING #1 = I. GET PROPERTY OF TMPSHEET 'NAME' = TMPNAME. IF TMPNAME = SHEETNAME. EXISTFLAG = 1. EXIT. ENDIF. ENDDO. 若sheetname不存在则创建 IF EXISTFLAG = 0. CALL METHOD OF EXCEL 'WORKSHEETS' = MODELSHEET “ 第一个sheet EXPORTING #1 = 'Sheet1'. * EXPORTING #1 = '模板'. PERFORM ERR_HDL. CALL METHOD OF EXCEL 'WORKSHEETS' = TMPSHEET “ 最后一个sheet EXPORTING #1 = SHEETCOUNT. PERFORM ERR_HDL. CALL METHOD OF MODELSHEET 'COPY' “ Copy一个新的sheet EXPORTING #1 = TMPSHEET. PERFORM ERR_HDL. GET PROPERTY OF WORKSHEETS 'COUNT' = NEWSHEETCOUNT. “ 重新得到Sheet数量 PERFORM ERR_HDL. IF NEWSHEETCOUNT > SHEETCOUNT. “ 判断是否创建sheet成功 CALL METHOD OF EXCEL 'WORKSHEETS' = NEWSHEET “ 如果创建成功则改Sheet的名字 EXPORTING #1 = SHEETCOUNT. PERFORM ERR_HDL. SET PROPERTY OF NEWSHEET 'NAME' = SHEETNAME. “注:此处修改的是倒数第二个sheet PERFORM ERR_HDL. * SET PROPERTY OF NEWSHEET 'SCENARIOS' = 0. PERFORM ERR_HDL. ENDIF. ENDIF. 将所有的sheet创建完毕后开始逐一向每个sheet添加报表内容. CALL METHOD OF h_excel 'WORKSHEETS' = h_sheet EXPORTING #1 = 'Sheet1'. “ 首先将第一个sheet也改名字
CALL METHOD OF h_sheet 'ACTIVATE'. CALL METHOD OF h_excel 'ACTIVEWINDOW' = activewindow. “ 得到活动窗口对象 SET PROPERTY OF activewindow 'DISPLAYGRIDLINES' = 0. “ 隐藏格线(虚线) SET PROPERTY OF h_sheet 'NAME' = sheet_name. “ 修改sheet name
LOOP AT TAB_ALL. CLEAR sheet_name. sheet_name+0(10) = TAB_ALL-NAME. sheet_name+10(1) = '-'. sheet_name+11(8) = TAB_ALL-pernr. CALL METHOD OF h_excel 'WORKSHEETS' = h_sheet EXPORTING #1 = sheet_name. CALL METHOD OF h_sheet 'ACTIVATE'. “ 逐个sheet激活 PERFORM fill_cell USING 2 3 1 tab_all-name. “ 向各个字段添加数值 PERFORM fill_cell USING 2 5 1 tab_all-xb. “ 向各个字段添加数值 … … … … … … … … ENDLOOP. SET PROPERTY OF h_excel 'Visible' = 1. “ 取消隐藏
CALL METHOD OF h_sheet 'CLOSE'. “ 关闭 FREE OBJECT h_excel. “ 释放 FREE OBJECT h_mapl. “ 释放 FREE OBJECT h_sheet. “ 释放 FREE OBJECT activewindow.
调用宏(带参数) CALL METHOD OF excel 'RUN' EXPORTING #1 = 'ZMACRO1' #2 = param1. “ 带参数
根据列数(1,2,3… …)换算Excel列名(a,b,c… …) CALL FUNCTION 'ZHRIS_GET_EXCEL_COLUMN' EXPORTING p_column = l_int IMPORTING f_column = col. FUNCTION ZHRIS_GET_EXCEL_COLUMN. *"---------------------------------------------------------------------- *"*"Local interface: *" IMPORTING *" REFERENCE(P_COLUMN) TYPE I *" EXPORTING *" REFERENCE(F_COLUMN) TYPE C *"----------------------------------------------------------------------
data : l_col type string . data : l_cyc type i . data : l_mod type i . data : c1, c2 .
l_col = 'abcdefghijklmnopqrstuvwxyz' .
if p_column >= 1 . l_cyc = p_column div 26 . l_mod = p_column mod 26 .
if l_cyc > 1 . * l_cyc = l_cyc - 1 . endif. if l_mod > 1 . l_mod = l_mod - 1 . endif.
if l_mod = 0 . l_cyc = l_cyc - 1 . l_mod = 25. endif.
if l_cyc >= 1 . l_cyc = l_cyc - 1 . c1 = l_col+l_cyc(1). endif. if l_mod >= 1 . if l_mod = 1 . l_mod = l_mod - 1 . endif. c2 = l_col+l_mod(1). endif.
concatenate c1 c2 into F_COLUMN . condense F_COLUMN no-gaps . endif.
ENDFUNCTION.
选择Excel中某个区域
CALL METHOD OF h_sheet 'range' = range EXPORTING #1 = 'a3' #2 = 'b10'. CALL METHOD OF range 'Select' NO FLUSH. “ 选择 GET PROPERTY OF range 'borders' = h_borders no flush. “ 加边框 * SET PROPERTY OF h_borders 'weight' = '2' no flush. SET PROPERTY OF h_borders 'linestyle' = '1' no flush. “ 框线格式 CALL METHOD OF excel 'Columns' = column “ 选定列 EXPORTING #1 = 1. SET PROPERTY OF column 'ColumnWidth' = 3. “ 定义列宽 SET PROPERTY OF column 'rowheight' = 30. “ 定义行高 CALL METHOD OF range 'ClearContents'. “ 清空内容 SET PROPERTY OF range 'MergeCells' = 1. “ 合并单元格 SET PROPERTY OF range 'HorizontalAlignment' = 3. “ 对齐方式-纵向 SET PROPERTY OF range 'ShrinkToFit' = 0 . “ 取消自动缩小字体 CALL METHOD OF h_mapl 'SAVEAS' “ 保存Excel EXPORTING #1 = 'C:/工资明细表.xls' #2 = 1. FREE OBJECT excel. FREE OBJECT h_sheet.
*********** 调整Sheet 之间的顺序 ***************** sheet_name+0(9) = '通知书'. sheet_name+9(1) = '-'. sheet_name+10(2) = '01'.
CALL METHOD OF h_excel 'WORKSHEETS' = h_sheet EXPORTING #1 = sheet_name.
sheet_name1+0(9) = '通知书'. sheet_name1+9(1) = '-'. sheet_name1+10(2) = '02'.
CALL METHOD OF h_excel 'WORKSHEETS' = h_move EXPORTING #1 = sheet_name1.
CALL METHOD OF h_sheet 'Move' EXPORTING #1 = h_move. *********** 调整Sheet 之间的顺序 ***************** |
OLE 操作Excel 祥解
最新推荐文章于 2024-09-10 14:15:01 发布