Python自動化 #2 — Excel自動化: openpyxl で繰り返しレポートを終わらせる

プログラミング言語 Python Automation Excel
読了 6分

毎週金曜日、5 つの支店から同じ様式の Excel ファイルがメールで届きます。1 つずつ開いてコピーし、本社の様式に貼り付け、書式を整え直すのに 1 時間かかります。この作業は毎週まったく同じように繰り返され、手作業でやっていると 1 行を見落としたり、違うシートをコピーしたりするミスも起きます。今回はこの集約作業を openpyxl ライブラリで丸ごと自動化します。#1 スクリプトの第一歩 で身につけた pathlib もそのまま再利用します。

  • #1 スクリプトの第一歩
  • #2 Excel自動化 ← 今回
  • #3 Webスクレイピング(静的ページ)
  • #4 Webスクレイピング(動的ページ)
  • #5 メールと通知
  • #6 スケジューリング
  • #7 CLIツールに仕上げる

openpyxl のインストール #

openpyxl は .xlsx ファイルを読み書きする純粋な Python ライブラリです。Excel 本体がインストールされていなくても動くので、サーバー上でもそのまま使えます。インストールは 1 行です。pip を使うなら pip install openpyxl でも同じです。

インストール 
uv add openpyxl

ファイルを読む: load_workbook #

まずファイルを 1 つ開いて構造を見てみます。Excel ファイルは、ワークブック(Workbook)の中にシート(Worksheet)があり、シートの中にセル(Cell)がある 3 段構造です。

read_one.py 
from openpyxl import load_workbook

wb = load_workbook("branch_a.xlsx")

print(wb.sheetnames)        # ['7月1週', 'Sheet2']
ws = wb["7月1週"]            # 名前でシートを選択
ws = wb.active              # またはアクティブシート

print(ws["B2"].value)                  # 座標でセルにアクセス
print(ws.cell(row=2, column=2).value)  # 行/列番号でアクセス (同じセル)

# 行単位で巡回。min_row=2: ヘッダー (1 行目) はスキップ
for row in ws.iter_rows(min_row=2, values_only=True):
    print(row)  # ('東京', 'キーボード', 12, 348000)

座標の文字列は人が読みやすく、行/列番号はループで便利です。表全体を行単位で巡回するときは iter_rows を使います。values_only=True を渡すと、セルオブジェクトの代わりに値のタプルが直接返ってくるので、読み取り専用のコードがすっきりします。

完成例: フォルダの支店別 Excel を 1 つのシートに集約 #

ここから実戦です。reports/ フォルダに branch_a.xlsx, branch_b.xlsx のような支店別ファイルがたまっていて、すべてのファイルの最初のシートが同じ様式(支店、品目、数量、売上)だと仮定します。#1 で扱った pathlib の glob でファイル一覧を取り、各ファイルのデータ行を 1 つの新しいワークブックに集めます。

merge_reports.py 
from pathlib import Path

from openpyxl import Workbook, load_workbook

REPORT_DIR = Path("reports")
OUT_FILE = Path("merged.xlsx")

merged = Workbook()
out = merged.active
out.title = "集約"
out.append(["支店", "品目", "数量", "売上"])  # ヘッダーは 1 回だけ

for file in sorted(REPORT_DIR.glob("*.xlsx")):
    wb = load_workbook(file, data_only=True)
    ws = wb.active
    for row in ws.iter_rows(min_row=2, values_only=True):
        if row[0] is None:  # 空行はスキップ
            continue
        out.append(row)
    print(f"{file.name}: {ws.max_row - 1}行を集約")

merged.save(OUT_FILE)
print(f"完了: {OUT_FILE}")

ポイントは 3 つです。glob("*.xlsx") でフォルダの対象ファイルをすべて取得し、iter_rows(min_row=2) で各ファイルのヘッダーを飛ばし、out.append(row) で新しいシートの最下行に行をつなげていきます。ファイルが 5 個でも 50 個でもコードはそのままです。sorted() で包んでいる理由は、実行のたびにファイルの順序が変わることを防ぐためです。

書き込みと書式: レポートらしく整える #

集約しただけでは素っ気ない表になります。ヘッダーを太字に、列幅を内容に合わせ、売上列に 3 桁区切りのカンマを入れて、レポートの体裁を整えます。

