技術文章

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

pivotField 元素上的 axis="rowAxis" 讀起來是再合理不過的 XML,把它映射成「列欄位」的讀取器會讓樞紐分析的每個欄位都落在正確的軸上。但把同一個部件送進以 ECMA-376 Part 1 §18.10 schema 建出的驗證器,它在第一個屬性就失敗:ST_Axis 恰好有四個 token——axisRow、axisCol、axisPage、axisValues——rowAxis 不在其中。樞紐寫入端得滿足的是 schema 而不是自己的讀取器,因為開檔的人手上是 Excel 與他們自己的剖析器

樞紐欄位定義裡滿是這種規則。三條特別容易溜過往返測試:列舉 token、items 元素不得為空的要求,以及 Excel 列在資料項目之後的尾隨小計條目。另有兩條在讀取端——Excel 因為存放的是 schema 預設值而省略的屬性。本文其餘部分逐一走過每條規則,展示 XML 該長什麼樣,以及產出它的 Delphi 呼叫

為什麼往返測試抓不到錯的軸 token

往返測試證明的只有一件事:寫入端與讀取端彼此合得來。若讀取器同時接受 axisRow 與 rowAxis,輸出錯誤 token 的寫入端能通過你寫得出的每一個存檔再載入檢查,因為讀取器在讀回來的路上把錯誤修掉了。故障只在更嚴格的消費端開啟部件時現形:schema 驗證器、從 XSD 產生的剖析器,或者 Excel 判定部件需要修復

HotXLS 刻意把兩端分開。XLSX 寫入端只輸出四個 ST_Axis token;樞紐讀取器則另外把看起來像英文的拼法 rowAxis、colAxis、pageAxis、dataAxis 映射到對應的軸,使用這類拼法的生產端檔案因此仍能以完整版面載入。輸出按 schema 把關;輸入只要求能辨識。反過來——嚴格讀取器配草率寫入器——至少在你自己的測試裡會大聲失敗;寬容讀取器配草率寫入器,則在哪裡都不會失敗給你看

<!-- 無效:rowAxis 不是 ST_Axis 的值,且 CT_Items 不得為空 -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- 有效:ST_Axis token、真實項目、尾隨的預設小計 -->
<pivotField axis="axisRow" defaultSubtotal="1">
  <items count="4"><item x="0"/><item x="1"/><item x="2" h="1"/><item t="default"/></items>
</pivotField>
無效與有效的 pivotField XML 對照:憑空發明的軸值 rowAxis 與空的 items 元素違反 CT_PivotField,HotXLS 則輸出 axisRow 這類 ST_Axis token、真實的 item 條目、保留的隱藏旗標,以及 schema 接受的尾隨預設小計
接受兩種拼法的寬容讀取器讓每次往返都通過,檔案卻過不了任何嚴格的 schema 檢查——只寫四個 ST_Axis token,並讓 CT_Items 至少帶一個 item

CT_PivotField 對 axis 之外的要求

CT_PivotField 另有三個容易漏掉的要求。在值區域彙總的欄位,必須在自己的定義上標 dataField="1",就算 <dataFields> 清單已經參照了它。HotXLS 會為 DataFields 裡每個條目指向的樞紐欄位設上這個旗標,所以以 AddDataFieldByName 加入的欄位在兩處都被標好

第二條規則是結構性的。CT_Items 要求至少一個 item 子元素,所以 <items count="0"></items> 雖然是完好的 XML、到處解析得動,仍屬無效。欄位沒有項目時,HotXLS 整個省略 items 元素。第三條規則關於每個項目上的狀態:隱藏的成員帶 h="1",摺疊明細的成員帶 sd="0"。兩者來自 TXLSPivotItem.IsHidden 與 TXLSPivotItem.IsDetailHidden,每次存檔都寫出,程式裡設的篩選因此活著進到 Excel

尾隨小計項目與 count 陷阱

