用 HotXLS 在 Delphi 建立與重新整理 Excel 樞紐分析表

HotXLS 能從 Delphi 與 C++Builder 建置並重新整理原生的 XLSX 樞紐分析表，機器上不必安裝 Excel，也不必用 COM 自動化。您用一個來源範圍呼叫 AddPivotTable，把欄位放上列、欄、頁面與資料四個軸，再疊上計算項目或佔總計百分比的顯示方式，元件就會寫出 Excel 能開成活的、可重新整理之樞紐分析的 pivotCacheDefinition 與 pivotTableDefinition 組件

讓這番功夫值得的場景是報表伺服器。您一個晚上產生數百份活頁簿，每份都帶著一個彙總單一帳戶的樞紐分析，而下個月數字變了，每個檔案都得反映新的來源列。從 Windows 服務驅動 Excel 既脆弱、在伺服器上又沒有授權，手寫樞紐分析 XML 則是一項永無止境的規範考古工程。HotXLS 就坐在這兩條死路之間：它在 OOXML 樞紐分析組件之上提供一套具型別的物件模型，所以填儲存格的那份 Pascal 程式，也能在同一個行程裡宣告樞紐分析並重建它的快取

沒有 Excel，要怎麼在 Delphi 中建立樞紐分析表？

您用一次呼叫建立它，然後把欄位放到軸上。AddPivotTable 接受 A1 標記法的來源範圍、表格錨定的目的地儲存格，以及一個名稱；它會剖析範圍、掃描每一欄以推斷其資料型別、建置一份樞紐分析快取（或重用已繫結到同一範圍的既有快取），並回傳一個 TXLSPivotTable，其欄位一開始全都不在軸上。接下來由便利方法 AddRowField、AddColumnField、AddPageField 與 AddDataFieldByName 接起版面配置，而每個資料欄位都從 TXLSPivotAggregation 列舉中取用十一種彙總方式之一（xlpaSum、xlpaCount、xlpaAverage、xlpaMax、xlpaMin、xlpaProduct、xlpaCountNums、xlpaStdDev、xlpaStdDevP、xlpaVar、xlpaVarP）

uses
  lxHandleX, lxPivot;

var
  Book : TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[1];                 // 報表工作表（XLSX 引擎中以 1 為起點）

    // 來源為 'Data' 工作表上的 A1:E500；把樞紐分析錨定在第 3 列、第 1 欄。
    Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
    if Pivot <> nil then
    begin
      Pivot.AddRowField('Region');
      Pivot.AddColumnField('Quarter');
      Pivot.AddPageField('Year');
      Pivot.AddDataFieldByName('Revenue', xlpaSum);
      Pivot.AddDataFieldByName('Units', xlpaAverage);
      Book.SaveAs('sales-pivot.xlsx');
    end;
  finally
    Book.Free;
  end;
end;

為什麼快取與表格是兩個分開的組件

樞紐分析表其實是兩件互相參照的產物，而看懂這道分野，才能把後面的一切理清楚。pivotCacheDefinition 是資料快照：一個指向來源範圍的 worksheetSource，以及每欄一個的 cacheField，裝著該欄的不重複值（它的 sharedItems）加上推導出來的界線。pivotTableDefinition 則是檢視：哪個快取欄位坐在哪個軸上、資料欄位與它們的彙總方式、各項版面配置開關。檢視透過活頁簿的 pivotCaches 關聯，以 cacheId 繫結到快取，正如 ECMA-376 Part 1 §18.10 與 [MS-XLSX] 所規定的那樣

這層間接不是官僚作風，它買到兩件事。一份快取可以支撐好幾張表，所以重新整理快取一次，就更新了由它繪出的每個檢視。而且快取存的是每欄去重後的值加上一張逐筆記錄的索引表，而不是原始格線，這正是為什麼建置樞紐分析是在掃描來源，而不是複製儲存格。HotXLS 對兩套引擎都以相同方式建模，所以上面那段程式碼，幾乎與傳統 .xls 樞紐分析背後的二進位 SX 記錄版面一文中記載的傳統路徑一模一樣。若您的來源位於另一個工作表，或者您透過名稱定址它，範圍前置詞以及定義名稱與跨工作表參照都遵循一般的 A1 規則，含空白的名稱則允許用引號括住工作表名稱

