月次レポートExcelをClaude Codeに自動生成させたら毎月8時間の集計作業がゼロになった体験記【2026年版】

import pandas as pd
import glob
from pathlib import Path

def collect_branch_data(data_dir: str, year_month: str) -> pd.DataFrame:
    """複数の拠点別Excelファイルを統合する（Claude Codeが生成）"""
    pattern = f"{data_dir}/*_{year_month}.xlsx"
    files = glob.glob(pattern)

    if not files:
        raise FileNotFoundError(f"ファイルが見つかりません: {pattern}")

    all_dfs = []
    for filepath in sorted(files):
        branch_name = Path(filepath).stem.split("_")[0]  # 拠点名を抽出

        try:
            df = pd.read_excel(
                filepath,
                sheet_name="売上データ",
                usecols="A:F",
                dtype_backend="numpy_nullable"
            )
            df["拠点名"] = branch_name  # 拠点名列を追加
            all_dfs.append(df)
            print(f"  ✅ {branch_name}: {len(df)}行 読み込み完了")

        except Exception as e:
            print(f"  ❌ {branch_name}: 読み込みエラー → {e}")

    combined = pd.concat(all_dfs, ignore_index=True)
    print(f"\n✅ 統合完了: {len(combined):,}行 / {len(files)}ファイル")
    return combined

# 実行
df = collect_branch_data("data", "2026年4月")
output_path = "all_data_2026-04.csv"
df.to_csv(output_path, index=False, encoding="utf-8-sig")
print(f"💾 {output_path} に保存しました")

from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.formatting.rule import CellIsRule

def create_pivot_sheets(df: pd.DataFrame, wb: Workbook) -> None:
    """Claude Codeが生成：ピボット集計シートをExcelに書き込む"""

    # ── 部門別集計シート ──────────────────────
    ws_dept = wb.create_sheet("部門別集計")
    dept_pivot = df.groupby("部門")["売上"].sum().reset_index()

    # ヘッダー
    headers = ["部門", "今月売上", "前月売上", "前月比", "目標", "達成率"]
    header_fill = PatternFill("solid", fgColor="107C41")
    header_font = Font(bold=True, color="FFFFFF", size=10)

    for col, h in enumerate(headers, 1):
        cell = ws_dept.cell(row=1, column=col, value=h)
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = Alignment(horizontal="center")

    # データ行 + Excel数式埋め込み
    for row_i, row in enumerate(dept_pivot.itertuples(), 2):
        ws_dept.cell(row=row_i, column=1, value=row.部門)
        ws_dept.cell(row=row_i, column=2, value=row.売上)
        ws_dept.cell(row=row_i, column=3, value=0)  # 前月値（別途設定）
        # 前月比をExcel数式で埋め込む
        ws_dept.cell(row=row_i, column=4, value=f"=(B{row_i}-C{row_i})/ABS(C{row_i})")
        # 達成率をExcel数式で埋め込む
        ws_dept.cell(row=row_i, column=6, value=f"=B{row_i}/E{row_i}")

    # 条件付き書式：前月比 D列 → プラスは緑、マイナスは赤
    last_row = 1 + len(dept_pivot)
    green_fill = PatternFill("solid", fgColor="D1FAE5")
    red_fill   = PatternFill("solid", fgColor="FEE2E2")
    ws_dept.conditional_formatting.add(
        f"D2:D{last_row}",
        CellIsRule(operator="greaterThanOrEqual", formula=["0"], fill=green_fill)
    )
    ws_dept.conditional_formatting.add(
        f"D2:D{last_row}",
        CellIsRule(operator="lessThan", formula=["0"], fill=red_fill)
    )
    print("  ✅ 部門別集計シート 作成完了")

from openpyxl.chart import BarChart, LineChart, PieChart, Reference, Series

