傳統 XLS 樞紐分析表 API

建立新的傳統 XLS 樞紐分析表

往返與有型別模型

當活頁簿已經包含樞紐分析表時，讀取器會保留原始 BIFF 記錄，並同時提供盡力而為的型別化模型。從檔案載入的表格與快取會維持 FromRawBlobs = True，因此儲存輸出時會重播原始位元組以保真。以程式建立的樞紐會使用型別化寫入器

const
  xlPivotBaseItemPrevious = $7FFB;
  xlPivotBaseItemNext = $7FFC;

type
  TXLSPivotFieldAxis = (xlpfaNone, xlpfaRowField, xlpfaColumnField, xlpfaPageField, xlpfaDataField);
  TXLSPivotAggregation = (xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP);
  TXLSPivotShowDataAs = (xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal, xlpsdaIndex);
  TXLSPivotExtendedShowDataAs = (xlpesdaNone, xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending, xlpesdaRankDescending);
  TXLSPivotSubtotal = (xlpsDefault, xlpsSum, xlpsCount, xlpsAverage, xlpsMax, xlpsMin, xlpsProduct, xlpsCountNums, xlpsStdDev, xlpsStdDevP, xlpsVar, xlpsVarP);
  TXLSPivotSubtotals = set of TXLSPivotSubtotal;
  TXLSPivotDesignerZone = (xlpdzAvailable, xlpdzRows, xlpdzColumns, xlpdzFilters, xlpdzValues);

  TXLSPivotCacheValue = record
    ValueType: TXLSPivotCacheFieldType;
  end;

  TXLSPivotCacheField = class
    property Name: WideString;
    property FieldType: TXLSPivotCacheFieldType;
    property ItemCount: Integer;
    property Items[Index: Integer]: TXLSPivotCacheItem;
  end;

  TXLSPivotCache = class
    property CacheId: Integer;
    property SourceRangeSheet: WideString;
    property SourceFirstRow: Integer;
    property SourceFirstCol: Integer;
    property SourceLastRow: Integer;
    property SourceLastCol: Integer;
    property RecordCount: Integer;
    property FieldCount: Integer;
    property Fields[Index: Integer]: TXLSPivotCacheField;
    property RecordIndices[RecordIdx, FieldIdx: Integer]: Integer;
    property FromRawBlobs: Boolean;
  end;

  TXLSPivotCaches = class
    function Add: TXLSPivotCache;
    function FindByCacheId(ACacheId: Integer): TXLSPivotCache;
    property Count: Integer;
    property Items[Index: Integer]: TXLSPivotCache;
  end;

  TXLSPivotField = class
    function AddItem: TXLSPivotItem;
    function AddCalculatedItem(const AName, AFormula: WideString): TXLSPivotCalculatedItem;
    property Name: WideString;
    property Axis: TXLSPivotFieldAxis;
    property CacheFieldIndex: Integer;
    property ItemCount: Integer;
    property Items[Index: Integer]: TXLSPivotItem;
    property SubtotalMask: Word;
    property Subtotals: TXLSPivotSubtotals;
    property PageItemIndex: Integer;
    property SortType: TXLSPivotFieldSortType;
    property SortDataField: Integer;
    property Filters: TXLSPivotFilters;
  end;

  TXLSPivotDataField = class
    property Field: TXLSPivotField;
    property Aggregation: TXLSPivotAggregation;
    property NumberFormat: Word;
    property DisplayName: WideString;
    property ShowDataAs: TXLSPivotShowDataAs;
    property ExtendedShowDataAs: TXLSPivotExtendedShowDataAs;
    property BaseField: Integer;
    property BaseItem: Integer;
  end;

  TXLSPivotSupplementalRecord = class
    property RecID: Word;
    property BodyLength: Integer;
    property Body[Index: Integer]: Byte;
  end;

  TXLSPivotTable = class
    function AddField(const AName: WideString; ACacheFieldIndex: Integer): TXLSPivotField;
    function FindFieldByName(const AName: WideString): TXLSPivotField;
    function SetFieldAxis(const AName: WideString; AAxis: TXLSPivotFieldAxis): TXLSPivotField;
    function AddRowField(const AName: WideString): TXLSPivotField;
    function AddColumnField(const AName: WideString): TXLSPivotField;
    function AddPageField(const AName: WideString): TXLSPivotField;
    function AddDataField(AField: TXLSPivotField; AAggregation: TXLSPivotAggregation): TXLSPivotDataField;
    function AddDataFieldByName(const AName: WideString; AAggregation: TXLSPivotAggregation = xlpaSum): TXLSPivotDataField;
    function AddCalculatedMember(const AName, AFormula: WideString): TXLSPivotCalculatedMember;
    function AddSupplementalRecord(ARecID: Word; const ABody: TBytes): TXLSPivotSupplementalRecord;
    function Make(AWriter: TlxPivotResultWriter): Integer;
    property Name: WideString;
    property DataCaption: WideString;
    property CacheId: Integer;
    property Cache: TXLSPivotCache;
    property FieldCount: Integer;
    property Fields[Index: Integer]: TXLSPivotField;
    property DataFieldCount: Integer;
    property DataFields[Index: Integer]: TXLSPivotDataField;
    property RowGrandTotals: Boolean;
    property ColumnGrandTotals: Boolean;
    property MaterializeGrandTotalRow: Boolean;
    property MaterializeGrandTotalColumn: Boolean;
    property SupplementalRecordCount: Integer;
    property SupplementalRecords[Index: Integer]: TXLSPivotSupplementalRecord;
    property FromRawBlobs: Boolean;
  end;

  TXLSPivotDesigner = class
    constructor Create(APivotTable: TXLSPivotTable = nil);
    procedure ClearLayout;
    function MoveField(const AName: WideString; AZone: TXLSPivotDesignerZone; APosition: Integer = -1): Boolean;
    function SetAggregation(const AName: WideString; AAggregation: TXLSPivotAggregation): Boolean;
    function Validate(out ErrorText: WideString): Boolean;
    function Apply(AWriter: TlxPivotResultWriter): Integer;
    property PivotTable: TXLSPivotTable;
    property FieldCount: Integer;
    property Fields[Index: Integer]: TXLSPivotDesignerField;
  end;

  TXLSPivotTables = class
    function Add: TXLSPivotTable;
    function FindByName(const AName: WideString): TXLSPivotTable;
    property Count: Integer;
    property Items[Index: Integer]: TXLSPivotTable;
  end;
      

