Python 自动化 Excel 批量生成报表详细教程
在企业日常运营中，生成各类报表是高频重复工作，比如每日销售报表、每月财务报表、各部门业绩报表、客户统计报表等。手动生成报表不仅耗时耗力，还容易出错。用Python可以实现报表全自动化生成，几分钟就能完成过去几小时的工作，而且格式统一、数据准确。本文将详细介绍各种报表生成场景的实现方法，包含完整代码示例和最佳实践。
一、环境准备
1.1 安装依赖库
bash
pip install openpyxl pandas numpy xlsxwriter jinja2
1.2 报表生成常见需求
二、基础报表生成
2.1 简单数据报表
从数据库或原始数据生成统计报表：
python
import pandas as pd
from datetime import datetime
# 1. 获取原始数据
df = pd.read_excel("销售数据.xlsx")
df["日期"] = pd.to_datetime(df["日期"])
# 2. 数据统计
# 月度销售统计
monthly_stats = df.groupby(df["日期"].dt.month, as_index=False).agg(
月份=("日期", lambda x: f"{x.iloc[0].month}月"),
销售额=("销售额", "sum"),
订单数=("订单号", "count"),
客单价=("销售额", "mean")
).round(2)
# 产品销售统计
product_stats = df.groupby("产品名称", as_index=False).agg(
销售额=("销售额", "sum"),
销量=("销量", "sum"),
平均单价=("单价", "mean")
).sort_values(by="销售额", ascending=False).round(2)
product_stats["排名"] = product_stats.index + 1
# 区域销售统计
region_stats = df.groupby("区域", as_index=False).agg(
销售额=("销售额", "sum"),
占比=("销售额", lambda x: round(x.sum()/df["销售额"].sum()*100, 2))
).sort_values(by="销售额", ascending=False)
# 3. 保存到Excel的不同工作表
report_date = datetime.now().strftime("%Y%m%d")
output_file = f"销售报表_{report_date}.xlsx"
with pd.ExcelWriter(output_file, engine="xlsxwriter") as writer:
monthly_stats.to_excel(writer, sheet_name="月度统计", index=False)
product_stats.to_excel(writer, sheet_name="产品排名", index=False)
region_stats.to_excel(writer, sheet_name="区域统计", index=False)
print(f"✅ 基础报表生成完成：{output_file}")
2.2 带有合并单元格的报表
生成复杂表头的报表：
python
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side
wb = Workbook()
ws = wb.active
ws.title = "销售报表"
# 大标题
ws.merge_cells("A1:E1")
ws["A1"] = "2026年第一季度销售报表"
ws["A1"].font = Font(size=16, bold=True)
ws["A1"].alignment = Alignment(horizontal="center", vertical="center")
# 二级表头
ws.merge_cells("A2:A3")
ws["A2"] = "区域"
ws.merge_cells("B2:D2")
ws["B2"] = "销售数据"
ws.merge_cells("E2:E3")
ws["E2"] = "同比增长"
# 三级表头
ws["B3"] = "销售额"
ws["C3"] = "订单数"
ws["D3"] = "客单价"
# 表头样式
header_font = Font(bold=True)
center_align = Alignment(horizontal="center", vertical="center")
thin_border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
# 应用样式到表头
for row in ws.iter_rows(min_row=1, max_row=3):
for cell in row:
cell.font = header_font
cell.alignment = center_align
cell.border = thin_border
# 填充数据
data = [
["华东区", 1200000, 1200, 1000, "12.5%"],
["华北区", 980000, 980, 1000, "8.3%"],
["华南区", 850000, 850, 1000, "15.2%"],
["西南区", 620000, 620, 1000, "20.1%"],
]
for i, row_data in enumerate(data, start=4):
for j, value in enumerate(row_data, start=1):
ws.cell(row=i, column=j, value=value)
ws.cell(row=i, column=j).border = thin_border
if j > 1: # 数值列居中
ws.cell(row=i, column=j).alignment = center_align
# 合计行
ws.merge_cells(f"A{len(data)+4}:B{len(data)+4}")
ws[f"A{len(data)+4}"] = "合计"
ws[f"C{len(data)+4}"] = sum([row[2] for row in data])
ws[f"D{len(data)+4}"] = 1000
ws[f"E{len(data)+4}"] = "13.8%"
# 设置列宽
column_widths = [15, 15, 15, 15, 15]
for i, width in enumerate(column_widths, start=1):
ws.column_dimensions[chr(64 + i)].width = width
wb.save("复杂表头报表.xlsx")
print("✅ 复杂表头报表生成完成")
三、报表格式美化
3.1 条件格式
给报表添加条件格式，自动高亮重要数据：
python
import pandas as pd
from openpyxl import load_workbook
from openpyxl.formatting.rule import ColorScaleRule, FormulaRule
from openpyxl.styles import PatternFill, Font
# 先写入数据
df = pd.DataFrame({
"区域": ["华东", "华北", "华南", "西南", "西北", "东北"],
"销售额": [1200000, 980000, 850000, 620000, 540000, 480000],
"目标完成率": [120, 98, 85, 76, 68, 55]
})
output_file = "带条件格式的报表.xlsx"
df.to_excel(output_file, index=False, engine="openpyxl")
# 加载文件添加格式
wb = load_workbook(output_file)
ws = wb.active
# 1. 销售额列添加颜色渐变：绿-黄-红
ws.conditional_formatting.add(
"B2:B7",
ColorScaleRule(
start_type='min', start_color='ef4444', # 低红
mid_type='percentile', mid_value=50, mid_color='f59e0b', # 中黄
end_type='max', end_color='10b981' # 高绿
)
)
# 2. 目标完成率小于80%标红
red_fill = PatternFill(start_color="fee2e2", end_color="fee2e2", fill_type="solid")
red_font = Font(color="dc2626")
ws.conditional_formatting.add(
"C2:C7",
FormulaRule(
formula=['C2<80'],
stopIfTrue=True,
fill=red_fill,
font=red_font
)
)
# 3. 完成率大于等于100%标绿
green_fill = PatternFill(start_color="dcfce7", end_color="dcfce7", fill_type="solid")
green_font = Font(color="166534")
ws.conditional_formatting.add(
"C2:C7",
FormulaRule(
formula=['C2>=100'],
stopIfTrue=True,
fill=green_fill,
font=green_font
)
)
wb.save(output_file)
print("✅ 条件格式添加完成")
3.2 插入图表
报表中插入图表，更直观展示数据：
python
import pandas as pd
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference, PieChart, LineChart
df = pd.DataFrame({
"月份": ["1月", "2月", "3月", "4月", "5月", "6月"],
"销售额": [120, 135, 148, 130, 160, 175],
"目标": [100, 120, 140, 140, 150, 160]
})
output_file = "带图表的报表.xlsx"
df.to_excel(output_file, index=False, engine="openpyxl")
wb = load_workbook(output_file)
ws = wb.active
# 1. 柱状图：销售额和目标对比
bar_chart = BarChart()
bar_chart.title = "月度销售额VS目标"
bar_chart.y_axis.title = "金额（万元）"
bar_chart.x_axis.title = "月份"
data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=7)
categories = Reference(ws, min_col=1, min_row=2, max_row=7)
bar_chart.add_data(data, titles_from_data=True)
bar_chart.set_categories(categories)
bar_chart.width = 15
bar_chart.height = 8
ws.add_chart(bar_chart, "F2")
# 2. 折线图：销售趋势
line_chart = LineChart()
line_chart.title = "销售趋势"
line_chart.y_axis.title = "销售额（万元）"
line_chart.x_axis.title = "月份"
data = Reference(ws, min_col=2, min_row=1, max_row=7)
line_chart.add_data(data, titles_from_data=True)
line_chart.set_categories(categories)
line_chart.width = 15
line_chart.height = 8
ws.add_chart(line_chart, "F20")
wb.save(output_file)
print("✅ 图表插入完成")
3.3 自定义样式模板
统一报表样式，保持风格一致：
python
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
# 定义样式模板
STYLES = {
"title": {
"font": Font(size=16, bold=True, color="FFFFFF"),
"fill": PatternFill(start_color="1e40af", end_color="1e40af", fill_type="solid"),
"alignment": Alignment(horizontal="center", vertical="center")
},
"header": {
"font": Font(bold=True, color="FFFFFF"),
"fill": PatternFill(start_color="3b82f6", end_color="3b82f6", fill_type="solid"),
"alignment": Alignment(horizontal="center", vertical="center"),
"border": Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
},
"data": {
"border": Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
),
"alignment": Alignment(horizontal="left", vertical="center")
},
"number": {
"border": Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
),
"alignment": Alignment(horizontal="right", vertical="center"),
"number_format": "#,##0"
},
"percent": {
"border": Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
),
"alignment": Alignment(horizontal="right", vertical="center"),
"number_format": "0.00%"
}
}
# 应用样式的函数
def apply_style(cell, style_name):
style = STYLES[style_name]
if "font" in style:
cell.font = style["font"]
if "fill" in style:
cell.fill = style["fill"]
if "alignment" in style:
cell.alignment = style["alignment"]
if "border" in style:
cell.border = style["border"]
if "number_format" in style:
cell.number_format = style["number_format"]
# 使用示例
# 应用标题样式
apply_style(ws["A1"], "title")
# 应用表头样式
for cell in ws[2]:
apply_style(cell, "header")
# 应用数据样式
for row in ws.iter_rows(min_row=3):
apply_style(row[0], "data")
for cell in row[1:3]:
apply_style(cell, "number")
apply_style(row[3], "percent")
四、批量生成报表
4.1 按部门批量生成独立报表
每个部门生成单独的Excel报表：
python
import os
import pandas as pd
from openpyxl import load_workbook
from datetime import datetime
# 创建输出文件夹
output_folder = "部门报表/"
os.makedirs(output_folder, exist_ok=True)
# 读取原始数据
df = pd.read_excel("全公司销售数据.xlsx")
df["日期"] = pd.to_datetime(df["日期"])
# 获取所有部门
departments = df["部门"].unique()
report_month = datetime.now().strftime("%Y年%m月")
for dept in departments:
print(f"正在生成{dept}报表...")
# 筛选本部门数据
dept_df = df[df["部门"] == dept]
# 统计数据
monthly_stats = dept_df.groupby(dept_df["日期"].dt.day, as_index=False).agg(
日期=("日期", lambda x: f"{x.iloc[0].day}日"),
销售额=("销售额", "sum"),
订单数=("订单号", "count")
)
# 计算汇总
total_sales = monthly_stats["销售额"].sum()
total_orders = monthly_stats["订单数"].sum()
avg_sales = dept_df["销售额"].mean()
# 生成报表
output_file = os.path.join(output_folder, f"{report_month}_{dept}销售报表.xlsx")
with pd.ExcelWriter(output_file, engine="openpyxl") as writer:
# 月度数据
monthly_stats.to_excel(writer, sheet_name="月度明细", index=False)
# 汇总数据
summary_df = pd.DataFrame({
"指标": ["总销售额", "总订单数", "日均销售额", "部门人数"],
"数值": [total_sales, total_orders, avg_sales, dept_df["员工ID"].nunique()]
})
summary_df.to_excel(writer, sheet_name="汇总", index=False)
# 添加格式
wb = load_workbook(output_file)
# 给明细工作表添加格式
ws = wb["月度明细"]
# 添加标题
ws.insert_rows(1)
ws.merge_cells("A1:C1")
ws["A1"] = f"{report_month} {dept}销售报表"
apply_style(ws["A1"], "title")
# 表头样式
for cell in ws[2]:
apply_style(cell, "header")
# 数据样式
for row in ws.iter_rows(min_row=3):
apply_style(row[0], "data")
apply_style(row[1], "number")
apply_style(row[2], "number")
# 调整列宽
ws.column_dimensions["A"].width = 15
ws.column_dimensions["B"].width = 15
ws.column_dimensions["C"].width = 15
wb.save(output_file)
print(f"✅ {dept}报表生成完成：{output_file}")
print(f"\n🎉 全部部门报表生成完成，共{len(departments)}份")
4.2 按客户生成对账单
每个客户生成月度对账单：
python
import pandas as pd
from openpyxl import Workbook
import os
output_folder = "客户对账单/"
os.makedirs(output_folder, exist_ok=True)
# 读取订单数据
df = pd.read_excel("订单数据.xlsx")
df["日期"] = pd.to_datetime(df["日期"])
current_month = df["日期"].dt.month.max()
# 筛选当月数据
month_df = df[df["日期"].dt.month == current_month]
# 所有客户
customers = month_df["客户名称"].unique()
for customer in customers:
print(f"正在生成{customer}对账单...")
customer_df = month_df[month_df["客户名称"] == customer]
# 创建工作簿
wb = Workbook()
ws = wb.active
ws.title = "对账单"
# 标题
ws.merge_cells("A1:F1")
ws["A1"] = f"{customer} 2026年{current_month}月对账单"
apply_style(ws["A1"], "title")
# 客户信息
ws["A3"] = "客户名称："
ws["B3"] = customer
ws["D3"] = "账单月份："
ws["E3"] = f"2026年{current_month}月"
# 表头
headers = ["日期", "订单号", "商品名称", "数量", "单价", "金额"]
for col, header in enumerate(headers, start=1):
ws.cell(row=5, column=col, value=header)
apply_style(ws.cell(row=5, column=col), "header")
# 明细数据
row_idx = 6
total_amount = 0
for _, row in customer_df.iterrows():
ws.cell(row=row_idx, column=1, value=row["日期"].strftime("%Y-%m-%d"))
ws.cell(row=row_idx, column=2, value=row["订单号"])
ws.cell(row=row_idx, column=3, value=row["商品名称"])
ws.cell(row=row_idx, column=4, value=row["数量"])
ws.cell(row=row_idx, column=5, value=row["单价"])
ws.cell(row=row_idx, column=6, value=row["金额"])
for col in range(1, 7):
apply_style(ws.cell(row=row_idx, column=col), "data" if col <4 else "number")
total_amount += row["金额"]
row_idx += 1
# 合计行
ws.merge_cells(f"A{row_idx}:E{row_idx}")
ws[f"A{row_idx}"] = "合计："
ws[f"F{row_idx}"] = total_amount
apply_style(ws[f"A{row_idx}"], "header")
apply_style(ws[f"F{row_idx}"], "header")
# 备注
ws.merge_cells(f"A{row_idx+2}:F{row_idx+2}")
ws[f"A{row_idx+2}"] = "备注：请于每月10日前完成付款，如有疑问请联系对接销售。"
# 设置列宽
ws.column_dimensions["A"].width = 15
ws.column_dimensions["B"].width = 20
ws.column_dimensions["C"].width = 30
ws.column_dimensions["D"].width = 10
ws.column_dimensions["E"].width = 12
ws.column_dimensions["F"].width = 15
output_file = os.path.join(output_folder, f"2026年{current_month}月_{customer}对账单.xlsx")
wb.save(output_file)
print(f"✅ {customer}对账单生成完成，总金额：{total_amount}元")
print(f"\n🎉 全部客户对账单生成完成，共{len(customers)}份")
五、高级报表功能
5.1 生成带公式的报表
自动计算合计、同比、环比等指标：
python
import pandas as pd
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# 数据
data = [
["月份", "销售额", "同比增长", "环比增长"],
["1月", 1000000, None, None],
["2月", 1200000, "=B3/B2-1", "=B3/B2-1"],
["3月", 1400000, "=B4/B2-1", "=B4/B3-1"],
["4月", 1300000, "=B5/B2-1", "=B5/B4-1"],
["5月", 1600000, "=B6/B2-1", "=B6/B5-1"],
["6月", 1750000, "=B7/B2-1", "=B7/B6-1"],
]
# 填充数据
for i, row in enumerate(data, start=1):
for j, value in enumerate(row, start=1):
ws.cell(row=i, column=j, value=value)
# 合计行
ws["A8"] = "合计"
ws["B8"] = "=SUM(B2:B7)" # 求和公式
# 设置百分比格式
for row in ws.iter_rows(min_row=2, max_row=7, min_col=3, max_col=4):
for cell in row:
cell.number_format = "0.00%"
ws.column_dimensions["A"].width = 10
ws.column_dimensions["B"].width = 15
ws.column_dimensions["C"].width = 15
ws.column_dimensions["D"].width = 15
wb.save("带公式的报表.xlsx")
print("✅ 带公式报表生成完成")
5.2 生成HTML网页报表
适合在网页展示或邮件发送：
python
from jinja2 import Template
import pandas as pd
# HTML模板
html_template = """
{{ report_title }}
{{ report_title }}
统计周期：{{ report_period }}
总销售额：{{ total_sales }}万元
同比增长：{{ growth_rate }}%
月度销售明细
月份
销售额（万元）
订单数
同比增长
完成率
{% for row in data %}
{{ row.月份 }}
{{ row.销售额 }}
{{ row.订单数 }}
{{ row.同比增长 }}%
{{ row.完成率 }}%
{% endfor %}
报表生成时间：{{ generate_time }}
"""
# 数据准备
df = pd.DataFrame({
"月份": ["1月", "2月", "3月", "4月", "5月", "6月"],
"销售额": [120, 135, 148, 130, 160, 175],
"订单数": [1200, 1350, 1480, 1300, 1600, 1750],
"同比增长": [12.5, 8.3, 15.2, -2.1, 20.1, 13.8],
"完成率": [120, 112.5, 105.7, 92.8, 106.7, 109.4]
})
# 渲染模板
template = Template(html_template)
html_content = template.render(
report_title="2026年上半年销售报表",
report_period="2026年1月-6月",
total_sales=df["销售额"].sum(),
growth_rate=13.8,
data=df.to_dict("records"),
generate_time=pd.Timestamp.now().strftime("%Y-%m-%d %H:%M:%S")
)
# 保存HTML文件
with open("销售报表.html", "w", encoding="utf-8") as f:
f.write(html_content)
print("✅ HTML报表生成完成")
5.3 自动发送邮件报表
生成报表后自动发送给相关人员：
python
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
import os
def send_email_with_attachment(to_email, subject, body, attachment_path):
# 邮箱配置
smtp_server = "smtp.qq.com"
smtp_port = 465
from_email = "your-email@qq.com"
password = "your-email-password"
# 创建邮件
msg = MIMEMultipart()
msg["From"] = from_email
msg["To"] = to_email
msg["Subject"] = subject
# 邮件正文
msg.attach(MIMEText(body, "html", "utf-8"))
# 添加附件
filename = os.path.basename(attachment_path)
with open(attachment_path, "rb") as f:
part = MIMEApplication(f.read(), Name=filename)
part["Content-Disposition"] = f'attachment; filename="{filename}"'
msg.attach(part)
# 发送邮件
server = smtplib.SMTP_SSL(smtp_server, smtp_port)
server.login(from_email, password)
server.sendmail(from_email, to_email, msg.as_string())
server.quit()
print(f"✅ 邮件已发送到：{to_email}")
# 使用示例
send_email_with_attachment(
to_email="manager@company.com",
subject="2026年6月销售报表",
body="您好，附件是2026年6月销售报表，请查收。
",
attachment_path="销售报表.xlsx"
)
六、定时自动生成报表
结合定时任务，实现报表全自动生成发送：
python
# generate_report.py 脚本
import pandas as pd
from datetime import datetime, timedelta
import os
def generate_daily_report():
"""生成每日销售报表"""
# 1. 获取数据（可以从数据库、API等读取）
end_date = datetime.now().date()
start_date = end_date - timedelta(days=1)
# 模拟从数据库读取数据
# df = pd.read_sql("SELECT * FROM sales WHERE date BETWEEN %s AND %s", conn, params=(start_date, end_date))
# 2. 统计
# 此处省略统计逻辑...
# 3. 生成报表
output_file = f"每日销售报表_{end_date.strftime('%Y%m%d')}.xlsx"
# df.to_excel(output_file, index=False)
# 4. 发送邮件
# send_email_with_attachment("manager@company.com", f"{end_date}销售报表", "请查收附件", output_file)
print(f"✅ {end_date}日报表生成发送完成")
if __name__ == "__main__":
generate_daily_report()
配置Linux定时任务，每天早上8点自动运行：
bash
# 编辑定时任务
crontab -e
# 添加以下内容（脚本路径替换成实际路径）
0 8 * * * /usr/bin/python3 /path/to/generate_report.py >> /var/log/report.log 2>&1
七、常见问题与解决方案
7.1 生成的报表打开提示格式错误
问题：Excel打开报表提示文件损坏或格式错误
解决方案：
保存时指定正确的engine：`pd.ExcelWriter(output_file, engine="openpyxl")`
操作文件后要关闭workbook：`wb.close()`
不要在文件打开的情况下重新生成，会导致文件损坏
避免文件名包含特殊字符，如斜杠、冒号等
7.2 报表生成速度慢
问题：数据量大时报表生成很慢
解决方案：
数据处理优先用pandas，尽量不用openpyxl逐行写
关闭不必要的格式设置，格式越多越慢
大文件分批写入，避免一次性加载所有数据
用xlsxwriter引擎比openpyxl更快
7.3 公式计算不生效
问题：打开报表公式不自动计算，显示#VALUE!
解决方案：
设置workbook的自动计算：`wb.calcPr.fullCalcOnLoad = True`
简单计算优先在Python中算好再写入，减少Excel公式
公式写法要正确，单元格引用要对应
7.4 中文乱码
问题：HTML报表或邮件中文显示乱码
解决方案：
所有文件保存时指定utf-8编码：`open(filename, "w", encoding="utf-8")`
HTML模板中指定``
邮件发送时指定charset="utf-8"
7.5 批量生成内存不足
问题：批量生成几百上千份报表时内存不足
解决方案：
每份报表生成后立即保存并关闭workbook，不要一直放在内存
增加内存限制，分批生成，比如每次生成100份休息一下
优化模板，减少不必要的格式和图片
八、最佳实践
1. 模板与数据分离：固定格式做成模板，数据动态填充，方便修改维护
2. 格式统一规范：所有报表使用统一的样式模板，保持风格一致
3. 多格式输出：关键报表同时生成Excel和PDF两种格式，满足不同需求
4. 异常处理：添加异常捕获和日志记录，失败时自动告警
5. 结果校验：生成报表后对关键指标做合理性校验，避免错误数据
6. 命名规范：报表文件名包含日期、类型、维度等信息，方便查找归档
7. 权限控制：敏感报表添加密码保护，控制访问权限
8. 版本管理：定期备份历史报表，便于后续对比分析
自动化生成报表是Python办公自动化最实用的场景之一，可以大幅减少重复劳动，提升工作效率。从简单的数据统计到复杂的多维度分析报表，Python都能轻松应对，结合定时任务可以实现完全无人值守的报表自动化体系。
