技術記事

HotXLSでDelphiから2つのExcelワークブックを比較する

同じ四半期ワークブックの2つのコピーが、セルD2に同じ1,250.00を表示しながら、そのセルの中身については食い違うことがあります。片方は入力されたリテラル、もう片方は=SUM(A2:C2)です。ファイルを行き来して確認するレビュアーの目には両者は同一の画面に映ります。逆に2つの.xlsxパッケージのバイト比較は、ほぼすべてを差分として報告します。2度書かれたzipアーカイブがバイト単位で一致することは稀だからです。どちらのチェックも、レビューラウンドの後に人が本当に尋ねる問い、つまりどのセルと数式とシートが変わったのかには答えてくれません

その問いが顔を出すのは、予測しやすいいくつかの場所です。財務ワークブックがレビューから戻ってくる、コード変更の後に夜間エクスポートが再生成される、2つの部門が同じテンプレートに対する各自の編集を送り返してくる。1枚のシートなら目視の並べ比べで足りますが20枚では絶望的で、疲れた目が見落とすのはまさに上の数式対リテラルの入れ替わりです

DelphiにおけるHotXLSワークブック比較の概要図。baseline.xlsxとreviewed.xlsxがTXLSXWorkbookCompareに入り、シートを名前でペアにし、保存されたセルをたどり、構造化された差分レコードと読みやすいレポートを出力する
TXLSXWorkbookCompareは、読み込まれた2つのHotXLSワークブックを、ロジック向けの構造化差分リストとレビュアー向けの読みやすいレポートに変換する

TXLSXWorkbookCompareで2つのワークブックを比較する

HotXLSはlxCompareユニットのTXLSXWorkbookCompareを通じてワークブックを比較します。Compare(Left, Right)は読み込まれた2つのTXLSXWorkbookオブジェクトを受け取り、見つかった差分の数を返します。差分リストはその後、Diff(I)とCountでインデックスアクセスでき、Reportは差分ごとに1行の読みやすい行を任意の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);              // 差分ごとに読みやすい1行
      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を返してリストをクリアし、空のワークブック同士も等しいと比較されるため、0という結果は両ファイルが本当に読み込まれたときに限り"等価"を意味します。workbook-diff.txtの典型的な行はvalue changed: Data!A2: 10 -> 99のようになります

デフォルトでは何が差分として数えられるのか

オプションを設定しない場合、TXLSXWorkbookCompareは8種類の差分を報告します。シートの追加または削除、セルの追加または削除、値の変更、数式の変更、結合範囲の追加または削除です。列挙値はlckSheetAdded、lckSheetRemoved、lckCellAdded、lckCellRemoved、lckValueChanged、lckFormulaChanged、lckMergeAdded、lckMergeRemovedです。すべては左側のワークブックをベースラインとして測定され、追加された項目は右側にのみ存在し、削除された項目は左側にのみ存在します

8種類のデフォルトのHotXLSワークブック差分種類をシート・セル・結合セルの各レベルに分類した図。それぞれ左側のワークブックをベースラインとして測定され、行と列のゼロがシートレベルと結合レベルのエントリを示す
デフォルトではHotXLSは3つのレベルにわたる8種類の差分をすべて左側のワークブック基準で表現するため、シートの並べ替えは何も報告しない

シートは名前でペアにされ、一致判定は大文字小文字を無視するので、左のDataは右のDATAとペアになります。したがってタブを新しい位置へドラッグしてもエントリは1つも生まれません。シートの改名は話が別です。Q3をQ3 Finalに改名すると、sheet removedが1行とsheet addedが1行として現れます。2つが同じシートであることを記録するものがファイル内にないからです。片側にしか存在しないシートは、その中のセルごとの行ではなくシートレベルのエントリ1つとして報告されるため、2つのワークブックが構造的に異なる場合でもレポートは読みやすく保たれます

セルは位置でペアにされます。比較は各ペアのシートに保存されたセルをたどり、書式だけを保持するセルも含まれます。左に存在し右に存在しない、書式付きの空セルはcell removed: Data!C4 was (blank)として報告されます。結合範囲はA1テキストで比較され、A1:D1がA1:E1に広がるとmerge removedが1つとmerge addedが1つ生まれます

同じ数値の数式とリテラルがなぜ変更として報告されるのか

数式は著者が記述した内容だからです。セルペアのどちらかが数式を保持する場合、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の往復やユーザーのコピーペーストを経たファイルでよく起こり、後でルックアップを壊すのがまさにこれです

テキストが変わっていない数式は、キャッシュされた結果が異なっていてもデフォルトでは何も報告しません。記述内容のレビューとしてはそれが正しい挙動です。ジェネレータの回帰テストには甘すぎます。入力シートの変更が数式に1本も触れずにすべての合計を動かし得るからです。OptionsにlcoFormulaCachedResultsを設定すると、キャッシュ結果が異なる場合、または片側にキャッシュ値がありもう片側にない場合((missing cache)と報告)にlckFormulaCacheChangedエントリが追加されます。ジェネレータの回帰テストではこのオプションを有効にしてください。数式が不変のまま合計がずれるのは、まさにその種のテストが存在して捕捉すべき失敗です

