ExcelダッシュボードをClaude Codeに頼んだら自動生成されました【2026年版】

import matplotlib
matplotlib.use(“Agg”) # GUIなし環境用バックエンド（サーバー実行時も動く）
import matplotlib.pyplot as plt
import japanize_matplotlib # 日本語フォント自動対応
import pandas as pd
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.drawing.image import Image as XLImage
from openpyxl.utils import get_column_letter

# ================================
# 設定（必要に応じて変更してください）
# ================================
CONFIG = {
“csv_path”: “sales.csv”,
“output_dir”: “./output”,
“excel_green”: “#107C41”,
“smtp_host”: “smtp.office365.com”,
“smtp_port”: 587,
“sender”: os.getenv(“MAIL_SENDER”),
“password”: os.getenv(“MAIL_PASSWORD”),
“recipients”: [“team@example.com”],
}

# ================================
# Step1: データ読み込みとKPI計算
# ================================
def load_data(csv_path: str) -> tuple:
“””CSVを読み込んでKPI値を計算する”””
df = pd.read_csv(csv_path, parse_dates=[“date”])
df = df.sort_values(“date”)

# 今月のデータを絞り込む
today = pd.Timestamp(“today”)
this_month = df[(df[“date”].dt.year == today.year) &
(df[“date”].dt.month == today.month)]

# KPI計算
kpi = {
“当月売上”: f”¥{this_month[‘sales’].sum()/1e6:.1f}M”,
“達成率”: f”{this_month[‘achievement’].mean():.1f}%”,
“新規受注”: f”{int(this_month[‘orders’].sum())}件”,
“当月売上_num”: this_month[“sales”].sum(),
}

print(f”データ読み込み完了: {len(df)}行”)
return df, kpi

# ================================
# Step2: グラフ3種を生成
# ================================
def generate_charts(df: pd.DataFrame, output_dir: str) -> dict:
“””折れ線・棒グラフ・パイチャートを生成してパスを返す”””
chart_dir = Path(output_dir) / “charts”
chart_dir.mkdir(parents=True, exist_ok=True)
green = CONFIG[“excel_green”]
paths = {}

# — グラフ1: 週次売上推移（折れ線）—
weekly = df.resample(“W”, on=“date”)[“sales”].sum() / 1e6
fig, ax = plt.subplots(figsize=(9, 4))
ax.plot(weekly.index.strftime(“%m/%d”), weekly.values,
color=green, linewidth=2.5, marker=“o”, markersize=6, zorder=3)
ax.fill_between(range(len(weekly)), weekly.values,
alpha=0.12, color=green)
# 最新値ラベル
ax.annotate(f”¥{weekly.values[-1]:.1f}M”,
xy=(len(weekly)-1, weekly.values[-1]),
xytext=(5, 5), textcoords=“offset points”,
color=green, fontweight=“bold”)
ax.set_title(“週次売上推移（百万円）”, fontsize=13, fontweight=“bold”, pad=12)
ax.set_ylabel(“売上（M円）”)
ax.set_xticklabels(weekly.index.strftime(“%m/%d”), rotation=45, ha=“right”)
ax.grid(axis=“y”, linestyle=“–“, alpha=0.5)
[ax.spines[s].set_visible(False) for s in [“top”, “right”]]
plt.tight_layout()
p = str(chart_dir / “line.png”)
fig.savefig(p, dpi=150, bbox_inches=“tight”); plt.close(fig)
paths[“line”] = p

