Python 自动化办公实战：Excel 数据处理与图表生成全流程

bash复制编辑pip install openpyxl pandas matplotlib

python复制编辑import pandas as pd

df = pd.read_excel("sales_data.xlsx")
print(df.head())

css复制编辑          日期   区域  销售员  产品   销量   单价
0  2025-06-01  华东   张三   A类  100   20
1  2025-06-01  华北   李四   B类  120   18

python复制编辑df["销售额"] = df["销量"] * df["单价"]

python复制编辑area_summary = df.groupby("区域")["销售额"].sum().reset_index()
print(area_summary)

python复制编辑product_summary = df.groupby("产品")["销量"].sum().sort_values(ascending=False)

python复制编辑import matplotlib.pyplot as plt

plt.figure(figsize=(8, 6))
product_summary.plot(kind="bar", color="skyblue")
plt.title("各产品销量统计")
plt.xlabel("产品")
plt.ylabel("销量")
plt.tight_layout()
plt.savefig("product_sales.png")
plt.show()

python复制编辑daily_sales = df.groupby("日期")["销售额"].sum()

plt.figure(figsize=(10, 5))
daily_sales.plot(kind="line", marker="o")
plt.title("每日销售额变化")
plt.ylabel("销售额")
plt.grid(True)
plt.tight_layout()
plt.savefig("daily_sales.png")

python复制编辑from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
ws = wb.active
ws.title = "销售图表报告"

# 插入图片
img = Image("product_sales.png")
ws.add_image(img, "A1")

img2 = Image("daily_sales.png")
ws.add_image(img2, "A20")

wb.save("sales_report.xlsx")

python复制编辑import glob

all_data = pd.DataFrame()

for file in glob.glob("data/month_*.xlsx"):
    df = pd.read_excel(file)
    df["销售额"] = df["销量"] * df["单价"]
    all_data = pd.concat([all_data, df], ignore_index=True)

print("合并后数据量：", all_data.shape)

python复制编辑import smtplib
from email.message import EmailMessage

msg = EmailMessage()
msg["Subject"] = "月度销售报告"
msg["From"] = "me@example.com"
msg["To"] = "boss@example.com"
msg.set_content("您好，附件为本月销售报告，请查收。")

with open("sales_report.xlsx", "rb") as f:
    msg.add_attachment(f.read(), maintype="application", subtype="vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename="sales_report.xlsx")

with smtplib.SMTP_SSL("smtp.example.com", 465) as smtp:
    smtp.login("me@example.com", "password")
    smtp.send_message(msg)

css复制编辑📂excel_auto_report
├── sales_data.xlsx
├── sales_report.xlsx
├── product_sales.png
├── daily_sales.png
├── main.py
└── readme.md