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

技術文章

Delphi 的樞紐分析 Show Values As：排名與累計總和

HotXLS Delphi Component

HotXLS 從 Delphi 設定 Excel 樞紐分析表的「Show Values As」模式，靠的是 TXLSPivotDataField 上的兩個屬性：ShowDataAs 對應累計總和、差異、列百分比等經典模式，ExtendedShowDataAs 對應排名、父項百分比與累計總和百分比。TXLSXWorkbook.MaterializeNativePivotTable 接著照 Excel 的方式把算好的網格寫出來。這兩個屬性互斥，而延伸模式依賴一個 Excel 會區分大小寫比對的延伸 URI

最後那個細節就是這篇文章存在的原因。帶排名欄的樞紐分析表看起來是個小活，第一次打開通常也確實是。麻煩從使用者在 Excel 裡點了重新整理開始：排名欄悄悄變回普通的總和，連一句警告都沒有。要把 Show Values As 做對，得把兩套 XML 詞彙都做對，外加一堆 Excel 從來不在 UI 裡寫明的數值規則：同分怎麼排名、平均值的累計百分比是什麼意思、以及哪種空儲存格該留白、哪種該報錯

一個 Excel 選單為什麼對應兩個屬性？

Excel 顯示的是一個「Show Values As」選單，檔案卻把它存在兩個不同的地方，所以 HotXLS 開出兩個屬性。Excel 2007 的原始模式住在 pivotTableDefinition 裡 dataField 元素的 showDataAs 屬性上，用的是 ECMA-376 的 ST_ShowDataAs 列舉。Excel 2010 加入的模式——排名、父項百分比與累計百分比——塞不進那個列舉，於是進了帶 pivotShowAs 屬性的 x14:dataField 元素，包在同一個資料欄位的 extLst 延伸區塊裡

TXLSPivotShowDataAs 對應第一組：xlpsdaNormal、xlpsdaDifference、xlpsdaPercent、xlpsdaPercentDiff、xlpsdaRunTotal、xlpsdaPercentOfRow、xlpsdaPercentOfCol、xlpsdaPercentOfTotal 與 xlpsdaIndex。TXLSPivotExtendedShowDataAs 覆蓋第二組：xlpesdaPercentOfParent、xlpesdaPercentOfParentRow、xlpesdaPercentOfParentColumn、xlpesdaPercentOfRunningTotal、xlpesdaRankAscending 與 xlpesdaRankDescending，另以 xlpesdaNone 作為中性值。資料欄位兩組只能用一組，不能都設。如果 ShowDataAs 留在非 normal 的值、又設了延伸模式，MaterializeNativePivotTable 會回傳 -1，既有輸出原封不動——儲存格與樞紐邊界都算在內

BaseField 與 BaseItem 承載比較目標。BaseField 是樞紐分析表欄位清單裡以零為基底的索引，不是來源欄的表頭名稱，它驅動差異比較、累計總和、對指定父項的百分比與排名。BaseItem 是該欄位內以零為基底的項目位置，或常數 xlPivotBaseItemPrevious、xlPivotBaseItemNext 之一，表示「與相鄰項比較」。父列與父欄百分比完全無視 BaseField；它們自己挑對應軸上最內層的欄位

在 Delphi 裡建出帶排名的樞紐分析表

最短的可運作路徑是：建樞紐、在資料欄位上設顯示模式、存檔前把它實體化。下面的範例把每個類別的地區金額按降序排名，所以每列最大的金額就是第 1 名

uses
  SysUtils, lxHandleX, lxPivot;

var
  Book: TXLSXWorkbook;
  Data, Report: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
  Measure: TXLSPivotDataField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Data.Cells[1, 1].Value := 'Category';
    Data.Cells[1, 2].Value := 'Region';
    Data.Cells[1, 3].Value := 'Amount';
    // ... 填入第 2..7 列 ...
    Report := Book.Sheets.Add('Report');

    Pivot := Report.AddPivotTable('Data!A1:C7', 4, 3, 'RegionRank');
    Pivot.AddRowField('Category');     // 樞紐欄位 0
    Pivot.AddColumnField('Region');    // 樞紐欄位 1
    Measure := Pivot.AddDataFieldByName('Amount', xlpaSum);
    Measure.DisplayName := 'Rank by Region';

    Measure.ShowDataAs := xlpsdaNormal;              // 基本模式保持關閉
    Measure.ExtendedShowDataAs := xlpesdaRankDescending;
    Measure.BaseField := 1;                          // 跨 Region 排名

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

