概要

PythonからExcelのピボットテーブルを使いたいが、難しいプログラムを記述したくない場合の方法です。ピボットテーブルの設定はExcelで行い、Pythonのプログラムではピボットテーブルの値の範囲のみを変更します。

確認環境

Python 3.8
OpenPyXL 3.0.5

参考情報

疑似個人情報データ生成サービス
- ダミーデータ生成
Pivot Tables — openpyxl 3.0.7 documentation
- OpenPyXL公式サイト Pivot Tables のページ

解説

テンプレートとなるExcelファイルを作成する

まずピボットテーブルを含んだExcelファイルを用意します。今回は疑似個人情報データ生成サービスを使って生成したExcelファイルに対してピボットテーブルを作成します。ピボットテーブルの作り方は検索して調べてください。

作成したExcelファイルは以下の通りになっているとします。

ピボットテーブルは pivot シートに含まれている
データは dummy_data シートに含まれている
Excelファイルの名前はPivotTemplate.xlsx

Pythonでピボットテーブルの値の範囲を設定する

Pythonのプログラムで以下の2つを合わせます。

PivotTemplate.xlsxに含まれているピボットテーブル
別途作成したデータシート

合わせるためのプログラムは以下のとおりです。

import openpyxl

# データシートを作成する
new_wb = openpyxl.Workbook()
ws = new_wb.create_sheet('data')

# ... wsにデータを追加する ...


def add_pivot(cache_id, data_sheet, destination_book):
    template_wb = openpyxl.load_workbook('PivotTemplate.xlsx')

    pivot_sheet = template_wb['pivot']  # ピボットテーブルが含まれたシート
    pivot = pivot_sheet._pivots[0]
    pivot.name = data_sheet.title + '_pivot'  # ピボットテーブルに名前を付ける
    pivot.cacheId = cache_id  # IDが重複するとエラーになる
    pivot.cache.refreshOnLoad = True  # 読み込み時にピボットテーブルを更新
    source = pivot.cache.cacheSource.worksheetSource
    print(source)

    print(data_sheet.dimensions)
    print(data_sheet.title)
    source.ref = data_sheet.dimensions
    source.sheet = data_sheet.title

    pivot_sheet = destination_book.create_sheet(pivot.name)
    pivot_sheet.add_pivot(pivot)
    template_wb.close()


add_pivot(1000, ws, new_wb)
new_wb.save('PivotSample.xlsx')

まず、はじめにdataという名前のシートをnew_bookに追加して、このdataシート(ws)にデータを用意しています。

次に、add_pivot関数でピボットテーブルを含むシートを追加しています。 cache_idはピボットテーブルのIDです。他のピボットテーブルと重複しない値にします。ここでは重複しないであろう値として適当に1000にしています。 data_sheetはデータを含むシートです。ここでは最初の段階で作成したdataシート(ws)を指定しています。 destination_bookはピボットテーブルを含むシートを追加する先のブックです。ここではnew_wbを指定しています。

add_pivot関数で行っている内容を説明します。まず、テンプレートとなるPivotTemplate.xlsxファイルを開き、ピボットテーブルが含まれるシートからピボットテーブルの情報を取得します。そして、このピボットテーブルの情報(pivot)を変更しています。 nameは設定しなくて構いませんが、ここではシート名に基づいて名付けています。 cacheIdは他のピボットテーブルと重複しない値を設定する必要があります。 refreshOnLoadをTrueに設定しておくとファイルを開いたときにピボットテーブルが更新されます。次に、ピボットテーブルが参照するデータの範囲を変更します。 refにデータの範囲、sheetにシート名を設定することで行えます。 data_sheetのデータ範囲(data_sheet.dimensions)とシート名(data_sheet.title)を設定しています。最後に、新規シートにピボットテーブルを追加します。

以上を行ってファイルを保存すれば、ピボットテーブルを含んだファイルが作成されます。