将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;