打造最快的数据导出到Excel文件

作者：lvjack 发表于：http://blog.youkuaiyun.com/lvjack

关键词：DBGrid,导出,Excel,快速,OLE,另存为xls文件,记录集导出为文件,记录集

在很多的数据库应用中我们都要用到将我们的数据导出到文件的功能，为了便于对导出数据的进一步操作，导出为Excel可以操作的文件成为很多时候的首选。但是将数据导出并不是非常的方便，Excel并没有直接提供调用的接口，所以对于数据导出到Excel文件存在以下几种方法：

1.导出为csv格式文件－－逗号分隔符的文本文件（有的直接将csv文件的扩展名csv也直接改为了xls，Excel都可以直接打开）
优点：代码编写相对简单，导出速度快（方法正确的话^_^），不依赖Excel（即电脑上没有安装Excel对导出没有影响）
缺点：Excel打开此文件的时候遇到较长的数字组成的字符串（比如说身份证号银行账号）会把它当做数字，并“智能”的截断或者科学计数；还有0043234这样的字符串也会被当做数字而丢掉了开头的“0”字符。给使用带来不便；另外就是没有漂亮的格式控制，如：列宽的调整、标题背景色，单元格合并等等。
2.导出为slk文件－－Excel支持的符号链接文件（可以直接按照其格式用文本直接输出，我没有测试成功）
优点：同上，而且可以有简单的格式控制，可以设置列为文本类型，避免了身份证号码被截断的问题，还可以有简单的背景色文字粗体等设置，导出速度快，不依赖Excel（但打开时还是需要excel的）
缺点：对于更加丰富的格式控制不支持，而且具体的格式不清楚，我曾经尝试写过输出到slk文件，因为不熟悉格式，输出的文件用Excel打开时忽略一堆错误可以得到目标格式的数据，但是这一堆的错误恐怕会把用户吓坏。
3.简单的导出为html文件（只输出为表格不加样式控制），将扩展名改为xls
优点：代码编写相对简单，导出速度快，不依赖Excel，格式控制丰富
缺点：不是真正的Excel文件，排序等等功能无法使用，仍存在长数字被“智能”处理的问题和“0043243”这样的字符串丢失开头的0的问题。不过可以加样式表控制进行解决。
4.王光红的输出到带格式控制的HTML文件再利用Excel转存为xls文件。
这个是比较完美的处理方法了，但是并非如作者所说“彻底抛弃用OLE做EXCEL”，转存的过程还是OLE控制Excel的，不过整个数据的输出过程没有用OLE控制Excel，我想作者所指的不用OLE也是这个意思。
优点：真正的Excel文件（xls格式），所以文本的格式控制可控性非常强，可以做出很好的效果来，速度比OLE控制Excel一个字段一个字段的输出数据快多了。而且第一步的成果不需要Excel，可以到有Excel的电脑再进行转换。
缺点：没有明显的缺点，第二步依赖Excel
5.OLE控制Excel，直接将数据一个字段一个字段的输出到Excel
优点：同上。
缺点：依赖Excel，而且是所有方法中最慢的，不是一般的慢～～-_-#
6.俺写的快速OLE控制Excel导出数据（严格的说也属于第五种，）
优点：同上。而且很快
缺点：依赖Excel。

说明：OLE控制Excel导出数据慢就慢在多次的程序间通讯，要想加速就要尽量减少操作次数和耗时操作。基于这样的考虑我将能改为用Range操作的步骤都用Range操作，另外利用剪贴板输送数据到Excel，而不是一个字段一个字段的写入。并且充分利用Excel本身的功能以提高效率。这样大大的提高了输出到Excel的速度。用代码说明我的想法如下：

#define   PG   OlePropertyGet
#define   PS   OlePropertySet
#define   FN   OleFunction
#define   PR   OleProcedure

