HotXLS 的 XLSX 樞紐分析欄位 Schema 有效性

HotXLS 寫出的 XLSX 樞紐分析表定義,pivotField 與 cacheField 元素能通過 ECMA-376 Part 1 §18.10 schema 驗證:axis 屬性用 ST_Axis 的 axisRow、axisCol 與 axisPage token,值區欄位帶 dataField="1",項目清單絕不為空，快取欄位則存數值的 numFmtId。從 v2.384.33 起，讀取端也把它過去弄錯的 schema 預設值補正了

這次清理背後的 bug 共有一個不太體面的特徵：沒有一個讓任何測試失敗過。HotXLS 寫一個樞紐,HotXLS 讀回來，每個欄位都落在正確的軸上,round trip 測試套件綠了好幾年。問題在於寫入器和讀取器悄悄講好了一種私家方言。從 Delphi 建出來的樞紐在生產它的元件眼裡一切正常，而對照 CT_PivotField 與 CT_CacheField 一查，就翻出非法的列舉 token、schema 明文禁止的空元素，還有 Excel 期待卻始終沒拿到的旗標。如果您在伺服器上產生樞紐、交給會用 Excel 開啟或餵給自家解析器的人，唯一算數的合約就是 schema,不是您自己的讀取器碰巧肯放行什麼

為什麼 HotXLS 的 round trip 從沒抓到錯的 axis token?

HotXLS 的 round trip 從沒抓到錯的 axis token,因為讀取器兩種拼法都收。舊的 XlsxPivotAxisAttr 發出 axis="rowAxis"、colAxis 與 pageAxis,英文讀起來很自然,schema 裡卻不存在;ST_Axis 定義的恰好是四個值:axisRow、axisCol、axisPage 與 axisValues。與此同時,lxPivotXml.pas 裡的 PivotAxisFromToken 對 schema token 和杜撰 token 都匹配，於是每個自測都通過。寫入器現在只發 schema token,讀取器保留對舊拼法的接受，讓更早版本 HotXLS 存的檔案照樣以完整版面載入

<!-- v2.384.33 之前:非法 ST_Axis 值,空的 CT_Items -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- v2.384.33 起 -->
<pivotField axis="axisRow" defaultSubtotal="1">
  <items count="4"><item x="0"/><item x="1"/><item x="2" h="1"/><item t="default"/></items>
</pivotField>

HotXLS 的 pivotField XML 在 v2.384.33 前後的對比：杜撰的 axis 值 rowAxis 與空的 items 元素違反 CT_PivotField,直到寫入器發出 axisRow 之類的 ST_Axis token、真實的 item 條目、保留的隱藏旗標，以及 schema 接受的尾隨預設小計 — 寬鬆的讀取器兩種拼法都收，每個 round trip 都過了，檔案卻過不了任何嚴格 schema 檢查——只寫 ST_Axis 四個 token,讓 CT_Items 至少帶一個 item

CT_PivotField 要求了哪些舊寫入器漏掉的事?

CT_PivotField 要求的三件事，舊的 BuildPivotTableXml 不是漏了就是寫錯。第一，在值區聚合的欄位必須在自己的定義上標明 dataField="1";寫入器現在對 DataFields 裡每個條目參照到的欄位都設這個旗標，而不只是在 <dataFields> 清單裡。第二,CT_Items 至少要有一個 item,所以沒有項目的欄位不再得到空的 <items count="0">,整個元素直接省略。第三，每個項目保留自己的狀態：隱藏項目是 h="1"(TXLSPivotItem.IsHidden),摺疊明細是 sd="0"(IsDetailHidden),這兩樣舊寫入器每次存檔都丟

微妙的部分是尾隨的小計項目。欄位有項目時,Excel 會在資料項目之後為每個小計函式多列一個 item,型別用 ST_ItemType:自動小計是 <item t="default"/>,明確指定的則是 sum、countA、avg、max、min、product、count、stdDev、stdDevP、var 與 varP。HotXLS 在存檔時從 TXLSPivotField.Subtotals 推導這些條目，並把它們計入 items count。AddPivotTable 建立的欄位一開始 Subtotals 是空集合，寫出來就是 defaultSubtotal="0" 而且沒有尾隨項目，所以報表需要小計時請明確指定。留意命名陷阱:xlpsCount 對應 countA(所有條目),xlpsCountNums 才對應 count(只數數字)

HotXLS 樞紐 items 清單解剖：資料 item 條目之後跟著由 TXLSPivotField.Subtotals 推導的尾隨小計項目(如 t=default 與 t=avg),並計入 items count,同時標明 xlpsCount 對 countA、xlpsCountNums 對 count 的命名陷阱 — AddPivotTable 建立的欄位一開始 Subtotals 是空集合，寫出 defaultSubtotal=0 且無尾隨項目——指名要哪些函式，寫入器就為每個函式推導一個項目並計入數量

uses
  lxHandleX, lxPivot;

var
  Book  : TXLSXWorkbook;
  Sheet : TXLSXWorksheet;
  Pivot : TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[1];                  // 從 1 起算,跟 XLS 引擎一樣
    Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 3, 6, 'RegionTotals');
    if Pivot = nil then
      raise Exception.Create('Bad source range or anchor');

    Region := Pivot.AddRowField('Region');    // 沒有這個欄位時是 nil
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default"、t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // 替 Revenue 設 dataField="1" 旗標

    Book.SaveAs('orders-pivot.xlsx');
  finally
    Book.Free;
  end;
end;

HotXLS 現在怎麼讀小計項目與 schema 預設值?

