PythonでExcelレポートを自動化する完全ガイド【2026年最新】初心者向けコード付き・VBAとの比較・AI活用・定期自動実行まで徹底解説

pip install openpyxl reportlab schedule

import pandas as pd
import matplotlib.pyplot as plt
import openpyxl
print(“環境OK！バージョン:”, pd.__version__)

import pandas as pd
from datetime import datetime

# ① 既存のExcelファイルを読み込む
df = pd.read_excel(‘売上データ.xlsx’)

# ② データを確認する（最初に必ずやること）
print(df.head()) # 先頭5行を表示
print(df.columns) # 列名を確認
print(df.dtypes) # 各列のデータ型を確認

# ③ 日別・商品別・支店別に集計
daily = df.groupby(‘日付’)[‘売上金額’].sum().reset_index()
product = df.groupby(‘商品’)[‘売上金額’].sum().sort_values(ascending=False).reset_index()
branch = df.groupby(‘支店’)[‘売上金額’].sum().sort_values(ascending=False).reset_index()

# ④ 複数シートのExcelに書き出す
filename = f’売上レポート_{datetime.now().strftime(“%Y%m%d”)}.xlsx’
with pd.ExcelWriter(filename, engine=‘openpyxl’) as writer:
daily.to_excel(writer, sheet_name=‘日別’, index=False)
product.to_excel(writer, sheet_name=‘商品別’, index=False)
branch.to_excel(writer, sheet_name=‘支店別’, index=False)
df.to_excel(writer, sheet_name=‘元データ’, index=False)

print(f”完了: {filename}”)

from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
import openpyxl

def format_sheet(ws):
“””シートのヘッダーを青くして列幅を自動調整する”””
# ヘッダー行を青字・白文字・中央揃えに
for cell in ws[1]:
cell.font = Font(bold=True, color=“FFFFFF”)
cell.fill = PatternFill(start_color=“1F4788”, end_color=“1F4788”, fill_type=“solid”)
cell.alignment = Alignment(horizontal=“center”)

# 列幅を内容に合わせて自動調整
for col in ws.columns:
max_len = max(len(str(cell.value or “”)) for cell in col)
ws.column_dimensions[get_column_letter(col[0].column)].width = max_len * 1.3

# 全シートに書式を適用
wb = openpyxl.load_workbook(filename)
for sheet_name in wb.sheetnames:
format_sheet(wb[sheet_name])
wb.save(filename)
print(“書式設定完了”)

import matplotlib.pyplot as plt
import matplotlib
matplotlib.rcParams[‘font.family’] = ‘DejaVu Sans’ # 日本語フォント未設定の場合の対処

fig, axes = plt.subplots(2, 2, figsize=(14, 9))
fig.suptitle(‘Sales Analysis Report’, fontsize=16, fontweight=‘bold’)

