Claude Code + openpyxl でExcel作業を完全自動化する入門ガイド【2026年版】

FILE_PATH = “sales_2026.xlsx”
GREEN = “107C41”
def thin_border() -> Border:
s = Side(style=“thin”, color=“D1D5DB”)
return Border(left=s, right=s, top=s, bottom=s)

# ================================
# Step1: Excelファイルを読み込む
# ================================
def load_sales_data() -> pd.DataFrame:
“””openpyxlでExcelを開いてpandasのDataFrameに変換する”””
wb = openpyxl.load_workbook(FILE_PATH, read_only=True)
ws = wb[“売上データ”]

rows = []
for row in ws.iter_rows(min_row=2, values_only=True):
if row[0] is None: continue
rows.append({
“date”: pd.Timestamp(row[0]),
“assignee”: row[1],
“product”: row[2],
“sales”: row[3] or 0,
“quantity”: row[4] or 0,
})
wb.close()
df = pd.DataFrame(rows)
print(f”読み込み完了: {len(df)}行”)
return df

# ================================
# Step2: 集計処理
# ================================
def aggregate(df: pd.DataFrame) -> dict:
“””担当者別・月別・商品別の集計を行う”””
# 担当者別集計
by_assignee = (df.groupby(“assignee”)
.agg(売上合計=(“sales”, “sum”), 件数=(“sales”, “count”))
.sort_values(“売上合計”, ascending=False)
.reset_index())
by_assignee[“平均単価”] = (by_assignee[“売上合計”] / by_assignee[“件数”]).round()

# 月別集計
df[“month”] = df[“date”].dt.to_period(“M”)
by_month = (df.groupby(“month”)[“sales”]
.sum()
.reset_index())
by_month[“month”] = by_month[“month”].astype(str)

# 商品別ランキング（上位5）
top5 = (df.groupby(“product”)[“sales”]
.sum()
.sort_values(ascending=False)
.head(5)
.reset_index())
top5.index = top5.index + 1 # 1位から始める
return {“by_assignee”: by_assignee,
“by_month”: by_month,
“top5”: top5}

# ================================
# Step3: 集計結果をExcelに書き込む
# ================================
def write_report(data: dict, file_path: str):
“””集計結果を新しいシート「集計レポート」に書き込む”””
wb = openpyxl.load_workbook(file_path)
# 既存の集計シートは削除して作り直す
if “集計レポート” in wb.sheetnames:
del wb[“集計レポート”]
ws = wb.create_sheet(“集計レポート”)

def write_section(ws, title: str, df_section: pd.DataFrame,
start_row: int, header_cols: list) -> int:
“””セクションタイトル + ヘッダー + データ行を書き込むヘルパー関数”””
end_col = get_column_letter(len(header_cols))
ws.merge_cells(f”A{start_row}:{end_col}{start_row}”)
title_cell = ws[f”A{start_row}”]
title_cell.value = title
title_cell.font = Font(bold=True, size=12, color=“FFFFFF”)
title_cell.fill = PatternFill(“solid”, fgColor=GREEN)
title_cell.alignment = Alignment(horizontal=“center”, vertical=“center”)
ws.row_dimensions[start_row].height = 28
current_row = start_row + 1
for ci, col_name in enumerate(header_cols, 1):
cell = ws.cell(row=current_row, column=ci, value=col_name)
cell.font = Font(bold=True, color=“FFFFFF”, size=10)
cell.fill = PatternFill(“solid”, fgColor=“0a5a2f”)
cell.alignment = Alignment(horizontal=“center”, vertical=“center”)
cell.border = thin_border()
current_row += 1
for ri, data_row in df_section.iterrows():
row_bg = “FFFFFF” if ri % 2 == 0 else “F0FAF4”
for ci, val in enumerate(data_row, 1):
cell = ws.cell(row=current_row, column=ci,
value=int(val) if isinstance(val, float) else val)
cell.fill = PatternFill(“solid”, fgColor=row_bg)
cell.font = Font(size=9)
cell.alignment = Alignment(horizontal=“right” if ci > 1 else “left”,
vertical=“center”)
cell.border = thin_border()
if ci == 2 and isinstance(val, (int, float)):
cell.number_format = ‘#,##0’
current_row += 1
return current_row + 2 # 空白2行あけて次セクション