工作表和活頁簿進入點

type
  TXLSWorksheet = class
    function AddPivotTable(const SourceRangeA1: WideString; DestRow, DestCol: Integer; const Name: WideString): TXLSPivotTable;
    function PivotSetDataFieldFormat(DataField: TXLSPivotDataField; const FormatStr: WideString): Word;
    property PivotTables: TXLSPivotTables;
  end;

  TXLSWorkbook = class
    property PivotCaches: TXLSPivotCaches;
    function RegisterNumFormat(const FormatStr: WideString): Word;
end;
      

RegisterNumFormat 會註冊數字格式字串並傳回其 BIFF8 ifmt 索引， TXLSPivotDataField.NumberFormat 和 TXLSPivotCacheField.NumberFormat 使用的正是這個索引；重複註冊同一個字串會得到同一個索引

DestRow 和 DestCol 從 1 開始計數，跟 Cells[Row, Col] 一樣，所以 AddPivotTable('Data!A1:C9', 3, 1, 'SalesPivot') 會把樞紐分析表錨定在 A3。TXLSPivotTable 上的各個位置屬性（FirstRow .. FirstDataCol）和 TXLSPivotCache 上的來源範圍同樣從 1 開始，與 XLSX 引擎一致；錨點落在 65,536 x 256 工作表之外時傳回 nil

建立拖放式樞紐配置

TXLSPivotDesigner 是一個非視覺化的配置模型，可以撐起 VCL、FMX、Web 或命令列的欄位清單介面，不必把樞紐邏輯綁死在任何一種 UI 工具組上

每個欄位都會回報自己落在可用、列、欄、篩選或值哪個區域，外加一個確定性的位置； MoveField 套用放置動作， SetAggregation 變更值欄位的彙總方式， Validate 回報不完整的配置， Apply 則先驗證再透過一般的樞紐寫入器把結果落地

新增的數值與日期值欄位預設為 xlpaSum，文字、布林、錯誤、空白與混合型欄位則預設為 xlpaCount

var
  Designer: TXLSPivotDesigner;
  ErrorText: WideString;
begin
  Designer := TXLSPivotDesigner.Create(Pivot);
  try
    Designer.MoveField('Region', xlpdzRows);
    Designer.MoveField('Quarter', xlpdzColumns);
    Designer.MoveField('Closed', xlpdzFilters);
    Designer.MoveField('Revenue', xlpdzValues);
    Designer.SetAggregation('Revenue', xlpaAverage);
    if Designer.Validate(ErrorText) then
      Designer.Apply(ResultWriter.WriteCell);
  finally
    Designer.Free;
  end;
end;
      

建立樞紐分析表

uses lxHandle;

var
  Workbook: TXLSWorkbook;
  SourceSheet, PivotSheet: TXLSWorksheet;
  Pivot: TXLSPivotTable;
  RowField: TXLSPivotField;
  DataField: TXLSPivotDataField;
