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

技術文章

在 Delphi 按值排序樞紐項目：autoSortScope 與 -2

HotXLS Delphi Component

HotXLS 透過 TXLSPivotField 上的兩個屬性在 Delphi 中按值排序樞紐分析表項目：SortType 選升冪或降冪，SortDataField 指名驅動順序的零基資料欄位。HotXLS 打開 Excel 寫的 XLSX 時，會從 autoSortScope 元素把那個資料欄位找回來——Excel 在那裡把 Values 欄位存成 field="4294967294"，即 -2 的無號拼寫

按值排序是每份業務報告遲早會收到的要求：「把區域從大到小給我看」。在 Excel 介面裡是兩下點擊。在檔案格式裡是一個屬性、一個巢狀樞紐區域、一個保留欄位索引與一個被選中的資料欄位序數，讀取器漏掉任何一件都照樣無怨言地開檔——只是按錯的東西排序

Excel 把按值排序的量值存在哪裡？

Excel 只把排序量值存在 autoSortScope 裡，不在欄位身上。<pivotField> 上的 sortType 屬性（ECMA-376 Part 1 的 ST_FieldSortType 值：manual、ascending 或 descending）說往哪個方向排。排哪個量值則住在子元素 <autoSortScope> 裡，它包著一個 <pivotArea>，其資料軸參照選出一個資料欄位

<pivotField axis="axisRow" showAll="0" sortType="descending">
  <items count="5"> ... </items>
  <autoSortScope>
    <pivotArea dataOnly="0" outlineLevel="all" fieldPosition="0">
      <references count="1">
        <reference field="4294967294" count="1" selected="0">
          <x v="0"/>
        </reference>
      </references>
    </pivotArea>
  </autoSortScope>
</pivotField>

reference 元素通常按索引指向樞紐欄位。欄位索引 -2 在 ECMA-376 的 PivotTable 部件裡保留給 Values 偽欄位——同一軸上有兩個以上量值時，Excel 顯示為「Values」的那個。屬性型別是無號 32 位元整數，所以 -2 上線時就是 4294967294。它底下的 <x v="0"/> 選出資料欄 0，即 <dataFields> 的第一項。<pivotField> 上還有個 rankBy 屬性，但我們比對過的檔案裡 Excel 都沒寫它，只靠 scope

Excel 把樞紐分析表的按值排序存在 autoSortScope 的 pivotArea 參照裡：field 4294967294 是 -2 的無號 32 位元拼寫，代表 Values 偽欄位，x 子元素選出 HotXLS 排序所依據的資料欄
巢狀參照把抽象的排序方向變成具體的量值：x v=0 指向資料欄 0，即 HotXLS 在 TXLSPivotField 上暴露為 SortDataField 的那個序數

排序量值為什麼變成了 -1？

HotXLS 讀取器最初弄丟排序量值，是因為 4294967294 塞不進帶號 32 位元整數。屬性輔助是 StrToIntDef 的薄包裝，超出範圍的值悄悄回傳預設值，於是資料軸測試從未命中，SortDataField 停在 -1，欄位只留下 sortType="descending"，身上沒有量值

這個失敗以最糟的方式安靜。沒有量值的降冪欄位照樣排序，只是反著按項目標籤排，於是 Excel 按總計排好的報告，從 HotXLS 出來變成項目順序顛倒。沒有任何例外，每個儲存格裡的數字都正確。只有列順序錯了，而且只在 Excel 寫的檔案上

lxPivotXml.pas 裡的修法在任何整數轉換之前，先把原始屬性 token 當字串比對。如果 rankBy 已經設了量值，以它為準；否則 field token 恰好是 '4294967294' 的參照，就從它選中的 x 值解析出 SortDataField。計算端不需要改：比較器早已在列軸與欄軸上定案每個成員的總計、處理降冪並打破並列。只是資料從來沒送到它手上

HotXLS 樞紐讀取器弄丟了 Excel 的按值排序，因為 4294967294 讓帶號 32 位元的 StrToIntDef 溢位、SortDataField 停在 -1；修法是在任何整數轉換之前先比對原始 token 字串
舊路徑把降冪按值排序退化成反轉的標籤排序、連例外都不丟；lxPivotXml.pas 裡的新路徑只要 field token 恰為 4294967294 就解析出量值

這個通用的教訓適用任何 Delphi 寫的 OOXML 讀取器。ECMA-376 把好幾個索引屬性定型為 xsd:unsignedInt，然後拿範圍頂端當哨兵，於是帶號讀取器的預設回退把保留值變成「屬性缺失」。凡欄位索引可能代表 Values 的地方，請比對 token，或解析進 Int64 或 Cardinal

