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

HotXLS 通过 TXLSXWorkbookCompare 比较两份工作簿,它按名称配对工作表,遍历每一对工作表中已填充的单元格,并把差异既报告为一份结构化的差异记录列表,也可按需报告为每条差异一行的可读文本。整个过程不涉及任何 Excel 安装,比较完全运行在 Delphi 或 C++Builder 中已加载的对象模型上

这项需求通常在有人第一次问"改了什么"时出现。一份财务工作簿从审阅那里返回,一次夜间导出在代码变更后被重新生成,或者两个部门各自发来同一模板的不同版本。把两个文件并排打开,对一张工作表还行,对二十张就不行了。逐字节比较文件则完全答不出任何问题,因为同一份工作簿保存两次也会产生没人在意的差异

什么才算是一处差异?

比较结果报告八种类型,这个集合是刻意设计得很小的:新增或移除的工作表、新增或移除的已填充单元格、值发生变化的单元格、公式发生变化的单元格,以及新增或移除的合并区域。所有内容都以左侧工作簿为基准来表达,因此"新增"项只存在于右侧,"移除"项只存在于左侧

工作表是按名称而不是按位置配对的。因此重新排列工作表顺序不会产生任何差异,这几乎总是你想要的行为:用户拖动一个标签页不算数据变化。只在一侧存在的工作表会报告一条工作表级条目,而不会展开其中每一个已填充的单元格,这让两份结构完全不同的工作簿的比较报告保持可读,而不是长达数千行

值还是公式,以及各自如何比较

每个单元格都贡献一个签名,规则很简单:带公式的单元格按其带有前导等号的公式文本比较,不带公式的单元格按其值转换成的文本比较。这个区分比初看上去更重要。两个单元格可能显示相同的数字,一个是字面值,另一个是公式,把它们视为相等会恰好掩盖已审阅工作簿中最值得关注的那种编辑

这也意味着一个公式文本未变的单元格不会报告差异,即使其缓存结果发生了变化,这对比较已编写的内容来说是正确行为,但对检测重新计算漂移来说就是错误行为。对于后一个问题,请在比较前先重新计算两份工作簿,这样你比较的数值才是公式如今实际产生的数值

运行一次比较

Compare 接受两份已加载的工作簿,返回发现的差异数量。之后可以按索引访问差异列表,也可以把它整体转存到任意 TStrings:

uses
  lxHandleX, lxCompare;

var
  Left, Right: TXLSXWorkbook;
  Cmp: TXLSXWorkbookCompare;
  Lines: TStringList;
begin
  Left := TXLSXWorkbook.Create;
  Right := TXLSXWorkbook.Create;
  Cmp := TXLSXWorkbookCompare.Create;
  Lines := TStringList.Create;
  try
    if (Left.Open('baseline.xlsx') <> 1) or
       (Right.Open('reviewed.xlsx') <> 1) then
      Exit;

    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;

Report 生成的一行大致是 value changed: Data!A2: 10 -> 99,这对审阅者来说够用,写进提交说明里也够用。这是面向人的一面。程序化的一面是差异记录本身,当比较结果要驱动某个决策而不是生成一份文档时,应该用这一层

从结构化差异中驱动业务逻辑

每条差异都暴露其类型、工作表名称、单元格级条目的一基行列号、合并区域级条目的 A1 引用,以及左右两侧的文本。工作表级和合并区域级条目的行列都报告为零,这是在不检视类型的情况下区分它们的方法:

var
  I: Integer;
  D: TlxCompareDiff;
  FormulaEdits: Integer;
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 R%dC%d: %s => %s',
            [D.Sheet, D.Row, D.Col, D.LeftText, D.RightText]));
        end;
      lckSheetAdded, lckSheetRemoved:
        Writeln(Format('structure: %s', [D.Describe]));
      lckMergeAdded, lckMergeRemoved:
        Writeln(Format('layout: %s at %s', [D.Describe, D.Ref]));
    end;
  end;

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

在你依赖输出编写断言之前,有两个特性值得了解。单元格级条目的顺序遵循单元格存储的内部遍历顺序,因此测试应当写成与顺序无关。另外,公式签名自带一个前导等号,这意味着靠拼接构造出的描述字符串可能出现重复的 ==;当结果要驱动逻辑时,请检查字段值本身,而不要去解析描述性文本行

工作簿比对能在哪些地方发挥价值

三种用途足以证明这项功能的价值。回归测试报表生成器:保留一份已知良好的工作簿,重新生成,比较,一旦出现意料之外的差异就让构建失败。变更审阅:把可读报告交给审阅者,而不是两个文件。以及迁移验证:批量转换一批遗留工作簿之后,把每个结果与其源文件比对,证明没有丢失任何内容

第三种情形与 工作簿审计与转换工作台 中描述的清点和审计流程天然搭配,清点工作簿内容发生在转换之前,比较发生在转换之后。如果你的差异都集中在插入的行附近,插入删除时的公式引用调整 中的引用重写规则能解释为什么看起来没变的公式会被报告为已变化

明确说明的局限

这项比较涵盖值、公式、合并区域和工作表存在性,不比较数字格式、字体、填充、条件格式规则、数据验证、图表、图片或已定义名称。一个值相同但格式从常规改成货币的单元格不会报告任何差异,这对数据比较来说是正确的,对格式审阅来说则是不够的

日期类单元格值得单独提醒一句:它们按其文本转换结果比较,因此一份使用 1904 日期系统的工作簿与一份使用 1900 系统的工作簿,如果底层序列号不同,比较结果可能相等也可能不等,方式会出乎意料。日期系统规则见 日期序列号与 1904 系统。当格式或对象级保真度也是问题的一部分时,应把差异比对与一次统计双方相应特性数量的审计一并使用

工作簿比较、审计和转换都运行在适用于 Delphi 和 C++Builder 的同一个引擎上;完整功能列表见 HotXLS Delphi 电子表格组件页面