SAP 导入XLS
Report z_po01.DATA: BEGIN OF i_table OCCURS 0,
a(10)TYPE c,
b(10)TYPE c,
c(10)TYPE c,
d(10)TYPE c,
e(10)TYPE c,
END OF i_table.
DATA : l_intern TYPE kcde_cells OCCURS 0 WITH HEADER LINE.
DATA : l_index TYPE i.
FIELD-SYMBOLS : <fs>.
PARAMETERS: p_file LIKE rlgrap-filename
DEFAULT 'c:\xxxx\xxxxxx.xls'.
CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
EXPORTING
filename = p_file
i_begin_col = 1
i_begin_row = 1
i_end_col = 256
i_end_row = 65536
TABLES
intern = l_intern.
SORT l_intern BY row col.
LOOP AT l_intern.
MOVE l_intern-col TO l_index.
ASSIGN COMPONENT l_index OF STRUCTURE i_table TO <fs>.
MOVE l_intern-value TO <fs>.
AT END OF row.
APPEND i_table.
CLEAR i_table.
ENDAT.
ENDLOOP.
LOOP AT i_table.
WRITE: / i_table.
ENDLOOP.
上面的一段代码是很久以前下载过来 应该是可以的现在在贴一段是测试通过的 这是导入数据后用alv输出显示等用户再次确认后在会写入数据库
*&---------------------------------------------------------------------*
*& ReportZMM_062A
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
reportzmm_062a.
type-pools: slis.
data: gt_fieldcat type slis_t_fieldcat_alv.
data: gs_layout type slis_layout_alv.
tables: mkpf,zguan.
"导入数据
data: begin of im_tab occurs 0 ,
bilak type char10, "期间标示
bukrs like zguan-bukrs, " 公司
docnr like zguan-docnr, "关封申请表编号
kunli like zguan-kunnr, " 客户/供应商
docum like zguan-docum, "关封序号
sapma like zguan-sapma, "SAP料号
klima like zguan-kunma, "客户供应商料号
vsten like zguan-vsten, "VST项号
kunen like zguan-kunen, "客户项号
eanw1 like zguan-eanw1, "商品编码
makt1 like zguan-maktx, "商品名称
typbz like zguan-typbz , "规格型号
denum like zguan-denum, "关封数量
mein2 like zguan-meins, "数量单位
kompf like zguan-kompf, "转换比例因子
degew like zguan-degew, "关封重量
deewe like zguan-deewe, "重量单位
handk like zguan-handk, "手册号
adatu type char10, "关封使用起始日期
datbi type char10, "关封使用有效期
end of im_tab.
data: tab like zguan occurs 0 with header line.
data:begin of iexcel occurs 0.
include structure alsmex_tabline."具有 Excel 数据的表行
data: end of iexcel.
data: begin of updata_taboccurs 0 ,
mblnr like mseg-mblnr, "收发货单号
mblpo like mseg-zeile, "项目
menge like mseg-menge, "交易数量
end of updata_tab.
data: begin of gt_bukrs occurs 0,"公司
bukrs like t001k-bukrs,
end of gt_bukrs.
data: begin of gt_lifnr occurs 0 , "供应商
lifnr like lfa1-lifnr,
end of gt_lifnr.
data: begin of gt_kunnr occurs 0,"客户
kunnr like kna1-kunnr,
end of gt_kunnr.
data: begin of gt_mara occurs 0, "物料
matnr like mara-matnr,
end of gt_mara.
data: filename type string,
lf_rc type c,
col type i.
field-symbols : <fs>.
data: l_index type i.
data: l_time type sy-uzeit.
data: xa(12) type c.
data: ind2(6) type c.
data: im_date(8) type c.
data:qus type string,
ans type c.
data: v_date(10) type c,
p_date like sy-datum.
ranges:s_time for mkpf-budat.
selection-screen begin of block b3 with frame title t3.
parameters: p_bukrs like mseg-bukrs default 'C101' obligatory.
selection-screen begin of line.
selection-screen comment 1(7) p_lif for field p_lifnr .
parameters p_lifnr radiobutton group g1 default 'X'."供应商
selection-screen comment 15(7) p_kunn for field p_kunnr.
parameters p_kunnr radiobutton group g1."客户
selection-screen end of line.
selection-screen end of block b3.
selection-screen begin of block b2 with frame title t2.
" parameters: up_s as checkbox.
parameters: fn1 like rlgrap-filename memory id mol.
select-options s_budat for mkpf-budat modif id g3.
selection-screen uline.
selection-screen begin of line.
selection-screen comment 1(5) rd1 for field p_update .
parameters p_update radiobutton groupg2 user-command com."供应商
selection-screen comment 15(7) rd2 for field p_add.
parameters p_add radiobutton group g2default 'X'."客户
selection-screen end ofline.
selection-screen end of block b2.
initialization.
t3 = '选择你导入的清单类型'.
t2 = '导入数据'.
rd1 = '覆盖导入'.
rd2 = '新增导入'.
p_lif = '收货清单'.
p_kunn = '发货清单'.
at selection-screen on value-request for fn1.
call function 'F4_FILENAME'"文件选择框
exporting
program_name= syst-cprog
dynpro_number = syst-dynnr
field_name = ''
importing
file_name = fn1.
at selection-screen output.
loop at screen.
if screen-group1 = 'G3' and p_add = 'X' .
" screen-invisible = 1.
screen-active = 0.
modify screen.
endif.
endloop.
at selection-screen.
filename = fn1.
call method cl_gui_frontend_services=>file_exist "判断物理文件是否存在
exporting
file = filename
receiving
result = lf_rc
exceptions
cntl_error = 1
error_no_gui = 2
wrong_parameter = 3
not_supported_by_gui = 4
others = 5.
if lf_rc <> 'X'.
" message e000(oo) with 'The file is not exist'.
message e000(oo) with '该文件不存在'.
endif.
start-of-selection.
if p_update = 'X' and s_budat-low = 000000.
message e000(oo) with '请选择覆盖期间'.
leave list-processing.
endif.
perform auth_check. "权限检查
perform get_s_fname."上传文件
perform move_data. "把上传的数据保存到 关联数据库的内表
perform show. " 显示数据
*&---------------------------------------------------------------------*
*& Formget_s_fname
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
form get_s_fname.
call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
exporting
filename = fn1
i_begin_col = 1
i_begin_row = 1
i_end_col = 256
i_end_row = 65536
tables
intern = iexcel
exceptions
inconsistent_parameters = 1
upload_ole = 2
others = 3.
*IF SY-SUBRC <> 0.
** MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
** WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
*ENDIF.
if sy-subrc <> 0.
"message 'Open the file error' type 'I'.
message e000(oo) with '打开文件失败'.
else.
loop at iexcel.
if iexcel-row <> 1.
move iexcel-col to l_index.
assign component l_index of structure im_tab to <fs> .
translate iexcel-valueto upper case.
move iexcel-value to <fs>.
endif.
at end of row.
if iexcel-row <> 1.
append im_tab.
clear im_tab.
endif.
endat.
endloop.
endif.
endform. "get_s_fname
*&---------------------------------------------------------------------*
*& Formshow
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
form show.
perform fieldcat_init using :
'BILAK' '导入日期' 13 '' '' '' '' '' '' '' '' '',
" 'EWAEL''主键标示' 13 '' '' '' '' '' '' '' '' '',
'BUKRS' '公司' 13 '' '' '' '' '' '' '' '' '',
'DOCNR' '关封申请表编号' 13 '' '' '' '' '' '' '' '' '',
'KUNLI' '客户/供应商 ' 15 '' '' '' '' '' '' '' '' '',
'DOCUM' '关封序号' 8'' '' '' '' '' '' '' '' '',
'SAPMA' 'SAP料号' 7'' '' '' '' '' '' '' '' '',
'KLIMA' '客户供应商料号' 20 '' '' '' '' '' '' '' '' '',
'VSTEN' 'VS项号' 14 '' '' '' '' '' '' '' '' '',
'KUNEN' '客户项号' 10 '' '' '' '' '' '' '' '' '',
'EANW1' '商品编码' 8 '' '' '' '' '' '' '' '' '',
'MAKT1' '商品名称' 10 '' '' '' '' '' '' '' '' '',
'TYPBZ' '规格型号' 10 '' 'Q' '' '' '' '' '' '' '',
'DENUM' '关封数量' 10 ' ' 'Q' '' '' '' '' '' '' '',
'MEIN2' '数量单位' 10 ' ' '' '' '' '' '' '' '' '',
'KOMPF' '转换比例因子' 10 ' ' '' '' '' '' '' '' '' '',
'DEGEW' '关封重量' 10 ' ' '' '' '' '' '' '' '' '',
'DEEWE' '重量单位' 10 ' ' '' '' '' '' '' '' '' '',
'HANDK' '手册号' 10 ' ' '' '' '' '' '' '' '' '',
'ADATU' '关封使用起始日期' 10 ' ' 'Q' '' '' '' '' '' '' '',
'DATBI' '关封使用有效期' 10 ' ' '' '' '' '' '' '' '' ''.
constants cns_pf_status type slis_formname value 'ALV_PF_STATUS'. "alv自定义按钮
constants cns_user_command type slis_formname value 'ALV_USER_COMMAND'. "alv自定义按钮响应事件
gs_layout-info_fieldname = 'COLOR'.
call function 'REUSE_ALV_GRID_DISPLAY'
exporting
i_callback_program = sy-repid "当前程序
* i_callback_pf_status_set = 'SET_PF_STATUS'
* i_callback_user_command= 'USER_COMMAND'
i_callback_top_of_page = 'F_TOP_OF_PAGE2 '
it_fieldcat = gt_fieldcat[]"子函数field填充的各列
is_layout = gs_layout "子函数 layout_buidld填充格式
i_callback_pf_status_set = cns_pf_status " 设置alv 自定义按钮GUI
i_callback_user_command =cns_user_command "设置响应事件
i_save = 'X'
tables
t_outtab = im_tab.
endform. "show
*&---------------------------------------------------------------------*
*& Formmove_data
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
form move_data.
if p_kunnr = 'X'.
select kunnr into table gt_kunnr from kna1."客户
else.
select lifnr into table gt_lifnr from lfa1."供应商
endif.
selectmatnr into table gt_mara from mara.
select bukrs into table gt_bukrs from t001k.
data: l_index type i value 1.
read table im_tab index 1.
s_time-low = im_tab-bilak.
s_time-high = im_tab-bilak.
loop at im_tab.
data: l_type(4) type c.
call function 'NUMERIC_CHECK'
exporting
string_in = im_tab-sapma
importing
htype = l_type.
if l_type = 'NUMC' and im_tab-sapma >= '000000000000000000'and im_tab-sapma <= '999999999999999999'.
call function 'CONVERSION_EXIT_ALPHA_INPUT'
exporting
input= im_tab-sapma
importing
output = im_tab-sapma.
tab-sapma = im_tab-sapma.
endif.
*--------判断导入的 公司 客户 物料----------*
if p_kunnr = 'X'.
read table gt_kunnr with key kunnr = im_tab-kunli.
ifsy-subrc <> 0.
message e000(oo) with '导入的客户不存在'.
endif.
else.
read table gt_lifnr with key lifnr = im_tab-kunli.
ifsy-subrc <> 0.
message e000(oo) with '导入的供应商不存在'.
endif.
endif.
read table gt_mara with key matnr = im_tab-sapma.
ifsy-subrc <> 0.
message e000(oo) with '导入的物料不存在'.
endif.
read table gt_bukrs with key bukrs = im_tab-bukrs.
ifsy-subrc <> 0.
message e000(oo) with '导入公司不存在'.
endif.
if im_tab-handk = ''.
message e000(oo) with '导入的手册号为空'.
endif.
if im_tab-degew = ''.
message e000(oo) with '导入的关封重量为空'.
endif.
*----------------- 转化成大写--------------*
* translate im_tab-bukrs to upper case. "工厂
* translate im_tab-kunli to upper case.
* translate im_tab-sapma to upper case.
* translate im_tab-deewe to upper case. "重量单位
* translate im_tab-handk to upper case. "手册号
if s_time-low > im_tab-bilak.
s_time-low = im_tab-bilak.
endif.
if s_time-high < im_tab-bilak.
s_time-high = im_tab-bilak.
endif.
move-corresponding im_tab to tab.
*--------------查找日期特俗符号为数字类型补0--------*
v_date=im_tab-bilak .
perform date_check using v_date changing p_date.
if p_date = ' '.
message e000(oo) with '导入日期格式有误请检查请按照20121212'.
else.
tab-bilak =p_date.
endif.
clear: v_date,p_date.
v_date =im_tab-adatu .
perform date_check using v_date changing p_date.
if p_date = ' '.
message e000(oo) with '关封起始日期中有误请检查'.
else.
tab-adatu = p_date.
endif.
clear: v_date,p_date.
v_date = im_tab-datbi.
perform date_check using v_date changing p_date.
if p_date = ' '.
message e000(oo) with '关封结束日期中有误请检查'.
else.
tab-datbi = p_date.
endif.
*-----------------***********----------*
get time.
l_time = sy-uzeit.
xa = l_time .
ind2 = l_index.
condense ind2. "去掉首位空格
concatenate xa ind2 into tab-ewael.
if p_kunnr = 'X'.
tab-flag= ' ' .
tab-kunnr = im_tab-kunli.
tab-kunma = im_tab-klima.
else.
tab-lifnr = im_tab-kunli.
tab-lifma = im_tab-klima.
tab-flag = 'X'.
endif.
tab-meins = im_tab-mein2.
tab-maktx = im_tab-makt1.
append tab.
l_index = l_index + 1.
endloop.
s_time-sign = 'I'.
s_time-option = 'BT'.
append s_time.
endform. "move_data
*&---------------------------------------------------------------------*
*& Formalv_user_command
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->R_UCOMM text
* -->RS_SELFIELDtext
*----------------------------------------------------------------------*
form alv_user_command using r_ucomm like sy-ucomm
rs_selfield type slis_selfield.
data: s type string.
case r_ucomm.
when '&EXECUTE'.
if p_update = 'X'.
if s_budat-high = '00000000'.
s_budat-high = s_budat-low.
endif.
*============判断输入日期与导入日期是否有差异
if s_time-low < s_budat-lowor s_time-high > s_budat-high.
concatenate '导入时间是:' s_time-low '-' s_time-high ' '
'覆盖时间是:' s_budat-low '-' s_budat-high' 存在差异是否导入' into s.
qus = s.
call function 'POPUP_TO_CONFIRM'
exporting
text_question = qus
importing
answer = ans.
else.
*============日期没有差异直接保存
if p_kunnr = 'X'.
delete from zguan where zguan~bilak in s_budat and flag = ''.
else.
delete from zguan where zguan~bilak in s_budat and flag = 'X'.
endif.
modify zguan from table tab[].
endif.
*============点击确认后保存
if ans = '1'.
if p_kunnr = 'X'.
delete from zguan where zguan~bilak in s_budat and flag = ''.
else.
delete from zguan where zguan~bilak in s_budat and flag = 'X'.
endif.
modify zguan from table tab[].
endif.
if sy-subrc = 0 and ans = '2' or ans = 'A'.
" message e000(oo) with '操作成功'.
message '操作取消' type 'S'.
" elseif sy-subrc = 0 and ans = 1.
"message '操作成功' type 'S'.
else.
message '操作成功' type 'S'.
endif.
else.
modify zguan from table tab[]. "------新增保存
if sy-subrc = 0.
message '操作成功' type 'S'.
endif.
endif.
endcase.
endform. "alv_user_command
*&---------------------------------------------------------------------*
*& Formalv_pf_status
*&---------------------------------------------------------------------*
* text 设置GUI状态
*----------------------------------------------------------------------*
* -->RT_EXTAB text
*----------------------------------------------------------------------*
form alv_pf_status using rt_extab type slis_t_extab.
set pf-status 'STANDARD_004' excluding rt_extab.
endform . "alv_pf_status
*&---------------------------------------------------------------------*
*& Formfieldcat_init
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->FIELD_NAME text
* -->FIELD_TEXT text
* -->FIELD_LENTH text
* -->FIELD_EDIT text
* -->FIELD_TYPE text
* -->FIELD_KEY text
* -->FIELD_HOTSPOT text
* -->FIELD_CHECKBOX text
* -->FIELD_NO_ZERO text
* -->FIELD_REF_TABNAMEtext
* -->FIELDCAT_DO_SUM text
* -->FIELD_EMPHASIZE text
*----------------------------------------------------------------------*
form fieldcat_init using
field_name type c
field_text type c
field_lenth type i
field_edit type c
field_type type c
field_key type c
field_hotspot type c
field_checkboxtype c
field_no_zero type c
field_ref_tabname type c
fieldcat_do_sum type c" 总计列值总和
field_emphasize type c.
data: ls_fieldcat type slis_fieldcat_alv.
clear ls_fieldcat.
ls_fieldcat-fieldname = field_name.
ls_fieldcat-seltext_l = field_text.
ls_fieldcat-seltext_m = field_text.
ls_fieldcat-seltext_s = field_text.
ls_fieldcat-checkbox= field_checkbox.
*LS_FIELDCAT-ROUND = 1.
ls_fieldcat-edit = field_edit.
if field_type = 'Q'.
ls_fieldcat-just = 'R'.
ls_fieldcat-datatype = 'QUAN'.
ls_fieldcat-inttype = 'C'.
else.
ls_fieldcat-just = 'L'.
endif.
ls_fieldcat-key= field_key.
ls_fieldcat-hotspot = field_hotspot .
ls_fieldcat-outputlen = field_lenth.
ls_fieldcat-no_zero = field_no_zero.
ls_fieldcat-ref_tabname = field_ref_tabname.
ls_fieldcat-do_sum= fieldcat_do_sum.
ls_fieldcat-emphasize = field_emphasize .
append ls_fieldcat to gt_fieldcat.
endform. "fieldcat_init
*&---------------------------------------------------------------------*
*& Formf_top_of_page2
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
form f_top_of_page2.
data: i_headertype slis_t_listheader,
wa_header type slis_listheader.
data: l_name type string,
name_frist like adrp-name_first,
name_last like adrp-name_last.
select single adrp~name_first adrp~name_last
into (name_frist, name_last)
from adrp
inner join usr21 on adrp~persnumber = usr21~persnumber
where usr21~bname = sy-uname.
if sy-subrc = 0.
concatenate name_last name_frist into l_name.
else.
l_name = sy-uname.
endif.
clear name_frist.
clear name_last.
data: l_date type string.
concatenate sy-datum+0(4)'-'
sy-datum+4(2)'-'
sy-datum+6(2)'' into l_date.
wa_header-typ = 'H'.
if p_kunnr = 'X'.
wa_header-info = '发货清单'.
else.
wa_header-info = '收货清单'.
endif.
append wa_header to i_header.
clear wa_header.
concatenate '制表人:' l_name into l_name.
concatenate '制表日期:' l_date into l_date.
wa_header-typ ='S'.
wa_header-key = l_name.
wa_header-info = l_date.
append wa_header to i_header.
clear wa_header.
call function 'REUSE_ALV_COMMENTARY_WRITE'
exporting
it_list_commentary = i_header
* I_LOGO =
* I_END_OF_LIST_GRID =
i_alv_form = 'X'.
endform. "f_top_of_page2
*&---------------------------------------------------------------------*
*& Formdate_check
*&---------------------------------------------------------------------*
form date_check using v_date changing p_date.
data: year(8)type n,
month(3) type n,
day(3) type n.
split v_date at '/' into year month day.
if strlen( year ) > 4.
split v_date at '-' into year month day.
endif.
if strlen( year ) > 4.
split v_date at '.' into year month day.
endif.
if strlen( year ) > 4.
p_date = year.
else.
month = 100 + month.
day = 100 + day.
concatenate year month+1(2) day+1(2) into p_date.
endif.
call function 'DATE_CHECK_PLAUSIBILITY'
exporting
date = p_date
exceptions
plausibility_check_failed = 1
others = 2.
if sy-subrc ne 0.
p_date = ''.
endif.
endform. "date_check
*&---------------------------------------------------------------------*
*& Form权限检查
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
form auth_check.
authority-check object 'M_MATE_BUK'
id 'ACTVT'dummy" 作业
id 'BUKRS'field p_bukrs." 公司代码
if sy-subrc <> 0.
message s888(sabapdocu) with'无相应的公司权限，请重新输入'.
leave list-processing.
endif.
endform. "auth_check
页:
[1]