Excel导入到SQL.docx
- 文档编号:7870480
- 上传时间:2023-05-12
- 格式:DOCX
- 页数:7
- 大小:16.04KB
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.
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 导入 SQL