別讓 Delphi 的 XLS 存檔默默重算公式

HotXLS 是給 Delphi 與 C++Builder 用的原生 Excel 函式庫，它存回傳統 BIFF8 .xls 活頁簿時是快取優先:TXLSWorksheet.WriteFormula 會向 TXLSWorkbook.TryGetCachedFormulaValue 要 Excel 存留在每條公式旁邊的值，只有在該快取缺失或失效時才呼叫求值器。您開過卻從未碰過的活頁簿會原樣存回同樣的數字，而要拿到新結果得明確呼叫一次 Recalculate,不再是 SaveAs 暗地裡的副作用

把這份合約逼上檯面的 bug 小得令人尷尬。一個叫 nested-subtotals.xls 的 corpus 檔案在 R2C4 有一個總計，快取值是 37。用 HotXLS 打開它，向 TryGetCachedFormulaValue 要那一格，拿到 37。連一個儲存格都沒改就存檔，打開存回來的副本，問同樣的問題，拿到 67。API 裡沒有任何東西被要求去做計算，可是檔案裡的數字卻正好移動了 30——而 30 剛好就是總計涵蓋範圍內那兩個群組小計 10 與 20 的和

為什麼存一次 XLS 檔案就改變了公式的值？

要讓那個 37 變成 67,得兩個彼此獨立的缺陷剛好湊在一起，而只修其中一個會把另一個藏起來。第一個是結構性的：傳統寫入器每次存檔都重算每一條公式。第二個是一個型別檢查，對從磁碟載入的公式永遠不可能成立，害求值器把巢狀的 SUBTOTAL 儲存格算了兩次。那個 corpus 檔案只是第一個「存檔時的重算得出與 Excel 不同答案、而且有人把兩者拿來比對」的輸入。結構性缺陷很容易講清楚：在 v2.382.3 之前,TXLSWorksheet.WriteFormula 與它的共用公式兄弟 WriteFormulaWithTExp 是呼叫 TXLSWorkbook.GetFormulaValue(也就是求值器)來取得每一筆 Formula 記錄那八個位元組的 FormulaValue 欄位。而 ParseFormula 在載入時從來源檔案仔細解碼出來的那份快取，在輸出的路上從來沒被問過。實際上每次存檔都是一次完整重算，而且繞過了活頁簿層級的 recalc API,所以您在活頁簿上設什麼都攔不住它。凡是 HotXLS 求值器與 Excel 看法不同的地方——不論是合法不支援的函式還是單純的 bug——都變成存檔時的無聲資料變更

第二個缺陷住在求值器用的巢狀小計回呼裡。Excel 規定每一種 SUBTOTAL 形式都要忽略自己公式也是另一個 SUBTOTAL 的儲存格，所以 lxCalc.pas 裡的計算器在彙總期間會舉起 FIgnoreSubtotalCells,並透過 TXLSWorkbook.GetClassicIsSubtotalCell 向活頁簿詢問範圍內每一格是不是這種儲存格。那個回呼把公式文字取成 Variant,然後用 VarType(f) = varOleStr 去測它。文字從 GetUnCompiledFormula 回來時是 Delphi 的 String,而一個指派給 Variant 的 String 是 varUString,永遠不是 varOleStr。於是這個述詞對每一個載入檔案裡的每一格都為 false,群組小計被第二次捲進總計，而在一次重算全部的存檔上,10 + 20 + 7 就變成了 67

// HotXLS 2.381 及更早:由 String 建出來的公式 Variant
// 是 varUString,所以這個比較永遠不會成立
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0:VarIsStr 接受 varString、varOleStr 與 varUString,
// 而且 AGGREGATE 也依 Excel 的做法被排除在外層小計之外
if VarIsStr(f) then
  Result := SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
    SameText(Copy(f, 1, 10), '=SUBTOTAL(') or
    SameText(Copy(f, 1, 10), 'AGGREGATE(') or
    SameText(Copy(f, 1, 11), '=AGGREGATE(');

v2.382.0 出貨了 VarIsStr 的修正，順道在同一個函式裡教會那個回呼:AGGREGATE 儲存格同樣被排除在外層小計之外。光這樣就讓 corpus 斷言通過了，因為重算出來的 37 現在與載入的 37 相符。但這並沒有讓函式庫變得誠實：存檔還是在重算，測試會綠只是因為求值器剛好在那個特定檔案上與 Excel 看法一致。SUBTOTAL 與 AGGREGATE 到底跳過哪些儲存格(包含隱藏列)的規則，見SUBTOTAL 與 AGGREGATE 的隱藏列一文;這裡要緊的是：對一個您沒有要求它計算的檔案，任何求值器都不該有投票權

