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

技术文章

Delphi 下使用 HotXLS 进行大型 Excel 工作簿性能优化

HotXLS 组件

当一个 300,000 行的导出耗尽其内存预算时，行数通常会成为替罪羊；但行数往往是无辜的；大型工作簿中昂贵的部分其实是作为副作用创建出来的那些内容：在循环内添加格式化导致样式池在每个单元格上都增加一个条目、在保存时工作表 XML 被组装为单个巨大的字符串、一百万个相同的公式体被逐一存储；HotXLS 作为 losLab 用于 XLS 和 XLSX 文件的原生 Delphi 库，为上述每种开销都提供了专门的控制杠杆；因为每个杠杆都改变了某种权衡，所以默认情况下都没有启用，因此了解哪个杠杆能够应对哪种症状才是真正的性能调优技巧

大型工作簿在何处消耗内存

需要考虑两个不同的内存阶段；在格式化生成期间，内存中的单元格模型随着您触及的每个单元格而增长：数值、格式和公式都会变成对象或池条目；在保存期间，默认的 XLSX 路径在将每个工作表的 XML 压缩进 zip 容器前，还会将其渲染为宽字符串，因此峰值使用量是模型加上最大工作表的序列化形式；在构建循环中幸存但在 SaveAs 内部崩溃的任务遇到的就是第二个阶段，而不是第一个阶段，且针对其中一个阶段的修复对另一个阶段毫无作用

文件大小也遵循类似的规则：单元格仅是贡献者之一，此外还有样式、共享字符串、公式、图像和批注；在对错误的资源进行优化前，使用 ForEachCell 和每工作表集合计数进行一次审查，可以告诉您究竟是哪种资源主导了问题文件；一个测量上的微妙之处：XLSX 侧的 Sheet.Cells.Count 报告的是稀疏存储中实例化的单元格数量，而不是已使用范围的面积；一个数据占据 1000 x 50 的矩形且有一半单元格为空的工作表，计数大约为 25,000，而不是 50,000；当您将客户的“巨型”文件与您的测试夹具进行比较时，这种区别很重要，因为在稀疏的财务布局中，已使用范围的面积和实际单元格数量可能会相差一个数量级

StreamingWrite 修复的是保存路径，而不是构建路径

设置 TXLSXWorkbook.StreamingWrite := True 会将 SaveAs 切换为流式序列化器，该序列化器将工作表 XML 直接写入 zip 流，从而消除了每工作表的中间字符串；为了行为兼容性，它默认值为 False，开启它只是一行代码的变更：

Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Bulk');
  for R := 1 to 100000 do
  begin
    Sheet.Cells[R, 1].Value := R;
    Sheet.Cells[R, 2].Value := 'Row ' + IntToStr(R);
    Sheet.Cells[R, 3].Value := R * 1.5;
  end;
  Book.StreamingWrite := True;   // sheet XML streams into the zip container
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

要准确理解这能带来什么：循环构建的单元格模型所占用的内存与以前完全相同；StreamingWrite 拉平了保存时的飙升，这正是能成功完成的批处理作业与在 95% 进度处失败的作业之间的区别；如果构建循环本身耗尽了内存，您需要的杠杆是接下来的两个

样式池：添加一次，重用索引

HotXLS 中的 XLSX 格式是基于池的：Book.Fonts.Add(...)、Fills.AddSolid(...) 和 Borders.Add(...) 返回单元格引用的基于 0 的池索引；在循环内部使用相同的参数调用 Fonts.Add 会进行去重，因此只会浪费时间而不会浪费空间；Alignments.Add 的行为则不同：它在每次调用时都返回一个新对象，因此每个单元格创建的对齐会使池随行数线性增长；一个习惯即可涵盖这两种情况：在循环之外解析一次每个池索引，并在循环内部指定索引

// hoist pool lookups out of the hot loop
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);   // 0-based pool index
for C := 1 to 24 do
  Sheet.Cells[1, C].FontIndex := HeaderFont + 1;            // cells store 1-based; 0 = default

这里的 + 1 并不是拼写错误，忘记它会成为引起典型症状的 bug：池分发的是基于 0 的索引，而单元格侧的属性将 0 视为“默认值”，因此在赋值时每个池索引都必须偏移 1；如果因忽略而弄错，您的表头将静默渲染为工作簿的默认字体，在品牌审查之前没人会注意到这一缺陷

使用行回调替换每单元格的 Variant 传输

