LlosLab Software Development Blog面向 PDF、Delphi 和文档软件开发者的工程笔记

技术文章

用 HotXLS 把 Delphi 数据集导出为 Excel 报表

HotXLS Delphi Component

把查询结果变成一份 Excel 报表，是三个问题穿着同一件外套。每种 Delphi 字段类型都必须以正确的 Excel 类型落进单元格，表头行读起来要像报表而不是模式转储，而数字、日期和金额必须带上能扛过这趟旅程的格式。漏掉其中任何一项，文件照样能打开，看着照样合理，而在财务用户选中一列、等一个永远不会出现的求和的那一刻照样翻车。值是以文本写进去的，Excel 把它们当成标签，而全程没有抛出任何异常来提醒你

HotXLS 是一个原生 Object Pascal 电子表格库，直接从 Delphi 和 C++Builder 写出 XLS 与 XLSX 文件，全程不涉及 Excel 自动化。它提供了从 TDataset 到工作簿的两条路线：开箱即用的 TDataToXLS 组件，以及针对工作簿 API 手写的循环。这两条路线并不可互换。组件是彻头彻尾的 VCL 公民，构建在 XLS 门面之上，因此正确的选择取决于代码跑在哪里、消费方期待哪种文件格式。下面讲的是这两条路线、组件不再是正确工具的那条界线，以及无论选哪条都要保住字段类型的做法

示意图：从 Delphi TDataset 出发的两条 HotXLS 导出路线，一条是写出 BIFF8 文件的 VCL 组件 TDataToXLS，另一条是面向 XLSX 的手写 TXLSXWorkbook 循环
TDataToXLS 是 VCL 桌面工具写 .xls 的一步到位路线，手写的 TXLSXWorkbook 循环则服务于无人值守任务和原生 .xlsx

字段类型才是真正的导出契约

在调用任何 API 之前，先决定每种 Delphi 字段类型如何落进单元格。接到 Delphi 字符串的单元格就是字符串。HotXLS 不会去猜 '1,234.50' 本意是个数字，它也不该猜，因为依赖区域设置的重新解析，正是德语小数逗号在英语服务器上变成千位分隔符的原因。可靠的写法是通过带类型的访问器赋值：数值字段用 AsFloat 或 AsCurrency，日期用 AsDateTime 以便单元格存的是真正的 Excel 日期序列号而不是格式化后的字符串，而 AsString 只留给确实是文本的字段

NULL 的处理值得做一次显式决定，而不是听凭默认。用 VarToStr 转换字段值会把 SQL NULL 变成空字符串，那是一个文本单元格；而干脆跳过赋值则让单元格真正为空，这才是 AVERAGE、COUNT 和数据透视表消费方所期待的。对金额列，要在写循环之前就定下 NULL 意味着零还是未知。一旦有人给这一列套上格式，两者看起来一模一样，而这点差别会改变下游算出的每一个聚合值

示意图：用 HotXLS 把 Delphi 数据集字段访问器映射到 Excel 单元格类型，并对比 VarToStr 的 NULL 处理与真正的空单元格
导出契约就是字段类型：带类型的访问器让数字和日期落成真正的 Excel 值，而 VarToStr 会悄悄把 SQL NULL 变成文本单元格

组件路线：VCL 应用里的 TDataToXLS

对于一个查询已经接进数据模块的经典 VCL 应用，TDataToXLS 是一步到位的路线。它遍历任何 TDataset 后代，无论 FireDAC、ADO、IBX，还是别的实现了抽象数据集接口的东西，产出一张带样式的工作表，含表头标题、字体、边框、可选的分组小计，以及针对大结果集的自动分表

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // 任意 TDataset 后代
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // 用标题，不用原始列名
    Exporter.GroupFields.Add('CustomerID');   // 每个客户一个小计块
    Exporter.RowsPerSheet := 50000;           // 保持在 BIFF8 行数上限之下
    Exporter.VisibleFieldsOnly := True;             // 尊重 Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

这里有两个属性挑起了生产中的大部分担子。HeaderSource := hsDisplayLabel 写出每个字段的 DisplayLabel 而不是原始 SQL 列名，于是工作簿里写的是“Customer Name”而不是 CUST_NM。RowsPerSheet 之所以存在，是因为组件写的是 BIFF8，其网格止步于 65536 行乘 256 列；把它设成 50000，就能在格式上限截断数据之前把大结果集拆到多张表上。外观由 HeaderFont、DetailFont、GroupColor 和边框样式属性掌管，而当消费方想要朴素单元格时，DisableFormat 集合能整类关闭格式。任何定制需求，都可以用 AfterCell 和 AfterRow 事件，它们会把刚写好的区域交到你手上做后处理

组件到哪里为止

TDataToXLS 在设计上带有三条约束，事先知道它们，能免去两个迭代之后一次尴尬的重新设计