這裡有兩個值得養成的習慣。第一，選了延伸模式就把 ShowDataAs := xlpsdaNormal 明確設好，就算面對的是全新欄位也一樣，因為同一段程式碼常常後來被拿去處理匯入的樞紐，而它的資料欄位可能已經帶著基本模式。第二，檢查回傳值。被拒絕的實體化是原子的，這很好，但也代表漏掉檢查的話，出貨的檔案裡躺著上一版的網格

就算您從不呼叫 MaterializeNativePivotTable，BaseField 也能在存檔後保留下來。延伸模式一設好，HotXLS 就會為父項百分比、累計總和百分比與兩種排名模式寫出 baseField 屬性，所以設定完直接存檔的工作簿，重新整理時仍然告訴 Excel 該跨哪個欄位排名

延伸 URI 會在哪裡出錯？

承載 pivotShowAs 的延伸區塊由 URI {E15A36E0-9728-4e99-A89B-3F7291B0FE68} 標識，Excel 在重新整理時會區分大小寫比對這個字串。注意 4e99 裡的小寫 e。GUID 一般不分大小寫，很多 XML 寫出器會把它轉成大寫——這正是出錯的樣子：帶 4E99 的檔案在 Excel 裡打開，每個快取儲存格都正確，因為 Excel 只是照著存的值畫出來。但按過 RefreshTable 之後，Excel 就不認得這個延伸了，丟掉延伸模式，把那一欄重新算成普通總和。沒有修復提示、沒有錯誤，只有一個悄悄變成總計的排名欄

HotXLS 會照 Excel 期望的字面形式寫出這個 URI，所以只有當下游有東西改寫了套件——例如把 GUID 大小寫正規化、或重建 extLst 元件的後處理步驟——您才會被咬到。能抓到它的測試很便宜：把產生的檔案在 Excel 裡打開、重新整理樞紐、確認延伸欄位顯示的仍是排名或百分比而不是原始聚合。只讀快取儲存格的測試，面對壞檔案每次都會通過

Excel 怎麼給同分排名、怎麼排累計總和的順序？

Excel 的樞紐排名是稠密排名（dense rank），HotXLS 照樣重現。金額降序 30、30、20 時，結果是 1、1、2，而不是在工作表上用 RANK.EQ 會得到的那種比賽式 1、1、3。一個同分只算一個不同的前值。如果報表需要比賽式排名，請在來源資料或樞紐旁邊的工作表公式裡算，因為沒有任何樞紐顯示模式會產出它

累計總和、累計總和百分比與前後項比較，跟隨的是成員實際出現的順序，不是欄位項目清單的順序。快取建好之後才改排序的話，這個區別就重要了

// 列已按降序排序：累計總和按顯示順序累加
Pivot.Fields[0].SortType := xlpfstDescending;

Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff;   // 與…的百分比差異…
Measure.BaseField := 0;                    // …相對 Category 欄位
Measure.BaseItem := xlPivotBaseItemPrevious; // …相對上一個可見成員

if Book.MaterializeNativePivotTable(Pivot) <> 1 then
  raise Exception.Create('Pivot display mode was rejected');

SortType 改成 xlpfstDescending 之後，「前一項」指的是顯示在上方的成員，累計總和從排好序的清單頂端開始。改走存放的項目清單的實作，會產出看似合理、但任何人一重新整理就與 Excel 對不上號的數字。邊界成員也要小心：第一個成員沒有前一個鄰居，只有百分比模式會拿邊界成員跟自己比；差異模式則把那個儲存格交給 Excel 自己的邊界行為，而不是自作主張補個零

累計百分比為什麼可能超過 100%？

