LlosLab 軟體開發部落格面向 PDF、Delphi 與文件軟體開發者的工程筆記

技術文章

為什麼 Excel 無視快取的 #SPILL! 與 #CALC! 錯誤

HotXLS Delphi Component

只有當工作簿帶齊完整的現代錯誤契約時，Excel 才信任快取的 #SPILL! 或 #CALC!：Rich Data 詮釋資料、其 XLRICHVALUE 延伸 URI 為小寫 GUID、fileVersion 為 7、calcId 至少 191029，以及 microsoft.com:RD 計算功能。原生 Delphi XLSX 元件 HotXLS，只要存出的儲存格帶著這類錯誤，四項全部照發

少任何一項，失敗通常都不吵。有時 Excel 16 乾脆拒絕開檔；更常見的是照開不誤，卻在您的計算引擎明明產出 #SPILL! 的地方顯示 #VALUE!——看起來像計算 bug，害您在完全正確的公式程式碼裡翻找。按個 F9 正確的錯誤就出現了，所以問題往往以「我機器上沒事」結案，直到某個客戶把自動計算關掉打開這份報表。本文逐一說明每個要素的作用、那個看似不可能有影響的 GUID 大小寫陷阱，以及以一模一樣的安靜方式失敗的陣列文字快取規則

Excel 為什麼顯示 #VALUE! 而不是快取的 #SPILL!？

Excel 顯示 #VALUE!，因為儲存格裡存的就是這個。ECMA-376 SpreadsheetML 的舊式錯誤型別有一份固定的錯誤字面值清單，#SPILL! 與 #CALC! 不在其中。帶 t="e" 的儲存格只能說 #NULL!、#DIV/0!、#VALUE!、#REF!、#NAME?、#NUM! 或 #N/A，所以較新的 Excel 版本寫 #VALUE! 當向後相容的後備值，把真正的錯誤型別掛在一個 value metadata 索引上

那個索引就是 vm 屬性，指向 xl/metadata.xml，再指向一個描述實際錯誤的 Rich Data 值。舊讀取器看到 #VALUE! 就繼續走；目前的 Excel 會順著鏈走、顯示現代錯誤——但前提是它認定這條鏈可信。溢出被擋的公式在磁碟上長這樣：

<!-- sheet1.xml: legacy literal plus a value-metadata pointer -->
<c r="A1" t="e" cm="1" vm="1">
  <f t="array" ref="A1">_xlfn.SEQUENCE(3)</f>
  <v>#VALUE!</v>
</c>

<!-- xl/richData/rdrichvaluestructure.xml -->
<s t="_error">
  <k n="errorType" t="i"/>
  <k n="colOffset" t="i"/>
  <k n="rwOffset" t="i"/>
</s>

<!-- xl/richData/rdrichvalue.xml: errorType 8 is #SPILL! -->
<rv s="0"><v>8</v><v>0</v><v>2</v></rv>

HotXLS 為 #SPILL! 寫錯誤型別 8 帶欄與列偏移，為 #CALC! 寫錯誤型別 13 不帶偏移。舊式的 <v> 在兩種情況下都刻意保持 #VALUE!，因為無視 Rich Data 的讀取器就該看到這個值

Excel 16 真正檢查的小寫 GUID

XLRICHVALUE future metadata 區塊必須使用全小寫的延伸 URI {3e2802c4-a4d2-4d8b-9148-e3be6c30e623}，一個字元都不能差。把十六進位字母改成大寫，本地的 Excel 16 版本就拒絕正常開啟工作簿——儘管每個 XML 部件都解析得了、每條 relationship 都解得開、每個計數與索引都對得上

<futureMetadata name="XLRICHVALUE" count="1">
  <bk>
    <extLst>
      <ext uri="{3e2802c4-a4d2-4d8b-9148-e3be6c30e623}">
        <xlrd:rvb i="0"/>
      </ext>
    </extLst>
  </bk>
</futureMetadata>
<valueMetadata count="1">
  <bk><rc t="2" v="0"/></bk>
</valueMetadata>

