excel数据导入到数据库---速度太慢求优化

Unit Unit1;

代码

Interface



Uses

  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.