.NET/DioDocs/Excel

ワークシートにピボットテーブルを追加する

by MESCIUS-dev

ピボットテーブルは、Microsoft ExcelやGoogle スプレッドシートなどの表計算ソフトで使用されるデータ集計・分析ツールの一つです。複雑な関数を組み合わせる必要がなくマウス操作のみで、大量のデータから自分が参照したい項目を軸に設定して、データの集計・分析を可能にする優れた機能です。

このピボットテーブルですが、一つ一つユーザー操作で作成するのではなくサーバーサイドでデータを取得しワークフローや業務システムなどに組み込んで作成を自動化したい、そのような要件もあるのではないでしょうか。

本記事では「DioDocs for Excel（ディオドック）」を使用してワークシートにピボットテーブルを追加する方法とあわせて、最新バージョン「V8J」で追加されたピボットテーブルの新機能についても紹介します。

ピボットテーブルを追加する（セル範囲を指定）

利用するデータ

ワークシートのセル範囲「A1」～「E101」に以下のようなデータを持つExcelファイル（sample-data.xlsx）を準備しておきます。

利用するデータ

ピボットテーブルを追加する

上記のExcelファイルを読み込んでセル範囲「A1」～「E101」のデータを元に、IPivotCachesインタフェースのCreateメソッドを使用してピボットキャッシュを作成し、IPivotTablesインタフェースのAddメソッドを使用してピボットテーブルを追加します。

// ワークブックを作成
Workbook workbook = new();
workbook.Open("sample-data.xlsx");

// ワークシートを取得
IWorksheet worksheet = workbook.Worksheets[0];

//ピボットテーブルを作成
IPivotCache pivotcache = workbook.PivotCaches.Create(worksheet.Range["A1:E101"]);
IPivotTable pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["G1"], "pivottable1");

ピボットテーブルのフィールドを設定する

ピボットテーブルの各フィールドはIPivotTableインタフェースのPivotFieldsプロパティを使用して設定します。フィールドの位置はIPivotFieldインタフェースのOrientationプロパティを使用して設定します。

// ピボットテーブルのフィールドを設定
var field_Region = pivottable.PivotFields["地域"];
field_Region.Orientation = PivotFieldOrientation.ColumnField;

var field_Category = pivottable.PivotFields["カテゴリ"];
field_Category.Orientation = PivotFieldOrientation.RowField;

var field_Product = pivottable.PivotFields["商品名"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["売上"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "¥#,##0";

実行すると以下のようにワークシートにピボットテーブルが追加されます。

ピボットテーブルのフィールドを設定する

ピボットテーブルを追加する（テーブルを指定）

上記ではピボットテーブルのデータソースとしてセル範囲を使用しましたが、最新バージョンのV8Jではワークシートにあるテーブルをデータソースとして使用することができます。

利用するデータ

ワークシートのセル範囲「A1」～「E101」に、以下のようなテーブルを持つExcelファイル（sample-data-table.xlsx）を準備しておきます。

利用するデータ

先ほどと同じように、IPivotCachesインタフェースのCreateメソッドを使用してピボットキャッシュを作成しますが、引数としてテーブルを指定します。

// ワークブックを作成
Workbook workbook = new();
workbook.Open("sample-data.xlsx");

// ワークシートを取得
IWorksheet worksheet = workbook.Worksheets[0];

// テーブルを取得
ITable datatable = worksheet.Tables[0];

//ピボットテーブルを作成
IPivotCache pivotcache = workbook.PivotCaches.Create(datatable);
IPivotTable pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["G1"], "pivottable1");

各フィールドの設定は先ほどと同じです。実行すると以下のようにワークシートにピボットテーブルが追加されます。

ピボットテーブルを追加する（テーブルを指定）

セル範囲を指定する場合、データの増減に応じてIPivotCachesインタフェースのCreateメソッドの引数に設定するセル範囲を更新する必要がありますが、テーブルを指定する場合にはその手間が必要ありません。

ピボットテーブルの設定

DioDocs for Excelでは、ピボットテーブルに対してさまざまな設定を行うことができます。ここでは最新バージョンのV8Jで新しく追加された以下の機能について紹介します。

  • ワークブックを開く際にピボットテーブルを更新
  • ワークブックを開く際にピボットテーブルのフィールドリストを表示
  • ピボットテーブルの更新時に列幅を自動調整

ワークブックを開く際にピボットテーブルを更新

IPivotCacheインタフェースのRefreshOnFileOpenプロパティを使用して、Excelファイルを開く際にピボットテーブルを更新する／しないを設定できます。

//ピボットテーブルを作成
IPivotCache pivotcache = workbook.PivotCaches.Create(datatable);
IPivotTable pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["G1"], "pivottable1");

// Excelファイルを開いた時にピボットテーブルを自動的に更新
pivotcache.RefreshOnFileOpen = true;
ワークブックを開く際にピボットテーブルを更新

ワークブックを開く際にピボットテーブルのフィールドリストを表示

IWorkbookインタフェースのShowPivotTableFieldListプロパティを使用して、Excelファイルを開く際にピボットテーブルのフィールドリストの表示／非表示を設定できます。

// Excelファイルを開いた時にフィールドリストを表示
workbook.ShowPivotTableFieldList = true;
ワークブックを開く際にピボットテーブルのフィールドリストを表示

ピボットテーブルの更新時に列幅を自動調整

IPivotTableインタフェースのHasAutoFormatプロパティを使用して、データを更新した後に列幅を自動で調整するかどうかを設定できます。ピボットテーブルの各データの見切れを回避することが可能です。

// ピボットテーブルを作成
IPivotCache pivotcache = workbook.PivotCaches.Create(worksheet.Range["A1:E101"]);
IPivotTable pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["G1"], "pivottable1");

// 列幅を自動調整
pivottable.HasAutoFormat = true;
ピボットテーブルの更新時に列幅を自動調整

この他にもDioDocs for Excelではピボットテーブルに対してさまざまな設定を行うことが可能です。詳細は製品ヘルプで確認いただけます。

今回紹介した機能については以下のデモで動作を確認いただけます。

DioDocsの最新バージョン「V8J」の新機能については、以下のページで紹介していますのでこちらも是非ご覧ください。

さいごに

弊社Webサイトでは、製品の機能を気軽に試せるデモアプリケーションやトライアル版も公開していますので、こちらもご確認いただければと思います。

また、ご導入前の製品に関するご相談やご導入後の各種サービスに関するご質問など、お気軽にお問合せください。

＼  この記事をシェアする  ／