欄位有項目時,Excel 會在資料項目之後為每個小計函式補一個 item,以 ST_ItemType 標型。自動小計是 <item t="default"/>;明確指定函式則用 sum、countA、avg、max、min、product、count、stdDev、stdDevP、var 與 varP。這些條目不帶指向快取的 x 索引,而且計入 items 的 count 屬性。略過它們的寫入端,產出的項目清單從此與同一元素上的小計旗標對不起來

HotXLS 存檔時從 TXLSPivotField.Subtotals 推導這段尾巴:集合裡每個函式一條 t="…" 項目、按列舉順序、計入 count,並在欄位上補上對應的 sumSubtotal="1" 式屬性。AddPivotTable 建出的欄位起始集合為空,寫出 defaultSubtotal="0"、完全沒有尾隨項目。產生式報告請讓集合保持為空:小計列會改變樞紐的高度、把底下擺的東西往下推。只 在讀方需要那些額外列的欄位上,才把函式加進 Subtotals

挑函式之前,命名值得再看一眼。xlpsCount 對應 countA——計數所有非空條目;xlpsCountNums 對應 count——只計數數字。在 XML 裡看到「count」就伸手抓 xlpsCount 的人,拿到的是另一個函式

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

底下的程式從既有資料工作表建出一個樞紐:一個列欄位、一個欄欄位、一個值欄位。Region 欄位拿到自動小計加平均,於是它的資料項目之後有兩條尾隨項目,Revenue 則得到 dataField="1"

uses
  SysUtils, lxHandleX, lxPivot;

var
  Book  : TXLSXWorkbook;
  Sheet : TXLSXWorksheet;
  Pivot : TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[1];                  // Sheets 從 1 起算
    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;

讀出 Excel 省略的東西

讀取端面對的是鏡像問題。Excel 做的樞紐欄位把小計條目列在 items 裡,若當普通成員載入,欄位會多出不指向任何快取值的幽靈項目。HotXLS 跳過任何 t 屬性存在且不是 data 的 item,涵蓋小計、總計與空白條目。小計函式經欄位自身的旗標抵達、落進 Subtotals,走訪 Items 的程式碼因此只看到真成員,每個 CacheItemIndex 都指向一個快取值

缺漏的屬性是另一半。在 schema 裡,CT_PivotField 的 defaultSubtotal 與 CT_SharedItems 的 containsString 都預設 true,Excel 在值為預設時省略它們。HotXLS 把缺席的 defaultSubtotal 讀成 true,並在欄位沒有明確小計旗標時把 xlpsDefault 加進 Subtotals——Excel 顯示這種欄位的方式正是如此。缺席的 containsString 也讀成 true,純文字的快取欄位因此分類為字串資料。把任一屬性讀成 false,Excel 存的每個樞紐載入後自動小計就消失,文字欄位則變成別的型別。永遠把每個屬性都寫出來的寫入器碰不到這條路;只有別家生產端的檔案會踩到

載入的 Excel 活頁簿正是這裡該用的測試輸入,因為只有這種檔案會省略那些屬性。底下的檢查開一份、走訪一個列欄位:

var
  Book : TXLSXWorkbook;
  Pivot: TXLSPivotTable;
  Fld  : TXLSPivotField;
  I, J : Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('saved-by-excel.xlsx');
    Pivot := Book.Sheets[1].PivotTables.FindByName('PivotTable1');
    if Pivot = nil then
      Exit;
    for I := 0 to Pivot.FieldCount - 1 do
    begin
      Fld := Pivot.Fields[I];
      if Fld.Axis <> xlpfaRowField then
        Continue;
      // 沒有小計屬性的 Excel 欄位照樣回報預設小計
      if xlpsDefault in Fld.Subtotals then
        Writeln(Fld.Name, ': automatic subtotal');
      // 小計條目已被跳過,每個 item 都映射到一個快取值
      for J := 0 to Fld.ItemCount - 1 do
        Assert(Fld.Items[J].CacheItemIndex >= 0);  // 建議放進你自己測試的檢查
    end;
  finally
    Book.Free;
  end;
end;

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