HotXLS 的讀取器現在會跳過任何帶 t 屬性且值不是 data 的 item,因為小計、總計與空白條目不帶快取索引。v2.384.34 之前，這些條目被當成普通項目載入、CacheItemIndex 設成 -1,Excel 做的樞紐讀回來就帶著一批指向虛空的幻影成員，任何走訪 Items 的程式碼都得自己動手過濾。既然寫入器會從 Subtotals 重建尾隨條目，讀取器的職責就是把它們翻譯回那個集合，而不是把它們留下當資料

第二個讀取器修正關於缺席的屬性。schema 裡,CT_PivotField 的 defaultSubtotal 與 CT_SharedItems 的 containsString 預設都是 true,Excel 在值等於預設時會把屬性省略。HotXLS 過去把缺席的屬性讀成 false,結果 Excel 存的每個樞紐載入後默默丟掉預設小計，純文字的快取欄位也被歸類成混合而非字串。這是 axis bug 的鏡像：一個總是把每個屬性都寫出來的寫入器，永遠不會踩到預設值路徑，所以只有別家產生的檔案才暴露得出來

為什麼快取欄位上的 numFmtId="General" 不合法?

numFmtId="General" 不合法，是因為 ST_NumFmtId 是無號整數，不是格式名稱。舊的快取寫入器在每個 cacheField 上寫死那個字串，借用了使用者在儲存格格式對話方塊裡看到的名字。HotXLS 現在把快取欄位的 NumberFormat 寫成數字——沒人動過它就是 0(內建的 General 格式)。按 schema 對屬性定型別的嚴格解析器會直接拒收舊值，而這正是會演變成修復對話方塊的那類失敗;Excel 修復提示背後的 OPC 與標記規則一文講的就是那些對話方塊怎麼被觸發

為什麼 65535 列以下的樞紐分析表被切掉?

放在第 65536 列或以下的 XLSX 樞紐分析表被切掉，是因為共用的樞紐模型把 FirstRow、LastRow、FirstHeaderRow、FirstDataRow 與對應的欄屬性都存成 Word,而平移列的程式碼用 Min(.., High(Word)) 夾住它們。這是 BIFF8 SxView 記錄的遺物——那裡 16 位元夠用——但 XLSX 工作表可以有 1,048,576 列。從 v2.384.37 起,TXLSPivotTable 上這些屬性是 Integer,夾限移除，只有 BIFF8 寫入器還收窄數值。TXLSXWorksheet.AddPivotTable 與 AddPivotTableCopy 現在對錨點落在 1..1048576 列 × 1..16384 欄之外的請求回傳 nil,範圍會超出格線的複製也一樣

HotXLS 樞紐錨點在第 70001 列對上 16 位元上限:FirstRow 與 LastRow 存成 Word、以 Min 對 High(Word) 在 65535 處夾住，切掉該線以下的樞紐，直到 v2.384.37 把模型改成 Integer 欄位、格線外回傳 nil — Word 欄位是 BIFF8 SxView 留在單張表可達 1048576 列的格式裡的遺物——超過 65536 列的錨點曾經迴繞進 16 位元範圍，存檔時樞紐就沒了

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // 第 70001 列過去會迴繞進 16 位元範圍;現在存檔與載入都撐得住
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // 錨點在工作表外,或來源範圍解析不開
  Pivot.AddRowField('Region');
  Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Book.SaveAs('late.xlsx');

  Check := TXLSXWorkbook.Create;
  try
    Check.Open('late.xlsx');
    Pivot := Check.Sheets[1].PivotTables.FindByName('LateTotals');
    Assert((Pivot <> nil) and (Pivot.FirstRow = 70001));
  finally
    Check.Free;
  end;
end;

classic XLS 引擎在 v2.384.38 拿到了對應的修正。它的模型過去直接存原始的從零起算 SxView 與 DConRef 值，並把 AddPivotTable 的錨點原樣放行，而文件、範例與 XLSX 引擎用的都是 Cells[Row, Col] 這類從 1 起算的儲存格。兩個引擎現在都在模型裡保留從 1 起算的位置,BIFF8 讀取器加 1、寫入器在記錄邊界減 1,所以錨在 (0, 0) 的程式碼必須改到 (1, 1),因為 classic 的 AddPivotTable 現在對 1..65536 列 × 1..256 欄之外的錨點回傳 nil;新呼叫寫出的位元組與舊的相同。記錄版面本身沒變,classic .xls 樞紐分析表背後的 BIFF8 SX 記錄一文有完整描述

對 schema 驗證，別對自己的讀取器驗證

教訓可以推廣到樞紐之外：寬鬆的讀取器會藏住寫入器的違規，所以經過自家程式碼的 round trip 證明的是一致性，不是正確性。這裡每個 bug 都活了下來，因為寬容的一側和出錯的一側住在同一個函式庫裡。真正抓得到這類缺陷的檢查是：對產生的部件做 schema 驗證、把 Excel 產生的檔案餵過自己的讀取器(屬性在預設值處被省略),以及把斷言釘在精確 token 而非解析結果的測試夾具。透過 API 建立的樞紐——包括用計算欄位建立與重新整理 XLSX 樞紐分析表展示的計算欄位、計算項目與占總計百分比版面——不必改程式碼就拿到修正後的 XML;從 Excel 檔案載入的樞紐則繼續原樣重播它們原本的部件，直到您動手修改

以上修正都已隨目前的HotXLS Delphi 試算表元件出貨，它讓 Delphi 與 C++Builder 讀寫 XLS、XLSX 與樞紐分析表，機器上不需 Excel 或 COM automation