Posted inPython

PythonのopenpyxlでExcel処理を自動化：大量データの読み書き、条件付き書式、グラフ作成

Python tutorial - IT technology blog
Python tutorial - IT technology blog

直面した問題：毎月500ファイルのExcel処理

今年の初め、12支店からの売上レポートを処理するタスクを担当した。各支店が週1ファイルのExcelを送ってくるので、月合計約500ファイルになる。手作業でマージし、手作業で色を塗り、手作業でボーダーを引く。その作業だけで毎月2日近くかかっていた。

openpyxlでスクリプトを書くのに3週間かかったが、あのパイプライン全体が4分で終わるようになった。この記事では実際に役立ったことをまとめる — Hello Worldチュートリアルではなく、本番環境で実際に使っているものだ。

まず押さえておくべきコアコンセプト

openpyxlは3つの主要レイヤーで動作する：Workbook（Excelファイル）、Worksheet（シート）、Cell（セル）。この3つを理解すればライブラリの80%を把握できる。

多くの人が見落とす、重要なファイル読み込みモードが2つある：

  • Normalモード：ファイル全体をRAMに読み込む — 柔軟な読み書きに便利だが、50MB以上のファイルではRAM消費が顕著になる。
  • Read-onlyモード（read_only=True）：ジェネレーターを使用し、メモリに全部読み込まずに1行ずつ読む — 200MB以上のファイルでもRAM不足の心配なし。

openpyxlを選んだのは、.xlsxをネイティブに扱えるからだ：読み込み、書き込み、書式の維持、スタイルとチャートのサポート。Pandasはデータ分析が得意だが、Excelにエクスポートするとボーダーや背景色が失われる。クライアントに直接渡す出力が必要なタスクでは、openpyxlの方が明確な選択肢だ。

実践：データ読み込みから完成レポートまで

1. RAM不足を気にせず大容量ファイルを読み込む

10MB未満の小さいファイルなら、normalモードで問題ない：

from openpyxl import load_workbook

wb = load_workbook('月次レポート.xlsx')
ws = wb.active

for row in ws.iter_rows(min_row=2, values_only=True):
    chi_nhanh, doanh_thu, so_don = row[0], row[1], row[2]
    print(f"{chi_nhanh}: {doanh_thu:,} VNĐ ({so_don}件)")

50MB以上のファイルはすぐにread-onlyモードに切り替えよう：

wb = load_workbook('大容量データ.xlsx', read_only=True)
ws = wb.active

total = 0
for row in ws.iter_rows(min_row=2, values_only=True):
    if row[1]:  # 空行をスキップ
        total += row[1]

wb.close()  # 重要：read_only使用時は必ずcloseすること
print(f"合計: {total:,}")

2. データの書き込みと新しいレポートファイルの作成

複数のソースファイルから統合レポートファイルを作成する — これが最もよく使うユースケースだ：

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
import glob

wb_out = Workbook()
ws_out = wb_out.active
ws_out.title = "統合"

# ヘッダー
headers = ["支店名", "売上", "注文数", "平均/件"]
ws_out.append(headers)

# ヘッダースタイル
for col_idx, cell in enumerate(ws_out[1], 1):
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill(fill_type="solid", fgColor="2E5090")
    cell.alignment = Alignment(horizontal="center")

# 各支店のファイルを読み込む
for filepath in glob.glob('支店_*.xlsx'):
    wb_src = load_workbook(filepath, read_only=True)
    ws_src = wb_src.active
    for row in ws_src.iter_rows(min_row=2, values_only=True):
        if row[0]:  # データあり
            tb = row[1] / row[2] if row[2] else 0
            ws_out.append([row[0], row[1], row[2], round(tb, 0)])
    wb_src.close()

wb_out.save('統合.xlsx')

3. 条件付き書式（Conditional Formatting）

これが最も調べるのに時間がかかった部分だ。openpyxlは条件付き書式をかなり充実してサポートしているが、APIはExcel UIとは少し異なる。

