Python 操作 Excel 报表自动化指南！

昨晚十一点多我在公司楼下抽烟…哎别学我，冷风一吹脑子反而更清醒。我们组那个小李喊我说：东哥，报表又得天天手搓？我看了眼他桌面那一坨 Excel，心里咯噔一下——这不就是一个“能自动干就别手干”的活么，对吧。就顺手把电脑合上又打开（你懂那种仪式感），想着用 Python 把这事儿一键化，省点命。

先别急着谈多高深的那个算发…哦是算法。现实里最常见的需求就仨：把一堆 CSV/数据库里的数据合到一个 Excel；顺手做点清洗、汇总；再搞个像样的样式和图表发给老板。就按这个来。

我当时边泡面边敲了个小脚本，最小闭环先跑起来——能把一个目录里所有 CSV 合并成一张“明细”，再来一张“汇总”，顺便画个柱状图，邮件就能丢出去了。代码丢你们，别嫌丑，能跑就行：

# -*- coding: utf-8 -*-
# 合并CSV -> 生成带汇总与图表的Excel
# 用法: python make_report.py --src ./data --out report.xlsx
import argparse, pathlib
import pandas as pd
defload_all_csv(src: pathlib.Path) -> pd.DataFrame:
    files = sorted(src.glob("*.csv"))
ifnot files:
raise SystemExit(f"目录 {src} 里没找到 CSV")
# 合并并规范列名
    dfs = []
for f in files:
        df = pd.read_csv(f, encoding="utf-8", low_memory=False)
        df.columns = [c.strip() for c in df.columns]
        df["__file__"] = f.name  # 留个来源，排错好用
        dfs.append(df)
    all_df = pd.concat(dfs, ignore_index=True)
return all_df
defbuild_summary(df: pd.DataFrame) -> pd.DataFrame:
# 假设有“日期”“品类”“金额”三列，自己按实际改列名
# 日期取月，金额求和
    df["日期"] = pd.to_datetime(df["日期"], errors="coerce")
    df["月份"] = df["日期"].dt.to_period("M").astype(str)
    df["金额"] = pd.to_numeric(df["金额"], errors="coerce").fillna(0)
    g = (df
         .groupby(["月份", "品类"], as_index=False)["金额"]
         .sum()
         .sort_values(["月份", "金额"], ascending=[True, False]))
return g
defto_excel_with_chart(detail: pd.DataFrame, summary: pd.DataFrame, out_path: pathlib.Path):
with pd.ExcelWriter(out_path, engine="xlsxwriter") as wb:
        detail.to_excel(wb, index=False, sheet_name="明细")
        summary.to_excel(wb, index=False, sheet_name="汇总")
# 一点点格式：列宽、千分位、冻结窗格
        ws_detail = wb.sheets["明细"]
        ws_summary = wb.sheets["汇总"]
        ws_detail.freeze_panes(1, 0)
        ws_summary.freeze_panes(1, 0)
# 估个列宽，别太窄（粗暴但实用）
defautofit(ws, df, max_width=40):
for col_idx, col in enumerate(df.columns):
                series = df[col].astype(str)
                width = min(max(series.map(len).max(), len(str(col))) + 2, max_width)
                ws.set_column(col_idx, col_idx, width)
        autofit(ws_detail, detail)
        autofit(ws_summary, summary)
# 数字格式给“金额”
        book = wb.book
        money_fmt = book.add_format({"num_format": "#,##0.00"})
# 找到“金额”列索引
        money_col_idx = summary.columns.get_loc("金额")
        ws_summary.set_column(money_col_idx, money_col_idx, 14, money_fmt)
# 画个柱状图：每月各品类金额
        chart = book.add_chart({"type": "column"})
# 数据范围（注意 Excel 的1基坐标）
        rows = len(summary)
# 类目轴：月份
        chart.set_x_axis({"name": "月份"})
        chart.set_y_axis({"name": "金额", "major_gridlines": {"visible": False}})
        chart.set_title({"name": "按月按品类销售额"})
# 为每个“品类”单独加一条序列
for i, cat in enumerate(summary["品类"].unique()):
            sub = summary[summary["品类"] == cat]
            start = sub.index.min()
            end   = sub.index.max()
# 汇总表在Excel的坐标：A1起点 -> 行偏移+1（去掉表头）
# 列：0 月份, 1 品类, 2 金额（如果你的列顺序不同，改索引）
            chart.add_series({
"name":       ["汇总", start + 1, 1],
"categories": ["汇总", start + 1, 0, end + 1, 0],
"values":     ["汇总", start + 1, 2, end + 1, 2],
"data_labels": {"value": False}
            })
# 把图表放到汇总表右边
        ws_summary.insert_chart("E2", chart, {"x_scale": 1.2, "y_scale": 1.2})
