LlosLab 軟體開發部落格適用於 PDF、Delphi 和文件軟體開發人員的工程筆記。

技術文章

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

HotXLS 元件

HotXLS 可以在未安裝 Excel 且沒有 COM 自動化的機器上，從 Delphi 和 C++Builder 建立和重新整理原生的 XLSX 樞紐分析表。您只需使用來源範圍呼叫 AddPivotTable、將欄位拖放至列、欄、頁和資料軸上，並加入計算項目或總計百分比顯示，該元件就會寫入 pivotCacheDefinition 和 pivotTableDefinition 部分，使 Excel 開啟時能呈現動態、可重新整理的樞紐分析表

讓這些努力物超所值的應用場景是報表伺服器。您每晚產生數百個活頁簿，每個活頁簿包含一個彙總單一帳戶的樞紐分析表，而下個月數據變動時，每個檔案都必須反映新的來源行。從 Windows 服務驅動 Excel 非常脆弱，且未獲得伺服器端使用的授權，而手動編寫樞紐分析表的 XML 更是一個永無止境的規格考古專案。HotXLS 介於這兩個死胡同之間：一個基於 OOXML 樞紐分析表部分的強型別物件模型，使填滿儲存格的同一個 Pascal 程式碼也能在同一個程序中宣告樞紐分析表並重建其快取

如何在沒有 Excel 的情況下在 Delphi 中建立樞紐分析表？

您只需一次呼叫即可建立樞紐分析表，然後將欄位放置在軸上。AddPivotTable 接收 A1 標記法表示的來源範圍、資料表定位的目標儲存格，以及名稱；它會解析該範圍，掃描每一欄以推斷其資料型別，建立樞紐分析表快取（或重複使用已繫結至該相同範圍的快取），並傳回 TXLSPivotTable，其所有欄位最初均處於軸外。從那裡，便利的方法 AddRowField、AddColumnField、AddPageField 和 AddDataFieldByName 會設定版面配置，且每個資料欄位會接收 TXLSPivotAggregation 列舉中的 11 種彙總之一（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）或日期階層旗標（帶有 GroupMonths、GroupQuarters、GroupYears 和 GroupStartDate / GroupEndDate 範圍的 GroupByDate）。HotXLS 輸出對應的 <fieldGroup><rangePr groupBy="months"/> 元素，使按月份或 1000 寬的數值頻帶群組的欄位開啟時能依 Excel 原本的群組方式呈現

頁面欄位是資料表上方的篩選器下拉功能表。AddPageField 將欄位放置在頁面軸上，且 PageItemIndex 會預先選取單一快取項目，預設為 xlPageItemAll（$7FFD，代表 (All)）。若要允許讀者同時勾選多個項目，請設定 MultipleItemSelectionAllowed := True，HotXLS 會將其寫入為 <pivotField multipleItemSelectionAllowed="1"/>。對於手動選擇之外的準則，每個欄位都攜帶一個具型別的 Filters 集合，橫跨 OOXML 的 ST_FilterType 系列——計數、百分比、加總、標題、數值和日期篩選器，每個項目都將篩選器型別與其比較值配對

來源變更時如何重新整理樞紐分析表快取？

TXLSXWorkbook 上的 RefreshPivotCache 會重新掃描來源範圍並就地重建快取，這正是批次處理管線在編輯底層行之後所需要的。傳入快取 ID，該方法會重新讀取來源範圍中的每個儲存格（第一列為頁首，第二列開始為資料）、透過重新去除重複值和重新衍生每種類型的邊界來重建每個欄位的共用項目網域，並重寫每個記錄的項目索引。它在成功時傳回 1，在快取 ID 未知或缺少來源工作表時傳回 -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 使儲存的快取在程序內保持最新，且因為資料表依據 ID 繫結至快取，因此從該快取繪製的每個樞紐分析表都能看到更新。它不會執行的是排版或彙總可見的網格——Excel 在檔案開啟時仍會從重新整理後的快取中重新計算轉譯的資料表

HotXLS 寫入的內容與 Excel 計算的內容

牢記這種分工，就不會有任何感到意外的事。HotXLS 是一個定義寫入器：它產生 pivotCacheDefinition、快取記錄和 pivotTableDefinition，其中包含軸、彙總、計算項目與成員、數值顯示、群組和篩選器。Excel 是計算器：在開啟時實體化群組值、評估計算的公式、套用 showDataAs 轉換，並彙總內容。使用者看到的數值是 Excel 的，是從 HotXLS 寫入的指令中產生的，這就是為什麼每個公式和基準索引都必須在寫入時正確，而不是在轉譯時進行檢查

動態陣列支援的限制

在圍繞此功能進行設計之前，有兩個限制值得瞭解。AddPivotTable 解析帶有選用工作表前置詞的矩形 A1 範圍；具名範圍和外部活頁簿來源不在建構器所能解析的範圍內，但從 Excel 建立的文件中讀取的快取在雙向往返中仍保留其具名範圍來源。且 HotXLS 從磁碟讀取的由 Excel 建立的樞紐分析表在儲存時會逐位元組重現，因此此處介紹的具型別編輯能乾淨地套用至您在程式碼中建立的樞紐分析表，而現有的樞紐分析表則保持無損。對於報表的輸入端——樞紐分析表彙總的已驗證儲存格和已篩選的資料表——請參考資料驗證、AutoFilter 和結構化資料表

此處介紹的樞紐分析表模型是適用於 Delphi 和 C++Builder 的標準 HotXLS Delphi Excel 元件 的一部分，該元件從同一個物件模型中讀寫 XLSX 具型別樞紐部分以及經典的 BIFF8 記錄