delphi读取Excel导入数据库

procedure TFmAutobs.toimportexceldata;
const
BeginRow=2; // hang
BeginClo=1; // lie
var
i,recordnum:integer;
iRow,iCol:Integer;
MsExcel,MsExcelWorkBook,Sheet:Variant;
vNum:Integer;
ExcelRowCount,ExcelColCount:Integer;
b,c,d,e,f,g,h:string;
d1:array [0…10] of string;
// vlist:TStrings;
reg: TPerlRegEx;
strs:TStringList;
vBizName,vDesignerMoney:String;
S,SQLtemp:String;
t1,t2:Double;
procedure readdata;
var
i:Integer;
begin
b := Trim(MsExcel.worksheets[1].cells[iRow,ICol].value); // 订单号
c := Trim(MsExcel.worksheets[1].cells[iRow,ICol+1].value);// 买家实际支付金额
//d := Trim(MsExcel.worksheets[1].cells[ICol,iRow+2].value);//copy(Trim(MsExcel.worksheets[1].cells[ICol,iRow+2].value),2,length(Trim(MsExcel.worksheets[1].cells[ICol,iRow+2].value)));// 订单备注
// d := replacestr(Trim(MsExcel.worksheets[1].cells[ICol,iRow+2].value),‘’‘’,‘’);
d:=StringReplace(Trim(MsExcel.worksheets[1].cells[iRow,ICol+2].value),‘’‘’,‘’,[rfReplaceAll]);
strs.Delimiter := ‘#’;
strs.DelimitedText := d;
for i := 0 to Strs.Count-1 do
begin
if i = 0 then
vBizName :=Strs[i]
else vDesignerMoney := Strs[i];
end;

			reg.Subject := vDesignerMoney;
				reg.RegEx   := '[^\\u4e00-\\u9fa5a]{0,}[0-9]+';//======读取设计师和金额,正则表达式
			 	vNum :=0 ;
		 		while reg.MatchAgain do  //MatchAgain 是下一个
		 		begin
		 		  //d1[vNum] := reg.MatchedExpression;
        d1[vNum]:=reg.MatchedText;
		 		  Inc(vNum);
		 		end;

       e := Trim(MsExcel.worksheets[1].cells[iRow,ICol+3].value);//   店铺名称
       f := Trim(MsExcel.worksheets[1].cells[iRow,ICol+4].value);//   退款金额
       g := Trim(MsExcel.worksheets[1].cells[iRow,ICol+5].value);//   确认收货时间
       h := Trim(MsExcel.worksheets[1].cells[iRow,ICol+6].value);//   打款商家金额
   //    j :=  vUserID;//Trim(MsExcel.worksheets[1].cells[ICol,iRow+7].value);//   用户id
  //     k :=  vUserName;//Trim(MsExcel.worksheets[1].cells[ICol,iRow+8].value);//   用户名
     with autodm do
     begin
       qinserttemp.Close;

       qinserttemp.SQL.Clear;

       SQLtemp:='INSERT INTO [dbo].[tempExceltest] ([b],[c],[d0],[d1],[d2],[e],[f],[g],[h])  VALUES  (:b,:c,:d0,:d1,:d2,:e,:f,:g,:h)';

       qinserttemp.SQL.Add(SQLtemp);

       qinserttemp.Parameters[0].Value:=b;
       qinserttemp.Parameters[1].Value:=c;
       qinserttemp.Parameters[2].Value:=d1[0];
       qinserttemp.Parameters[3].Value:=d1[1];
       qinserttemp.Parameters[4].Value:=d1[2];
       qinserttemp.Parameters[5].Value:=e;
       qinserttemp.Parameters[6].Value:=f;
       qinserttemp.Parameters[7].Value:=g;
       qinserttemp.Parameters[8].Value:=h;

       qinserttemp.ExecSQL;
     end;

       S:=b+','+c+','+d1[0]+','+d1[1]+','+d1[2]+','+e+','+f+','+g+','+h;
    //   S:=b+','+c+','+d+','+e+','+f+','+g+','+h;
    //   vlist.Add(S);
       inc(recordnum);
       listbox1.Items.Add(S) ;
       if (recordnum mod 5000)=0 then listbox1.Items.Clear;
       FmAutobs.caption:=inttostr(recordnum);

end;
begin
if OpenDialog1.Execute then
begin

 // vlist:=TStringList.Create;
  strs:=TStringList.Create;
  reg := TPerlRegEx.Create();
  listbox1.Items.clear;
  autodm.ADOTable1.Close;
  recordnum:=0;
  createtempExceltestTable;
  MsExcel:=CreateOleObject('Excel.application');
  try
  MsExcel.Visible := False;
  MsExcelWorkBook := MsExcel.WorkBooks.Open(Opendialog1.FileName);
  Sheet:=MsExcel.ActiveSheet;
  iRow := BeginRow;
  iCol := BeginClo;
  MsExcel.worksheets[1].Cells.EntireColumn.AutoFit;
  ExcelRowCount := MsExcel.WorkSheets[1].UsedRange.Rows.Count;

// ExcelColCount := MsExcel.WorkSheets[1].UsedRange.Columns.Count;
//MsExcel.WorkSheets[‘sheet1’].
t1:=GetTickCount;
while iRow<=ExcelRowCount do
begin
{opendata(dtTemp,‘biz_order_init’,‘*’,’ where code =‘’‘+Trim(MsExcel.worksheets[1].cells[ICol,iRow].value)+’‘’‘);// and substr(systime,1,10)=’‘’+FormatDatetime(‘yyyy-mm-dd’,now)+‘’‘’);
if dtTemp.RecordCount =0 then
begin }
if (trim(MsExcel.WorkSheets[1].cells[iRow,iCol].value)<> ‘’) then readdata;
iRow:=iRow+1;
end; //while

  t2:=GetTickCount;
  autodm.ADOTable1.TableName:='tempExceltest';
  autodm.ADOTable1.Open;
  FmAutobs.caption:='需要导入数：'+inttostr(ExcelrowCount)+'：'+Floattostr((t2-t1)/1000)+'秒'+':已导入记录数：'+inttostr(autodm.ADOTable1.RecordCount);
  showmessage(FmAutobs.Caption);
  showmessage('首行+空行数：'+inttostr(Excelrowcount-recordnum));



 finally
//  vlist.Free;
  strs.Free;
 // reg.Free;
  FreeAndNil(reg);
  MsExcel.Activeworkbook.close(false);
  MsExcel.quit;
  MsExcel:=unassigned;
 end;
end;

end;