next_row = 1
next_row = write_section(ws, “担当者別売上集計”,
data[“by_assignee”].values.tolist(),
next_row,
[“担当者”, “売上合計（円）”, “件数”, “平均単価（円）”])
next_row = write_section(ws, “月別売上推移”,
data[“by_month”].values.tolist(),
next_row,
[“年月”, “売上合計（円）”])
next_row = write_section(ws, “商品別売上ランキング（上位5）”,
data[“top5”].reset_index().values.tolist(),
next_row,
[“順位”, “商品名”, “売上合計（円）”])

for col in [“A”, “B”, “C”, “D”]:
ws.column_dimensions[col].width = 22

wb.save(file_path)
print(f”集計レポートシート書き込み完了: {file_path}”)

def main():
df = load_sales_data()
data = aggregate(df)
write_report(data, FILE_PATH)
print(“完了！”)

if __name__ == “__main__”:
main()

def create_formatted_excel(data: list, output_path: str):
wb = openpyxl.Workbook()
ws = wb.active
ws.title = “売上レポート”

headers = [“日付”, “売上金額”, “数量”, “単価”, “担当者”, “商品名”]
col_widths = [14, 16, 10, 14, 14, 24]

# —- ヘッダー行の書式 —-
thin = Side(style=“thin”, color=“CCCCCC”)
border = Border(left=thin, right=thin, top=thin, bottom=thin)

for ci, (h, w) in enumerate(zip(headers, col_widths), 1):
cell = ws.cell(row=1, column=ci, value=h)
cell.font = Font(bold=True, color=“FFFFFF”, size=10)
cell.fill = PatternFill(“solid”, fgColor=“107C41”)
cell.alignment = Alignment(horizontal=“center”, vertical=“center”)
cell.border = border
ws.column_dimensions[get_column_letter(ci)].width = w
ws.row_dimensions[1].height = 28
# —- データ行の書式 —-
for ri, row_data in enumerate(data, 2):
row_bg = “FFFFFF” if ri % 2 == 0 else “F0FAF4”
# A列: 日付
cell_date = ws.cell(row=ri, column=1, value=row_data[0])
cell_date.number_format = “YYYY/MM/DD”
# B列: 売上金額（C列×D列の数式を挿入）
cell_sales = ws.cell(row=ri, column=2)
cell_sales.value = f”=C{ri}*D{ri}” # 数式を挿入
cell_sales.number_format = FORMAT_NUMBER_COMMA_SEPARATED1

# C列: 数量, D列: 単価
ws.cell(row=ri, column=3).value = row_data[2]
ws.cell(row=ri, column=4).value = row_data[3]
ws.cell(row=ri, column=4).number_format = FORMAT_NUMBER_COMMA_SEPARATED1

# E列: 担当者, F列: 商品名
ws.cell(row=ri, column=5).value = row_data[4]
ws.cell(row=ri, column=6).value = row_data[5]

for ci in range(1, 7):
cell = ws.cell(row=ri, column=ci)
cell.fill = PatternFill(“solid”, fgColor=row_bg)
cell.font = Font(size=9)
cell.border = border
cell.alignment = Alignment(
horizontal=“right” if ci in [2, 3, 4] else “left”,
vertical=“center”)

# —- 合計行（SUM数式を自動挿入）—-
last_data_row = len(data) + 1
total_row = last_data_row + 1
ws.cell(row=total_row, column=1, value=“合計”)
ws.cell(row=total_row, column=2,
value=f”=SUM(B2:B{last_data_row})”).number_format = FORMAT_NUMBER_COMMA_SEPARATED1
ws.cell(row=total_row, column=3,
value=f”=SUM(C2:C{last_data_row})”)