defmain():
    ap = argparse.ArgumentParser()
    ap.add_argument("--src", required=True, help="CSV目录")
    ap.add_argument("--out", default="report.xlsx", help="输出Excel路径")
    args = ap.parse_args()
    src = pathlib.Path(args.src)
    out = pathlib.Path(args.out)
    detail = load_all_csv(src)
    summary = build_summary(detail)
    to_excel_with_chart(detail, summary, out)
    print(f"OK -> {out.resolve()}")
if __name__ == "__main__":
    main()

我当时跑完，正好电梯到一楼，领导发来一句“图表还挺清楚”，心情瞬间不困了。你们注意几个点哈，别踩坑：金额先转成数值，日期别信任“文本长得像日期”，一律 to_datetime；图表那段我用 xlsxwriter 的坐标是 1 基的，切片别搞错，不然图就空白，真的会怀疑人生。

说到样式，这玩意儿要不讲讲“把报表弄得像能见人”的细节。那晚回工位我给小李顺手加了条件格式、下拉校验、冻结、批注，全部纯代码，点开就像手工做过一样。这个我更喜欢 openpyxl 来改现成 Excel，因为它读写都方便。样例随手贴一下，你们照抄就能用：

# 给现有Excel加样式/校验/冻结/条件格式等（openpyxl）
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment, PatternFill, NamedStyle, numbers
from openpyxl.formatting.rule import CellIsRule
from openpyxl.worksheet.datavalidation import DataValidation
wb = load_workbook("report.xlsx")
ws = wb["明细"]
# 表头加粗、底色、居中
header = NamedStyle(name="header")
header.font = Font(bold=True, color="FFFFFF")
header.alignment = Alignment(horizontal="center", vertical="center")
header.fill = PatternFill("solid", fgColor="4F81BD")
for cell in ws[1]:
    cell.style = header
# 金额列设置数值格式（找列名=金额）
col_map = {c.value: idx+1for idx, c in enumerate(ws[1])}
money_col = col_map.get("金额")
if money_col:
for r in range(2, ws.max_row + 1):
        ws.cell(r, money_col).number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1
# 负数高亮（条件格式）
if money_col:
    col_letter = ws.cell(1, money_col).column_letter
    rng = f"{col_letter}2:{col_letter}{ws.max_row}"
    ws.conditional_formatting.add(
        rng,
        CellIsRule(operator="lessThan", formula=["0"], stopIfTrue=True,
                   fill=PatternFill("solid", fgColor="FFC7CE"))
    )
# 冻结首行 + 自动筛选
ws.freeze_panes = "A2"
ws.auto_filter.ref = ws.dimensions
# 数据有效性：品类列做下拉（枚举来自“汇总”表的品类）
cat_col = col_map.get("品类")
if cat_col:
    cats = wb["汇总"]["B2":"B9999"]  # 保险点写长些
# 定义一个命名区域，或者直接用公式 UNIQUE 取去重（Excel 365）
    dv = DataValidation(type="list", formula1="=UNIQUE(汇总!B2:B9999)", allow_blank=True)
    ws.add_data_validation(dv)
    dv.add(f"{ws.cell(2, cat_col).coordinate}:{ws.cell(ws.max_row, cat_col).coordinate}")
wb.save("report_styled.xlsx")

你别看这几步碎碎的，效果很顶：负数一眼红，筛选能点、表头不丑、金额有千分位，数据录错还会弹提示。我们测试妹子看了都说“这才像报表”。对了我当时还手欠给“日期”列加了注释说“必须 YYYY-MM-DD”，结果小李复制粘贴的时候还是带了中文冒号…唉，算了不想解释了，反正有校验。

有人问自动化就自动化，能不能别每次命令行手敲。我说这事儿分两块：一个是你脚本本身要容错，另一个是让系统替你按时跑。脚本这边加点异常处理、日志别省，这样半夜出问题也能追。排期这边我那天就这么设的——Windows 就“任务计划程序”，Linux 就 crontab，真的很朴素：

# Windows 任务计划程序 -> 操作填:
# Program/script:
python
# Add arguments:
C:\reports\make_report.py --src C:\reports\csv --out C:\reports\report.xlsx
# Linux crontab，每天 06:30 生成报表（注意虚拟环境路径）
30 6 * * * /usr/bin/env bash -lc 'source ~/venv/bin/activate && python ~/reports/make_report.py --src ~/reports/csv --out ~/reports/report.xlsx >> ~/reports/logs/job.log 2>&1'

对了对了，生产里数据不一定来自 CSV，多半是数据库。我那会儿在公司楼下又遇到一个同事，问能不能把 MySQL 直接吐成 Excel，我一口答应了，回到座位就写了个“边查边流式写”的，数据大也不怕。要点是：pandas.read_sql_query(chunksize=50000) 分块拉，边写到 Excel 明细，汇总别在 Excel 里做旋转表…哦 pivot 表那个在纯代码里可搞起来不太稳，就让 pandas 帮你 groupby 出来，再写到“汇总”就够用了。你看：