# — グラフ2: 部門別売上（棒グラフ）—
dept = (df.groupby(“department”)[“sales”]
.sum()
.sort_values(ascending=False) / 1e6)
colors = [green if i == 0 else “#5aaa7a”
for i in range(len(dept))]
fig, ax = plt.subplots(figsize=(9, 4))
bars = ax.bar(dept.index, dept.values, color=colors,
edgecolor=“white”, linewidth=0.8)
for bar, val in zip(bars, dept.values):
ax.text(bar.get_x() + bar.get_width()/2,
bar.get_height() + 0.2,
f”¥{val:.1f}M”, ha=“center”, fontsize=9, color=“#333”)
ax.set_title(“部門別売上（百万円）”, fontsize=13, fontweight=“bold”, pad=12)
ax.set_ylabel(“売上（M円）”)
ax.grid(axis=“y”, linestyle=“–“, alpha=0.5)
[ax.spines[s].set_visible(False) for s in [“top”, “right”]]
plt.tight_layout()
p = str(chart_dir / “bar.png”)
fig.savefig(p, dpi=150, bbox_inches=“tight”); plt.close(fig)
paths[“bar”] = p

# — グラフ3: 製品別構成比（パイチャート）—
prod = df.groupby(“product”)[“sales”].sum()
greens = [“#107C41”, “#4aaa6a”, “#8dd5a3”, “#c5edcf”, “#e8f5ee”]
fig, ax = plt.subplots(figsize=(6, 5))
wedges, texts, autotexts = ax.pie(
prod.values, labels=prod.index,
colors=greens[:len(prod)],
autopct=“%1.1f%%”, startangle=90,
wedgeprops=dict(edgecolor=“white”, linewidth=2)
)
for at in autotexts:
at.set_fontsize(9); at.set_color(“white”); at.set_fontweight(“bold”)
ax.set_title(“製品別売上構成比”, fontsize=13, fontweight=“bold”, pad=12)
plt.tight_layout()
p = str(chart_dir / “pie.png”)
fig.savefig(p, dpi=150, bbox_inches=“tight”); plt.close(fig)
paths[“pie”] = p

print(f”グラフ生成完了: {len(paths)}種”)
return paths

# ================================
# Step3: Excelダッシュボードをopenpyxlで組み立てる
# ================================
def build_dashboard_excel(kpi: dict, chart_paths: dict, output_path: str) -> str:
“””openpyxlでKPIカード＋グラフ付きダッシュボードExcelを生成する”””

wb = openpyxl.Workbook()
ws = wb.active
ws.title = “Dashboard”
# ページ設定（横向き印刷対応）
ws.page_setup.orientation = “landscape”
ws.page_setup.fitToPage = True
# 列幅の設定
for col in range(1, 20):
ws.column_dimensions[get_column_letter(col)].width = 10
# —- タイトル行 —-
ws.merge_cells(“A1:R1”)
title_cell = ws[“A1”]
title_cell.value = f”日次KPIダッシュボード — {datetime.date.today().strftime(‘%Y年%m月%d日’)} 更新”
title_cell.font = Font(name=“游ゴシック”, bold=True, size=16, color=“FFFFFF”)
title_cell.fill = PatternFill(“solid”, fgColor=“107C41”)
title_cell.alignment = Alignment(horizontal=“center”, vertical=“center”)
ws.row_dimensions[1].height = 36
# —- KPIカード生成ヘルパー関数 —-
def make_kpi_card(label_cell, value_cell, merge_label, merge_value, label, value):
ws.merge_cells(merge_label)
ws.merge_cells(merge_value)
thin = Side(style=“thin”, color=“107C41”)
border = Border(left=thin, right=thin, top=thin, bottom=thin)

lc = ws[label_cell]
lc.value = label
lc.font = Font(name=“游ゴシック”, size=9, color=“555555”)
lc.fill = PatternFill(“solid”, fgColor=“F0FAF4”)
lc.alignment = Alignment(horizontal=“center”, vertical=“center”)

vc = ws[value_cell]
vc.value = value
vc.font = Font(name=“游ゴシック”, bold=True, size=22, color=“107C41”)
vc.fill = PatternFill(“solid”, fgColor=“F0FAF4”)
vc.alignment = Alignment(horizontal=“center”, vertical=“center”)

# 3枚のKPIカードを配置
make_kpi_card(“A3”, “A4”, “A3:F3”, “A4:F5”, “当月売上”, kpi[“当月売上”])
make_kpi_card(“G3”, “G4”, “G3:L3”, “G4:L5”, “目標達成率”, kpi[“達成率”])
make_kpi_card(“M3”, “M4”, “M3:R3”, “M4:R5”, “新規受注件数”, kpi[“新規受注”])

# —- グラフ画像の埋め込み —-
line_img = XLImage(chart_paths[“line”])
line_img.width, line_img.height = 520, 260
ws.add_image(line_img, “A8”) # 折れ線グラフ（左上）

bar_img = XLImage(chart_paths[“bar”])
bar_img.width, bar_img.height = 520, 260
ws.add_image(bar_img, “A28”) # 棒グラフ（左下）

pie_img = XLImage(chart_paths[“pie”])
pie_img.width, pie_img.height = 380, 300
ws.add_image(pie_img, “K28”) # パイチャート（右下）

wb.save(output_path)
print(f”Excel保存完了: {output_path}”)
return output_path

# ================================
# Step4: メール自動送信
# ================================
import smtplib, ssl
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email import encoders

def send_dashboard_email(kpi: dict, attachment_path: str):
“””生成したExcelダッシュボードをメール添付で送信する”””
today = datetime.date.today()
subject = f”【自動配信】日次KPIダッシュボード {today}”
body = f”””おはようございます。

{today} の日次KPIダッシュボードを添付いたします（自動送信）。

【本日のハイライト】
・当月売上: {kpi[‘当月売上’]}
・目標達成率: {kpi[‘達成率’]}
・新規受注: {kpi[‘新規受注’]}

詳細は添付Excelをご確認ください。
“””

msg = MIMEMultipart()
msg[“From”] = CONFIG[“sender”]
msg[“To”] = “, “.join(CONFIG[“recipients”])
msg[“Subject”] = subject
msg.attach(MIMEText(body, “plain”, “utf-8”))

with open(attachment_path, “rb”) as f:
part = MIMEBase(“application”, “octet-stream”)
part.set_payload(f.read())
encoders.encode_base64(part)
part.add_header(“Content-Disposition”,
f’attachment; filename=”{os.path.basename(attachment_path)}”‘)
msg.attach(part)

context = ssl.create_default_context()
with smtplib.SMTP(CONFIG[“smtp_host”], CONFIG[“smtp_port”]) as server:
server.starttls(context=context)
server.login(CONFIG[“sender”], CONFIG[“password”])
server.sendmail(CONFIG[“sender”], CONFIG[“recipients”], msg.as_string())
print(f”メール送信完了: {‘, ‘.join(CONFIG[‘recipients’])}”)

# ================================
# メイン処理
# ================================
def main():
print(“=== KPIダッシュボード自動生成（Claude Code生成）===”)
today_str = datetime.date.today().strftime(“%Y%m%d”)
output_dir = CONFIG[“output_dir”]
Path(output_dir).mkdir(exist_ok=True)

print(“\n[1/4] データ読み込み中…”)
df, kpi = load_data(CONFIG[“csv_path”])

print(“\n[2/4] グラフ生成中…”)
chart_paths = generate_charts(df, output_dir)

print(“\n[3/4] Excelダッシュボード作成中…”)
xlsx_path = f”{output_dir}/dashboard_{today_str}.xlsx”
build_dashboard_excel(kpi, chart_paths, xlsx_path)

print(“\n[4/4] メール送信中…”)
send_dashboard_email(kpi, xlsx_path)

print(“\n完了！”)

if __name__ == “__main__”:
main()