クラシック XLS ピボットテーブル API

HotXLS は、XLS と XLSX のワークブックに対して、型付きピボットテーブルの検査、作成、結果の実体化をサポートします。リレーションシップ駆動の XLSX ロードは任意のパッケージ パーツ名を解決し、キャッシュ レコード、共有アイテム、フィールド メタデータ、フィルター、グループ化、計算済み定義、書式、拡張を公開モデルへ復元します。クラシック XLS の入力は、元のピボット レコードを利用可能なまま保ち、忠実な保存の往復を実現します

ラウンドトリップと型付きモデル

クラシック ワークブックに既にピボットテーブルが含まれている場合、リーダーは生の BIFF レコードを保持すると同時に型付きモデルも公開します。クラシック ファイルから読み込まれたテーブルとキャッシュは FromRawBlobs = True を保つため、保存出力は元のバイト列を再生して忠実性を維持します。XLSX のピボットは所有するパッケージのリレーションシップを通じて読み書きされ、プログラムで作成したピボットは型付きライターを使用します

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 が期待するのはこのインデックスです。同じ文字列を 2 回登録しても同じインデックスが返ります

DestRow と DestCol は Cells[Row, Col] と同じ 1 ベースなので、AddPivotTable('Data!A1:C9', 3, 1, 'SalesPivot') はピボットテーブルを A3 にアンカーします。TXLSPivotTable の位置プロパティ（FirstRow .. FirstDataCol）と TXLSPivotCache のソース範囲も 1 ベースで、XLSX エンジンと同じ扱いです。65,536 x 256 のシートの外側にアンカーを置くと nil が返ります

ドラッグ アンド ドロップのピボット レイアウトを構築する

TXLSPivotDesigner は非ビジュアルのレイアウト モデルで、ピボット ロジックを 1 つの UI ツールキットに結び付けることなく、VCL、FMX、Web、コマンドラインのフィールド リスト インターフェースを支えられます

各フィールドは、利用可能、行、列、フィルター、値のいずれかのゾーンと、決定論的な位置を報告します。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;
      

値の表示形式

Make を呼び出す前、またはワークブックを保存する前に、各データ フィールドに ShowDataAs を設定します。Difference、Percent、PercentDiff には、0 から始まる BaseField ピボット フィールド インデックスと BaseItem が必要です。RunTotal は BaseField だけを要求します。行、列、合計、インデックスの各モードは、設定された集計方法でソース レコードから分母を導出します

明示的な BaseItem は、ベース ピボット フィールド内の 0 から始まるアイテム インデックスです。隣接アイテムとの比較には 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 は安定したデータ値順序のための 0 から始まる値フィールドを選択します

計算式

計算済みキャッシュ フィールドはソース レコードごとに 1 回実行され、計算済みアイテムは兄弟アイテムの集計に対して実行され、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 ブック メタデータ 保持