String xls_coltag(int col_no)
{
  //取excel的列标签，用于建立range
  int num1,num2;
  String col_caption;
  num1=col_no/26;
  num2=col_no%26;
  if(num1>0)
    col_caption = String(char(64 +num1))+String(char(64 +num2));
  else
    col_caption = char(64 +num2);
  return col_caption;
}
//---------------------------------------------------------------------------
void __fastcall DBGrid2Excel(TDBGrid *dg, String strXlsFile)
{
  if(!dg->DataSource->DataSet->Active) // 数据集没有打开就返回
    return;
  Variant vExcel, vSheet;
  try
  {
    vExcel = Variant::CreateObject("Excel.Application");
  }
  catch(...)
  {
    MessageBox(NULL, "启动 Excel 出错, 可能是没有安装Excel.",
        "发生错误", MB_OK | MB_ICONERROR);
    return;
  }

  dg->DataSource->DataSet->DisableControls();
  TStringList *sl=new TStringList();
  AnsiString ln="",rng,col;
  Variant Range;
  int ln_no=0,row_cnt,col_cnt;
  row_cnt=dg->DataSource->DataSet->RecordCount;
  col_cnt=dg->Columns->Count;
    //取列名
  for(int i=0; i<col_cnt; i++)
  {
    ln+=dg->Columns->Items[i]->Title->Caption +"/t";
    //取列格式
    switch (dg->DataSource->DataSet->Fields->Fields[i]->DataType)
    {
      case ftBCD:
      case ftInteger:
      case ftFloat:
      case ftCurrency:
      break;
      default:
        col=xls_coltag(i+1);
        rng+=col+":"+col+",";
    }
  }
  //要设置成文本的区域
  rng=rng.SubString(1,rng.Length()-1); //去掉最后一个逗号
  sl->Add(ln);
  ln_no=dg->DataSource->DataSet->RecNo;//记录当前行号
  dg->DataSource->DataSet->First();
  //取明细数据
  while (!dg->DataSource->DataSet->Eof)
  {
    ln="";
    for(int j=0; j<col_cnt; j++)
    {
      ln+=Trim(dg->DataSource->DataSet->Fields->Fields[j]->AsString) + "/t";
    }
    sl->Add(ln);
    dg->DataSource->DataSet->Next();
  }
  dg->DataSource->DataSet->First();
  dg->DataSource->DataSet->MoveBy(ln_no-1);
  dg->DataSource->DataSet->EnableControls();
  try
  {
    vExcel.PS("Visible",true);  // 隐藏Excel界面
    vExcel.PG("Workbooks").FN("Add", 1); // 新建一个工作表
    vSheet = vExcel.PG("ActiveWorkbook").PG("Sheets", 1);
    vSheet.PG("Range","A2").PS("Value","正在检索数据。请稍候..");
    vExcel.PS("ScreenUpdating",false);
    Range = vSheet.PG("Range",rng.c_str());
    Range.PS("NumberFormatLocal","@");
    //写报表标题
    if(rpt_title!="")
    {
      vExcel.PG("Rows", 1).PS("RowHeight", 20);
      vSheet.PG("Cells", 1, 1)
            .PS("Value",rpt_title.c_str());
      rng="A1:"+xls_coltag(col_cnt)+"1";
      Range = vSheet.PG("Range",rng.c_str());
      Range.FN("Merge", false);
      Range.PS("HorizontalAlignment",-4108);
      Range.PS("VerticalAlignment",-4108);
      ln_no=2;
      row_cnt++;
    }
    else
      ln_no=1;
    rng="A"+IntToStr(ln_no);
    //----------------------------------------------------------------------------
    // 作者：lvjack lvjack(at)sohu.com
    // 发表于：http://blog.youkuaiyun.com/lvjack
    //----------------------------------------------------------------------------
    //将DBGrid中的数据粘贴入Excel表格
    vExcel.Exec(PropertyGet("Range")<<rng.c_str()).Exec(Procedure("Select"));
    Clipboard()->SetTextBuf(sl->Text.c_str());
    vExcel.PG("ActiveSheet").PR("Paste");
    vSheet.PG("Cells", 1, 1).PR("Select"); //去除粘贴造成的选区
    //设置边框
    rng="A1:"+xls_coltag(col_cnt)+String(row_cnt+1);
    Range = vSheet.PG("Range",rng.c_str());
    Range.PG("Borders").PS("LineStyle",1);
    //自动格式化表格
    Range.FN("AutoFormat", 11, true, true, true, false, true, true);
    // 设置列名单元格的背景色
    /*
    rng="A1:"+xls_coltag(col_cnt)+IntToStr(ln_no);
    Range = vSheet.PG("Range",rng.c_str());
    Range.PG("Font").PS("Bold",true);
    Variant vInter = vSheet.PG("Range",rng.c_str()).PG("Interior");
    vInter.PS("ColorIndex", 44); //橙色 45深橙色 15灰色
    vInter.PS("Pattern", 1); // xlSolid
    */
    vExcel.PG("ActiveWorkbook")
      .FN("SaveAs", strXlsFile.c_str());  // 保存
  }
  __finally
  {
    vExcel.PS("ScreenUpdating",true);
    //vExcel.PS("Visible", true);
    vSheet = Unassigned;
    vExcel = Unassigned;
    delete sl;
  }
}
//---------------------------------------------------------------------------