値に基づいてセルに色を付ける — 例えば売上が低いものをハイライト：

from openpyxl.formatting.rule import ColorScaleRule, CellIsRule
from openpyxl.styles import PatternFill

# カラースケール：赤 → 黄 → 緑（値に応じて）
color_scale = ColorScaleRule(
    start_type='min', start_color='FF0000',
    mid_type='percentile', mid_value=50, mid_color='FFFF00',
    end_type='max', end_color='00FF00'
)
ws_out.conditional_formatting.add('B2:B50', color_scale)

# 50,000,000未満のセルを赤で塗りつぶす
red_fill = PatternFill(start_color='FFB3B3', end_color='FFB3B3', fill_type='solid')
low_rule = CellIsRule(operator='lessThan', formula=['50000000'], fill=red_fill)
ws_out.conditional_formatting.add('B2:B50', low_rule)

この2つをよく組み合わせて使う：color scaleで全体の分布を一目で把握し、CellIsRuleですぐに確認が必要なセルを正確にマークする。

4. レポートグラフの作成

openpyxlは最初に思っていたより多くの種類のチャートをサポートしている — bar、line、pie、scatter。売上レポートにはBarChartで十分だ：

from openpyxl.chart import BarChart, Reference
from openpyxl.chart.series import SeriesLabel

# データはA列（名前）とB列（売上）、1〜13行目を想定
chart = BarChart()
chart.type = "col"  # 縦棒グラフ
chart.title = "支店別売上"
chart.y_axis.title = "売上（VNĐ）"
chart.x_axis.title = "支店"
chart.style = 10  # ExcelのプリセットスタイルNo.10
chart.width = 20
chart.height = 12

# データ系列（B列）
data = Reference(ws_out, min_col=2, min_row=1, max_row=13)
chart.add_data(data, titles_from_data=True)

# ラベル（A列）
categories = Reference(ws_out, min_col=1, min_row=2, max_row=13)
chart.set_categories(categories)

# シートにチャートを挿入（D2セルから）
ws_out.add_chart(chart, "D2")
wb_out.save('グラフ付きレポート.xlsx')

5. 列幅の自動調整

小さな細部だが、クライアントからよく列が切れていると言われる。この関数はすべてのExcelスクリプトにコピーして使っている：

def auto_fit_columns(ws):
    for col in ws.columns:
        max_len = 0
        col_letter = col[0].column_letter
        for cell in col:
            try:
                val_len = len(str(cell.value)) if cell.value else 0
                if val_len > max_len:
                    max_len = val_len
            except:
                pass
        # +2で少しパディングを追加
        ws.column_dimensions[col_letter].width = min(max_len + 2, 50)

auto_fit_columns(ws_out)

6. テーブルのボーダー設定

データ全体を囲む細いボーダーを付けると一気にプロらしい見栄えになる：

from openpyxl.styles import Border, Side

thin = Side(border_style='thin', color='AAAAAA')
border = Border(left=thin, right=thin, top=thin, bottom=thin)

for row in ws_out.iter_rows(min_row=1, max_row=ws_out.max_row,
                             min_col=1, max_col=ws_out.max_column):
    for cell in row:
        cell.border = border

まとめ：自動化する価値があること

6ヶ月の本番運用を経て、openpyxlが最も得意とする3つの作業がわかった：ファイルをマージして統合レポートを作成すること、テンプレートに沿って一貫した書式を適用すること、データからグラフを生成すること。タスクがこの3つのどれかに当てはまるなら — 50行のスクリプトで毎回2時間の手作業を代替できる。

openpyxlを使わないもの：複雑な統計分析（pandasの方が優れている）、旧形式の.xlsファイルの処理（xlrdを使う）、本格的なピボットテーブルが必要な場合（手作業かWindowsでwin32comを使うしかない）。

毎週1時間以上Excelを手作業で処理しているなら、スクリプトを検討するサインだ。最初にコードを書くのに半日かけた。その代わり、それ以降は毎月2日分の作業が浮いている。

Share:
Tags: