Excel导入到SQL.docx
《Excel导入到SQL.docx》由会员分享，可在线阅读，更多相关《Excel导入到SQL.docx（7页珍藏版）》请在冰豆网上搜索。
Excel导入到SQL
Excel导入到SQL
Excel导入到SQL
时间:
2011-5-24来源:
yang作者:
peng点击:
334次
方法一：
通过ADO连接Excel将Excel作为一个数据源，读出Excel中的数据将数据直接插入到SQL中的一个表中，
其中ADO的连接字符串是：
Provider=Microsoft.Jet.OLEDB.4.0;Password="";ExtendedProperties=Excel8.0unitUnit1;interfaceuses
Windows,Messages,SysUtils,Variants,Classes,Graphics,Controls,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:
TDataSource;
ADOConnection1:
TADOConnection;
dxStatusBar1:
TdxStatusBar;
dxStatusBar1Container1:
TdxStatusBarContainerControl;
ProgressBar1:
TProgressBar;
ADOQuery1:
TADOQuery;
ADOConnection2:
TADOConnection;
ADOQuery2:
TADOQuery;
Edit1:
TEdit;
Label3:
TLabel;
dxStatusBar1Container0:
TdxStatusBarContainerControl;
Label1:
TLabel;
Label2:
TLabel;
Label4:
TLabel;
DateTimePicker1:
TDateTimePicker;
DateTimePicker2:
TDateTimePicker;
procedureButton1Click（Sender:
TObject）;
private
{Privatedeclarations}
public
{Publicdeclarations}
end;var
Form1:
TForm1;implementation{$R*.dfm}procedureTForm1.Button1Click（Sender:
TObject）;
var
i:
Integer;
plan,stattime,endtime:
string;
//username:
string;
MyExcelFile:
string;
begin
OpenDialog1.Title:
=‘请选择相应的Excel文件‘;
OpenDialog1.Filter:
=‘Excel（*.xls）|*.xls‘;
try
begin
ifOpenDialog1.Executethen
MyExcelFile:
=OpenDialog1.FileName;
adoconnection1.Connected:
=false;
ADOConnection1.Close;
ADOConnection1.ConnectionString:
=‘Provider=Microsoft.Jet.OLEDB.4.0;DataSource=‘+MyExcelFile+‘;ExtendedProperties=excel8.0;PersistSecurityInfo=False‘;
ADOConnection1.Connected:
=true;
adoquery1.Close;
ADOQuery1.SQL.Clear;
adoquery1.SQL.Add（‘SELECTFPNumber,Fnumber,Fscrap*100asFscrap,Fmax*100asFmax,Fmin*100asFminFROM[sheet1$]‘）;
adoquery1.Open;
ProgressBar1.Max:
=ADOQuery1.RecordCount;
Label1.Caption:
=‘0‘;
fori:
=1toADOQuery1.RecordCountdo
begin
Application.ProcessMessages;
self.refresh;
ADOQuery2.Close;
ADOQuery2.SQL.Clear;
ADOQuery2.SQL.Add（‘select*fromf_lossrate2‘）;
ADOQuery2.Open;
Label1.Caption:
=IntToStr（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（‘insertintof_lossrate2（planName,pfnumber,fnumber,lowloss,midloss,bigloss,begineffect,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（‘Fmin‘）.AsString）+‘,‘+QuotedStr（stattime）+‘,‘+QuotedStr（endtime）+‘）‘）;
try
ADOQuery2.ExecSQL;
except
end;
Application.ProcessMessages;
ADOQuery1.Next;
ProgressBar1.Position:
=i;
end;
adoquery2.Active:
=false;
adoquery2.Close;
adoquery2.SQL.Add（‘select*fromf_lossrate2‘）;
adoquery2.Open;
adoquery2.Active:
=true;
Application.MessageBox（‘导入完毕！
‘,‘提示‘,MB_OK+MB_Iconinformation）;
end;
except
Application.MessageBox（‘打开Excel失败，错误；请重启软件再试。
‘,‘错误信息‘,MB_Ok）;
end;
end;end.方法二：
直接读出Excel数据，但是要将单元格跟SQL中的表中的字段一一对应起来
unitUnit1;interfaceuses
Windows,Messages,SysUtils,Variants,Classes,Graphics,Controls,Forms,
Dialogs,cxGraphics,dxSkinsCore,dxSkinsDefaultPainters,
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;
dxStatusBar1:
TdxStatusBar;
dxStatusBar1Container1:
TdxStatusBarContainerControl;
ProgressBar1:
TProgressBar;
ADOQuery1:
TADOQuery;
ADOConnection2:
TADOConnection;
ADOQuery2:
TADOQuery;
Edit1:
TEdit;
Label3:
TLabel;
dxStatusBar1Container0:
TdxStatusBarContainerControl;
Label1:
TLabel;
Label2:
TLabel;
Label4:
TLabel;
DateTimePicker1:
TDateTimePicker;
DateTimePicker2:
TDateTimePicker;
procedureButton1Click（Sender:
TObject）;
private
{Privatedeclarations}
public
{Publicdeclarations}
end;var
Form1:
TForm1;implementation{$R*.dfm}procedureTForm1.Button1Click（Sender:
TObject）;
const
BeginRow=2;BeginCol=1;
var
Excel:
OleVariant;
iRow,iCol:
integer;
xlsFilename:
string;
filename:
string;
begin
OpenDialog1.Title:
=‘请选择相应的Excel文件‘;
OpenDialog1.Filter:
=‘Excel（*.xls）|*.xls‘;
ifOpenDialog1.Executethen
filename:
=OpenDialog1.FileName;
if（trim（edit1.Text）=‘‘）then
begin
MessageBox（GetActiveWindow（）,‘方案名不能为空！
‘,‘警告‘,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）;
try
iRow:
=BeginRow;
iCol:
=BeginCol;
whiletrim（Excel.WorkSheets[1].Cells[iRow,iCol].value）<>‘‘dobegin
withADOQuery2dobegin
Append;
//Fields[0].AsString:
=trim（Excel.WorkSheets[1].Cells[iRow,iCol].value）;
Fields[1].AsString:
=trim（edit1.Text）;
Fields[2].Asstring:
=trim（Excel.WorkSheets[1].Cells[iRow,iCol].value）;
Fields[3].Asstring:
=trim（Excel.WorkSheets[1].Cells[iRow,iCol+2].value）;
Fields[4].AsString:
=trim（Excel.WorkSheets[1].Cells[iRow,iCol+1].value）;
Fields[5].Asstring:
=trim（Excel.WorkSheets[1].Cells[iRow,iCol+3].value）;
Fields[6].Asstring:
=trim（Excel.WorkSheets[1].Cells[iRow,iCol+4].value）;
Fields[7].AsString:
=trim（Excel.WorkSheets[1].Cells[iRow,iCol+5].value）;
Fields[8].Asstring:
=trim（FormatDateTime（‘yyyy-MM-dd‘,DateTimePicker1.Date））;
Fields[9].Asstring:
=trim（FormatDateTime（‘yyyy-MM-dd‘,DateTimePicker2.Date））;
Fields[10].AsString:
=trim（FormatDateTime（‘yyyy-MM-dd‘,now））;
Fields[11].AsString:
=trim（Excel.WorkSheets[1].Cells[iRow,iCol+11].value）;
Fields[12].AsString:
=trim（Excel.WorkSheets[1].Cells[iRow,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.
升级会员 