每个 Sheet.Cells[R, C].Value := X 都涉及单元格的查找或创建以及 Variant 赋值；在几十万个单元格时，这种每次访问的开销在性能分析中会变得很明显；HotXLS 在两个接口上都提供了批量回调 API（读取时使用 ForEachCell 和 ForEachRow，写入时使用 WriteCells 和 WriteRows），它们将迭代移至引擎内部，并一次向您的代码交付整行：

procedure TLedgerExport.FillRow(Sender: TObject;
  SheetIndex, Row, FirstCol, LastCol: Integer;
  var Values: Variant; var Skip: Boolean; var Cancel: Boolean);
begin
  if Row > FCount then
  begin
    Cancel := True;     // stop the whole write
    Exit;
  end;
  Values := VarArrayOf([FRows[Row - 1].Account,
                        FRows[Row - 1].PostedOn,
                        FRows[Row - 1].Amount]);
end;

// one engine call instead of hundreds of thousands of property hits
Sheet.WriteRows(1, 1, FCount, 3, FillRow);

回调的 Skip 标志可以在不中止的情况下保留一行不受影响，而 Cancel 会提前结束操作，这在源是您在进行过程中才发现其长度的读取器时很有用；将用于构建的 WriteRows 与用于保存的 StreamingWrite 结合起来，生成路径就不会再有每单元格的热点问题了

XLS 接口在读取端的杠杆

大型旧的 .xls 文件拥有自己的工具包；在 Open 之前设置 _DisableGraphics := True 会完全跳过解析绘图层，这能加速加载那些携带了多年积累的形状和嵌入图片的工作簿；该限制是硬性的：由于绘图层在模型中缺失，保存此类工作簿会写入一个没有绘图的文件；请将该标志保留给只读分析作业；SetTempDir 会重定向 BIFF 写入器的临时文件，这在默认临时位置有配额限制或位于慢速存储的服务器上很重要；UseSharedFormulas 将重复的公式体分组为共享公式记录，缩减了公式列重复了六万行的文件大小

对 XLS 数据进行读取循环有一个值得指出的索引陷阱，因为防守型处理它会使工作量翻倍，而一旦漏掉则会损坏结果：UsedRange 报告的 FirstRow、LastRow、FirstCol 和 LastCol 边界是基于 0 的，而 Cells.Item[Row, Col] 是基于 1 的；遍历已使用范围的扫描必须在单元格访问时为每个坐标加 1（如 Cells.Item[Row + 1, Col + 1]），否则它读取到的网格将是对角平移了一个单元格的，从而静默地丢掉了最后一行和最后一列，并包含了一个虚幻的第一行和第一列；ForEachCell 回调完全避开了这种不匹配，这也是在进行整表扫描时更倾向于使用它的另一个原因

在加载文件之前先对其进行探测

最廉价的大型工作簿操作是您避开的那一个；两个接口上的 GetSheetNames 都会列出工作表而无需加载单元格数据；XLSX 实现仅读取 zip 内部的工作簿清单，并显式地将工作簿实例保留为未填充，而 XLS 接口在第一个子流边界处停止扫描；这使其成为“此导入任务应当以哪个工作表为目标”的合适预检，且 CanReadEncrypted 在注定会失败的 Open 尝试之前回答了“这是否是加密的容器”

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetSheetNames('big-unknown.xlsx', Names) <= 0 then
    raise Exception.Create('cannot enumerate sheets');   // failure clears the list
  // pick the target sheet, then decide whether a full Open is worth it
finally
  Book.Free;
  Names.Free;
end;

注意返回码的约定：这些探测函数用等于或低于零的值来指示失败，并清空输出列表，因此应当测试 <= 0，而不是与某个具体的成功值进行比较

根据工作规模调整方法

当输出流向 HTTP 而不是磁盘时，TStream 保存重载与 StreamingWrite 相结合，从而使大型响应绝不会具体化为临时文件；一个适用的操作性注脚是：流保存从当前位置开始写入且不进行回滚，因此在将流交给响应框架之前，请自行设置 Position := 0；流式写入与批处理任务文章发展了该服务器端的模式，而数据库导出文章展示了这些杠杆在数据集驱动的报表中的应用

最后，请为每个报表家族保留一个最坏情况的测试夹具，并在 CI 中对其进行计时；文档生成中的性能退化很少会自行宣告；在循环内部添加样式，或者用完整的 Open 代替探测，在功能上没有任何改变，但夜间批处理可能会多花 40 分钟；在具有代表性的 50 万个单元格测试夹具上进行定时测试，可以把这种漂移转化为红色的构建，而不是运维事故

评估构建、带批量生成示例的演示项目以及完整的 API 参考均在 HotXLS Component 页面中提供