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;