LlosLab Software Development BlogPDF、Delphi、およびドキュメントソフトウェア開発者向けのエンジニアリングノート

技術記事

DelphiでHotXLSを使用してExcelピボットテーブルを構築および更新する

HotXLSコンポーネント

HotXLSは、ExcelのインストールやCOMオートメーションを使用せずに、DelphiおよびC++BuilderからネイティブのXLSXピボットテーブルを構築および更新します。ソース範囲を指定してAddPivotTableを呼び出し、行、列、ページ、およびデータ軸にフィールドをドロップし、集計アイテムや総計に対する比率の表示を重ねることで、コンポーネントはExcelがライブで更新可能なピボットとして開くpivotCacheDefinitionおよびpivotTableDefinitionパーツを書き込みます

この機能が真価を発揮するのはレポートサーバーのシナリオです。一晩に何百ものワークブックを生成し、それぞれに1つのアカウントを要約するピボットを含め、翌月に数値が変更された際にはすべてのファイルに新しいソース行を反映させる必要があります。WindowsサービスからExcelを操作するのは脆弱であり、サーバー使用のライセンスもありません。また、ピボットのXMLを手書きするのは終わりのない仕様考古学プロジェクトです。HotXLSはこれら2つのデッドエンドの間に位置します。OOXMLピボットパーツに対する型定義されたオブジェクトモデルを提供するため、セルを埋めるのと同じPascalコードでピボットを宣言し、同じプロセス内でそのキャッシュを再構築できます

ExcelなしでDelphiでピボットテーブルを作成する方法

1回の呼び出しでピボットテーブルを作成し、フィールドを軸に配置します。AddPivotTableは、A1参照形式のソース範囲、テーブルが配置される開始セル、および名前を受け取ります。範囲を解析し、各列をスキャンしてデータ型を推測し、ピボットキャッシュを構築（または同じ範囲にすでにバインドされているキャッシュを再利用）して、すべてのフィールドが最初は軸外にあるTXLSPivotTableを返します。そこから、便利なメソッドであるAddRowField、AddColumnField、AddPageField、およびAddDataFieldByNameを使用してレイアウトを構成し、各データフィールドはTXLSPivotAggregation列挙型（xlpaSum、xlpaCount、xlpaAverage、xlpaMax、xlpaMin、xlpaProduct、xlpaCountNums、xlpaStdDev、xlpaStdDevP、xlpaVar、xlpaVarP）から11個の集計方法のいずれかを選択します

uses
  lxHandleX, lxPivot;

var
  Book : TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[1];                 // report sheet (1-based in the XLSX engine)

    // Source A1:E500 on the 'Data' sheet; anchor the pivot at row 3, col 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;

キャッシュとテーブルが2つの別々のパーツである理由

ピボットテーブルは実際には互いを参照する2つの成果物であり、この分割を理解することが全体の把握に役まります。pivotCacheDefinitionはデータのスナップショットです。ソース範囲を指すworksheetSourceと、その列の一意の値（sharedItems）および派生境界を保持する列ごとの1つのcacheFieldで構成されます。pivotTableDefinitionはビューです。どのキャッシュフィールドがどの軸に配置されるか、データフィールドとその集計、レイアウトの切り替えなどを定義します。ビューは、ECMA-376 Part 1 §18.10および[MS-XLSX]の規定通り、ワークブックのpivotCachesリレーションシップを介してcacheIdによってキャッシュにバインドされます

この間接的な参照は無駄な手順ではなく、2つの利点をもたらします。1つのキャッシュで複数のテーブルをサポートできるため、キャッシュを1回更新するだけで、そこから描画されるすべてのビューが更新されます。また、キャッシュは各列の値を未整理のグリッドではなく、レコードごとのインデックステーブルで重複排除して保存します。そのため、ピボットの構築はセルをコピーすることではなく、ソースのスキャンを意味します。HotXLSは両方のエンジンでこれを同じようにモデル化しているため、上記のコードは従来の.xlsピボットの背後にあるバイナリSXレコードレイアウトと共にドキュメント化されているクラシックパスとほぼ同じです。ソースが別のシートにある場合や名前で参照する場合、範囲プレフィックスや定義された名前とシート間参照は通常のA1ルールに従い、スペースを含む名前には引用符で囲まれたシート名を使用できます

集計アイテムと集計フィールドの違い

これら3つの用語は異なるものを指しており、これらを混同することはピボットテーブルにおける典型的な間違いです。集計アイテムは1つのフィールド内に存在し、そのフィールド自体のアイテムを名前で結合します。たとえば、Regionフィールド内で、NorthにSouthを加えた合成のCoreMarkets行を定義できます。HotXLSはこれをTXLSPivotField.AddCalculatedItemとして公開し、そのフィールドの<calculatedItems>の下に<calculatedItem>を書き込みます。集計フィールドは異なり、RevenueとCostから算出するMarginのように、他の列から派生した新しい値であり、TXLSPivotCacheField.Formulaを通じてキャッシュフィールド上の数式として保持されます。TXLSPivotTable.AddCalculatedMemberで追加される集計メンバーは、メジャー（メンバータイプdata）またはディメンションメンバーとして機能するテーブルレベルのカスタムメンバーであり、主にOLAP形式のピボットに関係します

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // A calculated ITEM combines items of a single field by name.
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // A calculated MEMBER is declared at the table level. Member type
  // 'data' marks it as a measure; an empty type is a dimension member.
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