Excel 對存檔時的快取值保證了什麼？

Excel 把存檔當成一次快照，不是一次計算事件。寫進 Formula 記錄 FormulaValue 欄位([MS-XLS] §2.4.127,版面配置在 §2.5.133)的值，就是該儲存格當下顯示的東西，在手動計算模式下可能已經過時好幾年，而 Excel 還是忠實地把它寫下去。重算是另一個操作，有自己的觸發條件。HotXLS 現在對傳統存檔也依同一條規則:WriteFormula 與 WriteFormulaWithTExp 先呼叫 TryGetCachedFormulaValue,狀態為 xlfcsLoaded 或 xlfcsCalculated 時採 CacheInfo.Value,只有 xlfcsMissing 與 xlfcsInvalidated 才落到 GetFormulaValue。這份合約讀取那一半的說明——包含每個狀態是什麼意思、以及為什麼快取起來的空白或 False 仍然算一個值——見在 Delphi 中讀取 Excel 快取公式值而不重算一文

後備路徑是刻意留著的，不是沒拿掉。您在工作階段裡透過 Cells[Row, Col].Formula 指派的公式沒有快取，而您在已載入的儲存格上替換掉的公式會被 _SetCompiledFormula 標成 xlfcsInvalidated;兩者存檔時照舊都會求值，所以產生的活頁簿在 Excel 裡打開仍然有數字。當連求值器都產不出值時，寫入器會輸出零值並設上 fAlwaysCalc(§2.4.127 的 grbit bit 0),讓 Excel 開檔時重算那一格，而不是相信那個佔位值

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // 工作表、列、欄都從 1 起算:第一張工作表的 R2C4
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // 有快取的儲存格不會動用到求值器
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // 對 nested-subtotals.xls 而言 Before.Value = After.Value = 37
    // 一次會重算的存檔,在這裡會寫進 67
  finally
    Book.Free;
  end;
end;

BIFF 共用公式的根儲存格把快取值放在哪裡？

放在它自己的 Formula 記錄裡，跟其他每一格公式儲存格一樣，而這正是為什麼共用群組的根儲存格成了快取優先存檔唯一還會漏掉的地方。BIFF8 的共用公式存成一筆 ShrFmla 記錄([MS-XLS] §2.4.260),跟在左上角那一格的 Formula 記錄後面，而每一個成員儲存格(含根在內)都帶著一個只由單一 PtgExp token 構成的 rgce(§2.5.198):解析後運算式的第一個位元組是 $01,後面跟著根儲存格的列與欄。跟隨者儲存格是自足的——HotXLS 讀取每一格自己的 FormulaValue,並靠查找根的編譯公式來解析運算式。根儲存格不一樣，因為它的 Formula 記錄被剖析時，運算式還不存在；它晚一筆記錄才到

快取就是掉在那個一筆記錄的空隙裡。TXLSReader.ParseFormula 解出快取值，一看到 PtgExp 的座標等於這一格自己的座標，就把該格記在 FSharedFormulaRow 與 FSharedFormulaCol 裡，並把快取發布給這一格。當 ShrFmla 記錄($04BC)到達時,ParseSharedFormula 把運算式編譯好並用 _SetCompiledFormula 裝上去，而 _SetCompiledFormula 做了它對任何公式變更都必須做的事：清掉 FCachedFormulaValue、把狀態重設為 xlfcsMissing。根的 37 於是在任何人讀到它之前就被丟掉了,TryGetCachedFormulaValue 回報根沒有快取，而快取優先的寫入器就盡責地退回到求值器——針對的正是大家都在看的那一格。Array 記錄(§2.4.4)有同樣的順序，也有同樣的洞

v2.382.3 的修正在待處理的根座標旁邊加了第三個欄位 FSharedFormulaCachedValue。ParseFormula 認出根時就把解出來的快取暫存在那裡，而 ParseSharedFormula 與 ParseArrayFormula 都會在裝好編譯運算式之後立刻透過 _SetCellCachedFormulaValue 把它重播回去，然後把暫存重設為 Unassigned。快取的 String 變體完全不受這一切影響，因為它的內容放在另一筆 String 記錄裡，靠儲存格座標路由，而不是靠記錄順序。如果您處理的是同一個概念的 OOXML 那一側,XLSX 共用公式 si 展開一文說明了為什麼封裝格式沒有對等的順序問題，卻有它自己的展開陷阱