for ci in range(1, 7):
cell = ws.cell(row=total_row, column=ci)
cell.fill = PatternFill(“solid”, fgColor=“E8F5EE”)
cell.font = Font(bold=True, size=9, color=“107C41”)
cell.border = border
cell.alignment = Alignment(
horizontal=“right” if ci in [2, 3, 4] else “left”,
vertical=“center”)

# —- ウィンドウ枠の固定 —-
ws.freeze_panes = “A2” # 1行目（ヘッダー）を固定

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

def add_charts(ws):
“””棒グラフ・折れ線グラフをシートに追加する（Claude Code生成）”””
max_row = ws.max_row
# —- 棒グラフ: 月別売上 —-
bar = BarChart()
bar.type = “col” # 縦棒グラフ
bar.title = “月別売上推移”
bar.y_axis.title = “売上金額（円）”
bar.x_axis.title = “月”
bar.height = 15
bar.width = 22
bar.style = 10
# データ範囲の参照
data_ref = Reference(ws, min_col=2, min_row=1, max_row=max_row)
cats_ref = Reference(ws, min_col=1, min_row=2, max_row=max_row)
bar.add_data(data_ref, titles_from_data=True)
bar.set_categories(cats_ref)

# バーの色を Excel 緑に設定
series = bar.series[0]
series.graphicalProperties.solidFill = “107C41”
series.graphicalProperties.line.solidFill = “107C41”

ws.add_chart(bar, “D1”)

# —- 折れ線グラフ: 目標達成率推移 —-
line = LineChart()
line.title = “月別目標達成率推移”
line.y_axis.title = “達成率（%）”
line.x_axis.title = “月”
line.height = 15
line.width = 22
line.style = 10

rate_ref = Reference(ws, min_col=3, min_row=1, max_row=max_row)
line.add_data(rate_ref, titles_from_data=True)
line.set_categories(cats_ref)

series2 = line.series[0]
series2.graphicalProperties.line.solidFill = “107C41”
series2.graphicalProperties.line.width = 25000 # 2.5pt
series2.smooth = True # スムーズな曲線

ws.add_chart(line, “D20”)
print(“グラフ追加完了”)

def batch_merge_excel(data_dir: str = “data”,
output_path: str = “summary_2026.xlsx”):
“””フォルダ内の全xlsxファイルを読み込んで年次サマリーを生成する”””
xlsx_files = sorted(Path(data_dir).glob(“*.xlsx”))
print(f”対象ファイル数: {len(xlsx_files)}”)

all_dfs = []
for xlsx_file in tqdm(xlsx_files, desc=“読み込み中”):
try:
df = pd.read_excel(xlsx_file, sheet_name=0, engine=“openpyxl”)
df[“source_file”] = xlsx_file.stem # ソースファイル名を付加
all_dfs.append(df)
except Exception as e:
print(f”警告: {xlsx_file.name} の読み込みに失敗しました: {e}”)

if not all_dfs:
print(“読み込めるファイルがありませんでした”)
return

merged = pd.concat(all_dfs, ignore_index=True)
print(f”結合完了: 合計 {len(merged):,} 行”)

# 月別サマリー
monthly = (merged.groupby(“source_file”)[“売上金額”]
.agg([“sum”, “count”, “mean”])
.rename(columns={“sum”: “売上合計”, “count”: “件数”,
“mean”: “平均単価”})
.reset_index())

# pandas ExcelWriterでopenpyxlエンジンを使って出力
with pd.ExcelWriter(output_path, engine=“openpyxl”) as writer:
monthly.to_excel(writer, sheet_name=“月別サマリー”, index=False)
merged.to_excel(writer, sheet_name=“全明細データ”, index=False)

print(f”完了！ → {output_path}”)

if __name__ == “__main__”:
batch_merge_excel()