示意图：对比 TDataToXLS 拖进 Delphi 二进制文件的那些 VCL 单元，与 HotXLS 核心工作簿代码只需要的四个 RTL 单元
把 TDataToXLS 链接进服务会一并拖来 Forms、Controls 和 Dialogs，而核心工作簿单元只需要 Windows、Classes、SysUtils 和 Variants
  • 它是完完全全意义上的 VCL 组件。它所在的单元会引入 Forms、Controls 和 Dialogs，因此把它链接进控制台任务或 Windows 服务，就会把 VCL 拖进二进制文件。核心工作簿单元没有这类依赖。它们只需要 Windows、Classes、SysUtils 和 Variants，这正是服务端代码应改用下面那个循环的原因
  • 它构建在 XLS 门面之上。组件填充的是一个 IXLSWorkbook，写出的是 .xls（BIFF8）。没有哪个属性能把它切换成 OOXML 输出
  • 它的事件说的是 XLS 方言。AfterCell 里的 Cell: IXLSRange 参数属于 XLS 对象模型，因此写在那里的逐单元格定制代码是 XLS 风格的代码，即便文件事后被转换成 .xlsx 也一样

从组件的输出产出 .xlsx

当消费方坚持要 .xlsx，而导出逻辑已经落在 TDataToXLS 里时，lxXlsxExport 单元中的桥接函数能一次调用就转换已填充好的工作簿：

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// 组件会暴露它填充好的那个 IXLSWorkbook
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

把这座桥当作表格数据的搬运工，而不是全保真转换器。它复制值、公式、数字格式、填充色、字体属性、列宽和视图设置。它有意不复制边框、合并区域、批注、图表和条件格式。对一张表头加数据行的扁平网格，这刚好够用。对一份带样式的报表就不够了，而诚实的解法是直接生成 XLSX，而不是去修补转换后的文件

面向服务与批处理任务的手写循环

服务端代码应当直接面向 TXLSXWorkbook。在照抄任何示例之前，先留意两个门面在生命周期上的差别。XLS 一侧的 TXLSWorkbook 通过引用计数接口持有，绝不能手工释放，而 TXLSXWorkbook 是一个普通类，需要 try..finally Free。把这两套约定混着用，是稳定制造内存泄漏或二次释放的好办法

procedure ExportOrders(Q: TDataSet; const FileName: string);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Sheet.Cells[1, 1].Value := 'Order No';
    Sheet.Cells[1, 2].Value := 'Customer';
    Sheet.Cells[1, 3].Value := 'Ordered';
    Sheet.Cells[1, 4].Value := 'Amount';

    Row := 2;
    Q.First;
    while not Q.Eof do
    begin
      Sheet.Cells[Row, 1].Value := Q.FieldByName('OrderNo').AsInteger;
      Sheet.Cells[Row, 2].Value := Q.FieldByName('Customer').AsString;
      if not Q.FieldByName('Ordered').IsNull then
        Sheet.Cells[Row, 3].Value := Q.FieldByName('Ordered').AsDateTime;
      Sheet.Cells[Row, 4].Value := Q.FieldByName('Amount').AsFloat;
      Inc(Row);
      Q.Next;
    end;

    Book.StreamingWrite := True;  // 把工作表 XML 直接流式写进 zip
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

真正要紧的几行，是带类型的赋值和那个 IsNull 守卫。日期作为日期序列号到达，金额作为双精度数到达，而 NULL 的订单日期保持真正为空，不会变成空字符串。StreamingWrite := True 只改变保存路径：工作表 XML 直接流进 zip 容器，而不是先拼成一个大字符串，这在六位数行数时能把 SaveAs 时刻的内存尖峰压平。每个保存方法还都有 TStream 重载，因此工作簿可以不落盘直接进入 HTTP 响应。流式写入与批处理任务一文完整走了一遍这种部署模式，而大型工作簿性能一文则讲了行数继续攀升时该怎么办

这个循环也是能跨线程扩展的那条路线。两个引擎都是原生 Object Pascal 写入器，一侧是 BIFF8 记录流，另一侧是 OOXML 的 zip 加 XML，因此导出的任何环节都不碰 COM 自动化，服务器上也不需要 Excel 授权。这换来的是没有单实例瓶颈的并行度，前提是每个线程构建自己的工作簿。工作簿对象在共享使用时并非线程安全的，所以规则是每次导出一个实例，绝不要用锁去守一个共享实例

有一条限制值得在围绕它做设计之前就知道。XLSX 网格止步于 1048576 行乘 16384 列，因此 XLS 一侧靠 RowsPerSheet 处理的分表在这里很少用得上。一份百万行的工作簿通常也不是人类消费方想要的东西。当结果集真有那么大时，带分隔符的文件通常是更好的契约，而 CSV 与 TSV 导出一文讲了分隔符、BOM 行为，以及那里适用的公式求值注意事项

怎么挑起点

如果导出功能住在一个 VCL 桌面工具里，而且 .xls 输出可以接受，就从 TDataToXLS 及其分组支持开始。它代码最少，而当日后有人要 .xlsx 时，SaveXLSWorkbookAsXLSX 这座桥就在那儿，只要你接受前面已经说明的保真度限制。如果代码是无人值守运行的，或者消费方一开始就要求 .xlsx，那就写那个循环。两条路线都附带可运行的演示工程，同属 HotXLS Delphi Component 套件