将Excel导入数据库(速度快)

procedure tuserdef.ExportExcelToCDS(mygrid:TdxDBGrid;filename:string);//将excel导入数据集 var i,j,row,col,ValidFNCount:integer; MyExcel:TcxSpreadSheet; str1,Prompt:string; fieldnames:string; fieldList:array of string; ColIndex:array of Integer;//Excel列序号 tmpcds:TDataSet; tmpds:TDataSource; CelValue:Variant; //搜索Excel的标题是否有对应到数据表中的字段 procedure SetFieldList; var t,t2,js:Integer; str1,str2:string; begin //搜索Excel中的有效字段 for t:=0 to col-1 do begin str1:=StringReplace(MyExcel.Sheet.getcellobject(t,0).Text,' ','',[rfReplaceAll]); for t2:=0 to mygrid.ColumnCount-1 do begin str2:=StringReplace(mygrid.Columns[t2].Caption,' ','',[rfReplaceAll]); if Pos(str1,str2)<>0 then begin ValidFNCount:=ValidFNCount+1; Break; end; end; end; SetLength(fieldList,ValidFNCount); SetLength(ColIndex,ValidFNCount); js:=0; for t:=0 to col-1 do begin str1:=StringReplace(MyExcel.Sheet.getcellobject(t,0).Text,' ','',[rfReplaceAll]); for t2:=0 to mygrid.ColumnCount-1 do begin str2:=StringReplace(mygrid.Columns[t2].Caption,' ','',[rfReplaceAll]); if Pos(str1,str2)<>0 then begin fieldList[js]:=mygrid.Columns[t2].FieldName;//字段 ColIndex[js]:=t;//Excel列序号1... js:=js+1; Break; end; end; end; end; function CheckField:string; var t:Integer; str1:string; begin for t:=0 to col-1 do begin str1:=stringreplace(MyExcel.Sheet.getcellobject(t,0).Text,' ','',[rfReplaceAll]); if Pos(str1+',',fieldnames)=0 then begin Break; end; Result:=str1; end; end; //Excel列名至少有一个与grid中的字段相对应,是否不执行数据追加操作 function CheckFieldArray:Boolean; var t,t2:integer; begin t2:=0; for t:=0 to col-1 do begin if Trim(fieldList[t])<>'' then begin t2:=1; Break; end; end; if t2=0 then Result:=true else Result:=False; end; begin tmpcds:=mygrid.DataSource.DataSet; tmpds:=mygrid.DataSource; MyExcel:=TcxSpreadSheet.Create(nil); tmpcds.DisableControls; try for i:=0 to mygrid.ColumnCount-1 do begin if mygrid.Columns[i].Visible then fieldnames:=fieldnames+stringreplace(mygrid.Columns[i].Caption,' ','',[rfReplaceAll])+','; end; str1:=CheckField; if str1<>'' then begin MessageDlg('Excel中的'+str1+'不正确',mtError,[mbOK],0); Exit; end; MyExcel.LoadFromFile(filename); row:=MyExcel.Sheet.ContentRowCount;//行数 col:=MyExcel.Sheet.ContentColCount;//列数 if row<=1 then begin Prompt:='Excel中至少有一条数据'+#13+'第一行是标题,其它行为数据行'+#13+'条件不符,操作取消'; MessageDlg(Prompt,mtWarning,[mbOK],0); Exit; end; if col<=1 then begin Prompt:='Excel中至少有一列数据'+#13+'条件不符,操作取消'; MessageDlg(Prompt,mtWarning,[mbOK],0); Exit; end; SetFieldList; if CheckFieldArray then begin Prompt:='Excel中第一行中的列名至少有一个与列表中栏位相同'+#13+'条件不符,操作取消'; MessageDlg(Prompt,mtWarning,[mbOK],0); Exit; end; mygrid.DataSource:=nil; Screen.Cursor:=crHourGlass; if not tmpcds.Active then tmpcds.Open; for i:=1 to row-1 do begin Application.ProcessMessages; tmpcds.Append; for j:=0 to ValidFNCount-1 do begin CelValue:=MyExcel.Sheet.getcellobject(ColIndex[j],i).Text; if Length(CelValue)<>0 then begin try tmpcds.FieldByName(fieldList[j]).AsVariant:=CelValue; except MessageDlg(VarToStr(CelValue),mtError,[mbOK],0); end; end; end; tmpcds.Post; end; mygrid.DataSource:=tmpds; MessageDlg('数据导入完毕',mtInformation,[mbOK],0); finally FreeAndNil(MyExcel); tmpcds.EnableControls; if mygrid.DataSource=nil then mygrid.DataSource:=tmpds; Screen.Cursor:=crDefault; end; end;