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;
