前回の記事では複数のファイルの集計を扱いましたが、今回はその集計結果を既存の定型フォーマット(テンプレート)へ転記する方法を解説します。
こんな作業をしている人向けの記事です
- 会社所定の報告書テンプレートに、毎回同じ位置へ数値を手入力している
- テンプレートの見た目(罫線・色・ロゴなど)は変えずに、中身のデータだけ差し替えたい
- 前回の記事で集計したデータを、そのままレポートに反映させたい
使うライブラリ
今回はopenpyxlを使います。前回のpandasは「データの集計」が得意でしたが、openpyxlは「既存のExcelファイルの見た目を保ったまま、特定のセルに値を書き込む」ことが得意なライブラリです。
pip install openpyxl基本の考え方
- 転記先のテンプレートファイルを開く(既存のファイルをそのまま読み込む)
- 転記したいセルの位置を指定して、値を書き込む
- 別名で保存する(元のテンプレートを上書きしないのがポイント)
サンプルコード
import openpyxl
from datetime import date
# テンプレートファイルを開く(既存の書式・レイアウトはそのまま保持される)
wb = openpyxl.load_workbook("template.xlsx")
ws = wb["報告書"] # シート名を指定
# 転記したいデータ(前回記事の集計結果などをここに入れる想定)
data = {
"店舗A": 152000,
"店舗B": 98000,
"店舗C": 210000,
}
# 日付とタイトルをセルに書き込む
ws["B2"] = date.today()
ws["B3"] = "月次売上報告"
# 店舗ごとのデータを順番に書き込む
row = 5
for shop_name, sales in data.items():
ws.cell(row=row, column=2, value=shop_name) # B列に店舗名
ws.cell(row=row, column=3, value=sales) # C列に売上金額
row += 1
# 別名で保存(元のテンプレートは残しておく)
wb.save("報告書_出力.xlsx")
print("転記が完了しました")ポイント解説
openpyxl.load_workbook("template.xlsx"):既存のExcelファイルをそのまま読み込みます。セルの色、罫線、フォントなどの書式は保持されたまま開かれます。ws["B2"] = date.today():セル番地を直接指定して値を入れる書き方です。位置が決まっている項目(タイトル、日付など)を書き込むときに使います。ws.cell(row=row, column=2, value=shop_name):行・列を数値で指定する書き方です。データの件数によって書き込む行が変わる(繰り返し処理をする)場合に向いています。wb.save("報告書_出力.xlsx"):元のテンプレートファイルとは別名で保存しています。テンプレートを毎回上書きしてしまうと、次回使うひな形が消えてしまうため、必ず別名保存にするのが安全です。
よくあるつまずきポイント
- セルが結合されている:結合セルの左上以外のセルに書き込もうとするとエラーになります。テンプレート側の結合を事前に確認し、結合の左上セルを指定するようにしてください。
- シート名が分からない:
wb.sheetnamesを実行すると、そのExcelファイルに含まれる全シート名が一覧で確認できます。 - 数式が入っているセルを上書きしてしまう:テンプレートに合計を自動計算する数式が入っている場合、その部分には値を書き込まないよう注意が必要です。データを入れるセルと、数式が入っているセルの範囲は事前に確認しておきましょう。
- 書式が崩れる:
openpyxlは基本的に既存の書式を保持しますが、罫線やスタイルが複雑なテンプレートでは意図しない形で崩れることもあります。出力後は一度目視で確認する習慣をつけると安心です。
前回記事との組み合わせ
前回の「複数ファイルを1つに集計する」処理と組み合わせると、以下のような一連の自動化フローが作れます。
- 複数店舗のExcelファイルを集計(前回の内容)
- 集計結果を、社内所定の報告書テンプレートに転記(今回の内容)
- 完成した報告書ファイルをそのままメールで送付(次回以降で解説予定)
このように、1つ1つの処理を組み合わせていくことで、月初にまとめて行っていた作業を、スクリプトを実行するだけの数分の作業に置き換えられます。
まとめ
定型フォーマットへの転記は、openpyxlでセル位置を指定して値を書き込むだけで実現できます。今回のコードをベースに、自分の職場で使っているテンプレートのセル位置に合わせて書き換えてみてください。
次回は、完成したレポートファイルをメールで自動送付する方法を解説します。
コメント