# ① 折れ線グラフ：日別売上推移
daily = df.groupby(‘日付’)[‘売上金額’].sum()
axes[0,0].plot(daily.index, daily.values, marker=‘o’, color=‘#3b82f6’)
axes[0,0].set_title(‘Daily Sales Trend’)
axes[0,0].tick_params(axis=‘x’, rotation=45)

# ② 棒グラフ：商品別売上
prod = df.groupby(‘商品’)[‘売上金額’].sum().sort_values(ascending=False)
axes[0,1].bar(prod.index, prod.values, color=‘#10b981’)
axes[0,1].set_title(‘Sales by Product’)

# ③ 円グラフ：支店別構成比
branch = df.groupby(‘支店’)[‘売上金額’].sum()
axes[1,0].pie(branch.values, labels=branch.index, autopct=‘%1.1f%%’)
axes[1,0].set_title(‘Share by Branch’)

# ④ 箱ひげ図：週別売上分布
df[‘週’] = df[‘日付’].dt.isocalendar().week
weekly = [g[‘売上金額’].values for _, g in df.groupby(‘週’)]
axes[1,1].boxplot(weekly)
axes[1,1].set_title(‘Weekly Distribution’)

plt.tight_layout()
plt.savefig(‘sales_chart.png’, dpi=200, bbox_inches=‘tight’)
plt.close()
print(“グラフ保存完了: sales_chart.png”)

from reportlab.lib.pagesizes import A4
from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, Image
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib import colors
from reportlab.lib.units import inch
import os

def create_pdf_report(df, chart_path=‘sales_chart.png’):
filename = f’report_{datetime.now().strftime(“%Y%m%d”)}.pdf’
doc = SimpleDocTemplate(filename, pagesize=A4)
styles = getSampleStyleSheet()
elements = []

# タイトル
elements.append(Paragraph(“Monthly Sales Report”, styles[‘Title’]))
elements.append(Paragraph(f”Created: {datetime.now().strftime(‘%Y-%m-%d’)}”, styles[‘Normal’]))
elements.append(Spacer(1, 12))

# KPIサマリーテーブル
summary = [
[“Metric”, “Value”],
[“Total Sales”, f”¥{df[‘売上金額’].sum():,.0f}”],
[“Avg per Transaction”, f”¥{df[‘売上金額’].mean():,.0f}”],
[“Records”, f”{len(df):,} rows”],
]
tbl = Table(summary, colWidths=[3*inch, 3*inch])
tbl.setStyle(TableStyle([
(‘BACKGROUND’, (0,0), (-1,0), colors.HexColor(‘#1f4788’)),
(‘TEXTCOLOR’, (0,0), (-1,0), colors.white),
(‘FONTNAME’, (0,0), (-1,0), ‘Helvetica-Bold’),
(‘ALIGN’, (0,0), (-1,-1), ‘CENTER’),
(‘GRID’, (0,0), (-1,-1), 0.5, colors.grey),
(‘ROWBACKGROUNDS’, (0,1), (-1,-1), [colors.white, colors.lightgrey]),
]))
elements.append(tbl)
elements.append(Spacer(1, 20))

# グラフ画像を挿入
if os.path.exists(chart_path):
elements.append(Paragraph(“Sales Charts”, styles[‘Heading2’]))
elements.append(Image(chart_path, width=5.5*inch, height=3.5*inch))

doc.build(elements)
print(f”PDF完了: {filename}”)
return filename

create_pdf_report(df)

import schedule, time, smtplib, os
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders

def generate_and_send():
“””レポート生成→メール送信”””
# 1. データ取得（実際はDB・APIに変更）
df = pd.read_excel(‘latest_sales.xlsx’)

# 2. レポート生成
create_sales_charts(df)
pdf_path = create_pdf_report(df)

# 3. メール送信
send_email(
subject = f”Daily Report {datetime.now().strftime(‘%Y-%m-%d’)}”,
body = “Please see the attached daily report.”,
attachments = [pdf_path, ‘sales_chart.png’],
recipients = [“boss@company.com”, “team@company.com”]
)

def send_email(subject, body, attachments, recipients):
# Gmailのアプリパスワードを使う（通常のパスワードではなくアプリパスワードが必要）
sender = “your_email@gmail.com”
password = os.environ.get(“GMAIL_APP_PASSWORD”) # 環境変数から取得（安全）

msg = MIMEMultipart()
msg[‘From’] = sender
msg[‘To’] = “, “.join(recipients)
msg[‘Subject’] = subject
msg.attach(MIMEText(body, ‘plain’))

for fp in attachments:
if os.path.exists(fp):
with open(fp, ‘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(fp)}’)
msg.attach(part)

with smtplib.SMTP(‘smtp.gmail.com’, 587) as s:
s.starttls()
s.login(sender, password)
s.send_message(msg)
print(“メール送信完了”)

# スケジュール設定（毎朝9時・毎週月曜10時）
schedule.every().day.at(“09:00”).do(generate_and_send)
schedule.every().monday.at(“10:00”).do(generate_and_send)

if __name__ == “__main__”:
print(“スケジューラー起動（Ctrl+Cで停止）”)
while True:
schedule.run_pending()
time.sleep(60)