我先说一下下面代码的思路,Button1先把excel数据导入到dbgrid中,Button2把dbgird的数据导入到数据库有4000条数据,需要1分钟,速度太慢了,请各位大虾帮忙看看如嫌分少,问题完美解决了,我在散100分!Unit Unit1;
代码
InterfaceUses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, ADODB, StdCtrls, Grids, DBGrids, ComObj;Type
TForm1 = Class(TForm)
DBGrid1: TDBGrid;
Button1: TButton;
Button2: TButton;
ADOConnection1: TADOConnection;
ADOQuery1: TADOQuery;
DataSource1: TDataSource;
OpenDialog1: TOpenDialog;
Procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
PRIVATE
{ Private declarations }
PUBLIC
{ Public declarations }
End;Var
Form1: TForm1;Implementation{$R *.dfm}Procedure TForm1.Button1Click(Sender: TObject);
Var
ASql, APath: String;
ExcelApp: Variant;
AInt: Integer;
Begin
If OpenDialog1.Execute Then
APath := OpenDialog1.FileName
Else
Exit; If not FileExists(APath) Then
Begin
ShowMessage('您所选择的文件不存在!');
Exit;
End;
Try
//ADOQuery1.DisableControls;
ExcelApp := CreateOleObject('Excel.Application');
ExcelApp.Workbooks.Open(APath);
ExcelApp.WorkSheets[1].Activate; ASql := 'select * from stock where 1=2'; //表名 ,也可以在show里面先打开数据集
ADOQuery1.Close; //数据集的 LockType属性 要设为 ltBatchOptimistic
ADOQuery1.SQL.Text := ASql; //才可以批量保存
ADOQuery1.Open;
// DataSource1.DataSet:=nil; For AInt := 2 To ExcelApp.ActiveSheet.UsedRange.rows.count Do //AInt 起始行数,重哪行开始导入,Aint初始值就是几
Begin
ADOQuery1.Append;
ADOQuery1.FieldByName('st_id').AsString := ExcelApp.Cells[AInt, 1].Value; //Aint是第几行,后面的数字是第几列,
ADOQuery1.FieldByName('st_dcdate').AsDateTime := ExcelApp.Cells[AInt, 2].Value;
ADOQuery1.FieldByName('st_gc').AsString := ExcelApp.Cells[AInt, 3].Value;
ADOQuery1.FieldByName('st_kcd').AsString := ExcelApp.Cells[AInt, 4].Value;
ADOQuery1.FieldByName('st_zt').AsString := ExcelApp.Cells[AInt, 5].Value;
ADOQuery1.FieldByName('st_lx').AsString := ExcelApp.Cells[AInt, 6].Value;
ADOQuery1.FieldByName('st_sx').AsString := ExcelApp.Cells[AInt, 7].Value;
ADOQuery1.FieldByName('st_gys').AsString := ExcelApp.Cells[AInt, 8].Value;
ADOQuery1.FieldByName('st_sku').AsString := ExcelApp.Cells[AInt, 9].Value;
ADOQuery1.FieldByName('st_spmc').AsString := ExcelApp.Cells[AInt, 10].Value;
ADOQuery1.FieldByName('st_pch').AsString := ExcelApp.Cells[AInt, 11].Value;
ADOQuery1.FieldByName('st_tkdate').AsDateTime := ExcelApp.Cells[AInt, 12].Value;
ADOQuery1.FieldByName('st_kw').AsString := ExcelApp.Cells[AInt, 13].Value;
ADOQuery1.FieldByName('st_qty').AsString := ExcelApp.Cells[AInt, 14].Value;
ADOQuery1.FieldByName('st_bzs').AsString := ExcelApp.Cells[AInt, 15].Value;
ADOQuery1.FieldByName('st_tj').AsSingle := ExcelApp.Cells[AInt, 16].Value;
ADOQuery1.FieldByName('st_zl').AsFloat := ExcelApp.Cells[AInt, 17].Value;
ADOQuery1.FieldByName('st_tps').AsFloat := ExcelApp.Cells[AInt, 18].Value;
ADOQuery1.FieldByName('st_mj').AsFloat := ExcelApp.Cells[AInt, 19].Value;
ADOQuery1.FieldByName('st_kcts').AsString := ExcelApp.Cells[AInt, 20].Value;
ADOQuery1.FieldByName('st_bz').AsString := ExcelApp.Cells[AInt, 21].Value;
//因为你的excel是固定格式,所以,这里需要写死;
ADOQuery1.Post;
End;
//ADOQuery1.EnableControls;
Finally
ExcelApp.WorkBooks.Close;
ExcelApp.quit;
ShowMessage('读取库存数据成功 ');
End;
End;procedure TForm1.Button2Click(Sender: TObject);
begin
try
ADOQuery1.UpdateBatch;
ShowMessage('数据导入成功');
except
ShowMessage('数据库存储错误!');
end;
end;End.
代码
InterfaceUses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, ADODB, StdCtrls, Grids, DBGrids, ComObj;Type
TForm1 = Class(TForm)
DBGrid1: TDBGrid;
Button1: TButton;
Button2: TButton;
ADOConnection1: TADOConnection;
ADOQuery1: TADOQuery;
DataSource1: TDataSource;
OpenDialog1: TOpenDialog;
Procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
PRIVATE
{ Private declarations }
PUBLIC
{ Public declarations }
End;Var
Form1: TForm1;Implementation{$R *.dfm}Procedure TForm1.Button1Click(Sender: TObject);
Var
ASql, APath: String;
ExcelApp: Variant;
AInt: Integer;
Begin
If OpenDialog1.Execute Then
APath := OpenDialog1.FileName
Else
Exit; If not FileExists(APath) Then
Begin
ShowMessage('您所选择的文件不存在!');
Exit;
End;
Try
//ADOQuery1.DisableControls;
ExcelApp := CreateOleObject('Excel.Application');
ExcelApp.Workbooks.Open(APath);
ExcelApp.WorkSheets[1].Activate; ASql := 'select * from stock where 1=2'; //表名 ,也可以在show里面先打开数据集
ADOQuery1.Close; //数据集的 LockType属性 要设为 ltBatchOptimistic
ADOQuery1.SQL.Text := ASql; //才可以批量保存
ADOQuery1.Open;
// DataSource1.DataSet:=nil; For AInt := 2 To ExcelApp.ActiveSheet.UsedRange.rows.count Do //AInt 起始行数,重哪行开始导入,Aint初始值就是几
Begin
ADOQuery1.Append;
ADOQuery1.FieldByName('st_id').AsString := ExcelApp.Cells[AInt, 1].Value; //Aint是第几行,后面的数字是第几列,
ADOQuery1.FieldByName('st_dcdate').AsDateTime := ExcelApp.Cells[AInt, 2].Value;
ADOQuery1.FieldByName('st_gc').AsString := ExcelApp.Cells[AInt, 3].Value;
ADOQuery1.FieldByName('st_kcd').AsString := ExcelApp.Cells[AInt, 4].Value;
ADOQuery1.FieldByName('st_zt').AsString := ExcelApp.Cells[AInt, 5].Value;
ADOQuery1.FieldByName('st_lx').AsString := ExcelApp.Cells[AInt, 6].Value;
ADOQuery1.FieldByName('st_sx').AsString := ExcelApp.Cells[AInt, 7].Value;
ADOQuery1.FieldByName('st_gys').AsString := ExcelApp.Cells[AInt, 8].Value;
ADOQuery1.FieldByName('st_sku').AsString := ExcelApp.Cells[AInt, 9].Value;
ADOQuery1.FieldByName('st_spmc').AsString := ExcelApp.Cells[AInt, 10].Value;
ADOQuery1.FieldByName('st_pch').AsString := ExcelApp.Cells[AInt, 11].Value;
ADOQuery1.FieldByName('st_tkdate').AsDateTime := ExcelApp.Cells[AInt, 12].Value;
ADOQuery1.FieldByName('st_kw').AsString := ExcelApp.Cells[AInt, 13].Value;
ADOQuery1.FieldByName('st_qty').AsString := ExcelApp.Cells[AInt, 14].Value;
ADOQuery1.FieldByName('st_bzs').AsString := ExcelApp.Cells[AInt, 15].Value;
ADOQuery1.FieldByName('st_tj').AsSingle := ExcelApp.Cells[AInt, 16].Value;
ADOQuery1.FieldByName('st_zl').AsFloat := ExcelApp.Cells[AInt, 17].Value;
ADOQuery1.FieldByName('st_tps').AsFloat := ExcelApp.Cells[AInt, 18].Value;
ADOQuery1.FieldByName('st_mj').AsFloat := ExcelApp.Cells[AInt, 19].Value;
ADOQuery1.FieldByName('st_kcts').AsString := ExcelApp.Cells[AInt, 20].Value;
ADOQuery1.FieldByName('st_bz').AsString := ExcelApp.Cells[AInt, 21].Value;
//因为你的excel是固定格式,所以,这里需要写死;
ADOQuery1.Post;
End;
//ADOQuery1.EnableControls;
Finally
ExcelApp.WorkBooks.Close;
ExcelApp.quit;
ShowMessage('读取库存数据成功 ');
End;
End;procedure TForm1.Button2Click(Sender: TObject);
begin
try
ADOQuery1.UpdateBatch;
ShowMessage('数据导入成功');
except
ShowMessage('数据库存储错误!');
end;
end;End.
var s:string;
ADOConn:TADOConnection;
aDataSet:TADODataSet;
begin
if OpenDialog1.Execute then
begin
ADOConn:=TADOConnection.Create(self);
ADOConn.LoginPrompt:=false;
ADOConn.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
+OpenDialog1.FileName
+';Extended Properties=Excel 8.0;Persist Security Info=False';
aDataSet:=TADODataSet.Create(self);
aDataSet.Connection:=ADOConn;
ADOConn.OpenSchema(siTables,EmptyParam,EmptyParam,aDataSet);
aDataSet.Last;
s:=aDataSet.Fields.Fields[2].AsString;//获取第一工作表表名
aDataSet.Close;
aDataSet.Free;
ADOConn.Close;
ADOConn.Free; ADOQuery2.Connection:=ADOConnection1;
ADOQuery2.SQL.Clear;
ADOQuery2.SQL.Append('select * into qqq from OPENROWSET(');//将这个‘qqq’字改为你的数据库中的表名
ADOQuery2.SQL.Append(quotedstr('MICROSOFT.JET.OLEDB.4.0'));
ADOQuery2.SQL.Append(','+quotedstr('Excel 5.0;HDR=YES;Excel 8.0;DATABASE='+OpenDialog1.FileName)+',['+s+'])');
ADOQuery2.ExecSQL;
showmessage('ok');
end;
end;procedure TForm1.Button3Click(Sender: TObject);//在数据库中已经存在表 qqq
var s:string;
ADOConn:TADOConnection;
aDataSet:TADODataSet;
begin
if OpenDialog1.Execute then
begin
ADOConn:=TADOConnection.Create(self);
ADOConn.LoginPrompt:=false;
ADOConn.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
+OpenDialog1.FileName
+';Extended Properties=Excel 8.0;Persist Security Info=False';
aDataSet:=TADODataSet.Create(self);
aDataSet.Connection:=ADOConn;
ADOConn.OpenSchema(siTables,EmptyParam,EmptyParam,aDataSet);
aDataSet.Last;
s:=aDataSet.Fields.Fields[2].AsString;//获取第一工作表表名
aDataSet.Close;
aDataSet.Free;
ADOConn.Close;
ADOConn.Free; ADOQuery2.Connection:=ADOConnection1;
ADOQuery2.SQL.Clear;
ADOQuery2.SQL.Append('insert into qqq select * from OPENROWSET(');//将这个‘qqq’字改为你的数据库中的表名
ADOQuery2.SQL.Append(quotedstr('MICROSOFT.JET.OLEDB.4.0'));
ADOQuery2.SQL.Append(','+quotedstr('Excel 5.0;HDR=YES;Excel 8.0;DATABASE='+OpenDialog1.FileName)+',['+s+'])');
ADOQuery2.ExecSQL;
showmessage('ok');
end;
end;