PROCEDURE imp_excel_1 IS
excel ole2.obj_type;
books ole2.obj_type;
book ole2.obj_type;
-- sheets ole2.obj_type;
sheet ole2.obj_type;
cell ole2.obj_type;
args ole2.list_type;
appli ole2.obj_type;
filename varchar2(1000);
v1 varchar2(100);
v2 number;
v3 varchar2(100);
v4 varchar2(100);
v5 varchar2(100);
v6 varchar2(100);
v7 date;
v8 varchar2(100);
v9 varchar2(100);
v10 number;
m number(4);
n number(2);
cnt number;
seq number;
v_seq number;
BEGIN
appli:=ole2.create_obj('excel.application');
books:=ole2.get_obj_property(appli,'workbooks');
args:=ole2.create_arglist;
filename := get_file_name();
ole2.add_arg(args,filename);
book:=ole2.get_obj_property(books,'open',args);
ole2.destroy_arglist(args);
ole2.invoke(book,'activate');
n:= :basic.n;
m:= :basic.m;
loop
args:=ole2.create_arglist;
ole2.add_arg(args,n);
sheet:=ole2.get_obj_property(book,'worksheets',args);
ole2.destroy_arglist(args);
m:=m+1;
args:=ole2.create_arglist;
ole2.add_arg(args,m);
ole2.add_arg(args,2);
cell:=ole2.get_obj_property(sheet,'cells',args);
ole2.destroy_arglist(args);
v2:=ltrim(rtrim(ole2.get_char_property(cell,'value')));
ole2.release_obj(cell);
args:=ole2.create_arglist;
ole2.add_arg(args,m);
ole2.add_arg(args,3);
cell:=ole2.get_obj_property(sheet,'cells',args);
ole2.destroy_arglist(args);
v3:=ltrim(rtrim(ole2.get_char_property(cell,'value')));
ole2.release_obj(cell);
args:=ole2.create_arglist;
ole2.add_arg(args,m);
ole2.add_arg(args,4);
cell:=ole2.get_obj_property(sheet,'cells',args);
ole2.destroy_arglist(args);
v4:=ltrim(rtrim(ole2.get_char_property(cell,'value')));
ole2.release_obj(cell);
args:=ole2.create_arglist;
ole2.add_arg(args,m);
ole2.add_arg(args,5);
cell:=ole2.get_obj_property(sheet,'cells',args);
ole2.destroy_arglist(args);
v5:=ltrim(rtrim(ole2.get_num_property(cell,'value')));
ole2.release_obj(cell);
args:=ole2.create_arglist;
ole2.add_arg(args,m);
ole2.add_arg(args,6);
cell:=ole2.get_obj_property(sheet,'cells',args);
ole2.destroy_arglist(args);
v6:=ltrim(rtrim(ole2.get_char_property(cell,'value')));
ole2.release_obj(cell);
args:=ole2.create_arglist;
ole2.add_arg(args,m);
ole2.add_arg(args,7);
cell:=ole2.get_obj_property(sheet,'cells',args);
ole2.destroy_arglist(args);
v7:=ltrim(rtrim(ole2.get_char_property(cell,'value')),'''');
ole2.release_obj(cell);
args:=ole2.create_arglist;
ole2.add_arg(args,m);
ole2.add_arg(args,8);
cell:=ole2.get_obj_property(sheet,'cells',args);
ole2.destroy_arglist(args);
v8:=ltrim(rtrim(ole2.get_char_property(cell,'value')));
ole2.release_obj(cell);
args:=ole2.create_arglist;
ole2.add_arg(args,m);
ole2.add_arg(args,9);
cell:=ole2.get_obj_property(sheet,'cells',args);
ole2.destroy_arglist(args);
v9:=ltrim(rtrim(ole2.get_num_property(cell,'value')));
ole2.release_obj(cell);
args:=ole2.create_arglist;
ole2.add_arg(args,m);
ole2.add_arg(args,10);
cell:=ole2.get_obj_property(sheet,'cells',args);
ole2.destroy_arglist(args);
v10:=ltrim(rtrim(ole2.get_num_property(cell,'value')));
ole2.release_obj(cell);
message_alert('v2='||v2||';v3='||v3||';v4='||v4||';v5='||v5||';v6='||v6||';v7='||v7||';v8='||v8||';v9='||v9||';v10='||v10);
exit when v6 is null;
message_alert('v2='||v2||';v3='||v3||';v4='||v4||';v5='||v5||';v6='||v6||';v7='||v7||';v8='||v8||';v9='||v9||';v10='||v10);
/* begin
update hr0c0 set hr0c0_sim_name = v3,
hr0c0_sex = v4,
hr0c0_nation = v5,
hr0c0_birthday = to_date(to_char(v7),'yyyymmdd'),
hr0c0_address = v8,
hr0c0_organ = v9,
hr0c0_issue_date = to_date('19000101','yyyymmdd') + v2 - 2,
hr0c0_expire_date = to_date('19000101','yyyymmdd') + v10 - 2
where hr0c0_id_no = v6;
if sql%notfound then
insert into hr0c0 (hr0c0_sim_name,hr0c0_sex,hr0c0_nation,hr0c0_birthday,
hr0c0_address,hr0c0_id_no,hr0c0_organ,
hr0c0_issue_date,hr0c0_expire_date)
values (v3,v4,v5,to_date(to_char(v7),'yyyymmdd'),v8,v6,v9,
to_date('19000101','yyyymmdd') + v2 - 2,
to_date('19000101','yyyymmdd') + v10 - 2);
end if;
exception
when others then
message_alert(dbms_error_text);
raise form_trigger_failure;
end;*/
end loop;
ole2.release_obj(sheet);
ole2.invoke(book,'Close');
ole2.release_obj(book);
ole2.release_obj(books);
ole2.invoke(excel,'Quit');
ole2.release_obj(appli);
if form_success then
:system.message_level := 20;
commit;
:system.message_level := 0;
message_alert('已导入'||(m-2)||'笔身份证资料');
else
ole2.release_obj(sheet);
ole2.invoke(book,'Close');
ole2.release_obj(book);
ole2.release_obj(books);
ole2.invoke(excel,'Quit');
ole2.release_obj(appli);
rollback;
message_alert('导入资料出错,'||dbms_error_text);
raise form_trigger_failure;
end if;
END;
最新发布