//prepare for oracle table with ADODataSet2 do begin Active := False; CommandText := 'ora_table'; CommandType := cmdTable; Active := True; end;//insert with ADODataSet1 do begin Active := False; CommandText := 'select * from [Sheet1]'; Active := True; while not Eof do begin with ADODataSet2 do begin Fields.FieldByName('..').Value := ADODataSet1.Fields.FieldByName('..').Value; ...... end; Next; end; end;
function Tfrm_main.ReadDataFromExcel: boolean; var excelapp,excelsheet:olevariant; i,j,k,row_count,col_count,pri_con:integer; table_name,fields,temp_str,str,ls,filesname:string; pri_fields :array of string; pri_index :array of integer; seq_name :array of string; temp_list:tstringlist; flag1: boolean;//字段的有效性; pri_bz,fields_count:integer;//有无主键; arect:trect; begin result:=true; if IsObjectActive('Excel.Application') then begin messagebox(0,'"EXCEL",已经打开,请先关闭已经打开的EXCEL文件','FA_提示',mb_iconinformation+mb_taskmodal); end; if open1.Execute then begin filesname:=open1.FileName; end else begin result:=false; exit; end; try try // reset_mouse(0); ed_filepath.text:=open1.FileName; setlength(pri_fields,0); setlength(pri_fields,15); setlength(pri_index,0); setlength(pri_index,15); setlength(seq_name,0); setlength(seq_name,15); excelapp:=CreateOleObject('Excel.Application'); ExcelApp.WorkBooks.Open( open1.FileName ); //excelapp.visible:=true; except messagebox(0,'无法连接EXCEL 自动化服务器,'+#10+ '请确认您的系统已经安装EXCEL 2000 及更高版本。','系统错误',mb_ok+mb_iconerror+MB_TASKMODAl); result:=false; //reset_mouse(1); exit; end; try excel_data_list:=tstringlist.Create; temp_str:=uppercase(trim(excelapp.Cells.Item[1,1])); table_name:=uppercase(copy(temp_str,1,pos(':',temp_str)-1)); if table_name='' then begin messagebox(0,'请确认您打开的EXCEL文件的表头信息是否正确!','警告信息',mb_iconerror+mb_taskmodal); result:=false; // reset_mouse(1); exit; end; init.Close; init.SQL.Text:='select count(*) from user_tables where table_name='''+table_name+''''; init.Open; if init.Fields[0].AsInteger<=0 then begin messagebox(0,pchar('"'+table_name+'"不在数据库中,请确认EXCEL表头信息是否正确!'),'错误信息-导入失败',mb_iconerror+mb_taskmodal); result:=false; //reset_mouse(1); exit; end; excel_data_list.Add(table_name);//增加表名称; delete(temp_str,1,pos(':',temp_str)); str:=copy(temp_str,1,pos(':',temp_str)-1); excel_data_list.Add(str);//增加主键名称列表; if str='' then pri_bz:=0 //无主键; else pri_bz:=1;//有主键; if pri_bz=1 then begin i:=0; while pos(',',str)>0 do begin //增加主键字段名; pri_fields[i]:=copy(str,1,pos(',',str)-1); delete(str,1,pos(',',str)); inc(i); end; pri_fields[i]:=str;//增加最后一个主键字段; pri_con:=i; end; delete(temp_str,1,pos(':',temp_str));//字段名列表; i:=0; while pos(',',temp_str)>0 do begin //增加字段名称; str:=copy(temp_str,1,pos(',',temp_str)-1); if pos('(',str)>0 then//有seq存在 ; begin fields:=fields+copy(str,1,pos('(',str)-1)+','; //增加字段名称; seq_name[i]:=copy(str,pos('(',str)+1,length(str)-pos('(',str)-1);//确定SEQ的名字; try //判断SEQ的有无; init.Close; init.SQL.Text:='select sequence_name from user_sequences where sequence_name='+quotedstr(uppercase(trim(seq_name[i]))); init.Open; if init.Fields[0].AsString='' then begin messagebox(handle,pchar('当前数据中不存在"'+seq_name[i]+'"这个序列号!'+#13+ '请确认打开的EXCEL文件的表头信息是否正确'),'错误信息',mb_iconerror); result:=false; exit; end; except result:=false; //reset_mouse(1); exit; end; delete(temp_str,1,pos(')',temp_str)+1); end else begin fields:=fields+str+',';//增加字段名称; delete(temp_str,1,pos(',',temp_str)); seq_name[i]:=''; end; inc(i); end; if pos('(',temp_str)>0 then begin fields:=fields+copy(temp_str,1,pos('(',temp_str)-1); //增加最后一个字段名称; seq_name[i]:=copy(temp_str,pos('(',temp_str)+1,length(temp_str)-pos('(',temp_str)-1);//确定SEQ的名字; try //判断SEQ的有无; init.Close;
init.SQL.Text:='select sequence_name from user_sequences where sequence_name='+quotedstr(uppercase(trim(seq_name[i]))); init.Open; if init.Fields[0].AsString='' then begin messagebox(handle,pchar('当前数据中不存在"'+seq_name[i]+'"这个序列号!'+#13+ '请确认打开的EXCEL文件的表头信息是否正确'),'错误信息',mb_iconerror); result:=false; //reset_mouse(1); exit; end; except result:=false; //reset_mouse(1); exit; end; end else begin fields:=fields+temp_str;//增加最后一个字段名称; seq_name[i]:=''; end; fields_count:=i; fields:=uppercase(trim(fields)); excel_data_list.Add(fields);//增加字段名称列表; try //判断COLUMN_NAME; init.Close; init.SQL.Text:='select COLUMN_NAME from user_tab_columns where table_name='+quotedstr(table_name); init.Open; str:=fields; temp_list:=tstringlist.Create; while pos(',',str)>0 do begin temp_list.Add(copy(str,1,pos(',',str)-1));//临时存储各字段名称; delete(str,1,pos(',',str)); end; temp_list.Add(str); for i:=0 to temp_list.Count-1 do begin init.First; flag1:=false; while not init.Eof do begin if temp_list.Strings[i]=init.Fields[0].asstring then begin flag1:=true; init.Next; continue; end; init.Next; end; if not flag1 then begin messagebox(0,pchar('"'+table_name+'"表中没有"'+temp_list.Strings[i]+'"这个字段'+#13+ '请确认你打开的EXCEL文件的表头信息是否正确!'),'警告信息',mb_iconerror+mb_taskmodal); result:=false; exit; end; end; except on e:exception do begin messagebox(0,pchar('打开'+table_name+'时出现下列错误:'+#13+e.Message),'警告信息',mb_iconerror+mb_taskmodal); result:=false; exit; end; end; //end 判断COLUMN_NAME; row_count:=4; col_count:=1; while trim(excelapp.Cells[row_count,1])<>'' do inc(row_count); progress1.Min:=0; progress1.Max:=row_count; //while trim(excelapp.Cells[3,col_count])<>'' do // inc(col_count); if pri_bz=1 then //找主健在fields中的位置; begin for i:=0 to pri_con do begin temp_str:=fields; k:=1; while pos(',',temp_str)>0 do begin if copy(temp_str,1,pos(',',temp_str)-1)=pri_fields[i] then begin pri_index[i]:=k; break; end; delete(temp_str,1,pos(',',temp_str)); inc(k); end; if temp_str=pri_fields[i] then pri_index[i]:=k; end; end;//end 找主健在fields中的位置; progress1.Min:=0; progress1.Max:=row_count; for i:=4 to row_count-1 do begin progress1.Position:=i; application.ProcessMessages; frm_main.Update; temp_str:=''; str:=''; k:=0; col_count:=1; progress1.Step:=row_count; for j:=1 to fields_count+1 do begin ls:=uppercase(trim(excelapp.Cells[i,col_count])); if seq_name[j-1]<>'' then //如果是用SEQ自动产生; begin temp_str:=temp_str+seq_name[j-1]+'.nextval,'; continue; end; init.Close; init.SQL.Text:='select DATA_TYPE from user_tab_columns where table_name='+quotedstr(table_name)+ ' and column_name='+quotedstr(temp_list.Strings[j-1]); init.Open; if uppercase(init.Fields[0].AsString)='DATE' then begin temp_str:=temp_str+'to_date('+quotedstr(ls)+',''yyyy-mm-dd'')'+','; end else begin temp_str:=temp_str+quotedstr(trim(excelapp.Cells[i,col_count]))+','; end; //增加主键的判断条件; if pri_bz=1 then begin for k:=0 to pri_con do begin //主键; if j=pri_index[k] then begin if uppercase(init.Fields[0].AsString)='DATE' then begin str:=str+'to_char('+pri_fields[k]+',''yyyy-mm-dd'')='+quotedstr(ls)+' and '; end else begin str:=str+pri_fields[k]+'='+quotedstr(ls)+' and '; end; end; end;//end主键; end; inc(col_count); end; if pri_bz=1 then begin delete(str,length(str)-4,4);//删除最后一个and; excel_data_list.Add(str);//主键的判断条件; end else begin excel_data_list.Add(''); end; delete(temp_str,length(temp_str),1);//删除最后一个逗号; excel_data_list.Add(temp_str);//数据值; end; except on e:exception do begin messagebox(0,pchar('无法打开该文件!具体错误信息如下:'+#10+ e.message),'FA 错误信息',mb_ok+mb_iconerror+MB_TASKMODAl); result:=false; // reset_mouse(1); exit; end; end; //reset_mouse(1); finally excelapp.quit; end; end;
function Tfrm_main.WriteDataToOracle: boolean; var table_name,fields,temp_str,msg:string; i,j,ins_up_flag,flag_all:integer; begin result:=true; try myconnection.BeginTrans; if excel_data_list.Count<=0 then exit; table_name:=excel_data_list.Strings[0]; fields:=excel_data_list.Strings[2]; flag_all:=0; ins_up_flag:=0; Progress1.Max:=excel_data_list.Count-1; progress1.Min:=0; for i:=3 to excel_data_list.Count-1 do begin progress1.Position:=i; temp_str:=excel_data_list.Strings[i]; if i mod 2 <>0 then ////奇数; begin if temp_str<>'' then begin init.Close; init.SQL.Text:='select count(*) from '+table_name+' where '+temp_str; init.Open; end else begin//处理没有主键的 ins_up_flag:=0; continue; end; if init.Fields[0].AsInteger>0 then ins_up_flag:=1 //0:insert;1:update; else ins_up_flag:=0; end else begin //偶数行;
自己写的一段过程:procedure TForm1.ExcelToData(Caption: string; ADOQueryTest: TADOQuery); var ConnectionString1, Textstring, AStress: string; num: integer; begin if OpenDialog1.Execute then begin AStress := OpenDialog1.FileName; //连接Excel ConnectionString1 := 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;' + 'Data Source=' + AStress + ';Extended Properties=Excel 8.0'; showmessage(ConnectionString1); ADOConnection2.ConnectionString := ConnectionString1; //打开Excel数据集 adoquery1.close; adoquery1.sql.clear; //确定那个sheet Textstring := 'select * from [' + Caption + '$]'; showmessage(Textstring); adoquery1.sql.add(Textstring); adoquery1.open; //打开连接execl表的数据集 adoquery1.first; //移动到第一个记录 //逐条记录添加 while (not adoquery1.eof) do begin ADOQueryTest.open; ADOQueryTest.append; for num := 0 to adoquery1.FieldCount - 1 do //逐个字段添加 begin ADOQueryTest.Fields[num].value := adoquery1.Fields[num].value; //注意,如果是自动增加字段不能如此赋值 end; ADOQueryTest.post; adoquery1.next; //移动到下一条记录 end; end; end;procedure TForm1.Button5Click(Sender: TObject); begin ExcelToData('磨具', ADOQuery2) end;
顺便说一句: lesstif() 仁兄的CommandText := 'select * from [Sheet1]';Fields.FieldByName('..').Value := ADODataSet1.Fields.FieldByName('..').Value;是错误的!!!
with ADODataSet2 do begin
Active := False;
CommandText := 'ora_table';
CommandType := cmdTable;
Active := True;
end;//insert
with ADODataSet1 do begin
Active := False;
CommandText := 'select * from [Sheet1]';
Active := True;
while not Eof do begin
with ADODataSet2 do begin
Fields.FieldByName('..').Value := ADODataSet1.Fields.FieldByName('..').Value;
......
end;
Next;
end;
end;
var excelapp,excelsheet:olevariant;
i,j,k,row_count,col_count,pri_con:integer;
table_name,fields,temp_str,str,ls,filesname:string;
pri_fields :array of string;
pri_index :array of integer;
seq_name :array of string;
temp_list:tstringlist;
flag1: boolean;//字段的有效性;
pri_bz,fields_count:integer;//有无主键;
arect:trect;
begin
result:=true;
if IsObjectActive('Excel.Application') then
begin
messagebox(0,'"EXCEL",已经打开,请先关闭已经打开的EXCEL文件','FA_提示',mb_iconinformation+mb_taskmodal);
end;
if open1.Execute then
begin
filesname:=open1.FileName;
end
else
begin
result:=false;
exit;
end;
try
try
// reset_mouse(0);
ed_filepath.text:=open1.FileName;
setlength(pri_fields,0);
setlength(pri_fields,15);
setlength(pri_index,0);
setlength(pri_index,15);
setlength(seq_name,0);
setlength(seq_name,15);
excelapp:=CreateOleObject('Excel.Application');
ExcelApp.WorkBooks.Open( open1.FileName );
//excelapp.visible:=true;
except
messagebox(0,'无法连接EXCEL 自动化服务器,'+#10+
'请确认您的系统已经安装EXCEL 2000 及更高版本。','系统错误',mb_ok+mb_iconerror+MB_TASKMODAl);
result:=false;
//reset_mouse(1);
exit;
end;
try
excel_data_list:=tstringlist.Create;
temp_str:=uppercase(trim(excelapp.Cells.Item[1,1]));
table_name:=uppercase(copy(temp_str,1,pos(':',temp_str)-1));
if table_name='' then
begin
messagebox(0,'请确认您打开的EXCEL文件的表头信息是否正确!','警告信息',mb_iconerror+mb_taskmodal);
result:=false;
// reset_mouse(1);
exit;
end;
init.Close;
init.SQL.Text:='select count(*) from user_tables where table_name='''+table_name+'''';
init.Open;
if init.Fields[0].AsInteger<=0 then
begin
messagebox(0,pchar('"'+table_name+'"不在数据库中,请确认EXCEL表头信息是否正确!'),'错误信息-导入失败',mb_iconerror+mb_taskmodal);
result:=false;
//reset_mouse(1);
exit;
end;
excel_data_list.Add(table_name);//增加表名称;
delete(temp_str,1,pos(':',temp_str));
str:=copy(temp_str,1,pos(':',temp_str)-1);
excel_data_list.Add(str);//增加主键名称列表;
if str='' then
pri_bz:=0 //无主键;
else
pri_bz:=1;//有主键;
if pri_bz=1 then
begin
i:=0;
while pos(',',str)>0 do
begin //增加主键字段名;
pri_fields[i]:=copy(str,1,pos(',',str)-1);
delete(str,1,pos(',',str));
inc(i);
end;
pri_fields[i]:=str;//增加最后一个主键字段;
pri_con:=i;
end; delete(temp_str,1,pos(':',temp_str));//字段名列表;
i:=0;
while pos(',',temp_str)>0 do
begin //增加字段名称;
str:=copy(temp_str,1,pos(',',temp_str)-1);
if pos('(',str)>0 then//有seq存在 ;
begin
fields:=fields+copy(str,1,pos('(',str)-1)+','; //增加字段名称;
seq_name[i]:=copy(str,pos('(',str)+1,length(str)-pos('(',str)-1);//确定SEQ的名字;
try
//判断SEQ的有无;
init.Close;
init.SQL.Text:='select sequence_name from user_sequences where sequence_name='+quotedstr(uppercase(trim(seq_name[i])));
init.Open;
if init.Fields[0].AsString='' then
begin
messagebox(handle,pchar('当前数据中不存在"'+seq_name[i]+'"这个序列号!'+#13+
'请确认打开的EXCEL文件的表头信息是否正确'),'错误信息',mb_iconerror);
result:=false;
exit;
end;
except
result:=false;
//reset_mouse(1);
exit;
end;
delete(temp_str,1,pos(')',temp_str)+1);
end
else
begin
fields:=fields+str+',';//增加字段名称;
delete(temp_str,1,pos(',',temp_str));
seq_name[i]:='';
end;
inc(i);
end;
if pos('(',temp_str)>0 then
begin
fields:=fields+copy(temp_str,1,pos('(',temp_str)-1); //增加最后一个字段名称;
seq_name[i]:=copy(temp_str,pos('(',temp_str)+1,length(temp_str)-pos('(',temp_str)-1);//确定SEQ的名字;
try
//判断SEQ的有无;
init.Close;
init.Open;
if init.Fields[0].AsString='' then
begin
messagebox(handle,pchar('当前数据中不存在"'+seq_name[i]+'"这个序列号!'+#13+
'请确认打开的EXCEL文件的表头信息是否正确'),'错误信息',mb_iconerror);
result:=false;
//reset_mouse(1);
exit;
end;
except
result:=false;
//reset_mouse(1);
exit;
end;
end
else
begin
fields:=fields+temp_str;//增加最后一个字段名称;
seq_name[i]:='';
end;
fields_count:=i;
fields:=uppercase(trim(fields));
excel_data_list.Add(fields);//增加字段名称列表;
try //判断COLUMN_NAME;
init.Close;
init.SQL.Text:='select COLUMN_NAME from user_tab_columns where table_name='+quotedstr(table_name);
init.Open;
str:=fields;
temp_list:=tstringlist.Create;
while pos(',',str)>0 do
begin
temp_list.Add(copy(str,1,pos(',',str)-1));//临时存储各字段名称;
delete(str,1,pos(',',str));
end;
temp_list.Add(str);
for i:=0 to temp_list.Count-1 do
begin
init.First;
flag1:=false;
while not init.Eof do
begin
if temp_list.Strings[i]=init.Fields[0].asstring then
begin
flag1:=true;
init.Next;
continue;
end;
init.Next;
end;
if not flag1 then
begin
messagebox(0,pchar('"'+table_name+'"表中没有"'+temp_list.Strings[i]+'"这个字段'+#13+
'请确认你打开的EXCEL文件的表头信息是否正确!'),'警告信息',mb_iconerror+mb_taskmodal);
result:=false;
exit;
end;
end;
except on e:exception do
begin
messagebox(0,pchar('打开'+table_name+'时出现下列错误:'+#13+e.Message),'警告信息',mb_iconerror+mb_taskmodal);
result:=false;
exit;
end;
end; //end 判断COLUMN_NAME;
row_count:=4;
col_count:=1;
while trim(excelapp.Cells[row_count,1])<>'' do
inc(row_count);
progress1.Min:=0;
progress1.Max:=row_count;
//while trim(excelapp.Cells[3,col_count])<>'' do
// inc(col_count);
if pri_bz=1 then //找主健在fields中的位置;
begin
for i:=0 to pri_con do
begin
temp_str:=fields;
k:=1;
while pos(',',temp_str)>0 do
begin
if copy(temp_str,1,pos(',',temp_str)-1)=pri_fields[i] then
begin
pri_index[i]:=k;
break;
end;
delete(temp_str,1,pos(',',temp_str));
inc(k);
end;
if temp_str=pri_fields[i] then
pri_index[i]:=k;
end;
end;//end 找主健在fields中的位置;
progress1.Min:=0;
progress1.Max:=row_count;
for i:=4 to row_count-1 do
begin
progress1.Position:=i;
application.ProcessMessages;
frm_main.Update;
temp_str:='';
str:='';
k:=0;
col_count:=1;
progress1.Step:=row_count;
for j:=1 to fields_count+1 do
begin
ls:=uppercase(trim(excelapp.Cells[i,col_count]));
if seq_name[j-1]<>'' then //如果是用SEQ自动产生;
begin
temp_str:=temp_str+seq_name[j-1]+'.nextval,';
continue;
end;
init.Close;
init.SQL.Text:='select DATA_TYPE from user_tab_columns where table_name='+quotedstr(table_name)+
' and column_name='+quotedstr(temp_list.Strings[j-1]);
init.Open;
if uppercase(init.Fields[0].AsString)='DATE' then
begin
temp_str:=temp_str+'to_date('+quotedstr(ls)+',''yyyy-mm-dd'')'+',';
end
else
begin
temp_str:=temp_str+quotedstr(trim(excelapp.Cells[i,col_count]))+',';
end;
//增加主键的判断条件;
if pri_bz=1 then
begin
for k:=0 to pri_con do
begin //主键;
if j=pri_index[k] then
begin
if uppercase(init.Fields[0].AsString)='DATE' then
begin
str:=str+'to_char('+pri_fields[k]+',''yyyy-mm-dd'')='+quotedstr(ls)+' and ';
end
else
begin
str:=str+pri_fields[k]+'='+quotedstr(ls)+' and ';
end;
end;
end;//end主键;
end;
inc(col_count);
end;
if pri_bz=1 then
begin
delete(str,length(str)-4,4);//删除最后一个and;
excel_data_list.Add(str);//主键的判断条件;
end
else
begin
excel_data_list.Add('');
end;
delete(temp_str,length(temp_str),1);//删除最后一个逗号;
excel_data_list.Add(temp_str);//数据值;
end;
except on e:exception do
begin
messagebox(0,pchar('无法打开该文件!具体错误信息如下:'+#10+
e.message),'FA 错误信息',mb_ok+mb_iconerror+MB_TASKMODAl);
result:=false;
// reset_mouse(1);
exit;
end;
end;
//reset_mouse(1);
finally
excelapp.quit;
end;
end;
var table_name,fields,temp_str,msg:string;
i,j,ins_up_flag,flag_all:integer;
begin
result:=true;
try
myconnection.BeginTrans;
if excel_data_list.Count<=0 then exit;
table_name:=excel_data_list.Strings[0];
fields:=excel_data_list.Strings[2];
flag_all:=0;
ins_up_flag:=0;
Progress1.Max:=excel_data_list.Count-1;
progress1.Min:=0;
for i:=3 to excel_data_list.Count-1 do
begin
progress1.Position:=i;
temp_str:=excel_data_list.Strings[i];
if i mod 2 <>0 then ////奇数;
begin
if temp_str<>'' then
begin
init.Close;
init.SQL.Text:='select count(*) from '+table_name+' where '+temp_str;
init.Open;
end
else
begin//处理没有主键的
ins_up_flag:=0;
continue;
end;
if init.Fields[0].AsInteger>0 then
ins_up_flag:=1 //0:insert;1:update;
else
ins_up_flag:=0;
end
else
begin //偶数行;
ADOQueryTest: TADOQuery);
var
ConnectionString1, Textstring, AStress: string;
num: integer;
begin
if OpenDialog1.Execute then
begin
AStress := OpenDialog1.FileName;
//连接Excel
ConnectionString1 := 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;'
+ 'Data Source=' + AStress + ';Extended Properties=Excel 8.0';
showmessage(ConnectionString1);
ADOConnection2.ConnectionString := ConnectionString1; //打开Excel数据集
adoquery1.close;
adoquery1.sql.clear;
//确定那个sheet
Textstring := 'select * from [' + Caption + '$]'; showmessage(Textstring);
adoquery1.sql.add(Textstring);
adoquery1.open; //打开连接execl表的数据集 adoquery1.first; //移动到第一个记录 //逐条记录添加
while (not adoquery1.eof) do
begin
ADOQueryTest.open;
ADOQueryTest.append;
for num := 0 to adoquery1.FieldCount - 1 do //逐个字段添加
begin
ADOQueryTest.Fields[num].value := adoquery1.Fields[num].value;
//注意,如果是自动增加字段不能如此赋值
end;
ADOQueryTest.post;
adoquery1.next; //移动到下一条记录
end;
end;
end;procedure TForm1.Button5Click(Sender: TObject);
begin
ExcelToData('磨具', ADOQuery2)
end;
lesstif() 仁兄的CommandText := 'select * from [Sheet1]';Fields.FieldByName('..').Value := ADODataSet1.Fields.FieldByName('..').Value;是错误的!!!