用delphi把excel表数据导入oracle数据库时,有没有更好的办法,把速度提高一些,我的代码是:
var
excelx,excely:String;
ExcelApp,WorkBook:variant;
sFileName:String;
i,ExcelRowCount,Row:Integer;
begin
try
with OpenDialog1 do
begin
OpenDialog1.DefaultExt:='xls';
OpenDialog1.Filter:='Excel工作簿文件(*.xls)|*.xls';
if OpenDialog1.Execute then
begin
sFileName:=OpenDialog1.FileName;
end
else
exit;
end;
Row:=0;
ExcelApp := CreateOleObject('Excel.Application');
WorkBook:=ExcelApp.WorkBooks.Open(sFileName);
ExcelApp.visible:=false;
ExcelRowCount := WorkBook.WorkSheets[1].UsedRange.Rows.Count;
for i:=1 to ExcelRowCount+1 do
begin
excelx:=Excelapp.cells[i,1].value;
excely:=Excelapp.cells[i,2].value;
if (excelx='') and (excely='') then
exit
else
with ADOQuery1 do
begin
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('insert into test(name,address)values(');
SQL.Add(''''+excelx+''','''+excely+''')');
Row:=Row+1;
ADOQuery1.ExecSQL;
end;
end;
finally
ShowMessage('成功导入'+IntTostr(Row)+'条数据');
workBook.close;
excelapp.quit;
ExcelApp := Unassigned;
WorkBook := Unassigned;
end;
var
excelx,excely:String;
ExcelApp,WorkBook:variant;
sFileName:String;
i,ExcelRowCount,Row:Integer;
begin
try
with OpenDialog1 do
begin
OpenDialog1.DefaultExt:='xls';
OpenDialog1.Filter:='Excel工作簿文件(*.xls)|*.xls';
if OpenDialog1.Execute then
begin
sFileName:=OpenDialog1.FileName;
end
else
exit;
end;
Row:=0;
ExcelApp := CreateOleObject('Excel.Application');
WorkBook:=ExcelApp.WorkBooks.Open(sFileName);
ExcelApp.visible:=false;
ExcelRowCount := WorkBook.WorkSheets[1].UsedRange.Rows.Count;
for i:=1 to ExcelRowCount+1 do
begin
excelx:=Excelapp.cells[i,1].value;
excely:=Excelapp.cells[i,2].value;
if (excelx='') and (excely='') then
exit
else
with ADOQuery1 do
begin
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('insert into test(name,address)values(');
SQL.Add(''''+excelx+''','''+excely+''')');
Row:=Row+1;
ADOQuery1.ExecSQL;
end;
end;
finally
ShowMessage('成功导入'+IntTostr(Row)+'条数据');
workBook.close;
excelapp.quit;
ExcelApp := Unassigned;
WorkBook := Unassigned;
end;
可以通过ADO连接Excel,作为一张数据库表用Select SQL语句来读取
连接串的写法是:
Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=filename.xls;Mode=Share Deny None;Extended Properties=Excel 8.0;