begin
  Workbook := TXLSWorkbook.Create;
  try
    SourceSheet := Workbook.Sheets[1];
    SourceSheet.Name := 'SalesData';
    SourceSheet.Cells[1, 1].Value := 'Region';
    SourceSheet.Cells[1, 2].Value := 'Product';
    SourceSheet.Cells[1, 3].Value := 'Revenue';

    PivotSheet := Workbook.Sheets.Add;
    PivotSheet.Name := 'PivotReport';
    Pivot := PivotSheet.AddPivotTable('SalesData!A1:C20', 4, 1, 'SalesPivot');
    RowField := Pivot.AddRowField('Region');
    Pivot.AddColumnField('Product');
    DataField := Pivot.AddDataFieldByName('Revenue', xlpaSum);
    RowField.Subtotals := [xlpsSum];
    PivotSheet.PivotSetDataFieldFormat(DataField, '$#,##0.00');

    Workbook.SaveAs('pivot-report.xls');
  finally
    Workbook.Free;
  end;
end;
      

值顯示方式

在呼叫 ShowDataAs 或儲存活頁簿之前，先在每個資料欄位上設定 Make 。 Difference、 Percent與 PercentDiff 需要一個以零為基底的 BaseField 樞紐欄位索引，外加一個 BaseItem。 RunTotal 只需要 BaseField。列、欄、總計與索引模式會用設定的彙總方式，從來源記錄推導分母

明確指定的 BaseItem 是基準樞紐欄位內以零為基底的項目索引。相鄰項目的比較請用 xlPivotBaseItemPrevious 或 xlPivotBaseItemNext 。缺比較交集的位置會保留空白，分母為零則產生 #DIV/0!

DataField := Pivot.AddDataFieldByName('Revenue', xlpaSum);
DataField.ShowDataAs := xlpsdaPercentDiff;
DataField.BaseField := 0;
DataField.BaseItem := xlPivotBaseItemPrevious;
CellsWritten := Pivot.Make(ResultWriter.WriteCell);
      

篩選、群組與排序

Make 會在領域建構之前先套用隱藏項目與頁面選取，接著評估標題、日期、計數、百分比、總和與值篩選。數值範圍、日曆群組與離散群組會在彙總之前使用快取欄位的群組中繼資料。 SortType 決定穩定的標籤排序， SortDataField 則指定以零為基底的值欄位，用來做穩定的資料值排序

計算運算式

計算型快取欄位會對每一筆來源記錄執行一次；計算項目會跨同層項目的彙總結果執行；而 MemberType = 'data' 的計算成員則在來源彙總完成後加入虛擬值欄位。公式名稱不分大小寫，可以用方括號或引號包起來。支援算術、比較、百分比、括號，以及常用的數值與邏輯函式，並透過重複的相依性檢查確保計算定義沒有循環

ProfitField := Pivot.Cache.AddField('Profit', xlpcftNumber);
ProfitField.Formula := '=Revenue-Cost';
ProfitField.DatabaseField := False;
Pivot.AddDataField(Pivot.AddField('Profit', 4), xlpaSum);

RegionField.AddCalculatedItem('Combined', '=North+South');
Pivot.AddCalculatedMember('Margin', '=Profit/Revenue').MemberType := 'data';
      

小計與總計

每個列欄位或欄欄位都能透過 Subtotals 和 SubtotalTop，在其明細項目的上方或下方產生任何設定的小計函式。 RowGrandTotals 和 ColumnGrandTotals 控制儲存活頁簿時的政策； MaterializeGrandTotalRow 和 MaterializeGrandTotalColumn 則讓結果回呼直接輸出明確的總計列與總計欄，不必重新掃描來源記錄

擴充的值顯示方式

ExtendedShowDataAs 提供父項百分比、父列百分比、父欄百分比、執行總計百分比，以及遞增或遞減排名。這些模式走 XLSX 擴充模型，不會輸出傳統 BIFF 不支援的資料欄位值

檢查現有樞紐分析表活頁簿

Workbook.Open('excel-authored-pivot.xls');
for SheetIndex := 1 to Workbook.Sheets.Count do
begin
  Sheet := Workbook.Sheets[SheetIndex];
  for PivotIndex := 0 to Sheet.PivotTables.Count - 1 do
  begin
    Pivot := Sheet.PivotTables[PivotIndex];
    Writeln(Pivot.Name);
    Writeln(Pivot.FieldCount);
    if Assigned(Pivot.Cache) then
      Writeln(Pivot.Cache.SourceRangeSheet);
  end;
end;
      

相關主題

• IXLSWorkbook 介面
• TXLSWorksheet 類別
• InteractiveFeatures 示範
• 傳統 XLS 活頁簿中繼資料 保留