Python+Excel自动化报表：数据透视表、条件格式与图表集成实战

脚本专家

在日常数据处理工作中，经常需要从原始Excel数据生成带有透视分析、条件高亮和可视化图表的报表。如果手动操作Excel完成这些步骤，不仅耗时而且容易出错。本文基于Python的pandas和openpyxl库，提供一个完整的自动化流程：从数据读取、透视表生成、样式美化、条件格式设置，到柱状图、折线图、饼图嵌入，最后封装成批量处理和自动月报函数，实现真正的“一键生成”。

一、环境准备
首先安装必要的Python库：

2. pip install pandas openpyxl

复制代码

pandas负责数据聚合和透视表计算，openpyxl则处理Excel的样式、条件格式和图表功能。

二、数据透视表生成
假设有一张“销售数据.xlsx”，包含日期、城市、销售员、品类、销售额等字段。利用pandas的pivot_table可以快速生成汇总表。

示例代码：

2. import pandas as pd
3. df = pd.read_excel("销售数据.xlsx")
4. print(df.head())
5. #   日期      城市  销售员  品类   销售额
6. # 0 2026-01  北京  张三  手机  12000
7. # 1 2026-01  上海  李四  手机  15000
8. # 2 2026-01  北京  王五  电脑  18000
9. pivot = pd.pivot_table(
10.     df,
11.     values="销售额",
12.     index="城市",
13.     columns="品类",
14.     aggfunc="sum",
15.     fill_value=0,
16.     margins=True,
17.     margins_name="合计"
18. )
19. print(pivot)
20. # 品类    电脑    手机    合计
21. # 城市
22. # 北京  45000  32000  77000
23. # 上海  38000  41000  79000
24. # 合计  83000  73000  156000

复制代码

如果希望行索引多级展示（如城市→销售员），只需将index参数改为列表：

2. pivot = pd.pivot_table(
3.     df,
4.     values="销售额",
5.     index=["城市", "销售员"],
6.     columns="品类",
7.     aggfunc="sum",
8.     fill_value=0
9. )

复制代码

三、导出透视表到Excel并美化
使用pd.ExcelWriter可以将多个DataFrame写入同一工作簿的不同工作表。随后用openpyxl加载并设置样式。

写入与加载：

2. with pd.ExcelWriter("销售报表.xlsx", engine="openpyxl") as writer:
3.     df.to_excel(writer, sheet_name="原始数据", index=False)
4.     pivot.to_excel(writer, sheet_name="品类分析")
5. from openpyxl import load_workbook
6. from openpyxl.styles import Font, PatternFill, Alignment
7. wb = load_workbook("销售报表.xlsx")

复制代码

样式美化（表头加粗蓝底白字、数据行居中、自动列宽）：

2. for sheet_name in wb.sheetnames:
3.     ws = wb[sheet_name]
4.     header_font = Font(bold=True, color="FFFFFF", size=11)
5.     header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
6.     header_align = Alignment(horizontal="center", vertical="center")
7.     for cell in ws[1]:
8.         cell.font = header_font
9.         cell.fill = header_fill
10.         cell.alignment = header_align
11.     for row in ws.iter_rows(min_row=2, max_col=ws.max_column):
12.         for cell in row:
13.             cell.alignment = Alignment(horizontal="center", vertical="center")
14.     for col in ws.columns:
15.         max_length = 0
16.         col_letter = col[0].column_letter
17.         for cell in col:
18.             if cell.value:
19.                 max_length = max(max_length, len(str(cell.value)))
20.         ws.column_dimensions[col_letter].width = max_length + 4
21. wb.save("销售报表_美化.xlsx")

复制代码

四、条件格式应用
openpyxl提供三种常用条件格式：单元格规则、数据条和色阶。以下示例对“销售额”列（D列）应用：

2. from openpyxl.formatting.rule import CellIsRule, DataBarRule, ColorScaleRule
3. from openpyxl.styles import PatternFill
4. ws = wb.active
5. # 1. 销售额>15000标红
6. red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
7. ws.conditional_formatting.add(
8.     "D2:D100",
9.     CellIsRule(operator="greaterThan", formula=["15000"], fill=red_fill))
10. # 2. 数据条
11. ws.conditional_formatting.add(
12.     "D2:D100",
13.     DataBarRule(start_type="min", end_type="max",
14.                 color="5B9BD5", showValue=True))
15. # 3. 色阶（绿→黄→红）
16. ws.conditional_formatting.add(
17.     "D2:D100",
18.     ColorScaleRule(
19.         start_type="min", start_color="63BE7B",
20.         mid_type="percentile", mid_value=50, mid_color="FFEB84",
21.         end_type="max", end_color="F8696B"))
22. wb.save("销售报表_条件格式.xlsx")

复制代码

五、图表嵌入
openpyxl支持柱状图、折线图、饼图等多种图表。以柱状图为例：

2. from openpyxl.chart import BarChart, Reference
3. ws = wb.active
4. chart = BarChart()
5. chart.type = "col"
6. chart.title = "各城市销售额"
7. chart.y_axis.title = "销售额"
8. chart.x_axis.title = "城市"
9. data = Reference(ws, min_col=4, min_row=1, max_col=4, max_row=ws.max_row)
10. cats = Reference(ws, min_col=2, min_row=2, max_row=ws.max_row)  # 城市列
11. chart.add_data(data, titles_from_data=True)
12. chart.set_categories(cats)
13. ws.add_chart(chart, "F2")

复制代码

折线图适用于趋势展示：

2. from openpyxl.chart import LineChart
3. line_chart = LineChart()
4. line_chart.title = "月度销售趋势"
5. data = Reference(ws, min_col=4, min_row=1, max_row=ws.max_row)
6. cats = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
7. line_chart.add_data(data, titles_from_data=True)
8. line_chart.set_categories(cats)
9. ws.add_chart(line_chart, "F20")

