l Excel系统中保存的excel模版下载:
*&-------------------------------------------------------------------
*& Form FRM_DOWN_MODEL excel模板下载
*&-------------------------------------------------------------------
*& text excel_name 传递的是制定模版的名称(上传模版时创建的模版对象号)
*&-------------------------------------------------------------------
FORM FRM_DOWN_MODEL using excel_name.
data:lo_objdata like wwwdatatab,
lo_mime like w3mime,
ls_destination like rlgrap-filename,
ls_objnam type string,
li_rc like sy-subrc,
ls_errtxt type string.
data:p_objid type wwwdatatab-objid,
p_dest like sapb-sappfad.
p_objid = excel_name.
call method cl_gui_frontend_services=>file_save_dialog
EXPORTING
default_extension = 'XLS'
default_file_name = lc_filename
CHANGING
filename = lc_filename
path = lc_path
fullpath = lc_fullpath
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
others = 4.
if lc_fullpath = ''.
message '不能打开excel' type 'E'.
endif.
if sy-subrc = 0.
p_dest = lc_fullpath.
concatenate p_objid '.XLS' into ls_objnam.
condense ls_objnam no-GAPS.
select single relid objid from wwwdata into corresponding fields of lo_objdata
where srtf2 = 0 and relid = 'MI' and objid = P_OBJID.
if sy-subrc ne 0 or lo_objdata-objid eq space.
concatenate '模板文件' ls_objnam '不存在,请用TCODE:SMWO进行加载' into ls_errtxt.
message ls_errtxt type 'I'.
endif.
ls_destination = p_dest.
call function 'DOWNLOAD_WEB_OBJECT'
EXPORTING
KEY = LO_OBJDATA
DESTINATION = LS_DESTINATION
IMPORTING
RC = LI_RC.
if li_rc ne 0.
concatenate '模板文件:' ls_objnam '下载失败' into ls_errtxt.
message ls_errtxt type 'E'.
endif.
endif.
ENDFORM. " FRM_DOWN_MODEL
注释:其中只有红色字体的部分需要根据自己的要求修改,其他的变量可以不变
l Excel打开excel并且对相应的位置赋值(带有画格子功能)
*&-------------------------------------------------------------------
*& Form frm_openexcel open excel and 为指定的列添加字段 和 赋值
*&-------------------------------------------------------------------
form frm_openexcel.
data:num type i value 0,
l_num type i value 1.
CREATE OBJECT excel 'EXCEL.APPLICATION'.
SET PROPERTY OF excel 'Visible' = 1.
CALL METHOD OF excel 'WORKBOOKS' = w_book.
CALL METHOD OF w_book 'OPEN'
EXPORTING
#1 = lc_fullpath. "EXCEL存放路径
CALL METHOD OF excel 'Worksheets' = w_sheet
EXPORTING
#1 = 'Sheet1'.
CALL METHOD OF w_sheet 'Activate'. "以上是打开excel
*开始设置excel的样式
perform frm_get_addgrid using 1 1 1 4. "绘制单元格
perform frm_get_setcellvalues using excel 1 1 '项目相关字段批量导入' . "添加表头
perform frm_drawline using 3 1 3 1 '2' '1'.
perform frm_get_setcellvalues using excel 3 1 '项目定义'. "设置默认字段
I = 3. “控制列 由于列是动态变化
loop at it_field. "设置列的非默认字段 这里的it_field保存的是字段
perform frm_drawline using 3 3 3 I '2' '1'."3 3 3 I '2' '1'
perform frm_get_setcellvalues using excel 3 I it_field-proj_describe.
add 1 to I.
endloop.
I = I - 1. "用来控制列 获取的是总列数
num = 4. "用来控制行
loop at it_data.
l_num = 1. "1
perform frm_drawline using 4 1 num I '2' '1'. "4 1 num I '2' '1'
perform frm_get_setcellvalues using excel num l_num it_data-proj_pspid.
add 1 to l_num.
loop at it_field.
condense it_field-proj_field no-gaps. “去除空格
case it_field-proj_field.
when 'VERNR'.
perform frm_get_setcellvalues using excel num l_num it_data-p