碰過 COM 或登錄檔的人都知道 GUID 比對不分大小寫，而這個習慣恰恰就是這裡咬人的東西。我們用最笨的辦法隔離出它：先建一個 Excel 拒收的最小套件，然後一次只換一樣東西。換 content types 沒用；加 rich value types 部件沒用；改命名空間前綴、改寫 rich value 資料都沒用。把那一個 uri 屬性改成小寫，就好了——同一個套件隨即打開並顯示出 #CALC! 與 #SPILL!。請把延伸 URI 當成不透明的字串字面值，從格式定義裡原樣複製，永遠別讓任何自作主張選大小寫的 GUID 格式化工具碰它

給手工組診斷套件的人一條實用推論。改動請用 XML 解析器做，別對序列化後的 XML 做全域文字替換。針對某個 token 的一刀切替換，可能弄壞部件其他地方的 &gt; 之類實體，然後您 debug 的就是壞掉的探針，而不是原本的問題

Excel 讀快取的現代錯誤還需要什麼？

詮釋資料部件與它們的 relationship 是必要條件，卻不充分。Rich Data 鏈完全合法的情況下，Excel 仍無視快取的錯誤，直到工作簿部件宣告自己出自一個現代計算引擎。只要存出的儲存格帶 #SPILL! 或 #CALC!，HotXLS 就會往 workbook.xml 寫三項宣告：

<fileVersion appName="xl" lastEdited="7" lowestEdited="7"/>
...
<calcPr calcId="191029"/>
<extLst>
  <ext uri="...">
    <calcFeatures>
      <feature name="microsoft.com:RD"/>
    </calcFeatures>
  </ext>
</extLst>

fileVersion 元素是樸素的 ECMA-376 Part 1 工作簿詮釋資料，lastEdited 與 lowestEdited 為 7 告訴 Excel：這個檔案最後是由懂 Rich Data 的應用程式世代存的。calcPr 上的 calcId 標識計算引擎版本，191029 是我們實測 Excel 願意接受這類快取的最低值。計算功能延伸裡、位於 http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures 命名空間的 microsoft.com:RD 條目，宣告產出快取值的引擎是以 Rich Data 語意求值的。只給子型別、只給 types 部件、或只換前綴與 relationship 路徑，都改變不了結果。三項宣告必須同時在場

如果您要自己設這些值，HotXLS 套用它們的三個細節值得知道：

  • TXLSXWorkbook.CalcId 不會被覆寫。寫出器只把輸出裡的值提高到 191029，所以設了更高 CalcId 的呼叫者保得住它，記憶體中的屬性也維持您指派的值
  • 隨開啟的檔案進來的既有計算功能與不相干的工作簿延伸會被保留，不會被替換
  • 寫出器在加入 microsoft.com:RD 功能前會先檢查是否已存在，所以反覆存同一個工作簿絕不會疊出重複宣告
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('forecast.xlsx');
    Sheet := Book.Sheets[1];
    // A1 溢出三列；A2 被佔用，所以結果是 #SPILL!
    Sheet.Range['A1'].SetDynamicArrayFormula('=SEQUENCE(3)');
    Sheet.Cells[2, 1].Value := 'blocker';
    Book.Recalculate;
    // 更高的 CalcId 保留；寫出器只在較低時提高到 191029
    Book.CalcId := 191029;
    Book.SaveAs('forecast-out.xlsx');   // rich error 部件自動寫出
  finally
    Book.Free;
  end;
end;

Rich Data 部件永遠不必您自己動手。寫出之前，TXLSXWorkbook 會掃描每個儲存格找 #SPILL! 或 #CALC! 值，建立或擴充 xl/metadata.xml 與兩個 rich value 部件，並把錯誤已經變更或清除的儲存格上過時的 value metadata 連結拿掉。上面明確的 CalcId 指派是可選的，展示它只是為了說明它會被尊重

陣列文字快取該放 t="str"，不是共用字串

同樣安靜的失敗也出現在陣列裡的文字結果上。HotXLS 把公式儲存格、以及 CSE 或動態陣列範圍裡每個追隨儲存格的快取文字，存成 t="str"，字面字串放在 <v> 裡。把這些快取寫成帶 t="s" 的共用字串，Excel 讀快取檔時就會在陣列儲存格裡顯示 #VALUE!

