Excel导入到SQL.docx

1、Excel导入到SQLExcel导入到SQL Excel导入到SQL 时间:2011-5-24来源:yang 作者: peng点击: 334次方法一：通过ADO连接Excel将Excel作为一个数据源，读出Excel中的数据将数据直接插入到SQL中的一个表中，其中ADO的连接字符串是：Provider=Microsoft.Jet.OLEDB.4.0;Password=;Extended Properties=Excel 8.0unit Unit1;interfaceuses Windows, Messages, SysUtils, Variants, Classes, Graphics, Co

2、ntrols, Forms, Dialogs, cxGraphics, dxSkinsCore, dxSkinsDefaultPainters, dxSkinsdxStatusBarPainter, ComCtrls, dxStatusBar, cxControls, DB, ADODB, Grids, DBGrids, StdCtrls, ExtCtrls;type TForm1 = class(TForm) Panel1: TPanel; Button1: TButton; OpenDialog1: TOpenDialog; DBGrid1: TDBGrid; DataSource1: T

3、DataSource; ADOConnection1: TADOConnection; dxStatusBar1: TdxStatusBar; dxStatusBar1Container1: TdxStatusBarContainerControl; ProgressBar1: TProgressBar; ADOQuery1: TADOQuery; ADOConnection2: TADOConnection; ADOQuery2: TADOQuery; Edit1: TEdit; Label3: TLabel; dxStatusBar1Container0: TdxStatusBarCont

4、ainerControl; Label1: TLabel; Label2: TLabel; Label4: TLabel; DateTimePicker1: TDateTimePicker; DateTimePicker2: TDateTimePicker; procedure Button1Click(Sender: TObject); private Private declarations public Public declarations end;var Form1: TForm1;implementation$R *.dfmprocedure TForm1.Button1Click

5、(Sender: TObject);var i:Integer;plan,stattime,endtime:string;/username:string;MyExcelFile:string;beginOpenDialog1.Title:= 请选择相应的Excel文件;OpenDialog1.Filter:=Excel(*.xls)|*.xls;trybeginif OpenDialog1.Execute thenMyExcelFile :=OpenDialog1.FileName;adoconnection1.Connected:=false;ADOConnection1.Close;AD

6、OConnection1.ConnectionString:=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=+MyExcelFile+;Extended Properties=excel 8.0;Persist Security Info=False;ADOConnection1.Connected:=true;adoquery1.Close;ADOQuery1.SQL.Clear;adoquery1.SQL.Add(SELECT FPNumber,Fnumber,Fscrap*100 as Fscrap,Fmax*100 as Fmax,Fmin*

7、100 as Fmin FROM sheet1$);adoquery1.Open;ProgressBar1.Max:=ADOQuery1.RecordCount;Label1.Caption:=0;for i:=1 to ADOQuery1.RecordCount dobeginApplication.ProcessMessages;self.refresh;ADOQuery2.Close;ADOQuery2.SQL.Clear;ADOQuery2.SQL.Add(select * from f_lossrate2 );ADOQuery2.Open;Label1.Caption:=IntToS

