Delphi DataSet超高效率导出到Excel——60万个数据9秒导出

数据库中的数据导出到Excel通常的方式有几种：

1.Ole控件方式，创建一个Excel文档，一个数据一个数据往里面写，该方法效率比较低，数据量大时不可用。

2.使用文件流的方法，直接写Excel文件，对Excel的文件结构要有了解，网上代码有现成的，但需要调试，代码也比较复杂，效率应该可以。

3.用粘贴板方式，将数据写到一个TListString变量中，每个数据用#9分隔，再将ListString内容复制到Windows粘贴板，再粘贴到一个打开的Excel文档中，该方法为OLE方法的变种，但是效率较高，60万个数据30s左右。

4.Excel的ADO方法导出，就是刚试验成功的一个方法，在网上找了一下，好像没看到有人说到。下面重点说说这个方法。

在进行Excel导入Access的操作时，发现可以用ADO操作，用Jet或者ACE数据库引擎，把Excel看成一个数据库，每个工作簿为一个表

 

  ExcelCon1:=TADOConnection.Create(nil);
  try
    ExcelCon1.Connected:=false;
    if ExtractFileExt(ExcelPath)='.xlsx' then
      ExcelCon1.ConnectionString:='Provider=Microsoft.ACE.OLEDB.12.0;Password="";Data Source='
                                +ExcelPath+
                                ';Extended Properties=Excel 12.0;Persist Security Info=True;'
    else
      ExcelCon1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
                                +ExcelPath+
                                ';Extended Properties=Excel 8.0;Persist Security Info=False';
    Excelcon1.LoginPrompt:=false;
    Excelcon1.Connected:=true;
    Excelcon1.Open;
   except
    //MessageBox(Mainform.Handle,'无法打开 Excel文件！','系统提示', MB_OK);
    showmessage('无法打开 Excel文件！');
    ExcelCon1.Close;
    ExcelCon1.Free;
    exit;
  end;
    sl := TStringList.Create;
    ExcelCon1.GetTableNames(sl);
    Excelcon1.Close;
    Excelcon1.Free;

在Access用ADO方法导入Excel，ADOQuery的SQL为：

 

 

    str:='INSERT INTO '+dbTableName
            +' SELECT * FROM '+sExcelTableName+' in "'+sExcelFile+'" "EXCEL 12.0;"'    //追加到Access表，Excel版本为2007以上时为Excel 12.0
  
    str:='SELECT * into '+dbTableName
            +' FROM '+sExcelTableName+' in "'+sExcelFile+'" "EXCEL 5.0;"';      //导入到新的Access表，Excel版本为2003以下时为Excel 5.0Excel版本为2003以下时为Excel 5.0

 

 

后来反过来想，既然Excel可以当成数据库操作，那Excel也可以用insert into 语句组合in 加外部数据源实现数据导出到Excel，二话不说赶紧试一下。

 

        ExcelDBQuery:=TADOQuery.Create(nil);
        ExcelDBQuery.Connection:=ExcelCon1;  //该ADOConnection 为前面代码所创建。
        With ExcelDBQuery  do
        begin
          close;
          sql.clear;
          sql.text:='insert into  [sheet1$] select * from exceltmp in "'+ExtractFilePath(ParamStr(0))+'DB\accdb.mdb"'; //注意路径要加双引号
          //showmessage(sql.Text);
          ExecSQL;
          close;
          free;
        end;

导出成功！60万个数据只用了9s！！！果然是亲生的数据库引擎，快到不可想像！

 

不过需要保证Excel文件的表头与Access导出的表的表头一致，可以采取新建一个Excel文档，将Access表头用写单元格的方式写到Excel中，再用上面方法导出，代码简单明了，效率奇高。

不知道ACE、Jet引擎对外部数据源支持多少种，有空可以研究下。Jet引擎的SQL语法可以看《MicrosoftJetSQL语言参考》，里面有简单介绍。