<!-- Correct: literal text cache on an array follower -->
<c r="B3" t="str"><v>Northwind</v></c>

<!-- Wrong: shared-string index, Excel shows #VALUE! from the cache -->
<c r="B3" t="s"><v>17</v></c>

這個很容易搞錯，因為對普通值儲存格來說 t="s" 才是正確選擇，而大多數寫出器預設把每個字串都送進共用字串表。陷阱在驗證方式。在 Excel 裡重算，陣列儲存格顯示正確文字——這證明公式沒問題，對您寫的快取什麼也證明不了。如果您的使用者以手動計算打開報表，或下游工具不計算、直接讀快取值，共用字串版就是壞的

公式儲存語法：前導等號、逗號與 _xlws

同一個寫出器裡還落了一項相關清理：進 OOXML 的公式以檔案文法存放，而不是使用者輸入的文法。HotXLS 保留您指派給 Formula 的原文供往返顯示，但序列化的公式會拿掉前導 =、函式引數之間用逗號，陣列常數的列分隔符與帶引號文字則原樣不動。FILTER 與 SORT 以 Excel 對這類僅工作表函式期望的 _xlfn._xlws. 前綴存放，SEQUENCE 之類其他前瞻函式則用普通的 _xlfn. 前綴

// 輸入時用分號分隔，存放為
//   _xlfn._xlws.FILTER(A2:A50,C2:C50>1000)
Sheet.Range['E2'].SetDynamicArrayFormula('=FILTER(A2:A50;C2:C50>1000)');
Book.Recalculate;   // 沒有超過 1000 的列就得到 #CALC!，連同 Rich Data 一起存出

寫出器裡順序有講究。XML 轉義在語法正規化之後執行、絕不在之前，理由與診斷套件需要解析器相同：對已轉義的文字做分隔符改寫，會把比較運算子裡 &gt; 這類實體弄壞

如何不自欺地驗證一個快取錯誤

唯一可信的測試，是讓計算已設為手動的 Excel 打開檔案、讀取儲存格，然後才重算。Excel 會從工作階段裡第一個打開的工作簿繼承計算模式，所以我們的驗收程序先建一個自己的種子工作簿、把它切成手動，再打開受測檔案。跳過種子步驟的探測，只證明了檔案打得開、算得對。它們標成快取的東西，早就被重新算過了

這個區別就是整篇文章的縮影。現代錯誤快取的正確標準，是 Excel 在任何計算發生前就把它顯示出來；上面每一項要求——從小寫 GUID 到 t="str" 文字追隨格——都是靠檢查那個狀態、而不是重算後的狀態找到的。HotXLS 一開始怎麼給溢出範圍定尺寸與管轄權，背景見用 HotXLS 在 Delphi 裡寫動態陣列溢出公式。如果您的症狀是修復提示而不是錯誤顯示錯，Excel 為什麼修復合法 XLSX 裡的套件層級規則是更好的起點

FAQ

正確保存 #SPILL! 與 #CALC! 需要 HotXLS 額外設定嗎？

不需要。HotXLS 在存檔時偵測 #SPILL! 與 #CALC! 值，自動寫出 Rich Data 部件、fileVersion、最低 calcId 與 microsoft.com:RD 功能

儲存格 XML 裡為什麼還是 #VALUE!？

舊式 SpreadsheetML 錯誤型別沒有 #SPILL! 或 #CALC! 的字面值，所以 #VALUE! 是向後相容的後備。目前的 Excel 透過 vm value metadata 連結讀到真正的錯誤

我自己設的 CalcId 會被 HotXLS 調低嗎？

不會。TXLSXWorkbook.CalcId 只在出現現代錯誤時、於寫出的檔案裡被提高到 191029，您設定的更高值原樣保留

HotXLS 從 Delphi 與 C++Builder 讀寫 XLSX、XLSM 與舊式 XLS，用自己的計算引擎，動態陣列也在內。細節與試用下載，見 HotXLS Delphi spreadsheet component 產品頁