これら3つすべてに共通する明確な境界があります。HotXLSは数式テキストを定義XMLに出力するだけで、その評価は行いません。Excelは、グリッド内のすべての集計を計算するのと同様に、ファイルを開くときに入力された集計アイテム、フィールド、またはメンバーを計算します。HotXLSは結果ではなく指示を書き込んでいるため、提供する数式はExcel独自のダイアレクトで有効なピボット数式であり、Excelが認識できるようにフィールド名とアイテム名を参照している必要があります

値を総計に対する比率として表示する方法

数値を自分で変換するのではなく、データフィールドに値表示モードを設定します。TXLSPivotDataField.ShowDataAsは、OOXMLのST_ShowDataAs値を反映したTXLSPivotShowDataAs列挙型を受け取ります。これには、xlpsdaNormal、xlpsdaDifference、xlpsdaPercent、xlpsdaPercentDiff、xlpsdaRunTotal、xlpsdaPercentOfRow、xlpsdaPercentOfCol、xlpsdaPercentOfTotal、およびxlpsdaIndexが含まれます。一般的なテクニックは、同じソース列をデータ軸に2回配置し、1回目は生の合計として、2回目は総計に対する比率として配置することです。これにより、レポートに数値とその比重の両方を表示できます

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

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

  // Item-relative modes need a base to compare against. A running total
  // down 'Quarter' (cache-field index 3) would read:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // cache-field index the transform runs over
  //   Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

xlpsdaPercentOfTotalは総計に対する相対値であるため基準フィールドを必要としませんが、アイテム相対モードでは必要になります。xlpsdaDifference、xlpsdaPercentDiff、およびxlpsdaRunTotalには、BaseField（比較が実行されるキャッシュフィールドのインデックス）が必要であり、アイテムにアンカーされた形式ではBaseItemインデックスが必要になります。ここで、$7FFDは(すべて)を表すセンチネルです。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、(すべて)を意味する）です。読者が一度に複数のアイテムにチェックを入れられるようにするには、MultipleItemSelectionAllowed := Trueを設定します。HotXLSはこれを<pivotField multipleItemSelectionAllowed="1"/>として書き込みます。手動選択以外の基準として、すべてのフィールドには、OOXML의 ST_FilterTypeファミリー（件数、パーセント、合計、キャプション、値、および日付フィルター）にまたがる型付きのFiltersコレクションがあり、各エントリはフィルタータイプと比較値をペアにします

ソースが変更されたときにピボットキャッシュを更新する方法

TXLSXWorkbookのRefreshPivotCacheは、ソース範囲を再スキャンしてキャッシュをその場で再構築します。これは、基になる行を編集した後にバッチパイプラインが必要とする処理です。キャッシュIDを渡すと、メソッドはソース範囲内のすべてのセル（最初の行のヘッダー、2行目以降のデータ）を再読み込みし、値を再重複排除してタイプごとの境界を再導出することで各フィールドの共有アイテムドメインを再構築し、レコードごとのアイテムインデックスを書き換えます。成功した場合は1を返し、キャッシュIDが不明な場合やソースシートが見つからない場合は-1を返します

var
  Rc: Integer;
begin
  // ...the source data has changed since the pivot was built...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // a corrected Revenue figure

  // Rebuild the cache's shared items and record indices from the source.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = refreshed, -1 = cache/source missing
  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変換を適用し、ボディを集計します。ユーザーが表示する値は、HotXLSが書き込んだ指示から生成されたExcelの値です。そのため、すべての数式とベースインデックスは、レンダリング時のチェックではなく、書き込み時に正しくなければなりません

この機能を中心に設計する前に、2つの制限事項を知っておく必要があります。AddPivotTableは、オプションのシートプレフィックスを含む長方形のA1範囲を解析します。名前付き範囲や外部ワークブックのソースはビルダーが解決できる範囲外ですが、Excelで作成されたファイルから読み込まれたキャッシュは、ラウンドトリップ時に名前付き範囲のソースを保持します。また、HotXLSがディスクから読み込むExcel作成のピボットは、保存時にバイト単位で再現されるため、ここで説明する型付きの編集はコードで構築するピボットにきれいに適用され、既存のピボットは損失なく保持されます。ピボットが要約するレポートの入力側（検証済みセル、フィルター済みテーブル）については、データ検証、オートフィルター、構造化テーブルを参照してください

ここで示されているピボットモデルは、DelphiおよびC++Builder用の標準的なHotXLS Delphi Excelコンポーネントの一部であり、同じオブジェクトモデルからXLSX型のピボットパーツと従来のBIFF8レコードの両方を読み書きします