技术文章

用 HotXLS 在 Delphi 中比较两份 Excel 工作簿

同一份季度工作簿的两个副本,D2 单元格可能都显示 1,250.00,但这个单元格本身是什么却各执一词:一边是手工敲入的字面值,另一边是 =SUM(A2:C2)。审阅者在两个文件之间来回切换,看到的屏幕毫无二致。而把两个 .xlsx 包做字节比较又会走向另一个极端,几乎报出全部不同,因为一份 zip 归档写两次都很难做到逐字节一致。这两种检查都回答不了审阅轮次之后人们真正想问的问题:哪些单元格、公式和工作表变了

这个问题总在几个可以预见的地方冒出来。一份财务工作簿从审阅那里返回,一次夜间导出在代码变更后重新生成,或者两个部门各自退回同一模板的修改版。并排检查对一张工作表还行,对二十张就无能为力,而恰是上面那种公式与字面值的互换,最容易从一双疲惫的眼睛底下溜走

Delphi 中 HotXLS 工作簿对比总览图:baseline.xlsx 与 reviewed.xlsx 供入 TXLSXWorkbookCompare——按名称配对工作表、遍历已存储单元格,产出结构化差异记录和可读报告
TXLSXWorkbookCompare 把两个已加载的 HotXLS 工作簿变成一份供逻辑使用的结构化差异列表和一份供审阅者阅读的报告

用 TXLSXWorkbookCompare 比较两份工作簿

HotXLS 通过 lxCompare 单元中的 TXLSXWorkbookCompare 比较工作簿。Compare(Left, Right) 接收两个已加载的 TXLSXWorkbook 对象,返回发现的差异数量。差异列表随后可经 Diff(I) 和 Count 按索引访问,Report 则把每条差异一行可读文本追加到任意 TStrings。全程不涉及 Excel;比较运行在 HotXLS 已加载的对象模型上,所以在控制台作业、服务或 C++Builder 应用里行为都一样

uses
  SysUtils, Classes, lxHandleX, lxCompare;

var
  Left, Right: TXLSXWorkbook;
  Cmp: TXLSXWorkbookCompare;
  Lines: TStringList;
begin
  Left := TXLSXWorkbook.Create;
  Right := TXLSXWorkbook.Create;
  Cmp := TXLSXWorkbookCompare.Create;
  Lines := TStringList.Create;
  try
    // Open 成功时返回 1;打开失败必须在这里停下,
    // 否则比较的就是空工作簿,结果没有任何意义
    if Left.Open('baseline.xlsx') <> 1 then
      raise Exception.Create('cannot open baseline.xlsx');
    if Right.Open('reviewed.xlsx') <> 1 then
      raise Exception.Create('cannot open reviewed.xlsx');

    if Cmp.Compare(Left, Right) = 0 then
      Writeln('workbooks are equivalent')
    else
    begin
      Cmp.Report(Lines);              // 每条差异一行可读文本
      Lines.SaveToFile('workbook-diff.txt');
      Writeln(Format('%d difference(s) written', [Cmp.Count]));
    end;
  finally
    Lines.Free;
    Cmp.Free;
    Right.Free;
    Left.Free;
  end;
end;

显式的打开检查比看上去更重要。任一参数为 nil 时 Compare 返回 0 并清空自己的列表,而两份空工作簿也会被比较为相等,所以只有当两个文件都真正加载成功时,零结果才意味着"等价"。workbook-diff.txt 里的一行典型内容是 value changed: Data!A2: 10 -> 99

默认情况下什么才算差异?

不设置任何选项时,TXLSXWorkbookCompare 报告八种差异:新增或移除的工作表、新增或移除的单元格、值变化、公式变化,以及新增或移除的合并区域。枚举值是 lckSheetAdded、lckSheetRemoved、lckCellAdded、lckCellRemoved、lckValueChanged、lckFormulaChanged、lckMergeAdded 和 lckMergeRemoved。一切都以左侧工作簿为基准来度量,所以"新增"项只存在于右侧,"移除"项只存在于左侧

示意图:HotXLS 八种默认工作簿差异类型归入工作表级、单元格级和合并级,均以左侧工作簿为基准度量;行列为零表示工作表级与合并级条目
默认情况下 HotXLS 报告三个层级的八种差异类型,全部以左侧工作簿为基准表达,因此仅调整工作表顺序不会报告任何差异

工作表按名称配对,匹配忽略大小写,所以左侧的 Data 与右侧的 DATA 配成一对。拖动标签页换位置因此不会产生任何条目。重命名工作表则是另一回事:Q3 改成 Q3 Final,会报出一行 sheet removed 和一行 sheet added,因为文件里没有任何东西记录这两者本是同一张表。只在一侧存在的工作表产生一条工作表级条目,而不是其中每个单元格各一行,两份结构差异很大的工作簿比较起来,报告依然可读

