delphi 导入Excel到数据库
转载来源:http://gaowenjie7758.blog.163.com/blog/static/5946788920095951348322/
procedure Tfrmchanpin_daoru.btn2Click(Sender: TObject);
// uses Comobj; //
// Excel 导入到数据库 //
// 高文杰 2009.6.22 //
var
ExcelApp:variant;
n1:string;
i,j:integer;
FileName:string;
myfloat:double;
OpenDialog:TOpenDialog;
begin
OpenDialog:=TOpenDialog.Create(nil);
OpenDialog.Filter:='Excel文件|*.xls';
if not OpenDialog.Execute then begin
OpenDialog.Free;
exit;
end;
FileName := OpenDialog.FileName;
ExcelApp:=CreateOleObject('Excel.Application');
ExcelApp.visible:=False;
ExcelApp.workbooks.open(FileName);
//自适应宽度
ExcelApp.worksheets[1].Cells.EntireColumn.AutoFit; //整个表所有列
j:=2; //假定标题在第一行
try
with qry1 do
begin
n1:='insert into xcm_chanpin_info(dl_dm,xl_dm,cp_dm,cp_name,cp_jldw,cp_jj,cp_sj,cp_isZengPin)'
+'values(:dl_dm,:xl_dm,:cp_dm,:cp_name,:cp_jldw,:cp_jj,:cp_sj,:cp_isZengPin)';
close;
sql.Clear;
sql.Add(n1);
while ExcelApp.worksheets[1].cells[j,1].text<>'' do
begin
for i:=1 to 8 do begin //共8列
Parameters.Items[i-1].Value:=ExcelApp.worksheets[1].cells[j,i].text;
if i=1 then begin
//检查大类是否存在
qry2.Close;
qry2.SQL.Text:='select * from xcm_dalei_info where dl_dm='''+ExcelApp.worksheets[1].cells[j,i].text+'''';
qry2.Prepared;
qry2.Open;
if qry2.Eof then begin
mmo1.Lines.Add('导入第'+IntToStr(j)+'行时,产品大类不存在,请检查');
ExcelApp.Activeworkbook.close(false);
ExcelApp.quit;
ExcelApp:=unassigned;
OpenDialog.Free;
exit;
end;
end;
if i=2 then begin
//检查小类是否存在
qry2.Close;
qry2.SQL.Text:='select * from xcm_xiaolei_info where xl_dm='''+ExcelApp.worksheets[1].cells[j,i].text+'''';
qry2.Prepared;
qry2.Open;
if qry2.Eof then begin
mmo1.Lines.Add('导入第'+IntToStr(j)+'行时,产品小类不存在,请检查');
ExcelApp.Activeworkbook.close(false);
ExcelApp.quit;
ExcelApp:=unassigned;
OpenDialog.Free;
exit;
end;
end;
if i=3 then begin
//检查产品编号是否已经存在
qry2.Close;
qry2.SQL.Text:='select * from xcm_chanpin_info where cp_dm='''+ExcelApp.worksheets[1].cells[j,i].text+'''';
qry2.Prepared;
qry2.Open;
if not qry2.Eof then begin
mmo1.Lines.Add('导入第'+IntToStr(j)+'行时,产品编号重复,请检查');
ExcelApp.Activeworkbook.close(false);
ExcelApp.quit;
ExcelApp:=unassigned;
OpenDialog.Free;
exit;
end;
end;
if i=6 then begin
//检查产品进价格式是否正确
if not trystrtofloat(ExcelApp.worksheets[1].cells[j,i].text,myfloat) then begin
mmo1.Lines.Add('导入第'+IntToStr(j)+'行时,产品进价格式错误,请检查');
ExcelApp.Activeworkbook.close(false);
ExcelApp.quit;
ExcelApp:=unassigned;
OpenDialog.Free;
exit;
end;
end;
if i=7 then begin
//检查产品建议零售价格式是否正确
if not trystrtofloat(ExcelApp.worksheets[1].cells[j,i].text,myfloat) then begin
mmo1.Lines.Add('导入第'+IntToStr(j)+'行时,产品建议零售价格式错误,请检查');
ExcelApp.Activeworkbook.close(false);
ExcelApp.quit;
ExcelApp:=unassigned;
OpenDialog.Free;
exit;
end;
end;
if i=8 then begin
//检查赠品格式是否正确
if (ExcelApp.worksheets[1].cells[j,i].text<>'是') or (ExcelApp.worksheets[1].cells[j,i].text<>'否') then begin
mmo1.Lines.Add('导入第'+IntToStr(j)+'行时,产品建议零售价格式错误,请检查');
ExcelApp.Activeworkbook.close(false);
ExcelApp.quit;
ExcelApp:=unassigned;
OpenDialog.Free;
exit;
end;
end;
end;
execsql;
mmo1.Lines.Add('导入第'+IntToStr(j)+'行成功');
Application.ProcessMessages;
j:=j+1;
end;
mmo1.Lines.Add('导入完毕,共导入'+IntToStr(j-2)+'行数据');
end;
except
mmo1.Lines.Add('导入第'+IntToStr(j)+'行时,数据库错误或有重复编号,请检查');
ExcelApp.Activeworkbook.close(false);
ExcelApp.quit;
ExcelApp:=unassigned;
OpenDialog.Free;
end;
ExcelApp.Activeworkbook.close(false);
ExcelApp.quit;
ExcelApp:=unassigned;
OpenDialog.Free;
end;