Delphi excel导入到数据库

uses ComObj;

procedure TMemo_form_form.N5Click(Sender: TObject);

var

  ExcelApp: Variant;//EXCEL打开对象

  link:string;

  i:integer;

begin

  if dlgOpen1.Execute then

  begin

   link:=dlgOpen1.FileName;

     try

        ExcelApp:= CreateOleObject('Excel.Application');

       ExcelApp.WorkBooks.Open(link);

       {打开EXCEL文件，不然提示外部表不是预期的格式}

       con1.Connected:=False ;

       qry1.Active :=False ;

        //con1:ADOConnection   

       con1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Password="";DataSource=';

       con1.ConnectionString:=con1.ConnectionString+link;

       con1.ConnectionString:=con1.ConnectionString+';ExtendedProperties=excel 8.0;Persist Security Info=false';

       con1.LoginPrompt:=False ;

       con1.Connected:=True;

        //qry1:ADOQuery  

       qry1.close;

       qry1.SQL.Clear;

       qry1.sql.Add('SELECT * FROM [sjdr$]');

       qry1.open;

       if Qry1.RecordCount > 0 then

       begin

        for i:=1 to qry1.RecordCount  do

        begin

          Application.ProcessMessages;

          self.refresh;

          ClientDataSet5.Close;

          ClientDataSet5.CommandText:='insert into czxdb(IDBH,NUM,CZXDNR,CZTIME,CZXDBT,CZBZMC)values(:a,:b,:c,:d,:e,:f)';

          ClientDataSet5.Params[0].AsString :=qry1.FieldByName('IDBH').AsString;

          ClientDataSet5.Params[1].AsString :=qry1.FieldByName('NUM').AsString;

          ClientDataSet5.Params[2].AsString :=qry1.FieldByName('CZXDNR').AsString;

          ClientDataSet5.Params[3].AsString :=qry1.FieldByName('CZTIME').AsString;

          ClientDataSet5.Params[4].AsString :=qry1.FieldByName('CZXDBT').AsString;

          ClientDataSet5.Params[5].AsString :=qry1.FieldByName('CZBZMC').AsString;

          try

            ClientDataSet5.Execute;

          except

          end;

          Application.ProcessMessages;

          Qry1.Next;

         end;

       end;

       try

         ClientDataSet5.Execute;

         ShowMessage('导入成功');

       Except

         ShowMessage('导入失败');

       end;

       {对EXCEL文件存盘，关闭文件,退出}

       ExcelApp.ActiveWorkBook.Saved := True;

       {对EXCEL文件存盘，关闭文件,退出}

     Finally

       ExcelApp.WorkBooks.Close;

       ExcelApp.Quit;

     end;

  end;

end;

//excel 读到 dbgrid

var

  str:string;

begin

  str:='select * from opendatasource(''microsoft.jet.oledb.4.0'',''data source="文件路径";user id=admin;password=;extened properties=excel 5.0'')...[sheet1$]';

  adoquery.close;

  adoquery.sql.clear;

  adoquery.sql.add(str);

  adoquery.active:=true;

  adoquery.open;

end;

//dbgrid 导入数据表

var

  str:string;

begin

  str:='select *  into t from opendatasource(''microsoft.jet.oledb.4.0'',''data source="文件路径";user id=admin;password=;extened properties=excel 5.0'')...[sheet1$]';

  with adoquery do

    begin

      close;

      sql.clear;

      sql.add(str);

      execsql;

      if rowsaffected>0 then

        showmessage('插入成功')

      else

        showmessage(插入失败);

    end;

end;

procedure TForm1.btnClick(Sender: TObject);

begin

  OpenDialog1.Title := '请选择正确的excel文件';

  OpenDialog1.Filter := 'Excel(*.xls)|*.xls';

  if OpenDialog1.Execute then

  edit1.Text := OpenDialog1.FileName;

end;

procedure TForm1.btninClick(Sender: TObject);

const

  BeginRow = 2; BeginCol = 1;

var

  Excel: OleVariant;

  iRow,iCol : integer;

  xlsFilename: string;

begin

if (trim(edit1.Text) = '') then

  begin

  MessageBox(GetActiveWindow(), 请选择正确的excel路径', MB_OK +

  MB_ICONWARNING);

  exit;

  end;

  xlsFilename := trim(edit1.Text);

  try

  Excel := CreateOLEObject('Excel.Application');

  except

  Application.MessageBox('excel没有安装', '提示信息', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);

  Exit;

  end;

  Excel.Visible := false;

  Excel.WorkBooks.Open(xlsFilename);

  try

  iRow := BeginRow;

  iCol := BeginCol;

  while trim(Excel.WorkSheets[1].Cells[iRow,iCol].value) <> '' do begin

  with ADOQuery1 do begin

  Append;

  Fields[0].AsString := trim(Excel.WorkSheets[1].Cells[iRow,iCol].value);

  Fields[1].AsString := trim(Excel.WorkSheets[1].Cells[iRow,iCol+1].value);

  Fields[2].Asstring := trim(Excel.WorkSheets[1].Cells[iRow,iCol+2].value);

  iRow := iRow + 1;

  end;

  end;

  Excel.Quit;

  ADOQuery1.UpdateStatus ;

  except

  Application.MessageBox('导入数据出错', '提示信息', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);

  Excel.Quit;

  end;

  MessageBox(GetActiveWindow(), '数据导入成功', '提示信息', MB_OK +

  MB_ICONWARNING);

end;