复制代码

饼图显示占比：

2. from openpyxl.chart import PieChart
3. pie_chart = PieChart()
4. pie_chart.title = "品类占比"
5. data = Reference(ws, min_col=4, min_row=1, max_row=ws.max_row)
6. cats = Reference(ws, min_col=3, min_row=2, max_row=ws.max_row)
7. pie_chart.add_data(data, titles_from_data=True)
8. pie_chart.set_categories(cats)
9. ws.add_chart(pie_chart, "F38")

复制代码

六、批量合并多个Excel
如果有多个月度报表需要合并分析，可用以下函数自动扫描文件夹并汇总：

2. import os
3. import pandas as pd
4. def merge_excel_files(input_dir, output_file):
5.     all_data = []
6.     for f in os.listdir(input_dir):
7.         if f.endswith((".xlsx", ".xls")):
8.             df = pd.read_excel(os.path.join(input_dir, f))
9.             df["来源文件"] = f
10.             all_data.append(df)
11.             print(f"已读取: {f} ({len(df)}行)")
12.     result = pd.concat(all_data, ignore_index=True)
13.     with pd.ExcelWriter(output_file, engine="openpyxl") as writer:
14.         result.to_excel(writer, sheet_name="汇总数据", index=False)
15.         pivot = pd.pivot_table(result,
16.                                values="销售额", index="城市", aggfunc=["sum", "mean", "count"])
17.         pivot.to_excel(writer, sheet_name="统计报表")
18.     print(f"合并完成！共 {len(result)} 行 → {output_file}")
19. merge_excel_files("月度报表", "年度汇总.xlsx")

复制代码

七、完整案例：自动生成月报
下面封装一个函数，读数据→生成透视表→写入多工作表→美化表头→添加柱状图，最终输出一个带分析的Excel报告。

2. import pandas as pd
3. from openpyxl import load_workbook
4. from openpyxl.chart import BarChart, Reference
5. from openpyxl.styles import Font, PatternFill, Alignment
6. from datetime import datetime
7. def generate_monthly_report(data_file, output_file):
8.     df = pd.read_excel(data_file)
9.     month = datetime.now().strftime("%Y年%m月")
10.     with pd.ExcelWriter(output_file, engine="openpyxl") as writer:
11.         df.to_excel(writer, sheet_name="数据源", index=False)
12.         city_pivot = pd.pivot_table(df, values="销售额", index="城市", aggfunc=["sum", "count"])
13.         city_pivot.to_excel(writer, sheet_name="城市分析")
14.         cat_pivot = pd.pivot_table(df, values="销售额", index="品类", aggfunc="sum")
15.         cat_pivot.to_excel(writer, sheet_name="品类分析")
16.     wb = load_workbook(output_file)
17.     ws = wb["城市分析"]
18.     header_font = Font(bold=True, color="FFFFFF", size=11)
19.     header_fill = PatternFill("solid", fgColor="4472C4")
20.     for cell in ws[1]:
21.         cell.font = header_font
22.         cell.fill = header_fill
23.         cell.alignment = Alignment(horizontal="center")
24.     chart = BarChart()
25.     chart.title = f"{month}各城市销售额"
26.     data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=ws.max_row)
27.     cats = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
28.     chart.add_data(data, titles_from_data=True)
29.     chart.set_categories(cats)
30.     chart.width = 18
31.     chart.height = 12
32.     ws.add_chart(chart, "D2")
33.     wb.save(output_file)
34.     print(f"月报已生成: {output_file}")
35. generate_monthly_report("6月销售数据.xlsx", "6月销售报表.xlsx")

复制代码

八、常用样式速查
Font(name="微软雅黑", size=11, bold=True, italic=False, color="FF0000")
PatternFill(start_color="FFC000", end_color="FFC000", fill_type="solid")
Alignment(horizontal="center", vertical="center", wrap_text=True)
Border(left=Side(style="thin"), right=Side(style="thin"), top=Side(style="thin"), bottom=Side(style="thin"))
ws.row_dimensions[1].height = 30
ws.column_dimensions["A"].width = 15

通过上述代码，你可以将重复性的Excel报表工作交给Python自动完成，无论是单次处理还是批量整合，都能显著提升效率。

热心网友2

非常实用的分享！之前我手动做透视表和条件格式，步骤繁琐还容易漏改。您这个思路整合了数据聚合、样式美化和图表嵌入，代码清晰，尤其是把索引设为多级和列宽自适应的部分很贴心，正好解决了我的痛点。想问一下，如果要处理多个数据源或者跨工作簿合并，是不是在pandas读入时多加几个read_excel再concat就行？期待后续的批量处理和自动月报函数封装！

热心网友2

感谢楼主分享这么详细的实战教程！之前手动做Excel报表确实费时费力，尤其是透视表和条件格式，经常要来回点菜单。您用pandas+openpyxl这套方案把流程自动化了，思路很清晰，代码也写得很完整，直接就能拿来用。特别是表头美化那部分，蓝底白字加自动列宽，做出来的报表专业多了。条件格式也刚好是我需要的，以前只会用VBA，现在发现Python处理起来更灵活。期待您后续把图表集成的部分也补上，那样一键生成完整报表就彻底实现了。再次感谢！

热心网友2

这个流程写得很清晰，干货满满！我一直手动做这类报表，确实费时又容易漏改。想问下楼主，第四部分条件格式之后的“图表集成”部分好像没贴完？柱状图、折线图用 openpyxl 的 chart 怎么嵌入到指定位置？还有批量处理时如果原始数据有合并单元格或者空行，pandas 读到 DataFrame 里会有什么坑吗？