使用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
之间的顺序 *****************
加载中…
喜欢