從 Delphi 代碼按量值排序樞紐項目

要按值排序 HotXLS 的樞紐欄位，把 SortType 設成 xlpfstAscending 或 xlpfstDescending，並把 SortDataField 設成該量值在表格 DataFields 清單裡的零基索引。SortType = xlpfstManual（預設值）時，SortDataField 被忽略，項目保持快取或程式化順序

uses
  SysUtils, lxHandleX, lxPivot;

var
  Book: TXLSXWorkbook;
  Data, Report: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Data.Cells[1, 1].Value := 'Region';
    Data.Cells[1, 2].Value := 'Product';
    Data.Cells[1, 3].Value := 'Units';
    // ... fill rows 2..41 ...
    Report := Book.Sheets.Add('Report');

    Pivot := Report.AddPivotTable('Data!A1:C41', 3, 1, 'UnitsByRegion');
    Region := Pivot.AddRowField('Region');
    Pivot.AddDataFieldByName('Units', xlpaSum);   // 資料欄 0

    Region.SortType := xlpfstDescending;  // 最大的區域排最前
    Region.SortDataField := 0;            // 按 Units 的總和排序
    Region.AutoSortScope := '0';          // 也輸出 Excel 自己的 autoSortScope

    if Book.MaterializeNativePivotTable(Pivot) <> 1 then
      raise Exception.Create('Pivot layout was rejected');
    Book.SaveAs('units-by-region.xlsx');
  finally
    Book.Free;
  end;
end;

有兩個細節值得記一筆。HotXLS 寫入器把 SortDataField 寫成 rankBy，而 AutoSortScope 非空時還會寫出一個帶 4294967294 資料軸參照的 autoSortScope 區塊，把屬性字串放進 x v 槽位。載入時 AutoSortScope 收到的正是那個 x v token，所以按值排序時屬性裝的是以文字表示的資料欄序數。設成 '0' 產出的檔案因此在兩處都帶著量值，最接近 Excel 自己寫出的樣子。MaterializeNativePivotTable 接著把排好的網格與版面中繼資料一起暫存，成功回傳 1，失敗回傳 -1 並附診斷

被排序的不只是項目列。欄欄位走同一個比較器，用的是逐欄總計而不是逐列總計，所以帶 SortType = xlpfstDescending 與有效 SortDataField 的欄欄位，以完全相同的方式按值排列它的欄

檢視 Excel 活頁簿已經帶著的排序

對 Excel 寫的檔案，HotXLS 在載入樞紐定義時就填好 SortType、SortDataField 與 AutoSortScope，所以您可以稽核或修改報告的排序，而不必重建表格。下面的片段列出每個按量值排序的欄位，並以 DisplayName 指名那個量值

var
  Book: TXLSXWorkbook;
  Pivot: TXLSPivotTable;
  Field: TXLSPivotField;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales-report.xlsx');
    Pivot := Book.Sheets.SheetByName('Report').PivotTables[0];
    for I := 0 to Pivot.FieldCount - 1 do
    begin
      Field := Pivot.Fields[I];
      if (Field.SortType <> xlpfstManual) and (Field.SortDataField >= 0) then
        Writeln(Field.Name, ' sorted by ',
          Pivot.DataFields[Field.SortDataField].DisplayName);
    end;

    // 把 Region 的排序翻成升冪，並重建既有網格
    Field := Pivot.FindFieldByName('Region');
    if Field <> nil then
      Field.SortType := xlpfstAscending;
    if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
      raise Exception.Create('Could not re-materialize the pivot');
    Book.SaveAs('sales-report-asc.xlsx');
  finally
    Book.Free;
  end;
end;

MaterializeNativePivotTable 的第二個參數告訴 HotXLS 採用樞紐既有的輸出範圍，而不是把它當成衝突。同樣的模式見於HotXLS 的 Show Values As 排名與累計總計指南，那裡排序順序很重要，因為累計總計按顯示順序累加

兩個項目同值時，HotXLS 怎麼打破並列？

HotXLS 按項目標籤確定性地打破並列，這是與 Excel 的已知差異。兩個成員總計相同時，比較器退回去比較它們的標題，降冪旗標連這個比較也反轉。量值不是有效數字的項目在升冪時排在有效項目之後，降冪旗標反轉整個比較，包括這個分組

Excel 根本不承諾任何並列順序。在某個測試夾具裡，A、B、C、D 四個成員總計全相同，Excel 的升冪排序給出 B、C、A、D。儲存的中繼資料裡沒有任何東西預言這個排列；它是不穩定排序的副產品，檔案與檔案之間還可能不同。同樣的輸入，HotXLS 回傳 A、B、C、D。我們對這個案例的驗證檢查成員集合與逐成員的值，而不是序列；15/15 並列的降冪夾具則與 Excel 逐儲存格一致。如果您的下游代碼依賴並列列的確切位置，請按第二個量值排序，或讓值彼此不同

