前言
SAP支持使用系统功能导出excel文件,但如果要求以用户的模板来导出对应的文件,就需要另寻出路。这篇博文主要讨论一下OLE这门技术在SAP导出EXCEL方面的效果。
实现步骤
使用 Tcode:SMW0 命令,将excel模板文件上传到服务器后继续下面的步骤
1. 公用参数定义
data: application type ole2_object,
workbook type ole2_object,
sheet type ole2_object,
cell type ole2_object.
data: l_fullname type string.
2. 选择文件保存路径
form frm_select_path changing p_fullname type string.
data: l_filename type string,
l_path type string.
clear: l_filename, l_path, p_fullname.
"设置默认文件名称
l_filename = '测试文件.xls'.
call method cl_gui_frontend_services=>file_save_dialog
exporting
default_extension = 'XLS'
default_file_name = l_filename
changing
filename = l_filename
path = l_path
fullpath = p_fullname
exceptions
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
others = 4.
if sy-subrc <> 0 .
message s001(00) display like 'E'.
endif.
if p_fullname = space.
"错误逻辑处理
endif.
endform.
3. 打开excel模板
form frm_create_excel using p_fullname type string.
create object application 'Excel.Application'. "创建excel对象
if sy-subrc ne 0.
message '不能创建Excel对象' type 'E'.
exit.
endif.
set property of application 'VISIBLE' = 0. "设置excel可视化属性 0不可见 1可见
call method of application 'WORKBOOKS' = workbook. "激活工作簿
"按照指定路径打开工作簿
call method of workbook 'OPEN' = workbook
exporting
#1 = p_fullname.
"设置工作表
call method of application 'WORKSHEETS' = sheet
exporting
#1 = 1.
call method of sheet 'SELECT'.
"激活工作表
call method of sheet 'ACTIVATE'.
endform.
4.填充数据
以下为举例操作。正式使用时,当模板中需要填写的字段比较多,通过以下的方式进行对应数据填写,速度会极其缓慢,内表数据仅有五十几条在博主的电脑上就会达到两分钟左右,应考虑优化
form frm_fill_data.
data: l_row type i.
"模板有表头,所以不是从第一行开始填写
l_row = 3.
loop at it_tab into data(is_tab).
perform fill_cell using l_row 1 is_tab-matnr.
perform fill_cell using l_row 2 is_tab-maktx.
perform fill_cell using l_row 3 is_tab-meins.
perform fill_cell using l_row 4 is_tab-matkl.
perform fill_cell using l_row 5 is_tab-bismt.
add 1 to l_row.
clear is_tab.
endloop.
endform.
form fill_cell using p_row p_col p_value.
call method of application 'CELLS' = cell
exporting
#1 = p_row
#2 = p_col.
set property of cell 'VALUE' = p_value.
endform.
5.保存excel
form frm_excel_save.
get property of application 'ActiveSheet' = sheet.
get property of application 'ActiveWorkbook' = workbook.
call method of workbook 'SAVE'.
set property of application 'VISIBLE' = 1.
call method of workbook 'OPEN'.
"或者选择直接关闭
"set property of application 'VISIBLE' = 0.
"call method of workbook 'CLOSE'.
"call method of application 'QUIT'.
free object sheet.
free object workbook.
free object application.
endform.
总结
对于按照用户模板导出excel的需求,使用以上的方式就可以实现,但一些过程的耗时过长,应考虑优化。