代码写完了，但是关于效率还有潜力可挖，下面是一些耗时操作的时间消耗统计，2000条记录，30多个字段，取程序运行三次的数据，时间单位毫秒。

CreateObject 641 453 297
取明细数据 1312 1235 1297
新建一个工作表 156 140 281
设置列格式 15 31 32
发送到剪贴板 47 31 0
Paste 234 235 266
设置边框 62 78 78
AutoFormat 1219 1500 1265
SaveAs 422 296 250

在准备明细数据这一步应该可以利用更好的方法提高速度，如果嫌AutoFormat那一步消耗时间多可以换为我代码中注释掉的那一块“设置列名单元格的背景色”这样处理效果会差些速度会提高一点。不过我觉得多花那一点时间值得，作出来的效果很不错，列宽背景色什么的都弄好了。

呵呵，这就是我工作中用到的一个功能，希望能够抛砖引玉，看到大虾提供效率更高的方法：）

补充，此文写作中得到妖哥的大力支持（www.ccrun.com）。另外写写报表标题的那一段和我的程序有关，直接删掉即可，替换为ln_no=1;
嘻嘻，另外学妖哥在代码中加了一段说明^_^!

－－－－－－－－－－－－－－－－－－－－－－－－－－
2006.3.3 最新发现，可能更快的数据导入方式，来自TR的blog，呵呵我抛出的这块砖引出玉了，惭愧的是发现TR老大早在2004年就已经提出了，我在2006拾人牙慧-_-#

不过让更多人知道好的方法总是不错的：）
TR的方法是利用了Excel的TExcelQueryTable，将SQL检索的数据直接送入了Excel，应该很快的，看老大的测试数据也说明了这点，我还没有进行代码测试，不知道格式啊长数字这方面的问题怎么样，根据使用Excel 的Query工具的经验应该是不错的，不过用Query时发生过一些SQL不能正确执行的问题不知有没有。等着试试。TR@SOE的原作见http://borland.mblogger.cn/tr/posts/5722.aspx，示例代码转载如下：

Delphi代码
procedure TForm1.ExcelQryBtnClick(Sender: TObject);
var ConStr:OleVariant;
var R: Range;
var SQL: String;
var STick, ETick: integer;
begin
   ConStr:='ODBC;DSN=bookodbc;UID="";PWD=""';
   SQL:='select name, author, country from tbook';
   R:=ExcelWorkSheet2.Range['a1', 'a1'];
   ExcelWorkSheet2.Activate;
   STick:=GetTickCount();
   ExcelQueryTable1.ConnectTo(ExcelWorkSheet2.QueryTables.Add(ConStr, R, SQL) as _QueryTable);
   ExcelQueryTable1.Refresh;
   ETick:=GetTickCount();
   ShowMessage(FloatToStr((ETick-STick)/1000.0)+' seconds');
end;
BCB代码
void __fastcall TForm1::Button4Click(TObject *Sender)
{
   String ConStr;
   String SQL;
   int STick, ETick;
   RangePtr R;
   ExcelWorksheet2->Activate();
   ConStr="ODBC;DSN=bookodbc;UID='';PWD=''";
   SQL="select name, author, country from tbook";
   R=ExcelWorksheet2->Get_Range("a1", "a1");
   STick=GetTickCount();
   ExcelQueryTable1->ConnectTo(ExcelWorksheet2->QueryTables->Add(ConStr, R, SQL));
   ExcelQueryTable1->Refresh();
   ETick=GetTickCount();
   ShowMessage(FloatToStr((ETick-STick)/1000.0)+" seconds");
}