HotXLS 在 Delphi 中按項目標題確定性地打破 Excel 樞紐按值排序的並列，四成員夾具回傳 A、B、C、D，而 Excel 不穩定的排序產出 B、C、A、D
總計相同時落到標題比較，降冪旗標連它也反轉，所以兩個引擎之間只可能差在並列列的順序

摺疊成員與最內層欄位

摺疊的成員在 HotXLS 中實體化為每個被摺疊項目一列聚合。Excel 隱藏某個項目的明細時，會在那個 <item> 上寫 sd="0"；HotXLS 把它暴露為 TXLSPivotItem.IsDetailHidden。被摺疊的項目以純標籤渲染，替換掉整棵子樹，且該摺疊對它在任何父項下的實例都生效，而外層區塊保留自己的小計

var
  Category: TXLSPivotField;
begin
  // 樞紐的列軸上有 Category（外層）與 Product（內層）
  Category := Pivot.FindFieldByName('Category');
  if (Category <> nil) and (Category.ItemCount > 0) then
    Category.Items[0].IsDetailHidden := True;   // 摺疊第一個類別
  if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
    raise Exception.Create('Collapse was rejected');
end;

摺疊與排序的互動可以預期：被摺疊的列仍是它欄位的成員，所以像其他項目一樣按總計取得位置。最內層欄位不同。它沒有更內層，無東西可摺疊，而且自某次修復起，即使 defaultSubtotal 旗標設著，HotXLS 也不為它渲染小計列。Excel 在單欄位與多層軸上行為相同，而在那次改動之前，HotXLS 可能在最內層欄位底下吐出一條幽靈總計列。這裡涉及的欄位級屬性，HotXLS 的 CT_PivotField 結構描述有效性走查說明了結構描述對 <pivotField> 與其項目的要求

對照 Excel 自動化的陷阱

透過 COM 自動化把 HotXLS 的排序與 Excel 對照時，呼叫 AutoSort 要用量值的當地語系化標題，拼寫與 Excel 顯示的完全一致。中文版 Excel 上，加總的 Units 欄位預設標題是 '求和项:Units'；在那台機器上傳 'Sum of Units' 不丟錯也不改變任何東西。同一支腳本在英文 Excel 上正常，換個環境悄悄變成 no-op，所以請從資料欄位讀標題，不要寫死

  • 我們測過的 Excel 版本裡，PivotField.SortType 無法透過 COM 讀回，所以請改從存出的 XML 或渲染後的儲存格驗證排序
  • 同一版本裡，把手動群組父欄位放在被排序欄位之外的同一軸上，會抑制按值的成員排序；手動群組父項下的排序渲染未經驗證
  • 同一版本裡對 PivotField.Outline 與 PivotField.LayoutForm 的寫入也被拒，所以請改透過 PivotTable.RowAxisLayout 為夾具設定大綱版面

這些怪癖都不改變檔案格式，但它們決定一個迴歸測試能不能證明什麼。用錯標題呼叫 AutoSort 的測試，對著一份從未被排序的樞紐照樣通過

FAQ

PivotTable XML 檔裡的欄位 4294967294 是什麼意思？

欄位 4294967294 是欄位索引 -2 的無號 32 位元編碼，ECMA-376 把它保留給 Values 偽欄位。在 autoSortScope 裡它標記一個資料軸參照，其 x 子元素選出項目按哪個資料欄位排序

HotXLS 不裝 Excel 也能按值排序樞紐項目嗎？

可以。HotXLS 在實體化樞紐網格時自己計算排序，用的是被選資料欄位的逐成員總計，並寫出 sortType、rankBy 與可選的 autoSortScope，讓 Excel 重新整理後保持同樣順序。只有等值的並列順序可能與 Excel 不同

為什麼我的樞紐項目按字母排而不是按值排？

通常是因為欄位有排序方向卻沒有量值。檢查 SortType 不是 xlpfstManual，且 SortDataField 落在 0 到 DataFieldCount - 1 之間；是 -1 的話，HotXLS 按標籤以要求的方向排序項目

按值排序、摺疊成員與其餘原生樞紐管線都隨HotXLS，XLS 與 XLSX 的 Delphi 試算表元件提供，不需要安裝 Excel 即可讀寫編輯 Excel 活頁簿。如果您是從零開始建樞紐，HotXLS 建立與重新整理樞紐分析表教學先講快取、欄位與重新整理，之後才輪到排序