對平均值做累計總和百分比，完全可以合理地超過 100%，因為 Excel 累加的是各顯示成員的聚合值，除數卻不是它們的總和。對總和來說兩者是同一件事，所以最後一個成員正好落在 100%。對平均值來說，三個類別平均加起來再除以整體平均，不是任何東西的分數，最後一列可以遠超過 100%。HotXLS 在這裡刻意跟 Excel 一致，而不是把值夾住，因為夾過的數字會跟重新整理後的檔案對不上

除數還取決於基準欄位坐在哪裡。基準欄位是其軸上最內層欄位時，除數是該軸位置的原始聚合值。基準欄位是巢狀版面裡的外層欄位時，除數是沿其餘軸上對應成員加總的結果。兩種位置都各自對照過 Excel 輸出，因為「一個巢狀層級適用、另一層悄悄失靈」的規則，正是經典的樞紐 bug

單純百分比的行為與累計版不同。列、欄與總計百分比都是除以相應範圍的原始聚合值，平均值這類不可加聚合也一樣，所以平均值上的總計百分比，是該儲存格平均除以整體平均，不是某個加總池子的份額

比較模式裡的空白、#NULL! 與 #DIV/0!

對基準的百分比比較區分三種空情況，各自產出不同的儲存格。分子交集處沒有來源列時，儲存格顯示 #NULL!。基準項目在該交集沒有資料時，儲存格留白。基準項目存在、聚合值為零時，儲存格顯示 #DIV/0!。讀實體化網格的程式碼，應該用 VarIsEmpty 與 VarIsError 來測，而不是假設每個值儲存格裡都是數字

單純百分比寬容一些：分子缺席就當零。父列與父欄百分比多一種情況——對應軸上沒有欄位的樞紐沒有父項可除，所以那些結果留白。總計也遵循各模式自己的語意，包括比較或累計走過的軸上留白的總計；累計總和底下整列空白的總計列是正確結果，不是實體化失敗

切換匯入樞紐的模式

改變來自既有工作簿的樞紐的顯示模式，做法相同，只多一個旗標。把 True 傳給 AAdoptExisting，MaterializeNativePivotTable 就可以接管 Excel 已為該樞紐寫好的儲存格

Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
  ShowDataAs := xlpsdaNormal;                    // 清掉基本模式
  ExtendedShowDataAs := xlpesdaPercentOfParentRow; // 不需要 BaseField
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
  raise Exception.Create('Could not re-materialize the pivot');

模式變更時，HotXLS 會從暫存的定義裡移除過時的 showDataAs、pivotShowAs 與 baseItem 屬性，所以存出的檔案絕不會在新延伸模式旁邊還掛著過時的基本模式。同一個暫存步驟也讓資料欄位與非作用欄位上的 compact 與 outline 旗標跟報表版面保持同步，這很重要，因為 Excel 推導重新整理後的資料標題時靠的是那些旗標，不只是列欄位與欄欄位。無效的列舉值同樣會被拒絕：在 Delphi 裡把 255 這個 byte 轉型進任一列舉，可能號位延伸成 -1，所以 HotXLS 上下界都查，回傳 -1 且不碰網格

如果還要在同一份報表裡加計算欄位或計算項目，先讀在 HotXLS 裡建立含計算欄位的樞紐分析表並重新整理；要避免 Excel 修復檔案的欄位層級 XML 規則，見Delphi 的 XLSX 樞紐欄位結構描述有效性

FAQ

HotXLS 的資料欄位可以同時用 ShowDataAs 與 ExtendedShowDataAs 嗎？

不行。TXLSPivotDataField.ShowDataAs 與 ExtendedShowDataAs 互斥；兩者都設時，MaterializeNativePivotTable 回傳 -1，先前的輸出維持不變

為什麼我的樞紐排名在 Excel 重新整理後變成了總和？

延伸 URI 八成被改寫成不同的大小寫了。Excel 區分大小寫比對 {E15A36E0-9728-4e99-A89B-3F7291B0FE68}，比不中時 RefreshTable 之後就退回普通聚合

HotXLS 的樞紐排名在同分之後會跳號嗎？

不會。HotXLS 遵循 Excel 的稠密樞紐排名，所以 30、30、20 排出 1、1、2

Show Values As 支援、原生樞紐實體化與其餘 XLSX 與 XLS 功能集，都在給 Delphi 與 C++Builder 用的 HotXLS Delphi spreadsheet component 裡