*------------------------------------------------------------*
* Program ID/Name: ZFIRD_07
* Author's name: 易登科
* Date written: 2008-09-04
* Last update:
* Program title: EXCEL表格模版导入函数.
*-
* Date Userid Reason/Description of Change
* DESC:INCLUDES文件
*------------------------------------------------------------*
function z_co_xlsmdl.
*"----------------------------------------------------------------------
*"*"Local interface:
*" IMPORTING
*" REFERENCE(P_XLSADD) TYPE STRING
*" VALUE(P_SAVEAS) TYPE STRING OPTIONAL
*" VALUE(P_CLOSE) TYPE I OPTIONAL
*" EXPORTING
*" REFERENCE(P_STATUS) TYPE C
*" TABLES
*" T_ITEM STRUCTURE ZCO_XLSMDL
*"----------------------------------------------------------------------
* ZCO_XLSMDL表的结构如下:
*=================以下为子过程=============================================
include ole2incl.
data:
l_oexcel type ole2_object,
l_osheet type ole2_object,
l_ocell type ole2_object,
l_orow type ole2_object,
l_oworkbook type ole2_object,
l_msg type string.
*初始化
p_status = 0.
if p_xlsadd is initial.
p_status = -1.
message e888(sabapdocu) with 'ZCO_XLSMDL函数所传入的XLS文件地址参数(P_XLSADD)为空,EXCEL表格数据写入失败!'.
endif.
if p_saveas is initial.
p_saveas = p_xlsadd.
endif.
* 创建excel进程
create object l_oexcel 'EXCEL.APPLICATION'(001).
set property of l_oexcel 'Visible' = 1.
call method of l_oexcel 'WORKBOOKS' = l_oworkbook.
call method of l_oworkbook 'Open'
exporting
#1 = p_xlsadd.
get property of l_oexcel 'ACTIVESHEET' = l_osheet.
get property of l_oexcel 'ACTIVEWORKBOOK' = l_oworkbook.
* Break-Point.
if p_xlsadd <> p_saveas.
get property of l_oexcel 'ACTIVESHEET' = l_osheet.
get property of l_oexcel 'ACTIVEWORKBOOK' = l_oworkbook.
call method of l_oworkbook 'SAVEas'
exporting
#1 = p_saveas
#2 = 1.
endif.
data: l_nrec type i.
data: l_nline type i
,l_nrow type i
,l_cvalue type string
,l_nsheet type i
,l_nfmttype type i
.
describe table t_item lines l_nrec.
if l_nrec is initial.
p_status = -2.
message e888(sabapdocu) with 'ZCO_XLSMDL函数所传入的内表无记录,EXCEL表格数据写入失败!'.
endif.
loop at t_item.
l_nfmttype = t_item-nfmttype.
l_nsheet = t_item-nsheet.
l_nline = t_item-nline.
* IF l_nline > 5.
* l_nfmttype = 2.
* endif.
if l_nsheet is initial.
l_nsheet = 1.
endif.
* SET PROPERTY OF L_oEXCEL 'SHEETSINNEWWORKBOOK' = L_nSheet.
* SET PROPERTY OF L_oEXCEL 'ACTIVESHEET' = L_oSHEET.
call method of l_oexcel 'Worksheets' = l_osheet
exporting
#1 = l_nsheet.
call method of l_osheet 'Activate '.
* 选中excel中的cell, 行 列。
"处理格式
if l_nfmttype = 1. "复制上一行的格式
"break-point.
l_nline = l_nline - 1.
call method of l_oexcel 'ROWS' = l_orow exporting #1 = l_nline.
call method of l_orow 'select' no flush.
call method of l_orow 'copy' no flush.
call method of l_orow 'insert' EXPORTING #1 = '-4121'.
elseif l_nfmttype = 2. "复制当前行的格式
l_nline = l_nline .
call method of l_oexcel 'ROWS' = l_orow exporting #1 = l_nline .
call method of l_orow 'select' no flush.
call method of l_orow 'copy' no flush.
call method of l_orow 'insert' EXPORTING #1 = '-4121'.
elseif l_nfmttype = 3. "复制当前行的格式
l_nline = l_nline + 1.
call method of l_oexcel 'ROWS' = l_orow exporting #1 = l_nline.
call method of l_orow 'select' no flush.
call method of l_orow 'copy' no flush.
call method of l_orow 'insert' EXPORTING #1 = '-4121'.
elseif l_nfmttype = 99. "复制当前行的格式
l_nline = l_nline + 1.
call method of l_oexcel 'ROWS' = l_orow exporting #1 = l_nline.
call method of l_orow 'select' no flush.
* call method of l_orow 'copy' no flush.
call method of l_orow 'delete' EXPORTING #1 = '-4162'.
endif.
call method of l_oexcel 'CELLS' = l_ocell
exporting
#1 = t_item-nline
#2 = t_item-nrow.
* 设置被选中的cell的值
set property of l_ocell 'VALUE' = t_item-cvalue. "你要输出的内容
endloop.
* EXCEL文件保存并释放资源过程.
get property of l_oexcel 'ACTIVESHEET' = l_osheet.
get property of l_oexcel 'ACTIVEWORKBOOK' = l_oworkbook.
* 保存文件
call method of l_oexcel 'SAVE'.
* Break-point.
if p_close = 1.
call method of l_oworkbook 'CLOSE'.
* 退出excel
call method of l_oexcel 'QUIT'.
else.
set property of l_oexcel 'Visible' = 1.
endif.
* 释放对象
free object l_osheet.
free object l_oworkbook.
free object l_oexcel.
free object l_ocell.
free object l_orow.
p_status = 1.
concatenate '已成功导出到EXCEL文件:[' p_saveas ']!' into l_msg.
message i888(sabapdocu) with l_msg.
endfunction.
调用例子:
* EXCEL倒出表
data: t_data like zco_xlsmdl occurs 0 with header line.
t_data-nfmttype = 2.
t_data-nline = l_nrecat.
t_data-nrow = 1.
t_data-cvalue = x_vbrphs-matnr.
t_data-crow = '物料号'.
append t_data.
t_data-nsheet = 1. "第一个Sheet,不填时,默认为第一个SHEET
t_data-nfmttype = 99. "删除多出的尾行
t_data-nline = l_nNewRec. "删除的行号
t_data-nrow = 1. " 删除时此列号不起作用
t_data-cvalue = ''. "
t_data-crow = '删除尾行'.
t_data-cline = ''.
append t_data.
data: l_xlsfl type string
,l_xlsas type string
.
l_xlsfl = p_xlsfl.
l_xlsas = p_xlsas.
* 导出数据到EXCEL文件
call function 'Z_CO_XLSMDL'
exporting
p_xlsadd = l_xlsfl "Excel文件所在目录及名称(一般放在服务器上) 必须
p_saveas = l_xlsas "EXCEL文件下载到本地的目录及名称(一般放在本地般) 可选
p_close = 0 "是否关闭EXCEL文件,为0时打开并显示EXCEL文件
* IMPORTING
* P_STATUS =
tables
t_item = t_data
.
write: / '<--[已完成EXCEL表格的导出]-----------<<<<<<<<'.