キャッシュ結果は最後の計算と同じ鮮度しかないので、問いが"これらの数式が今日何を生み出すか"である場合は先に両ワークブックを再計算してください。TXLSXWorkbook.Recalculateは成功時にlxOkを返し、反復計算がオフの状態での参照循環はlxErrorRefを返します。浮動小数点のノイズは誤報のもう1つの源で、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;

許容差はデフォルトでゼロ、つまり完全一致の比較です。適用されるのは数値のみです。日付、行と列のジオメトリ、スタイルは常に完全に比較されるため、通貨の丸めに合わせた許容差が1秒ずれた日付を隠すことはありません

構造化された差分からロジックを駆動する

各TlxCompareDiffレコードはKind、Sheet、1始まりの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は差分をシート名、次に行、列、種類、参照の順でソートするので、同じファイルペアに対する2回の実行は行単位で同じレポートテキストを生みます。シート名はタブ位置ではなく序数の文字列比較でソートされ、行ゼロのエントリは各シートのグループ内で先頭に来ます。これによりレポートテキストは回帰テストの正当なゴールデンファイルになります。期待されるworkbook-diff.txtをコミットし、新しいものが異なればビルドを失敗させます。ポリシー判断では記録フィールドを直接読み、説明的な行は人間のために残します

書式、レイアウト、オブジェクトチェックへの追加

デフォルトのセットはデータ比較であり、数値書式が標準から通貨に変わったセルはその下では何も報告しません。Optionsプロパティは比較を1カテゴリずつ広げます。lcoCellStylesはペアになった各セルの解決済みスタイルを比較し、lcoBlankStyleRegionsは保存されたセルのない書式付き領域に対して同じことを行い、隣接する差分を四角形にまとめます。その遠い角はLastRowとLastColに入ります。lcoAxisGeometryは行高と列幅を、非表示、アウトラインレベル、折りたたみ状態と合わせて比較します。lcoRichTextRunsはテキストセル内の書式付きランを比較します

オブジェクトレベルの内容には専用のスイッチがあります:lcoDefinedNames、lcoDataValidations、lcoConditionalFormats、lcoTables、lcoDrawings(セル内画像も含む)、lcoPivotTables、lcoPivotCaches、lcoSlicerState、lcoTimelineStateです。それらのエントリは機能のキーをRefに入れ、Changeで追加、削除、変更のどれかを示します。定義名、ピボットキャッシュ、ワークブックレベルのスライサーとタイムラインのキャッシュは空のSheetを保持します

これらのチェックの出力は、適切な期待値を持って読みます。スタイル、リッチテキスト、機能のエントリは正規化された定義のSHA-256ダイジェストをLeftTextとRightTextで報告するため、レポートはセルC5のスタイルが変わったと述べるだけで、どのプロパティが変わったかは述べません。ジオメトリのエントリはsize=15; hidden=0; outline=0; collapsed=0のように読み取り可能で、明示的な行高のない行はシートの標準行高で比較されるため、標準行高も15なら明示的な15ポイントの行は未設定の行と一致します。内容レビューではlcoCellStylesをオフに保ってください。大きなワークブックでは、再書式された列がハッシュの壁になり、レビュアーに承認を求められていた3つのセル編集を隠してしまいます

// レイアウトレビュー:ジオメトリ、結合セル、条件付き書式のみ
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年システムにあります

位置によるペアリングには、計画しておく価値のある帰結が1つあります。シートの上部近くに1行挿入すると、その下のすべてのセルが動くため、比較はシートの下に向かって変更、追加、削除されたセルの連なりを報告し、挿入によって参照が書き換えられた数式は変更として報告されます。パイプラインを自分で制御できるなら、構造編集の前に1回、後に1回の比較を実行し、各レポートが1種類の変更だけを扱うようにします。書き換え規則は挿入と削除における数式参照の調整で扱っています

この機能を組み込む価値のある仕事は3つあります。レポート生成器の回帰テストでは、既知の正しいワークブックを保持し、再生成し、キャッシュ結果を有効にして比較し、差分があれば失敗させます。変更レビューでは、2つのファイルの代わりにソート済みのレポートをレビュアーに渡します。移行チェックでは、レガシーワークブックのバッチを変換した後、各結果をそのソースと比較し、ワークブック監査と変換ワークベンチのインベントリパスで両側の書式とオブジェクト機能を数えます

比較、再計算、変換は、DelphiとC++Builder向けのHotXLSにおいて1つのエンジンを共有しており、HotXLS Delphiスプレッドシートコンポーネントのページがそのエンジンが読み書きする残りの機能を一覧にしています