numFmtId="General" 不合法,因為 ST_NumFmtId 是無號整數——借使用者在儲存格格式對話框裡看到的那個名字再自然不過,但沒用。按 schema 定屬性型別的剖析器直接拒收這個名字,而這類失敗正是變成 Excel 修復對話框的那一種;Excel 修復提示背後的 OPC 與標記規則一文講了這些對話框如何被觸發。HotXLS 為每個 cacheField 寫數值的 numFmtId,取自快取欄位的 NumberFormat——沒人設定過就是 0,內建的 General 格式

65535 列以下的樞紐也需要 32 位元位置

XLS 與 XLSX 引擎共用的樞紐模型,很容易想從 BIFF8 的 SxView 記錄借欄位寬度——那裡 16 位元蓋得住傳統工作表的每一列。XLSX 工作表卻有 1,048,576 列。把 FirstRow、LastRow、FirstHeaderRow、FirstDataRow 與對應的欄位置存成 Word、再用 Min(Value, High(Word)) 鉗制,錨在第 70001 列的樞紐就會被砍到 65535

在 TXLSPivotTable 上這些屬性是 Integer,只有 BIFF8 寫入端在記錄邊界收窄它們。TXLSXWorksheet.AddPivotTable 對錨點落在 1..1048576 × 1..16384 之外的情況回傳 nil,AddPivotTableCopy 在副本範圍會跑出網格時也回傳 nil

樞紐錨點第 70001 列對上 16 位元上限:FirstRow 與 LastRow 存成 Word 並以 Min 對 High(Word) 在 65535 處鉗制,會把壓線或以下的樞紐切掉;HotXLS 把它們保留為 Integer 欄位,超出網格時回傳 nil
Word 寬度的列欄位裝得進 BIFF8 的 SxView 記錄,卻裝不下有 1048576 列的工作表——用它們的話,超過第 65535 列的錨點被鉗到 65535,樞紐就不再坐在你放的位置
var
  Book, Check: TXLSXWorkbook;
  Pivot      : TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    // 第 70001 列超出 16 位元範圍
    Pivot := Book.Sheets[1].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');
  finally
    Book.Free;
  end;

  Check := TXLSXWorkbook.Create;
  try
    Check.Open('late.xlsx');
    Pivot := Check.Sheets[1].PivotTables.FindByName('LateTotals');
    // Assert 是建議放進你自己測試的檢查
    Assert((Pivot <> nil) and (Pivot.FirstRow = 70001));
  finally
    Check.Free;
  end;
end;

傳統 XLS 引擎遵循同樣的 1 起算慣例。SxView 與 DConRef 記錄存 0 起算的值,模型則與 XLSX 引擎及 Cells[Row, Col] 一樣保持 1 起算:BIFF8 讀取端在記錄邊界加 1、寫入端減 1。傳統的 AddPivotTable 對錨點落在 1..65536 × 1..256 之外的情況回傳 nil,所以錨點請用 (1, 1) 而不是 (0, 0)。記錄版面本身見傳統 .xls 樞紐分析表背後的 BIFF8 SX 記錄

對著 schema 測試,而不是對著自己的讀取器

當寬容的一端與犯錯的一端住在同一個程式庫裡,上面每個陷阱都能活過往返測試。三種檢查抓得到它們。對產生的 pivotTableN.xml 與 pivotCacheDefinitionN.xml 部件做 schema 驗證,抓得到憑空發明的 token、空的 items 元素與名稱形式的格式 id。把 Excel 存的檔案餵進你的讀取器,鍛鍊到 Excel 以預設值省略的那些屬性。斷言寫出 XML 裡的確切 token——例如 axis="axisRow"——抓得到寫入端漂向只有自家讀取器才懂的拼法;對解析後的軸做斷言做不到

經 API 建立的樞紐——包括以計算欄位建立與重新整理 XLSX 樞紐分析表展示的計算欄位、計算項目與佔總數百分比版型——出廠就套好這些規則,你這邊不用加任何程式碼。留給你的工作是:哪些欄位要有小計,以及你指的到底是哪個 count 函式

HotXLS Delphi spreadsheet component 能從 Delphi 與 C++Builder 程式碼建立、讀取、儲存 XLS 與 XLSX 樞紐分析表,執行它的機器不需要安裝 Excel、也不需要 COM 自動化