同一份季報活頁簿的兩個副本,可以在儲存格 D2 顯示同樣的 1,250.00,卻對「這個儲存格是什麼」看法分歧:一份是直接輸入的常值,另一份是 =SUM(A2:C2)。審閱者在兩份檔案之間切換,看到的是一模一樣的畫面。而對兩個 .xlsx 套件做逐位元組比對則走向另一個極端,幾乎把所有東西都回報為不同,因為同一個 zip 封存寫兩次,位元組很難完全一致。兩種檢查都回答不了審閱輪結束後人們真正想問的問題:哪些儲存格、公式與工作表變了
這個問題出現在幾個可預期的場景。財務活頁簿審閱完送回、夜間匯出在程式碼變更後重新產生,或是兩個部門各自交回同一份範本的自家修改。並排檢查一張工作表還行,二十張就沒轍了,而疲憊的雙眼恰恰最容易漏掉的,就是上面那種公式與常值互換
用 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。一切都是以左側活頁簿為基準量測,因此新增的項目只存在於右側,移除的項目只存在於左側
工作表依名稱配對,比對不分大小寫,所以左側的 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,只要快取結果不同就會多出一筆 lckFormulaCacheChanged 項目;一側有快取值、另一側沒有時也一樣,此時回報為 (missing cache)。產生器回歸測試請開啟這個選項:公式原封不動、合計卻漂移,正是這類測試存在的目的要抓的失敗
快取結果只和上次計算一樣新,所以當問題是 "這些公式今天算出什麼" 時,先把兩份活頁簿都重算一次。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、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 會把差異按工作表名稱排序,再按列、欄、類型與參照,所以同一對檔案跑兩次,產生的報告文字逐行相同。工作表名稱按字串序比較,不按索引標籤位置,列為零的項目在每張工作表的群組裡排在最前面。這讓報告文字成為回歸測試理想的黃金檔:把預期的 workbook-diff.txt 提交進版控,新的檔案不同時就讓建置失敗。要做政策決策時,直接讀紀錄欄位,把描述性文字留給人看
加入格式、版面與物件層級的檢查
預設集合是資料比對,數字格式從一般改成貨幣的儲存格,在它底下不會回報任何差異。Options 屬性能逐類擴大比對範圍。lcoCellStyles 比較每對儲存格解析後的樣式,lcoBlankStyleRegions 對沒有儲存儲存格、但帶格式設定的區域做同樣的事,把相鄰的差異合併成矩形,遠端角落記在 LastRow 與 LastCol。lcoAxisGeometry 比較列高與欄寬,連同隱藏、大綱層級與摺疊狀態。lcoRichTextRuns 比較文字儲存格內帶格式的文字區段
物件層級的內容有自己的一組開關:lcoDefinedNames、lcoDataValidations、lcoConditionalFormats、lcoTables、lcoDrawings(也涵蓋儲存格內嵌影像)、lcoPivotTables、lcoPivotCaches、lcoSlicerState 與 lcoTimelineState。這類項目把該特徵的鍵放進 Ref,並透過 Change 說明它是新增、移除還是變更。已定義名稱、樞紐分析快取,以及活頁簿層級的篩選器與時間表快取,Sheet 為空
解讀這些檢查的輸出要有正確的預期。樣式、豐富文字與特徵項目在 LeftText 與 RightText 回報正規化定義的 SHA-256 摘要,所以報告只會告訴你儲存格 C5 的樣式變了,不會說是哪個屬性變了。幾何項目則可讀,例如 size=15; hidden=0; outline=0; collapsed=0;未明確設定列高的列按工作表標準列高比對,標準列高同為 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 系統一文
依位置配對有一個值得預先規劃的後果。在工作表靠上的位置插入一列,下方每個儲存格都會挪動,比對因此沿著整張工作表回報一串變更、新增與移除的儲存格,參照被插入改寫過的公式也回報為變更。管線由你掌控時,結構性編輯前後各跑一次比對,讓每份報告只涵蓋一種變化。改寫規則的說明見插入與刪除時的公式參照調整一文
有三種工作值得把這條鏈路接起來。回歸測試報表產生器:保留一份已知正確的活頁簿,重新產生,開著快取結果比對,任何差異都讓測試失敗。變更審閱:把排好序的報告交給審閱者,而不是兩份檔案。遷移檢查:轉換一批舊版活頁簿之後,把每份結果與其來源比對,再用活頁簿稽核與轉換工作台一文介紹的盤點步驟,統計兩側各自的格式與物件特徵
在 HotXLS for Delphi 與 C++Builder 中,比對、重算與轉換共用同一套引擎;HotXLS Delphi 試算表元件頁面列出了這套引擎其餘可讀寫的功能