ワークシートのデータにアウトラインを追加して小計を計算する

アウトラインとは？

Excelファイルのワークシートに大量のデータが存在する場合、アウトラインを利用すると任意の列でソートされたデータをグループ化することができます。また、アウトラインを展開したり折りたたんだりして、特定のグループを表示または非表示にすることができます。さらに、アウトラインでグループ化された値に小計を適用することもできます。

ただし、ワークシートを開いてアウトラインを各シートごとにそれぞれ追加していくのは手間がかかる作業です。DioDocs for Excel（ディオドック）を利用すれば、データが含まれるワークシートを読み込んで、アウトラインを追加し、各グループに小計を表示するといった処理をカンタンに実装できます。

アウトラインを追加して小計を計算する

サンプルデータとして以下のようなワークシートを用意します。このデータを「日付」ごとにアウトラインでグループ化して「売上金額」の小計を計算するケースを想定します。

IRangeインタフェースのSubtotalメソッドを使用して、アウトライン小計を作成できます。以下のコードでは、ワークシートでデータが存在するセル範囲をUsedRangeプロパティを使用して取得して、そのセル範囲にアウトラインを追加して小計を計算しています。

// 新規ワークブックの作成
var workbook = new GrapeCity.Documents.Excel.Workbook();

// テストデータを読み込み
workbook.Open("test-data.xlsx");

// 使用中の範囲を取得
var worksheet = workbook.Worksheets[0];
var usedrange = worksheet.UsedRange;

// アウトラインを追加、小計を計算
usedrange.Subtotal(1, ConsolidationFunction.Sum, new[] { 6 }); 

// 列幅を調整
usedrange.AutoFit();

// Excelファイルに保存
workbook.Save("AddSubtotal.xlsx");

Subtotalメソッドでは、どの列でグループ化するか（グループの基準）、各グループに使用する関数（集計の方法）、どの列に関数を適用するか（集計するフィールド）などをパラメーターで設定できます。

実行すると以下のように日付ごとにアウトラインでグループ化され、小計が追加されます。

アウトラインのグループを折りたたむ

アウトラインのグループを折りたたんだ状態に設定することもできます。RowGroupInfoプロパティで行グループ情報を取得し、Collapseメソッドで指定したグループを折りたたむことが可能です。

// 新規ワークブックの作成
var workbook = new GrapeCity.Documents.Excel.Workbook();

// テストデータを読み込み
workbook.Open("test-data.xlsx");

// 使用中の範囲を取得
var worksheet = workbook.Worksheets[0];
var usedrange = worksheet.UsedRange;

// アウトラインを追加、小計を計算
usedrange.Subtotal(1, ConsolidationFunction.Sum, new[] { 6 }); 

// 列幅を調整
usedrange.AutoFit();

// グループ情報を取得、グループを折りたたみ
var groupInfo = worksheet.Outline.RowGroupInfo;
var rowInfo = new Dictionary<int, int>();

foreach (var item in groupInfo)
{
    if (item.Children != null)
    {
        foreach (var childItem in item.Children)
        {
            childItem.Collapse();
        }
    }
}

// Excelファイルに保存
workbook.Save("AddSubtotal.xlsx");

アウトラインをクリアする

アウトラインをクリアすることで、アウトラインの表示を解除しつつ計算した小計は残しておくことも可能です。ClearOutlineメソッドでアウトラインをクリアできます。

// 新規ワークブックの作成
var workbook = new GrapeCity.Documents.Excel.Workbook();

// テストデータを読み込み
workbook.Open("test-data.xlsx");

// 使用中の範囲を取得
var worksheet = workbook.Worksheets[0];
var usedrange = worksheet.UsedRange;

// アウトラインを追加、小計を計算
usedrange.Subtotal(1, ConsolidationFunction.Sum, new[] { 6 }); 

// 列幅を調整
usedrange.AutoFit();

// グループ情報を取得、グループを折りたたみ
var groupInfo = worksheet.Outline.RowGroupInfo;
var rowInfo = new Dictionary<int, int>();

// 使用中の範囲を取得
var usedrange1 = worksheet.UsedRange;

// アウトラインをクリア
usedrange1.ClearOutline();

// Excelファイルに保存
workbook.Save("AddSubtotalClearOutline.xlsx");

特定の行をグループ化する

特定の行を指定してグループ化することも可能です。IRangeインタフェースのGroupメソッドを使用して指定した行をグループ化します。

// 新規ワークブックの作成
var workbook = new GrapeCity.Documents.Excel.Workbook();

// テストデータを読み込み
workbook.Open("test-data.xlsx");

// 特定の行をグループ化
var worksheet = workbook.Worksheets[0];
worksheet.Range["8:13"].Group();
worksheet.Range["20:23"].Group();

// Excelファイルに保存
workbook.Save("AddRowGroup.xlsx");

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

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

さいごに

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

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

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