def add_all_charts(ws, data_row_count: int) -> None:
    """Claude Codeが生成：棒グラフ・折れ線・円グラフを自動挿入"""

    # ── 1. 棒グラフ（部門別売上）────────────────
    bar_chart = BarChart()
    bar_chart.type = "col"
    bar_chart.style = 10
    bar_chart.title = "部門別売上（2026年4月）"
    bar_chart.y_axis.title = "売上（円）"
    bar_chart.x_axis.title = "部門"
    bar_chart.width = 15
    bar_chart.height = 12

    data_ref = Reference(ws, min_col=2, max_col=2,
                         min_row=1, max_row=data_row_count + 1)
    cats_ref = Reference(ws, min_col=1,
                         min_row=2, max_row=data_row_count + 1)
    bar_chart.add_data(data_ref, titles_from_data=True)
    bar_chart.set_categories(cats_ref)
    ws.add_chart(bar_chart, "H2")

    # ── 2. 折れ線グラフ（月次推移）──────────────
    line_chart = LineChart()
    line_chart.style = 10
    line_chart.title = "売上月次推移"
    line_chart.y_axis.title = "売上（円）"
    line_chart.x_axis.title = "月"
    line_chart.width = 15
    line_chart.height = 12
    ws.add_chart(line_chart, "H22")

    # ── 3. 円グラフ（構成比）────────────────────
    pie_chart = PieChart()
    pie_chart.style = 10
    pie_chart.title = "商品カテゴリ別構成比"
    pie_chart.width = 12
    pie_chart.height = 12
    ws.add_chart(pie_chart, "H42")

    print("  ✅ 棒グラフ・折れ線グラフ・円グラフを挿入しました")

from openpyxl.styles import (
    PatternFill, Font, Alignment, Border, Side, numbers
)

def apply_professional_format(wb: Workbook) -> None:
    """Claude Codeが生成：全シートにプロ書式を一括適用"""

    thin_side = Side(style="thin", color="D1D5DB")
    border = Border(left=thin_side, right=thin_side,
                    top=thin_side, bottom=thin_side)

    header_fill  = PatternFill("solid", fgColor="107C41")
    zebra_fill   = PatternFill("solid", fgColor="F0FFF4")
    header_font  = Font(bold=True, color="FFFFFF", size=10, name="Yu Gothic UI")
    number_fmt   = '#,##0'

    for ws in wb.worksheets:
        max_row = ws.max_row
        max_col = ws.max_column

        for row_i in range(1, max_row + 1):
            for col_i in range(1, max_col + 1):
                cell = ws.cell(row=row_i, column=col_i)
                cell.border = border

                if row_i == 1:
                    # ヘッダー行
                    cell.fill = header_fill
                    cell.font = header_font
                    cell.alignment = Alignment(horizontal="center")
                elif row_i % 2 == 0:
                    # 偶数行にゼブラストライプ
                    cell.fill = zebra_fill

                # 数値列にカンマ書式
                if isinstance(cell.value, (int, float)):
                    cell.number_format = number_fmt

        # 列幅自動調整
        for col in ws.columns:
            max_len = max(
                (len(str(cell.value)) if cell.value else 0)
                for cell in col
            )
            ws.column_dimensions[col[0].column_letter].width = min(max_len + 4, 40)

        # 先頭行固定
        ws.freeze_panes = "A2"

    print("  ✅ 全シートに書式を適用しました")