書式を整える 
from openpyxl.styles import Font

# 1. ヘッダーを太字に
for cell in out[1]:  # out[1] は 1 行目全体
    cell.font = Font(bold=True)

# 2. 列幅 (単位はおおよそ文字数)
widths = {"A": 12, "B": 18, "C": 8, "D": 14}
for col, width in widths.items():
    out.column_dimensions[col].width = width

# 3. 売上 (D 列) の数値フォーマット: 1234567 → 1,234,567
for row in out.iter_rows(min_row=2, min_col=4, max_col=4):
    for cell in row:
        cell.number_format = "#,##0"

# 4. スクロールしてもヘッダー (1 行目) を固定
out.freeze_panes = "A2"  # A2 より上を固定するという意味
merged.save(OUT_FILE)

number_format は Excel のセル書式コードをそのまま受け取ります。日付なら "yyyy-mm-dd"、パーセントなら "0.0%" のように、Excel で使っていたコードをそのまま書けば動きます。

数式の扱い: openpyxl は計算しません #

ここで一番よくはまる落とし穴です。openpyxl は数式を計算するエンジンではありません。 D10 セルに =SUM(D2:D9) が入っているとき、読み方によって見える値が変わります。

data_only の違い 
wb = load_workbook("branch_a.xlsx")
print(wb.active["D10"].value)   # '=SUM(D2:D9)' (数式の文字列)
wb = load_workbook("branch_a.xlsx", data_only=True)
print(wb.active["D10"].value)   # 858000 (最後に保存した時点の計算値)

data_only=True が返す値は、openpyxl が計算した結果ではなく、Excel が最後に保存したときにファイル内へキャッシュしておいた値です。そのため 2 点に注意が必要です。

  • Python だけで作って Excel で一度も開いていないファイルは、キャッシュがないので None が返ります。
  • 数式のあるファイルを openpyxl で読んで保存し直すと、読んだモードの値(数式またはキャッシュ値)だけが残ります。元ファイルを上書きせず、結果は新しいファイルに保存する習慣が安全です。

上の集約例で data_only=True を渡した理由がまさにこれです。支店ファイルの売上列が数式でも、数式の文字列ではなく数値を取得するためです。合計が必要なら、Python 側で直接足すか、結果シートに =SUM(...) の数式を書いておき、受け取った人が Excel で開いたときに計算されるようにすれば十分です。

CSV と pandas はどこで使うのか #

データに書式が必要なければ、.xlsx の代わりに標準ライブラリの csv モジュールで足りるケースも多いです。逆に、集約後にグループ別集計やピボットのような分析が目的なら、pandas が適した道具です。pandas はデータ分析シリーズで別途扱います。今回のシリーズの焦点は「Excel ファイルそのものを作って整える自動化」で、その用途には openpyxl が最も直接的です。

よくある落とし穴 2 つ #

結合セル: 結合された範囲では、値は左上のセルにだけあり、残りのセルは None として読まれます。支店ファイルに結合セルが混ざっていると集約結果に穴が空くので、ws.merged_cells.ranges で結合範囲を確認し、左上の値を残りの行に埋める処理が必要です。書き込み側でも、結合範囲の左上以外のセルには値を入れられません。

大きいファイルの性能: デフォルトのモードはファイル全体をメモリに載せます。数十万行を超えるファイルは、読み取りは load_workbook(file, read_only=True)、書き込みは Workbook(write_only=True) のストリーミングモードを使うのがおすすめです。その代わり、read_only モードではセルへのランダムアクセスが制限され、write_only モードでは append でしか行を追加できません。

まとめ #

今回作った流れを整理します。

  • load_workbook + iter_rows(values_only=True) で Excel を行単位で読む
  • pathlib の glob + append でフォルダの支店別ファイルを 1 つのシートに集約
  • Font(bold=True), column_dimensions, number_format, freeze_panes でレポートの書式
  • 数式の落とし穴(openpyxl は計算しない、data_only=True はキャッシュ値)、結合セルの None、大容量ファイルの read_only/write_only モード

毎週 1 時間かかっていた集約が、スクリプト 1 回の実行で終わります。次回(#3 Webスクレイピング(静的ページ))では、データが Excel ファイルではなく Web ページにある場合を扱います。requests と BeautifulSoup で HTML から目的の表やリストを抜き出す方法を整理します。

X