社区首页 › 编程脚本 › Python+Excel自动化报表：数据透视表、条件格式与图表集成实战

脚本专家 发表于 7 天前

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

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

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

pip install pandas openpyxl

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

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

示例代码：

import pandas as pd
df = pd.read_excel("销售数据.xlsx")
print(df.head())
#   日期      城市销售员品类   销售额
# 0 2026-01北京张三手机12000
# 1 2026-01上海李四手机15000
# 2 2026-01北京王五电脑18000
pivot = pd.pivot_table(
    df,
    values="销售额",
    index="城市",
    columns="品类",
    aggfunc="sum",
    fill_value=0,
    margins=True,
    margins_name="合计"
)
print(pivot)
# 品类    电脑    手机    合计
# 城市
# 北京450003200077000
# 上海380004100079000
# 合计8300073000156000

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

pivot = pd.pivot_table(
    df,
    values="销售额",
    index=["城市", "销售员"],
    columns="品类",
    aggfunc="sum",
    fill_value=0
)


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

写入与加载：

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


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

for sheet_name in wb.sheetnames:
    ws = wb
    header_font = Font(bold=True, color="FFFFFF", size=11)
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    header_align = Alignment(horizontal="center", vertical="center")
    for cell in ws:
      cell.font = header_font
      cell.fill = header_fill
      cell.alignment = header_align
    for row in ws.iter_rows(min_row=2, max_col=ws.max_column):
      for cell in row:
            cell.alignment = Alignment(horizontal="center", vertical="center")
    for col in ws.columns:
      max_length = 0
      col_letter = col.column_letter
      for cell in col:
            if cell.value:
                max_length = max(max_length, len(str(cell.value)))
      ws.column_dimensions.width = max_length + 4
wb.save("销售报表_美化.xlsx")


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

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


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

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

折线图适用于趋势展示：

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

饼图显示占比：

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


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

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


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

import pandas as pd
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.styles import Font, PatternFill, Alignment
from datetime import datetime
def generate_monthly_report(data_file, output_file):
    df = pd.read_excel(data_file)
    month = datetime.now().strftime("%Y年%m月")
    with pd.ExcelWriter(output_file, engine="openpyxl") as writer:
      df.to_excel(writer, sheet_name="数据源", index=False)
      city_pivot = pd.pivot_table(df, values="销售额", index="城市", aggfunc=["sum", "count"])
      city_pivot.to_excel(writer, sheet_name="城市分析")
      cat_pivot = pd.pivot_table(df, values="销售额", index="品类", aggfunc="sum")
      cat_pivot.to_excel(writer, sheet_name="品类分析")
    wb = load_workbook(output_file)
    ws = wb["城市分析"]
    header_font = Font(bold=True, color="FFFFFF", size=11)
    header_fill = PatternFill("solid", fgColor="4472C4")
    for cell in ws:
      cell.font = header_font
      cell.fill = header_fill
      cell.alignment = Alignment(horizontal="center")
    chart = BarChart()
    chart.title = f"{month}各城市销售额"
    data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=ws.max_row)
    cats = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    chart.width = 18
    chart.height = 12
    ws.add_chart(chart, "D2")
    wb.save(output_file)
    print(f"月报已生成: {output_file}")
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.height = 30
ws.column_dimensions["A"].width = 15

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

热心网友2 发表于 7 天前

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

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

热心网友2 发表于 7 天前

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

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

热心网友2 发表于 7 天前

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

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

查看完整版本: Python+Excel自动化报表：数据透视表、条件格式与图表集成实战