def create_monthly_comparison(wb: Workbook,
                               df_current: pd.DataFrame,
                               df_prev: pd.DataFrame) -> None:
    """Claude Codeが生成：前月比較シートを自動生成"""
    ws = wb.create_sheet("前月比較")

    # 部門別集計
    cur = df_current.groupby("部門")["売上"].sum().rename("今月")
    prv = df_prev.groupby("部門")["売上"].sum().rename("前月")
    merged = pd.concat([cur, prv], axis=1).fillna(0)
    merged["差分"]  = merged["今月"] - merged["前月"]
    merged["増減率"] = (merged["差分"] / merged["前月"].replace(0, float("nan")) * 100).round(1)

    def arrow(rate):
        if pd.isna(rate): return "→"
        if rate >= 5:   return "↑"
        if rate <= -5:  return "↓"
        return "→"

    merged["矢印"] = merged["増減率"].apply(arrow)

    # 上位3・下位3部門を特定
    top3    = set(merged.nlargest(3, "今月").index)
    bottom3 = set(merged.nsmallest(3, "今月").index)

    # Excelへ書き込み
    headers = ["部門", "今月売上", "前月売上", "差分", "増減率(%)", "判定"]
    for col_i, h in enumerate(headers, 1):
        ws.cell(row=1, column=col_i, value=h).font = Font(
            bold=True, color="FFFFFF")
        ws.cell(row=1, column=col_i).fill = PatternFill("solid", fgColor="107C41")

    for row_i, (dept, row) in enumerate(merged.iterrows(), 2):
        bold = dept in top3 or dept in bottom3
        ws.cell(row=row_i, column=1, value=dept).font       = Font(bold=bold)
        ws.cell(row=row_i, column=2, value=row["今月"]).font  = Font(bold=bold)
        ws.cell(row=row_i, column=3, value=row["前月"]).font  = Font(bold=bold)
        ws.cell(row=row_i, column=4, value=row["差分"]).font  = Font(bold=bold)
        ws.cell(row=row_i, column=5, value=row["増減率"]).font = Font(bold=bold)
        ws.cell(row=row_i, column=6, value=row["矢印"]).font  = Font(bold=bold)

    print("  ✅ 前月比較シートを生成しました")

import smtplib, os, csv
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email import encoders
from pathlib import Path

def send_monthly_reports(recipients_csv: str, year_month: str) -> None:
    """Claude Codeが生成：月次レポートを各担当者へ自動メール配信"""

    # Gmail認証（環境変数から取得）
    gmail_user = os.environ["GMAIL_USER"]
    gmail_pass = os.environ["GMAIL_APP_PASSWORD"]

    # 受信者リストを読み込む
    with open(recipients_csv, encoding="utf-8-sig") as f:
        recipients = list(csv.DictReader(f))

    print(f"\n📧 送信予定: {len(recipients)}件")
    for r in recipients:
        print(f"  - {r['担当者名']} ({r['メールアドレス']}) → {r['部門名']}_report_{year_month}.xlsx")

    # 送信確認
    confirm = input("\n上記内容で送信しますか？ (Y/n): ").strip().upper()
    if confirm != "Y":
        print("送信をキャンセルしました")
        return

    # SMTP接続
    with smtplib.SMTP_SSL("smtp.gmail.com", 465) as smtp:
        smtp.login(gmail_user, gmail_pass)

        for r in recipients:
            attach_path = Path(f"reports/{r['部門名']}_report_{year_month}.xlsx")
            if not attach_path.exists():
                print(f"  ⚠️ ファイルなし: {attach_path}")
                continue

            # メール作成
            msg = MIMEMultipart()
            msg["Subject"] = f"【自動配信】{year_month} 月次売上レポート"
            msg["From"]    = gmail_user
            msg["To"]      = r["メールアドレス"]

            body = f"""{r['担当者名']} 様

お疲れ様です。月次売上レポートをお送りします。

添付ファイルをご確認ください。
ご不明な点があればご連絡ください。

本メールはClaude Codeによって自動生成・送信されています。
"""
            msg.attach(MIMEText(body, "plain", "utf-8"))

            # Excelファイルを添付
            with open(attach_path, "rb") as fp:
                part = MIMEBase("application", "octet-stream")
                part.set_payload(fp.read())
            encoders.encode_base64(part)
            part.add_header("Content-Disposition",
                            f'attachment; filename="{attach_path.name}"')
            msg.attach(part)

            smtp.send_message(msg)
            print(f"  ✅ 送信完了: {r['担当者名']} ({r['メールアドレス']})")

    print(f"\n🎉 全{len(recipients)}件のメール送信が完了しました")