8、tr(StrTOint(Label1.caption)+1);ADOQuery2.Close;ADOQuery2.SQL.Clear;plan:=edit1.text;stattime:=FormatDateTime(yyyy-MM-dd,DateTimePicker1.Date);endtime:=FormatDateTime(yyyy-MM-dd,DateTimePicker2.Date);ADOQuery2.SQL.Add(insert into f_lossrate2(planName,pfnumber,fnumber,lowloss,midloss,bigloss,begineffe

9、ct,endeffect);ADOQuery2.SQL.Add(Values(+QuotedStr(plan)+,+QuotedStr(ADOQuery1.fieldbyName(FPNumber).AsString)+,+QuotedStr(ADOQuery1.fieldbyName(Fnumber).AsString)+,+QuotedStr(ADOQuery1.fieldbyName(Fscrap).AsString)+,+ QuotedStr(ADOQuery1.fieldbyName(Fmax).AsString)+,+QuotedStr(ADOQuery1.fieldbyName(

10、Fmin).AsString)+,+QuotedStr(stattime)+,+QuotedStr(endtime)+);tryADOQuery2.ExecSQL;exceptend;Application.ProcessMessages;ADOQuery1.Next;ProgressBar1.Position :=i;end;adoquery2.Active:=false;adoquery2.Close;adoquery2.SQL.Add(select * from f_lossrate2);adoquery2.Open;adoquery2.Active:=true;Application.

11、MessageBox(导入完毕！,提示,MB_OK+MB_Iconinformation);end;exceptApplication.MessageBox(打开Excel失败，错误；请重启软件再试。,错误信息,MB_Ok);end;end;end.方法二：直接读出Excel数据，但是要将单元格跟SQL中的表中的字段一一对应起来unit Unit1;interfaceuses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, cxGraphics, dxSkinsCore, d

12、xSkinsDefaultPainters, dxSkinsdxStatusBarPainter, ComCtrls, dxStatusBar, cxControls, DB, ADODB, Grids, DBGrids, StdCtrls,ComObj, ExtCtrls;type TForm1 = class(TForm) Panel1: TPanel; Button1: TButton; OpenDialog1: TOpenDialog; DBGrid1: TDBGrid; DataSource1: TDataSource; ADOConnection1: TADOConnection;

13、 dxStatusBar1: TdxStatusBar; dxStatusBar1Container1: TdxStatusBarContainerControl; ProgressBar1: TProgressBar; ADOQuery1: TADOQuery; ADOConnection2: TADOConnection; ADOQuery2: TADOQuery; Edit1: TEdit; Label3: TLabel; dxStatusBar1Container0: TdxStatusBarContainerControl; Label1: TLabel; Label2: TLabe

14、l; Label4: TLabel; DateTimePicker1: TDateTimePicker; DateTimePicker2: TDateTimePicker; procedure Button1Click(Sender: TObject); private Private declarations public Public declarations end;var Form1: TForm1;implementation$R *.dfmprocedure TForm1.Button1Click(Sender: TObject);const BeginRow = 2; Begin

15、Col = 1;var Excel: OleVariant; iRow,iCol : integer; xlsFilename: string; filename:string;beginOpenDialog1.Title := 请选择相应的Excel文件; OpenDialog1.Filter := Excel(*.xls)|*.xls; if OpenDialog1.Execute then filename := OpenDialog1.FileName;if (trim(edit1.Text) = ) then begin MessageBox(GetActiveWindow(), 方

16、案名不能为空！, 警告, MB_OK + MB_ICONWARNING); exit; end; xlsFilename := trim(filename); 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); tr

17、y iRow := BeginRow; iCol := BeginCol; while trim(Excel.WorkSheets1.CellsiRow,iCol.value) <> do begin with ADOQuery2 do begin Append; /Fields0.AsString := trim(Excel.WorkSheets1.CellsiRow,iCol.value); Fields1.AsString := trim(edit1.Text); Fields2.Asstring := trim(Excel.WorkSheets1.CellsiRow,iCo

18、l.value); Fields3.Asstring := trim(Excel.WorkSheets1.CellsiRow,iCol+2.value); Fields4.AsString := trim(Excel.WorkSheets1.CellsiRow,iCol+1.value); Fields5.Asstring := trim(Excel.WorkSheets1.CellsiRow,iCol+3.value); Fields6.Asstring := trim(Excel.WorkSheets1.CellsiRow,iCol+4.value); Fields7.AsString :

19、= trim(Excel.WorkSheets1.CellsiRow,iCol+5.value); Fields8.Asstring := trim(FormatDateTime(yyyy-MM-dd,DateTimePicker1.Date); Fields9.Asstring := trim(FormatDateTime(yyyy-MM-dd,DateTimePicker2.Date); Fields10.AsString := trim(FormatDateTime(yyyy-MM-dd,now); Fields11.AsString := trim(Excel.WorkSheets1.

20、CellsiRow,iCol+11.value); Fields12.AsString := trim(Excel.WorkSheets1.CellsiRow,iCol+12.value); iRow := iRow + 1; Label1.Caption:=导入记录:+inttostr(iRow-1)+条; end; end; Excel.Quit; ADOQuery2.UpdateStatus ; except Application.MessageBox(导入数据出错！请检查文件的格式是否正确！, 提示信息, MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL); Excel.Quit; end; MessageBox(GetActiveWindow(), 数据导入成功!, 提示, MB_OK + MB_ICONWARNING);end;end.