单元格按位置配对。比较会遍历每对工作表中存储的单元格,其中包括只携带格式的单元格。一个有格式但无内容、左侧存在右侧不存在的单元格,会被报告为 cell removed: Data!C4 was (blank)。合并区域按其 A1 文本比较,所以 A1:D1 扩成 A1:E1 会得到一条 merge removed 和一条 merge added

为什么公式和数值相同的字面值会被报告为变化?

因为公式才是作者写下的内容。当单元格对中任一侧带公式时,TXLSXWorkbookCompare 就按公式文本比较,一边是公式、另一边是字面值,即使两边都显示 1,250,也是一条 lckFormulaChanged 条目。开头那个例子的报告行是 formula changed: Data!D2: 1250 -> =SUM(A2:C2),记录则带着 LeftKind = lcvNumber 和 RightKind = lcvFormula。公式文本按存储原样精确比较,显示时才补上前导等号

字面值按敲入的类型比较。数字与数字比,文本与文本比,数字 10 对上文本 "10",即使两个单元格打印出相同的字符也算变化。两侧类型不同时,报告会标出类型标签:value changed: Data!B7: [number] 10 -> [text] 10。这种情况在经过 CSV 往返或用户复制粘贴的文件里很常见,也正是日后弄坏查找函数的那一类

公式文本未变时,默认不报告任何内容,哪怕其缓存结果已经不同。对审阅作者内容来说这是对的;对回归测试生成器来说则太宽松,因为一张输入表的改动可以让所有合计值漂移,而不碰任何一条公式。在 Options 里设置 lcoFormulaCachedResults 后,只要缓存结果不同——或者一侧有缓存值而另一侧没有,后者报告为 (missing cache)——就会增加一条 lckFormulaCacheChanged 条目。做生成器回归测试时请打开这个选项:公式未变而合计值漂移,恰恰是这类测试存在的意义

缓存结果只新鲜到最后一次计算为止,所以当你问的是"这些公式今天产出什么"时,先重算两份工作簿。TXLSXWorkbook.Recalculate 成功时返回 lxOk;在迭代关闭的情况下,引用环返回 lxErrorRef。浮点噪声是误报的另一来源,SetNumericTolerance(AbsoluteTolerance, RelativeTolerance) 对数字字面值和缓存结果一视同仁地处理它:

uses
  SysUtils, lxCalc, lxHandleX, lxCompare;

function CompareGeneratedReport(Expected, Actual: TXLSXWorkbook;
  Cmp: TXLSXWorkbookCompare): Integer;
begin
  // 比较结果之前,先把两边的缓存都刷新到最新
  if (Expected.Recalculate <> lxOk) or (Actual.Recalculate <> lxOk) then
    raise Exception.Create('recalculation failed');

  Cmp.Options := [lcoFormulaCachedResults];
  // 1e-9 绝对误差或 1e-12 相对误差;两个值都必须是有限非负数,
  // 否则抛出 EArgumentException,原值保持不变
  Cmp.SetNumericTolerance(1e-9, 1e-12);

  Result := Cmp.Compare(Expected, Actual);
end;

容差默认为零,即精确比较,而且只对数字生效。日期、行列几何和样式永远精确比较,所以为货币舍入调好的容差绝不会掩盖一个挪动了一秒的日期

用结构化差异驱动逻辑

每条 TlxCompareDiff 记录都暴露 Kind、Sheet、一基的 Row 和 Col、A1 Ref、LeftText 和 RightText,单元格条目还带 LeftKind 和 RightKind。工作表级和合并级条目的 Row、Col 为零。可选检查启用后,行几何条目只有行号,列几何条目只有列号,定义名称等工作簿级特性则两者皆无,所以分支请按 Kind 走,别按零坐标判断:

var
  I, FormulaEdits: Integer;
  D: TlxCompareDiff;
begin
  FormulaEdits := 0;
  for I := 0 to Cmp.Count - 1 do
  begin
    D := Cmp.Diff(I);
    case D.Kind of
      lckFormulaChanged:
        begin
          Inc(FormulaEdits);
          Writeln(Format('%s!%s: %s => %s',
            [D.Sheet, D.Ref, D.LeftText, D.RightText]));
        end;
      lckValueChanged:
        if D.LeftKind <> D.RightKind then
          Writeln('type change: ' + D.Describe);
      lckSheetAdded, lckSheetRemoved:
        Writeln('structure: ' + D.Describe);
      lckMergeAdded, lckMergeRemoved:
        Writeln('layout: ' + D.Describe);
    end;
  end;

  // 只在公式改动时才阻断的审核策略
  if FormulaEdits > 0 then
    raise Exception.CreateFmt(
      '%d formula change(s) need sign-off', [FormulaEdits]);