計算項目不是計算欄位

這三個詞指的是三件不同的東西，把它們混為一談正是樞紐分析的經典錯誤。計算項目住在單一欄位裡，按名稱組合該欄位自己的項目，所以在 Region 欄位中，您可以定義一個等於 North 加 South 的合成 CoreMarkets 列；HotXLS 以 TXLSPivotField.AddCalculatedItem 暴露它，並在該欄位的 <calculatedItems> 底下寫出一個 <calculatedItem>。計算欄位則不同：它是從其他欄推導出來的新值，例如由 Revenue 與 Cost 得出的 Margin，並透過 TXLSPivotCacheField.Formula 以公式的形式掛在快取欄位上。計算成員以 TXLSPivotTable.AddCalculatedMember 加入，是表格層級的自訂成員，可以當作量值（成員型別 data）或維度成員，主要適用於 OLAP 形態的樞紐分析

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // 計算「項目」按名稱組合單一欄位的各個項目。
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // 計算「成員」宣告在表格層級。成員型別
  // 'data' 標示它為量值；型別留空則是維度成員。
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

有一條誠實的界線同時適用於這三者。HotXLS 把公式文字輸出到定義 XML 裡，它並不求值。計算項目、計算欄位或計算成員，都是由 Excel 在開啟檔案時算出來的，就跟它算格線裡每個彙總值一樣。HotXLS 寫的是指令，不是結果，所以您提供的公式必須是 Excel 自家方言中有效的樞紐分析公式，並以 Excel 的方式參照欄位與項目名稱

要怎麼把值顯示成佔總計的百分比？

您在資料欄位上設定值的顯示方式，而不是自己去換算數字。TXLSPivotDataField.ShowDataAs 接受 TXLSPivotShowDataAs 列舉，它對映 OOXML 的 ST_ShowDataAs 值：xlpsdaNormal、xlpsdaDifference、xlpsdaPercent、xlpsdaPercentDiff、xlpsdaRunTotal、xlpsdaPercentOfRow、xlpsdaPercentOfCol、xlpsdaPercentOfTotal 與 xlpsdaIndex。常見的手法，是把同一個來源欄放到資料軸上兩次，一次當原始總和，一次當佔總計的比重，讓報表同時顯示數字與它的分量

var
  Rev, Share: TXLSPivotDataField;
begin
  Rev := Pivot.AddDataFieldByName('Revenue', xlpaSum);

  Share := Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Share.DisplayName := 'Share of total';
  Share.ShowDataAs  := xlpsdaPercentOfTotal;

  // 項目相對的模式需要一個比較基準。沿著 'Quarter'
  // （快取欄位索引 3）往下的累計會這樣寫：
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // 轉換所依循的快取欄位索引
  //   Share.BaseItem   := $7FFD;     // $7FFD = 「(All)」
end;

xlpsdaPercentOfTotal 不需要基準，因為它是相對於總計的，但項目相對的那些模式就需要。xlpsdaDifference、xlpsdaPercentDiff 與 xlpsdaRunTotal 需要 BaseField（比較所依循的快取欄位索引），而以項目為錨的形式還需要一個 BaseItem 索引，其中 $7FFD 代表 (All) 哨兵值。HotXLS 把這些寫成 <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> 屬性，而且和計算公式一樣，把算術留給 Excel

日期與數字分組，以及頁面欄位篩選

分組設定在快取欄位上，而不是樞紐分析欄位上，因為它改變的是來源定義域如何分桶。請在 Cache.FindFieldByName 回傳的快取欄位上設定 HasGroup := True，接著在數值範圍那組旋鈕（GroupStartNum、GroupEndNum、GroupInterval）與日期階層那組旗標（GroupByDate 搭配 GroupMonths、GroupQuarters、GroupYears，以及 GroupStartDate / GroupEndDate 的區間）之間擇一。HotXLS 會輸出對應的 <fieldGroup><rangePr groupBy="months"/> 元素，讓按月或按 1000 寬數值帶分組的欄位，開起來就像 Excel 自己分的一樣

