用 Python 自动生成 Excel 报表,从此告别手动复制粘贴
- 2026-09-22 12:14:28
每天早上打开邮箱，把昨天导出的 CSV 复制到 Excel，加表头、调格式、画图表、另存为……这套动作重复了三年，直到我用 Python 写了一个脚本。
今天这篇文章，我带你从零实现一个自动生成 Excel 报表的完整方案。最终效果是：运行一次脚本，直接产出一份带明细页、汇总透视表、条件格式数据条、柱状图的 Excel 文件，格式还比手搓的漂亮。
一、环境准备
pip install pandas openpyxl numpy分工很简单：
pandas：负责数据的读取、计算、透视
openpyxl：负责"精装修"——字体、颜色、边框、数字格式、图表
二、整体思路
读取数据 → 聚合计算 → pandas 写入 Excel → openpyxl 二次美化 → 保存关键点是：先用 pandas 把数据"倒"进 Excel，再用 openpyxl 打开这个文件做装修。这样既享受了 pandas 的计算能力，又能拿到 openpyxl 的全部样式控制权。
三、第一步：准备数据
真实场景里，这里应该是 pd.read_excel() / pd.read_sql() / pd.read_csv()。为了你能直接跑通，我用随机数据模拟一份销售流水：
import pandas as pdimport numpy as npfrom datetime import datetime, timedeltadef build_data(n=500, seed=42):rng = np.random.default_rng(seed)regions = ["华东", "华北", "华南", "西南", "东北"]products = ["A款", "B款", "C款", "D款"]start = datetime(2024, 1, 1)records = []for _ in range(n):d = start + timedelta(days=int(rng.integers(0, 181)))qty = int(rng.integers(1, 50))price = float(rng.integers(20, 200))records.append({"订单日期": d,"区域": str(rng.choice(regions)),"产品": str(rng.choice(products)),"销量": qty,"单价": price,})df = pd.DataFrame(records)df["销售额"] = df["销量"] * df["单价"]df["月份"] = df["订单日期"].dt.to_period("M").astype(str)return df
四、第二步：pandas 写入 Excel
我们要产出两张表：明细数据（原始流水）和区域汇总（交叉透视表）。
df = build_data()# 生成透视表：行=区域，列=产品，值=销售额summary = df.pivot_table(index="区域", columns="产品",values="销售额", aggfunc="sum", fill_value=0).round(0)# 加"合计"列和"合计"行summary["合计"] = summary.sum(axis=1)summary.loc["合计"] = summary.sum()summary.index.name = "区域"summary.columns.name = NoneOUT = f"销售报表_{datetime.now():%Y%m%d_%H%M}.xlsx"with pd.ExcelWriter(OUT, engine="openpyxl") as writer:df.to_excel(writer, sheet_name="明细数据", index=False)summary.to_excel(writer, sheet_name="区域汇总")print("数据已写入，开始美化...")
⚠️ 一定要用
with语句，否则文件可能在缓冲区里没写完，打开时报"文件损坏"。
五、第三步：openpyxl 精装修
这一步是全文的核心。先把样式常量集中定义，方便统一换肤：
from openpyxl import load_workbookfrom openpyxl.styles import Font, PatternFill, Alignment, Border, Sidefrom openpyxl.utils import get_column_letterHEADER_FILL = PatternFill("solid", fgColor="2F5597") # 深蓝表头HEADER_FONT = Font(name="微软雅黑", size=11, bold=True, color="FFFFFF")BODY_FONT = Font(name="微软雅黑", size=10)BOLD_FONT = Font(name="微软雅黑", size=10, bold=True)ZEBRA_FILL = PatternFill("solid", fgColor="F2F6FC") # 斑马纹TOTAL_FILL = PatternFill("solid", fgColor="FFF2CC") # 合计行底色CENTER = Alignment(horizontal="center", vertical="center")THIN = Side(style="thin", color="D9D9D9")BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
自动列宽（支持中文）
Excel 的列宽是按"字符数"算的，一个汉字大约占 2 个字符宽度。不处理的话中文列会被挤成 ####：
def autofit(ws, min_w=10, max_w=32):for c in range(1, ws.max_column + 1):width = 0for r in range(1, ws.max_row + 1):v = ws.cell(row=r, column=c).valueif v is None:continues = str(v)# 中文按 2 个宽度计算w = sum(2 if ord(ch) > 127 else 1 for ch in s)width = max(width, w)ws.column_dimensions[get_column_letter(c)].width = min(max(width + 4, min_w), max_w)
通用美化函数
def paint(ws, header_row=1, zebra=True):max_row, max_col = ws.max_row, ws.max_column# 表头for c in range(1, max_col + 1):cell = ws.cell(row=header_row, column=c)cell.fill = HEADER_FILLcell.font = HEADER_FONTcell.alignment = CENTERcell.border = BORDERws.row_dimensions[header_row].height = 26# 正文for r in range(header_row + 1, max_row + 1):for c in range(1, max_col + 1):cell = ws.cell(row=r, column=c)cell.font = BODY_FONTcell.alignment = CENTERcell.border = BORDERif zebra and (r - header_row) % 2 == 0:cell.fill = ZEBRA_FILL
六、第四步：数字格式、条件格式与图表
数字格式不加的话，金额会显示成 12345.6789 这种一长串，非常难看：
from openpyxl.formatting.rule import DataBarRulefrom openpyxl.chart import BarChart, Referencewb = load_workbook(OUT)# ============ Sheet1：明细数据 ============ws1 = wb["明细数据"]paint(ws1)autofit(ws1)ws1.freeze_panes = "A2" # 冻结首行ws1.auto_filter.ref = ws1.dimensions # 全表加筛选按钮for r in range(2, ws1.max_row + 1):ws1.cell(row=r, column=1).number_format = "yyyy-mm-dd" # 日期ws1.cell(row=r, column=4).number_format = "#,##0" # 销量ws1.cell(row=r, column=5).number_format = "#,##0.00" # 单价ws1.cell(row=r, column=6).number_format = "#,##0.00" # 销售额# 销售额列加数据条ws1.conditional_formatting.add(f"F2:F{ws1.max_row}",DataBarRule(start_type="num", start_value=0,end_type="max", color="638EC6"))# ============ Sheet2：区域汇总 ============ws2 = wb["区域汇总"]paint(ws2)autofit(ws2)ws2.freeze_panes = "B2"last_row, last_col = ws2.max_row, ws2.max_columnfor r in range(2, last_row + 1):for c in range(2, last_col + 1):ws2.cell(row=r, column=c).number_format = "#,##0"# 高亮合计行 / 合计列for c in range(1, last_col + 1):ws2.cell(row=last_row, column=c).fill = TOTAL_FILLws2.cell(row=last_row, column=c).font = BOLD_FONTfor r in range(1, last_row + 1):ws2.cell(row=r, column=last_col).fill = TOTAL_FILLws2.cell(row=r, column=last_col).font = BOLD_FONT# 插入柱状图（排除"合计"行列）chart = BarChart()chart.type = "col"chart.style = 10chart.title = "各区域产品销售情况"chart.y_axis.title = "销售额（元）"chart.x_axis.title = "区域"data = Reference(ws2, min_col=2, max_col=last_col - 1,min_row=1, max_row=last_row - 1)cats = Reference(ws2, min_col=1, min_row=2, max_row=last_row - 1)chart.add_data(data, titles_from_data=True)chart.set_categories(cats)chart.width, chart.height = 18, 9ws2.add_chart(chart, f"A{last_row + 2}") # 锚点放在表格下方wb.save(OUT)print(f"✅ 报表生成完毕：{OUT}")
运行完，你会得到一个这样的文件：
明细数据：500 行流水，带筛选、冻结窗格、斑马纹，销售额列有蓝色数据条
区域汇总：5×4 透视表 + 合计行列自动高亮
图表：一张分区域分产品的柱状图，直接可用在汇报 PPT 里
七、让它"自动"跑起来
脚本写完只是第一步，真正的解放是不用手动点。
Windows：用「任务计划程序」创建基本任务，触发器设为每天 8:00，操作选 python.exe，参数填脚本路径。
Linux / macOS：一行 crontab 搞定。
# 每天早上 7 点生成报表0 7 * * * /usr/bin/python3 /home/user/report.py >> /home/user/report.log 2>&1
pip install pyinstallerpyinstaller -F report.py
再进阶一点，可以把 OUT 换成共享盘路径，或者用 smtplib 自动发邮件——这套组合拳下来，你就彻底从日报里消失了。
八、几个我踩过的坑
ExcelWriter忘记关闭：不用with也不调close()，文件会残缺。openpyxl 只认
.xlsx：老式.xls需要xlrd读取，且不能写入。中文列宽：必须按字符宽度加权计算，否则中文列全是
###。大文件性能：超过 10 万行时，pandas 用
xlsxwriter引擎开constant_memory，或者直接用openpyxl的write_only=True模式流式写入。数字格式要后设：先写数据再设
number_format，顺序反了不生效。
结语
整套代码不到 100 行，但能省掉每天半小时的机械劳动。更重要的是，它把"做报表"这件事从体力活变成了可维护的工程——数据源换了改一行，样式要调改常量，需求变了加个 pivot_table 就行。
完整的脚本我已经整理好了，把 build_data() 换成你自己的数据读取逻辑就能直接用。