end;

列表的顺序是固定的。Compare 把差异按工作表名排序,再按行、列、类型和引用排,所以同一对文件跑两遍,产出的报告文本逐行一致。工作表名按字符串序数比较,而不是按标签页位置,行号为零的条目在每个工作表组(sheet's group)内排在最前。这让报告文本成了一个像样的回归测试基准文件:把期望的 workbook-diff.txt 提交进仓库,新的报告一旦不同就让构建失败。要做策略判断,直接读记录字段,把描述性的文本行留给人看

按需启用格式、布局与对象检查

默认集合是一次数据比较,一个数字格式从常规改成货币的单元格在它底下什么都不会报告。Options 属性让比较按类目逐项放宽。lcoCellStyles 比较每对单元格解析后的样式,lcoBlankStyleRegions 对没有存储单元格的格式化区域做同样的事,并把相邻差异合并成矩形,远端角落落在 LastRow 和 LastCol。lcoAxisGeometry 比较行高和列宽,连同隐藏、分级显示层级和折叠状态。lcoRichTextRuns 比较文本单元格内的格式化文本段

对象级内容有自己的一组开关:lcoDefinedNames、lcoDataValidations、lcoConditionalFormats、lcoTables、lcoDrawings(也覆盖单元格内嵌图片)、lcoPivotTables、lcoPivotCaches、lcoSlicerState 和 lcoTimelineState。这些条目把特性的键(feature's key)放进 Ref,并通过 Change 说明它是被新增、移除还是修改。定义名称、透视缓存以及工作簿级切片器和时间线缓存的 Sheet 为空

读这些检查的输出时,期望要先摆对。样式、富文本和特性条目在 LeftText 和 RightText 里报告的是规范化定义的 SHA-256 摘要,所以报告只会说 C5 单元格的样式(cell C5's style)变了,却不会说是哪个属性变的。几何条目是可读的,例如 size=15; hidden=0; outline=0; collapsed=0,而且没有显式行高的行按工作表的标准行高(the sheet's standard height)比较,所以显式的 15 磅行高在标准行高同为 15 时与未设置完全等价。内容审阅请把 lcoCellStyles 关着:在一份大工作簿上,它会把每一列重排格式都变成一堵哈希墙,把审阅者真正要批的那三处单元格编辑埋在里面

// 布局审阅:只看几何、合并区域和条件格式
Cmp.Options := [lcoAxisGeometry, lcoConditionalFormats, lcoDataValidations];
// 限制对象检查可捕获的内容量:32 MiB、500,000 项
Cmp.SetFeatureLimits(32 * 1024 * 1024, 500000);
try
  Cmp.Compare(Left, Right);
except
  on E: EXLSXSemanticBudget do
    Writeln('workbooks too large for this check: ' + E.Message);
end;

对象检查在比较之前先捕获各自的定义,SetFeatureLimits 负责给这个捕获封顶。默认值是 67,108,864 字节和 1,000,000 项;任一超出都会抛出 EXLSXSemanticBudget。Compare 抛异常时,上一次成功调用的差异列表原地不动,所以捕获异常的调用方不应把 Count 当作这次失败运行的结果来读

日期、插入的行,以及差异比对的价值所在

工作簿以日期形式保存的单元格按日期精确比较,打印为带底层日期序列值的 date(...)。一边是日期、另一边是普通数字时会显示类型标签,这通常是某列在经过其他工具时丢了日期格式的信号。混合日期系统是这里的坑。1904 日期系统的工作簿与 1900 系统的工作簿,同一个日期的序列值相差 1,462,所以在信任一列日期变化之前,先检查两侧的 Date1904。背景知识见 日期序列号与 1904 系统

按位置配对有一个值得提前规划的后果。在一张工作表靠上的位置插入一行,下面每个单元格都会挪动,比较会报告一串沿工作表向下的变化、新增和移除单元格,引用被插入操作改写过的公式也会报告为变化。管线由你控制时,在结构编辑前后各跑一次比较,让每份报告只覆盖一类变化。引用重写规则见 插入删除时的公式引用调整

三种工作足以证明接入它是值得的。回归测试报表生成器:保留一份已知良好的工作簿,重新生成,开着缓存结果比较,出现任何差异就让测试失败。变更审阅:把排好序的报告交给审阅者,而不是两个文件。迁移验证:批量转换一批遗留工作簿后,把每个结果与其源文件比对,再用 工作簿审计与转换工作台 的清点流程统计两侧的格式与对象特性

比较、重算和转换在适用于 Delphi 和 C++Builder 的 HotXLS 里共享同一个引擎,这个引擎还能读写什么,见 HotXLS Delphi 电子表格组件页面