頁面欄位就是表格上方那些篩選下拉選單。AddPageField 把一個欄位放到頁面軸上，而 PageItemIndex 會預先選定單一快取項目，預設值為 xlPageItemAll（$7FFD，意思是 (All)）。若要讓讀者一次勾選多個項目，請設定 MultipleItemSelectionAllowed := True，HotXLS 會把它寫成 <pivotField multipleItemSelectionAllowed="1"/>。若需要的條件超出手動挑選的範圍，每個欄位都帶著一個具型別的 Filters 集合，橫跨 OOXML 的 ST_FilterType 各族——計數、百分比、加總、標題、值與日期篩選——每一筆條目都把篩選型別與它的比較值配成一對

來源變動時，要怎麼重新整理樞紐分析快取？

TXLSXWorkbook 上的 RefreshPivotCache 會重新掃描來源範圍並就地重建快取，這正是批次流水線在編輯完底層列之後所需要的。傳入快取識別碼，這個方法就會重新讀取來源範圍中的每個儲存格（首列為標題，資料從第二列起），以重新去重值與重新推導各型別界線的方式重建每個欄位的共用項目定義域，並改寫逐筆記錄的項目索引。成功時回傳 1，快取識別碼不明或來源工作表不存在時回傳 -1

var
  Rc: Integer;
begin
  // ...自從樞紐分析建好之後，來源資料已經變動...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // 一筆更正後的 Revenue 數字

  // 從來源重建快取的共用項目與記錄索引。
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = 已重新整理，-1 = 快取或來源不存在
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

這裡的語意界線值得說白。在這個方法出現之前，HotXLS 依賴 refreshOnLoad="1" 旗標，把所有重新整理工作留給 Excel 下次開檔時處理；當檔案會由人打開時這沒問題，但在必須交出正確資料的無人值守流水線裡就毫無用處。RefreshPivotCache 讓存下來的快取在行程內就是最新的，而由於表格是以識別碼繫結快取，從該快取繪出的每個樞紐分析都會看到更新。它沒做的，是排出或彙總可見的格線——開檔時，Excel 仍會依據重新整理過的快取重新計算呈現出來的表格

HotXLS 寫什麼，Excel 算什麼

把這道分工放在眼前，就不會有任何意外。HotXLS 是定義的撰寫者：它產出 pivotCacheDefinition、快取記錄與 pivotTableDefinition，連同各軸、彙總方式、計算項目與計算成員、值顯示方式、分組與篩選一應俱全。Excel 則是計算者：開檔時它具體化分組的桶、對計算公式求值、套用 showDataAs 轉換，並彙總主體。使用者看到的值是 Excel 的產物，由 HotXLS 寫下的指令產生，這正是為什麼每一條公式與每一個基準索引都必須在寫入時就正確，而不是等到呈現時才檢查

在圍繞這項功能做設計之前，有兩條限制值得先知道。AddPivotTable 剖析的是帶選用工作表前置詞的矩形 A1 範圍；具名範圍與外部活頁簿來源不在建置器解析得了的範圍內，不過從 Excel 撰寫之檔案讀進來的快取，往返時會保留它們的具名範圍來源。而 HotXLS 從磁碟讀進來的、由 Excel 建立的樞紐分析，存檔時會逐位元組原樣播放回去，所以本文描述的具型別編輯，能乾淨地套用在您以程式碼建置的樞紐分析上，既有的樞紐分析則維持無損。至於報表的輸入面——樞紐分析所彙總的那些經驗證儲存格與已篩選表格——請參閱資料驗證、自動篩選與結構化表格

本文示範的樞紐分析模型，是適用於 Delphi 與 C++Builder 的標準版 HotXLS Delphi Excel 元件的一部分，它以同一套物件模型讀寫 XLSX 的具型別樞紐分析組件與傳統的 BIFF8 記錄