import pandas as pd
import sqlalchemy as sa
engine = sa.create_engine("mysql+pymysql://user:pwd@host:3306/db?charset=utf8mb4")
sql = """
SELECT 日期, 品类, 金额
FROM order_items
WHERE 日期 BETWEEN :start AND :end
"""
params = {"start": "2025-10-01", "end": "2025-10-31"}
writer = pd.ExcelWriter("report_db.xlsx", engine="xlsxwriter")
detail_iter = pd.read_sql_query(sql, engine, params=params, chunksize=50_000)
row_offset = 0
for chunk in detail_iter:
    chunk.to_excel(writer, sheet_name="明细", startrow=row_offset, index=False, header=(row_offset==0))
    row_offset += len(chunk)
# 构建汇总（直接在数据库侧 group by 也行，这里演示 pandas）
df_all = pd.read_sql_query(sql, engine, params=params)
df_all["日期"] = pd.to_datetime(df_all["日期"])
df_all["月份"] = df_all["日期"].dt.to_period("M").astype(str)
df_all["金额"] = pd.to_numeric(df_all["金额"], errors="coerce").fillna(0)
summary = (df_all.groupby(["月份", "品类"], as_index=False)["金额"].sum()
                  .sort_values(["月份", "金额"], ascending=[True, False]))
summary.to_excel(writer, sheet_name="汇总", index=False)
writer.close()

顺手插几句“为什么我这么写”，别等你们线上挨打了再想起来：第一，chunksize 能救命，不然几百万行直接爆内存；第二，金额和日期一定要强制转型，Excel 的“长得像数字”的字符串比骗子还会装；第三，图表范围别用“最后一行未知”的那种魔法，稳定=加班少；第四，别在 Excel 里上来就建透视表，库里 group by 或 pandas 聚合更可控，兼容性好；第五，路径用绝对路径，计划任务就不迷路了。

你们是不是还想要点花活？嗯…那会儿我蹲在茶水间想了个“通用化一点的结构”，把清洗逻辑抽成函数，列名映射写成配置，拿到新部门也能复用，像这样：

# 简易“规则驱动”的清洗：列映射、正则清洗、必填校验
import re, json, pandas as pd
RULES = {
"columns": { "date": r"^(日期|时间)$", "category": r"^(品类|类别)$", "amount": r"^(金额|实收)$" },
"required": ["date", "amount"],
"filters": [
        {"field": "amount", "op": "ge", "value": 0},  # 过滤负数，示例
    ]
}
defnormalize_columns(df: pd.DataFrame, rules=RULES):
    mapping = {}
for std, pattern in rules["columns"].items():
for col in df.columns:
if re.match(pattern, col):
                mapping[col] = std
break
    df = df.rename(columns=mapping)
    missing = [c for c in rules["required"] if c notin df.columns]
if missing:
raise ValueError(f"缺少必需列: {missing}")
return df
defapply_filters(df: pd.DataFrame, rules=RULES):
for f in rules.get("filters", []):
if f["op"] == "ge":
            df = df[df[f["field"]] >= f["value"]]
return df
# 用法
# df = pd.read_csv("xxx.csv")
# df = normalize_columns(df)
# df["date"] = pd.to_datetime(df["date"], errors="coerce")
# df["amount"] = pd.to_numeric(df["amount"], errors="coerce").fillna(0)
# df = apply_filters(df)

有人在群里问“能不能在 Excel 里做数据验证的级联下拉？”我说能，但没必要用太多黑魔法；要真想玩，可以用 openpyxl 在隐藏 Sheet 里放字典，再用 INDIRECT 做二级联动，只是维护成本上去了。还有密码保护、只读、签名这种合规需求，openpyxl 的保护更多是“防误操作”，不是安全手段，别把它当保险柜。

哦对，坑里最大的一个我差点忘说了：数字当文本、文本当数字，尤其金额列。你明明看上去是 1000，其实是 "1,000" 或 "1000 "（注意空格），groupby 完直接变两条。解决方案就是刚才那套“入口统一转型”，再加一个 strip() 去空格。还有时区，Excel 会把无时区的日期当本地时区，跨服务器部署时一个写 CST 一个写 UTC，最后你图表一个月多一天少一天，业务要打人…你就老老实实在数据库里转成日期，不带时区，或统一转 UTC，别混。

我现在困死了但是还得说一句，自动化这事儿别追求一口吃成胖子。先把你每天最烦的那 30 分钟干掉，再去补样式、再补图表、再接数据库、最后再把计划任务挂上监控（失败告警个飞书/钉钉 Webhook，两行就够）。这样一步步上，稳得很。好了我先…等等我先接个电话，喂小李？你把“金额(元)”那列的括号也算列名的一部分了是吧…行我一会儿给你把列映射正则改宽一点儿…