将大量数据导出到Excel

之前做了一个对将数据导出到Excel的性能优化。

原有程序对格式的控制比较精致，但是缺点在于它是逐cell写数据的，效率是在是太低了，200万数据导了三个小时。但好处是这样子进度比较好跟踪。

要注意，excel每页最大允许记录数是65536行，列为256列，所以最好要定义个全局常量来限制。一般来说列是不会超过256的，行是很容易超过。

目前我所找到的最快的方法有两种
1.利用OLE编程中Excel自带的querytables属性，将查询SQL输入，直接得到结果
2.利用Excel的剪贴板属性，一页粘贴

这两种方法都涉及到分页。这里介绍下第一种，这一种相对而言还要快一些。
分页时采用 ceil(记录总数/每页最大记录数)，round()也是可以的，以这个结果作为excel的页号。注意使用querytables时它是将所有数据一次性写入excel，如果你查询SQL选择了所有数据，然后想再用ADOQuery自带的filter属性是不对的，导完后就会报数据超出最大限制。因为它始终只读取最原始的SQL。所以过滤应该在原始SQL上就进行了。

贴代码

    {1.获取查询记录行数}
    with qryADOExport do
    begin
      if qryADOExport.Active then Close;
      qryADOExport.SQL.Text:='select count(1) cnt from ('+mmo1.Lines.Text+' )';//根据输入的SQL(末尾勿加分号)查询记录总数
      try
        qryADOExport.Open;
      except
        on E:Exception do
        begin
          Application.MessageBox(PChar('执行SQL错误，原因为'+E.Message),'错误',0);
          Exit;
        end;
      end;
    end;

    if qryADOExport.FieldByName('cnt').asinteger>MaxPerSheet-2 then //每一页留出页首和页尾，供写标题行与统计行
    begin
      iPageCount := Round(qryADOExport.FieldByName('cnt').asinteger/(MaxPerSheet-2));
      iRecordCount := MaxPerSheet-2;
    end
    else begin
      iPageCount := 1;
      iRecordCount := qryADOExport.FieldByName('cnt').asinteger;
    end;

    if qryADOExport.Active then
      qryADOExport.Close;

    StartTime:=Now;
    ExcelApp.SheetsInNewWorkbook := 1;

    while iPageCount>0 do
    begin
      if SheetNo>0 then ExcelApp.Sheets.Add;
      Inc(SheetNo);

      //stringreplace，第四个参数为空，默认替换第一个
      strSql:='select * from ('+StringReplace(UpperCase(mmo1.Lines.Text),'SELECT','SELECT CEIL(ROWNUM/'+Inttostr(MaxPerSheet-2)+') L_PAGENO,',[])+')';//替换第一个select，加上序号字段，为分页做好准备。
      strFilter:=' where l_pageno='+inttostr(iPageCount);

      ExcelApp.ActiveSheet.Name := '第'+IntToStr(iPageCount)+'页';
      xlSheet := ExcelApp.ActiveSheet;
      j := 0;
      iNextRow := 0;

      if iCount < iPageCount then
      begin
        Dec(iPageCount);
        Inc(iNextRow);
        strTmp:= 'OLEDB;'+qryADOExport.ConnectionString;
        xlQuery := xlSheet.QueryTables.Add(strTmp ,xlSheet.Range[Format('A%d', [iNextRow])],strSql + strFilter);//此方法重点，使用QueryTables属性，依次传入数据库连接串，列标题，查询SQL。注意此处连接串应该使用OLEDB类型的
        xlQuery.FieldNames := false;
        xlQuery.Refresh;
        Inc(iNextRow, iRecordCount);
      end
      else Break;
    end;

效率方面测了一下，20万数据集，十几个字段，导了一分钟左右，效率有非常显著的提高。