為什麼共用公式的跟隨者需要相對位移？

因為存在 ShrFmla 裡的運算式是相對於根儲存格寫的，而逐字重用它的跟隨者算出來的是根的參照，不是自己的。舊讀取器在每個跟隨者上裝的是 Value.GetCopy(),一份沒有任何位移的深層複製，所以一個以 B1 為根、公式為 =A1*3 的群組，會讓每個跟隨者也拿到 =A1*3。快取優先的存檔其實替已載入的檔案把這個問題遮住了，因為跟隨者有自己的 FormulaValue,存檔根本不需要那個運算式；但只要有任何東西重算，它就會浮出來。讀取器現在裝的是 TXLSCompiledFormula.GetCopy(row - srow, col - scol),它會走過語法樹，把每一個相對參照按跟隨者與根的距離位移，所以 B2 這個跟隨者擁有的是貨真價實的 =A2*3

釘住這兩個行為的迴歸測試值得一讀，因為它不讓巧合過關。它建出一本活頁簿，在輸入 2 與 4 之上放 =A1*3 與 =A2*3,然後透過 _SetCellCachedFormulaValue 刻意注入錯誤的快取 999 與 888,UseSharedFormulas 開一次、關一次。存檔再重新載入之後，兩格都必須仍然回報 999 與 888——證明這次存檔既沒碰根的快取、也沒碰跟隨者的快取。只有在明確 Recalculate 之後，它們才必須變成 6 與 12——證明跟隨者那份位移過的運算式是對的。一個植入真值的測試在舊寫入器下也會通過，這正是為什麼要植入錯的

var
  Book: TXLSWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('quarterly-model.xls');
    Book.Sheets[1].Cells[1, 1].Value := 5;   // 改一個輸入

    // 相依公式的已載入快取不會被一次字面值編輯標成失效,
    // 所以單純的 SaveAs 會保留舊數字。
    // 真的想要新結果時,請明確要求重算:
    Book.Recalculate;

    if Book.TryGetCachedFormulaValue(1, 1, 2, Info) then
      Writeln('B1 now ', VarToStr(Info.Value),
        ', state ordinal ', Ord(Info.State));   // xlfcsCalculated
    Book.SaveAs('quarterly-model-updated.xls');
  finally
    Book.Free;
  end;
end;

快取優先的合約不會替您做的事

快取優先的存檔保存的是載入進來的東西；它不追蹤載入進來的東西是否仍然為真。改掉某條公式相依的字面值，會替求值器把依賴圖標成髒的，但它讓該相依儲存格的 xlfcsLoaded 快取原封不動，而傳統寫入器會很樂意把那個過期的值寫下去，除非您先呼叫 Recalculate,或先讀取該儲存格的 Value(那會把它算出來，並把狀態推進到 xlfcsCalculated)。這跟 Excel 在手動計算模式下做的取捨相同，而對一條「打開第三方的檔案、改幾個標籤、存回去」的流程來說是對的——但那也意味著一本會編輯輸入的活頁簿，必須自己明確擁有它的重算步驟。XLSX 寫入器的 RecalcBeforeSave 政策不因這次改動而變，它有自己的一套手動模式，本著同樣的精神保留快取。由此還有兩條比較小的界線：快取優先路徑只幫得到狀態為 xlfcsLoaded 或 xlfcsCalculated 的儲存格；一個只寫公式、從不求值的產生器，存檔時照樣得為每一格付一次求值的代價，跟以前一模一樣。而巢狀小計的修正改的是求值器跳過哪些儲存格，不是求值器實作的每一個函式——一份 HotXLS 無法算得跟 Excel 一模一樣的檔案，現在可以安全地原封不動走完 round trip,但對那份檔案刻意做一次 Recalculate,得到的仍然是函式庫的答案而不是 Excel 的，您應該把兩者比對過再信任一次重算過的存檔

快取優先的傳統存檔、復原回來的共用與陣列公式根快取、共用跟隨者的相對參照位移，以及修正後的 SUBTOTAL 與 AGGREGATE 巢狀規則，全都隨標準的 HotXLS Delphi Spreadsheet Component(給 Delphi 與 C++Builder)出貨，不依賴 Excel 或任何 OLE automation 伺服器；產品頁載有本文用到的活頁簿、快取讀取器與重算進入點的完整 API 參考