openpyxl进阶：图表、公式与数据透视表

Python 办公自动化专题 · 深入掌握Excel高级功能的Python自动化实现

专题：Python 自动化办公系统学习

关键词：Python, 自动化办公, openpyxl, Excel图表, 数据透视表, 公式, 报表自动化

一、图表基础

openpyxl提供了强大的图表引擎，支持在Excel工作表中创建和嵌入多种类型的图表。其图表系统基于Excel的底层绘图对象模型，允许开发者通过Python代码完全控制图表的每一个视觉元素。了解和掌握openpyxl的图表架构是进行高级报表自动化的第一步。

openpyxl的图表模块位于 openpyxl.chart 包中，核心类包括 Chart（所有图表的基类）、Reference（数据引用对象）和 Series（数据系列）。创建图表的基本流程为：准备数据源 → 创建图表对象 → 添加数据系列 → 配置图表属性 → 将图表插入工作表。数据通过 Reference 对象引用工作表中的单元格区域，支持多行多列的数据范围选择。

图表创建流程中，Reference 是最关键的概念之一。它并不直接复制数据，而是建立对单元格区域的引用关系，这使得图表可以与源数据保持动态关联——当源数据发生变化时，图表会自动更新。这种设计模式遵循了Excel本身的数据驱动特性，也是实现报表自动化的基础。

图表引擎核心组件

from openpyxl import Workbook from openpyxl.chart import BarChart, Reference wb = Workbook() ws = wb.active # 准备数据 data = [ ["月份", "销售额"], ["1月", 12000], ["2月", 15000], ["3月", 13500], ["4月", 18000], ["5月", 16500], ["6月", 20000], ] for row in data: ws.append(row) # 创建数据引用 data_ref = Reference(ws, min_col=2, min_row=1, max_row=7) categories_ref = Reference(ws, min_col=1, min_row=2, max_row=7) # 创建图表并添加数据系列 chart = BarChart() chart.add_data(data_ref, titles_from_data=True) chart.set_categories(categories_ref) chart.title = "月度销售额统计" chart.x_axis.title = "月份" chart.y_axis.title = "销售额（元）" # 插入图表 ws.add_chart(chart, "E2") wb.save("chart_basic.xlsx")

要点提示：使用 titles_from_data=True 参数可以自动将数据区域的第一行作为系列名称，省去手动设置 Series 对象的步骤。同时务必通过 set_categories() 方法设置X轴类别标签，否则图表将使用默认的序号作为标签。

from openpyxl.chart import BarChart, Reference, Series from openpyxl.chart.label import DataLabelList # 手动创建Series对象的进阶方式 chart2 = BarChart() chart2.type = "col" chart2.style = 10 # 通过Series对象精细控制 s1 = Series(Ref=Reference(ws, min_col=2, min_row=2, max_row=7), title="销售额") s1.graphicalProperties.solidFill = "2E7D32" # 自定义颜色 chart2.append(s1) chart2.set_categories(Reference(ws, min_col=1, min_row=2, max_row=7)) chart2.title = "手动创建Series示例" chart2.y_axis.scaling.min = 0 chart2.y_axis.scaling.max = 25000 ws.add_chart(chart2, "E18")

理解 add_data 与 append 两种添加数据方式的区别至关重要：add_data 方法一次性引用整个数据区域，适合简单场景；而 append 方法逐个添加 Series 对象，适合需要对每个系列单独配置样式、颜色和标签的复杂场景。实际开发中，当需要在一个图表中展示多个对比数据系列时，推荐使用 append 方式以获得更大的灵活性。

二、常用图表类型

openpyxl支持Excel中的绝大多数图表类型，每种图表都有其适用场景和配置要点。掌握不同图表类型的特点和创建方式，能够帮助我们在报表自动化中根据数据特征选择最合适的可视化方案。

2.1 柱状图与条形图

柱状图（BarChart）是最常用的图表类型之一，适用于分类数据的对比展示。通过设置 type 属性为 "col"（纵向柱状图）或 "bar"（横向条形图），可以灵活切换图表的展示方向。柱状图支持分组（grouped）和堆叠（stacked）两种模式，分别适用于并列对比和构成比例分析。

from openpyxl.chart import BarChart, Reference # 分组柱状图 - 多产品对比 ws1 = wb.create_sheet("柱状图") data = [ ["产品", "Q1", "Q2", "Q3", "Q4"], ["产品A", 230, 340, 280, 390], ["产品B", 180, 260, 310, 350], ["产品C", 150, 200, 240, 280], ] for row in data: ws1.append(row) chart_bar = BarChart() chart_bar.type = "col" chart_bar.grouping = "clustered" # 分组模式（默认） chart_bar.title = "各产品季度销售额对比" chart_bar.y_axis.title = "销售额（万元）" chart_bar.x_axis.title = "季度" data_ref = Reference(ws1, min_col=2, min_row=1, max_col=5, max_row=4) cats_ref = Reference(ws1, min_col=1, min_row=2, max_row=4) chart_bar.add_data(data_ref, from_rows=True, titles_from_data=True) chart_bar.set_categories(cats_ref) ws1.add_chart(chart_bar, "A8")
# 堆叠柱状图 - 展示构成比例 chart_stacked = BarChart() chart_stacked.type = "col" chart_stacked.grouping = "stacked" # 堆叠模式 chart_stacked.title = "各产品季度销售构成" chart_stacked.y_axis.title = "销售额（万元）" chart_stacked.add_data(data_ref, from_rows=True, titles_from_data=True) chart_stacked.set_categories(cats_ref) # 为每个系列设置不同颜色 from openpyxl.chart.series import DataPoint from openpyxl.drawing.fill import PatternFillProperties, ColorChoice colors = ["2E7D32", "1976D2", "F57C00"] for idx, s in enumerate(chart_stacked.series): s.graphicalProperties.solidFill = colors[idx] ws1.add_chart(chart_stacked, "A25")

2.2 折线图

折线图（LineChart）适合展示数据随时间变化的趋势，在时间序列分析和趋势预测中应用广泛。openpyxl的折线图支持平滑曲线、标记点样式、数据标签等丰富配置。可以创建单系列趋势线，也可以创建多系列对比折线图。折线图还可以与柱状图组合，形成更丰富的可视化效果。

from openpyxl.chart import LineChart from openpyxl.chart.series import SeriesLabel ws2 = wb.create_sheet("折线图") data = [ ["日期", "访问量", "转化率"], ["第1周", 5000, 0.032], ["第2周", 6200, 0.038], ["第3周", 7800, 0.041], ["第4周", 6500, 0.035], ["第5周", 9100, 0.045], ["第6周", 10500, 0.052], ] for row in data: ws2.append(row) line_chart = LineChart() line_chart.title = "每周访问量与转化率趋势" line_chart.y_axis.title = "访问量" line_chart.style = 10 # 访问量系列（主纵轴） data_visits = Reference(ws2, min_col=2, min_row=1, max_row=7) cats = Reference(ws2, min_col=1, min_row=2, max_row=7) line_chart.add_data(data_visits, titles_from_data=True) line_chart.set_categories(cats) # 在次坐标轴上添加转化率系列 from openpyxl.chart.axis import NumericAxis line_chart.y_axis_2 = NumericAxis() line_chart.y_axis_2.title = "转化率" data_conv = Reference(ws2, min_col=3, min_row=1, max_row=7) line_chart.add_data(data_conv, titles_from_data=True) line_chart.series[1].graphicalProperties.line.solidFill = "FF5722" line_chart.series[1].graphicalProperties.line.width = 25000 # EMUs ws2.add_chart(line_chart, "A10")
# 平滑折线图与数据标记 smooth_chart = LineChart() smooth_chart.title = "平滑趋势折线图" data_ref2 = Reference(ws2, min_col=2, min_row=1, max_row=7) smooth_chart.add_data(data_ref2, titles_from_data=True) smooth_chart.set_categories(Reference(ws2, min_col=1, min_row=2, max_row=7)) series = smooth_chart.series[0] series.smooth = True # 启用曲线平滑 series.marker.symbol = "circle" # 标记点样式 series.marker.size = 7 series.graphicalProperties.line.solidFill = "1976D2" series.graphicalProperties.line.width = 28000 ws2.add_chart(smooth_chart, "A28")

2.3 饼图

饼图（PieChart）用于展示各部分占整体的比例关系，适合构成分析场景。openpyxl的饼图支持分离饼图（爆炸效果）、环形图（donut）、以及多种数据标签格式（百分比、数值、类别名称等）。需要注意的是，饼图通常只适合展示一个数据系列，且分类不宜过多，否则会影响可读性。

from openpyxl.chart import PieChart from openpyxl.chart.label import DataLabelList ws3 = wb.create_sheet("饼图") data = [ ["渠道", "用户数"], ["搜索引擎", 45000], ["社交媒体", 32000], ["直接访问", 23000], ["邮件营销", 12000], ["广告投放", 18000], ] for row in data: ws3.append(row) pie = PieChart() pie.title = "用户来源渠道分布" pie.style = 10 data_ref3 = Reference(ws3, min_col=2, min_row=1, max_row=6) cats_ref3 = Reference(ws3, min_col=1, min_row=2, max_row=6) pie.add_data(data_ref3, titles_from_data=True) pie.set_categories(cats_ref3) # 数据标签配置：显示百分比和类别名 pie.dataLabels = DataLabelList() pie.dataLabels.showPercent = True pie.dataLabels.showCatName = True pie.dataLabels.showVal = False pie.dataLabels.separator = "\n" # 分离饼图（爆炸效果） # 设置第一个扇区分离 from openpyxl.chart.series import DataPoint pt = DataPoint(idx=0) pt.graphicalProperties.solidFill = "2E7D32" pt.explosion = 20 # 爆炸距离（单位：百分比） pie.series[0].data_points = [pt] ws3.add_chart(pie, "A8")
# 环形图（甜甜圈图） from openpyxl.chart import DoughnutChart donut = DoughnutChart() donut.title = "用户来源分布（环形图）" donut.add_data(data_ref3, titles_from_data=True) donut.set_categories(cats_ref3) donut.dataLabels = DataLabelList() donut.dataLabels.showPercent = True donut.dataLabels.showCatName = True # 逐个设置扇区颜色 colors_pie = ["2E7D32", "1976D2", "F57C00", "9C27B0", "607D8B"] for i, s in enumerate(donut.series): for j in range(len(data) - 1): pt = DataPoint(idx=j) pt.graphicalProperties.solidFill = colors_pie[j] s.data_points.append(pt) ws3.add_chart(donut, "A26")

2.4 散点图

散点图（ScatterChart）用于展示两个变量之间的相关关系，在数据分析和科学计算中应用广泛。与折线图不同，散点图的X轴是数值轴而非分类轴，因此可以展示数据的分布特征。openpyxl支持带平滑线的散点图、带折线的散点图以及纯散点图三种变体。

from openpyxl.chart import ScatterChart ws4 = wb.create_sheet("散点图") data = [ ["广告投入", "销售额", "利润"], [1.2, 12.5, 3.8], [2.5, 18.3, 5.2], [3.1, 22.0, 6.1], [4.8, 30.5, 8.5], [5.2, 35.2, 10.3], [6.7, 40.1, 11.8], [8.0, 48.5, 14.2], ] for row in data: ws4.append(row) scatter = ScatterChart() scatter.title = "广告投入与销售额/利润关系" scatter.x_axis.title = "广告投入（万元）" scatter.y_axis.title = "金额（万元）" scatter.style = 10 # 销售额系列 x_ref = Reference(ws4, min_col=1, min_row=2, max_row=8) y_ref1 = Reference(ws4, min_col=2, min_row=2, max_row=8) s1 = Series(y_ref1, x_ref, title="销售额") s1.graphicalProperties.line.solidFill = "2E7D32" s1.graphicalProperties.line.width = 25000 s1.marker.symbol = "circle" s1.marker.size = 7 scatter.append(s1) # 利润系列 y_ref2 = Reference(ws4, min_col=3, min_row=2, max_row=8) s2 = Series(y_ref2, x_ref, title="利润") s2.graphicalProperties.line.solidFill = "F57C00" s2.graphicalProperties.line.width = 25000 s2.marker.symbol = "diamond" s2.marker.size = 7 scatter.append(s2) ws4.add_chart(scatter, "A12")
# 气泡图（扩展应用） # 气泡图可以通过数据点大小展示第三个维度 from openpyxl.chart import BubbleChart ws4_bubble = wb.create_sheet("气泡图") data_bubble = [ ["产品", "市场份额", "增长率", "投入预算"], ["产品A", 25, 15, 80], ["产品B", 20, 22, 65], ["产品C", 15, 30, 50], ["产品D", 12, 18, 40], ["产品E", 8, 35, 30], ] for row in data_bubble: ws4_bubble.append(row) bubble = BubbleChart() bubble.title = "产品组合分析气泡图" x_ref = Reference(ws4_bubble, min_col=2, min_row=2, max_row=6) y_ref = Reference(ws4_bubble, min_col=3, min_row=2, max_row=6) size_ref = Reference(ws4_bubble, min_col=4, min_row=2, max_row=6) s = Series(values=y_ref, xvalues=x_ref, zvalues=size_ref, title="产品") bubble.append(s) ws4_bubble.add_chart(bubble, "A8")

2.5 面积图与雷达图

面积图（AreaChart）是折线图的变体，通过在折线下方填充颜色来强调变化的幅度，特别适合展示总量变化趋势和多个部分的累积效果。雷达图（RadarChart）则在多维度对比分析中表现出色，能够直观地展示多个对象在不同维度上的均衡性，常用于能力评估和性能对比场景。

from openpyxl.chart import AreaChart, RadarChart # 面积图 - 展示累积趋势 ws5 = wb.create_sheet("面积图") data_area = [ ["季度", "产品A", "产品B", "产品C"], ["Q1", 120, 90, 60], ["Q2", 150, 110, 80], ["Q3", 180, 130, 95], ["Q4", 210, 160, 110], ] for row in data_area: ws5.append(row) area = AreaChart() area.title = "各产品累积销售趋势" area.grouping = "stacked" # 堆叠面积图 area.y_axis.title = "销售额（万元）" data_ref_area = Reference(ws5, min_col=2, min_row=1, max_col=4, max_row=5) cats_ref_area = Reference(ws5, min_col=1, min_row=2, max_row=5) area.add_data(data_ref_area, titles_from_data=True) area.set_categories(cats_ref_area) colors_area = ["2E7D32", "1976D2", "F57C00"] for idx, s in enumerate(area.series): s.graphicalProperties.solidFill = colors_area[idx] s.graphicalProperties.line.solidFill = colors_area[idx] ws5.add_chart(area, "A8")
# 雷达图 - 多维度能力对比 ws6 = wb.create_sheet("雷达图") data_radar = [ ["维度", "产品A", "产品B"], ["性能", 95, 80], ["稳定性", 88, 92], ["易用性", 75, 85], ["扩展性", 70, 90], ["安全性", 85, 78], ["成本", 82, 70], ] for row in data_radar: ws6.append(row) radar = RadarChart() radar.type = "filled" # 填充型雷达图 radar.title = "产品能力雷达对比图" data_ref_radar = Reference(ws6, min_col=2, min_row=1, max_col=3, max_row=7) cats_ref_radar = Reference(ws6, min_col=1, min_row=2, max_row=7) radar.add_data(data_ref_radar, titles_from_data=True) radar.set_categories(cats_ref_radar) radar.series[0].graphicalProperties.solidFill = "2E7D32" radar.series[1].graphicalProperties.solidFill = "1976D2" ws6.add_chart(radar, "A10")

三、图表美化

创建图表只是第一步，真正专业的报表需要精心美化。openpyxl提供了丰富的图表美化接口，可以控制从标题字体到数据点颜色的每一个细节。合理的美化不仅能提升视觉体验，更能增强数据的表达力和说服力。

图表美化可以从以下几个方面入手：标题字体与样式、图例位置与格式、坐标轴刻度与标签、数据标签格式、网格线样式、图表区域背景色、以及数据系列的颜色主题。openpyxl通过 graphicalProperties 属性暴露了底层的图形属性接口，支持 solidFill（纯色填充）、gradientFill（渐变填充）、line（线条属性）等丰富的视觉效果控制。

标题与图例配置

from openpyxl.chart.text import RichText from openpyxl.drawing.text import Paragraph, ParagraphProperties, CharacterProperties, Font as DrawingFont from openpyxl.chart.layout import Layout, ManualLayout chart_styled = BarChart() chart_styled.title = "月度销售数据" # 使用RichText设置标题字体 title_para = Paragraph() title_para.pPr = ParagraphProperties(defrPr=CharacterProperties( sz=1800, # 字体大小（单位：百分之一磅，1800=18pt） b=True, # 加粗 latin=DrawingFont(typeface="Microsoft YaHei"), solidFill="2E7D32" # 字体颜色 )) title_para.text = "月度销售数据" chart_styled.title = title_para # 图例配置 chart_styled.legend.position = "b" # 底部: l左 r右 t上 b下 chart_styled.legend.overlay = False # 不覆盖图表

颜色主题与样式系统

from openpyxl.chart.series import DataPoint # 自定义调色板 - 设置每个数据点的颜色 colors_theme = ["1A237E", "283593", "3949AB", "5C6BC0", "7986CB", "9FA8DA"] chart_color = BarChart() chart_color.title = "自定义颜色主题" # 添加数据后逐点设置颜色 for i, color in enumerate(colors_theme[:len(chart_color.series)]): pt = DataPoint(idx=i) pt.graphicalProperties.solidFill = color chart_color.series[0].data_points.append(pt) # 图表背景墙设置 chart_color.plot_area.graphicalProperties.solidFill = "FAFAFA" chart_color.plot_area.graphicalProperties.line.solidFill = "E0E0E0"

数据标签与网格线

from openpyxl.chart.label import DataLabelList # 数据标签高级配置 chart_labels = BarChart() chart_labels.add_data(data_ref, titles_from_data=True) chart_labels.dataLabels = DataLabelList() chart_labels.dataLabels.showVal = True # 显示数值 chart_labels.dataLabels.showCatName = True # 显示类别名 chart_labels.dataLabels.showPercent = False chart_labels.dataLabels.separator = ", " # 分隔符 chart_labels.dataLabels.numFmt = '#,##0' # 数字格式 # 网格线定制 from openpyxl.chart.shapes import GraphicalProperties chart_labels.y_axis.majorGridlines = openpyxl.chart.axis.ChartLines() chart_labels.y_axis.majorGridlines.spPr = GraphicalProperties() chart_labels.y_axis.majorGridlines.spPr.line.solidFill = "E0E0E0" chart_labels.y_axis.majorGridlines.spPr.line.width = 10000 # 删除次要网格线 chart_labels.y_axis.minorGridlines = None

经验之谈：图表美化的核心原则是"数据墨水比最大化"——去除一切不必要的装饰元素，让数据本身成为视觉焦点。建议使用企业品牌色作为图表主色，保持整个报表的色彩一致性。坐标轴标签字体不小于10pt，标题不小于14pt，数据标签不小于9pt。

四、组合图表

在实际报表开发中，单一的图表类型往往难以完整地表达复杂的数据关系。组合图表的出现很好地解决了这一问题——它允许在同一张图表中混合使用多种图表类型，并通过主次坐标轴来展示量级差异较大的不同数据系列。openpyxl通过在同一Chart对象中混合不同类型的Series，可以轻松实现组合图表效果。

组合图表的典型应用场景包括：柱状图与折线图的组合（用柱状图展示绝对值、折线图展示增长率）、双Y轴图表（主轴显示销售额、次轴显示同比增长率）、以及多指标仪表板中的复合可视化。创建组合图表的核心技巧在于，先创建一个基础Chart对象，然后通过 append 方法添加不同类型的Series，并利用 graphicalProperties 配置每个系列的视觉样式。

柱线组合图（主次坐标轴）

from openpyxl.chart import BarChart, LineChart from openpyxl.chart.axis import NumericAxis ws7 = wb.create_sheet("组合图表") data = [ ["月份", "销售额", "同比增长率"], ["1月", 120, 0.12], ["2月", 150, 0.18], ["3月", 135, 0.08], ["4月", 180, 0.22], ["5月", 165, 0.15], ["6月", 210, 0.28], ] for row in data: ws7.append(row) # 方法1: 在同一图表中混合类型 combo = BarChart() combo.type = "col" combo.title = "月度销售额与增长率组合分析" combo.y_axis.title = "销售额（万元）" # 添加柱状图系列（销售额） sale_ref = Reference(ws7, min_col=2, min_row=1, max_row=7) cats = Reference(ws7, min_col=1, min_row=2, max_row=7) combo.add_data(sale_ref, titles_from_data=True) combo.set_categories(cats) # 创建折线图系列（增长率）并添加到同一图表 line_part = LineChart() growth_ref = Reference(ws7, min_col=3, min_row=1, max_row=7) line_part.add_data(growth_ref, titles_from_data=True) # 将折线系列添加到柱状图对象中 combo.series.append(line_part.series[0]) # 配置次坐标轴 combo.y_axis_2 = NumericAxis() combo.y_axis_2.title = "增长率" combo.y_axis_2.numFmt = "0%" combo.y_axis_2.crosses = "max" # 美化组合图表 combo.series[0].graphicalProperties.solidFill = "1976D2" combo.series[1].graphicalProperties.line.solidFill = "FF5722" combo.series[1].graphicalProperties.line.width = 28000 ws7.add_chart(combo, "A10")

多系列组合与双层饼图

# 堆积柱状图 + 折线组合 ws7_2 = wb.create_sheet("多系列组合") data2 = [ ["季度", "收入", "成本", "利润率"], ["Q1", 500, 350, 0.30], ["Q2", 680, 450, 0.34], ["Q3", 720, 480, 0.33], ["Q4", 900, 580, 0.36], ] for row in data2: ws7_2.append(row) combo2 = BarChart() combo2.type = "col" combo2.grouping = "stacked" combo2.title = "收入成本构成与利润率趋势" income_ref = Reference(ws7_2, min_col=2, min_row=1, max_col=3, max_row=5) cats2 = Reference(ws7_2, min_col=1, min_row=2, max_row=5) combo2.add_data(income_ref, titles_from_data=True) combo2.set_categories(cats2) # 利润率折线（次坐标轴） line2 = LineChart() profit_ref = Reference(ws7_2, min_col=4, min_row=1, max_row=5) line2.add_data(profit_ref, titles_from_data=True) combo2.series.append(line2.series[0]) combo2.y_axis_2 = NumericAxis() combo2.y_axis_2.title = "利润率" combo2.y_axis_2.numFmt = "0%" combo2.series[0].graphicalProperties.solidFill = "2E7D32" combo2.series[1].graphicalProperties.solidFill = "F57C00" combo2.series[2].graphicalProperties.line.solidFill = "1976D2" ws7_2.add_chart(combo2, "A8")

关键技巧：在组合图表中，主次坐标轴的数值范围设定非常重要。建议将次坐标轴的比例调整为与主坐标轴的数据量级相匹配，避免因量级差异过大导致某一系列的数据被"压扁"。可以通过 axis.scaling.min 和 axis.scaling.max 属性手动调整坐标轴范围。

五、复杂公式

openpyxl不仅能操作数据，还能通过编程方式向单元格写入Excel公式，让Excel引擎完成计算而非在Python中硬编码所有逻辑。这种方式充分利用了Excel自身的计算能力，生成的报表具有动态计算特性——当源数据变化时，公式会自动重新计算。掌握公式写入技巧是实现真正"智能报表"的关键能力。

openpyxl写入公式的核心在于 ws.cell().value 属性设置为公式字符串（以等号开头），或者使用 ws[单元格地址] = '=FORMULA(...)' 的赋值方式。公式字符串完全遵循Excel的公式语法，支持绝对引用、混合引用和跨工作表引用。需要注意的是，openpyxl在写入公式后不会自动计算公式结果，而是将公式本身写入Excel文件，由Excel在打开文件时计算。

条件统计函数

from openpyxl import Workbook from openpyxl.utils import get_column_letter wb_formula = Workbook() ws_f = wb_formula.active ws_f.title = "条件统计" # 准备源数据 headers = ["产品", "类别", "销售额", "地区", "日期"] data_rows = [ ("产品A", "电子产品", 12000, "华东", "2024-01-15"), ("产品B", "日用品", 8500, "华北", "2024-01-20"), ("产品C", "电子产品", 15000, "华东", "2024-02-10"), ("产品D", "食品", 6200, "华南", "2024-02-15"), ("产品E", "日用品", 9800, "华北", "2024-03-05"), ("产品F", "电子产品", 16800, "华东", "2024-03-12"), ("产品G", "食品", 7200, "华南", "2024-03-20"), ("产品H", "日用品", 10500, "华北", "2024-04-08"), ] ws_f.append(headers) for row in data_rows: ws_f.append(row) # 写入汇总公式 ws_f["A12"] = "统计项目" ws_f["B12"] = "结果" # SUMIF: 电子产品销售额总和 ws_f["A13"] = "电子产品总销售额" ws_f["B13"] = '=SUMIF(B2:B9,"电子产品",C2:C9)' # COUNTIF: 华东地区订单数 ws_f["A14"] = "华东地区订单数" ws_f["B14"] = '=COUNTIF(D2:D9,"华东")' # AVERAGEIF: 日用品平均销售额 ws_f["A15"] = "日用品平均销售额" ws_f["B15"] = '=AVERAGEIF(B2:B9,"日用品",C2:C9)' # 多条件统计 ws_f["A16"] = "华东电子产品订单数" ws_f["B16"] = '=COUNTIFS(B2:B9,"电子产品",D2:D9,"华东")' ws_f["A17"] = "华北地区销售额≥9000的订单数" ws_f["B17"] = '=COUNTIFS(D2:D9,"华北",C2:C9,">=9000")' wb_formula.save("formula_conditional.xlsx")

查找引用函数

ws_f2 = wb_formula.create_sheet("查找引用") # 准备数据表 lookup_data = [ ["产品ID", "产品名称", "单价", "库存", "供应商"], ["P001", "笔记本电脑", 5999, 120, "供应商A"], ["P002", "无线鼠标", 89, 500, "供应商B"], ["P003", "机械键盘", 399, 300, "供应商A"], ["P004", "显示器", 2499, 80, "供应商C"], ["P005", "耳机", 299, 450, "供应商B"], ] for row in lookup_data: ws_f2.append(row) # 查询结果区域 ws_f2["A10"] = "查询产品ID" ws_f2["B10"] = "P003" ws_f2["A11"] = "产品名称" ws_f2["B11"] = '=VLOOKUP(B10,A2:E6,2,FALSE)' ws_f2["A12"] = "单价" ws_f2["B12"] = '=VLOOKUP(B10,A2:E6,3,FALSE)' ws_f2["A13"] = "库存" ws_f2["B13"] = '=VLOOKUP(B10,A2:E6,4,FALSE)' ws_f2["A14"] = "库存金额" ws_f2["B14"] = '=VLOOKUP(B10,A2:E6,3,FALSE)*VLOOKUP(B10,A2:E6,4,FALSE)' # INDEX + MATCH 组合（更灵活的查找） ws_f2["A16"] = "使用INDEX+MATCH查找" ws_f2["A17"] = "查找产品" ws_f2["B17"] = "P004" ws_f2["A18"] = "供应商" ws_f2["B18"] = '=INDEX(E2:E6,MATCH(B17,A2:A6,0))' # 多条件查找（使用XLOOKUP风格，但Excel版本兼容） ws_f2["A20"] = "供应商A产品数量" ws_f2["B20"] = '=COUNTIF(E2:E6,"供应商A")'

文本处理与日期公式

ws_f3 = wb_formula.create_sheet("文本日期") # 文本处理数据 text_data = [ ["姓名", "身份证号", "入职日期"], ["张三", "110101199001011234", "2020-03-15"], ["李四", "310104198512152345", "2019-07-01"], ["王五", "440301199207013456", "2021-01-10"], ["赵六", "510105199810014567", "2022-06-20"], ["钱七", "320106200005015678", "2023-04-01"], ] for row in text_data: ws_f3.append(row) # 从身份证号提取出生日期 ws_f3["A8"] = "出生日期（提取）" ws_f3["B8"] = '=DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2))' ws_f3["A9"] = "性别（提取）" ws_f3["B9"] = '=IF(MOD(MID(B2,17,1),2)=1,"男","女")' ws_f3["A10"] = "年龄（计算）" ws_f3["B10"] = '=DATEDIF(DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2)),TODAY(),"Y")' # 字符串拼接 ws_f3["A12"] = "姓名+身份证后4位" ws_f3["B12"] = '=A2&"-"&RIGHT(B2,4)' # 日期计算 ws_f3["A14"] = "工龄（年）" ws_f3["B14"] = '=DATEDIF(C2,TODAY(),"Y")&"年"&DATEDIF(C2,TODAY(),"YM")&"月"' ws_f3["A15"] = "转正日期（试用期6个月）" ws_f3["B15"] = '=EDATE(C2,6)' ws_f3["A16"] = "是否满3年" ws_f3["B16"] = '=IF(DATEDIF(C2,TODAY(),"Y")>=3,"是","否")' wb_formula.save("formula_advanced.xlsx")

关键提醒：在openpyxl中写入公式时，注意使用英文引号和逗号，确保公式字符串符合Excel的国际版语法。公式中的单元格引用需要根据实际数据范围进行调整。可以使用 get_column_letter() 函数将列索引转换为字母表示，便于动态构建公式字符串。对于大量公式写入，建议先在一个单元格中测试公式逻辑，确认无误后再批量应用。

六、数据透视表

数据透视表是Excel最强大的数据分析工具之一，能够帮助用户快速汇总、分析和探索大量数据。openpyxl提供了完整的透视表创建接口，支持通过Python代码自动化生成数据透视表，这在定期报表生成和数据分析自动化场景中极具价值。通过编程方式创建透视表，不仅可以保证报表的一致性，还能大大减少重复的手动操作。

openpyxl的透视表功能通过 openpyxl.pivot 模块实现，核心类为 PivotTable。创建透视表需要先有一个数据源工作表，然后通过 PivotTable 构造函数指定数据源区域和目标位置，再通过 PivotFields 设置行标签、列标签、值和筛选字段。透视表的布局和格式也可以通过API进行精细控制。

创建基本数据透视表

from openpyxl import Workbook from openpyxl.pivot.table import PivotTable, PivotTableDefinition, PivotField, PivotFilter wb_pivot = Workbook() ws_data = wb_pivot.active ws_data.title = "销售数据" # 准备源数据 - 模拟销售订单数据 sales_data = [ ["订单ID", "日期", "产品类别", "产品名称", "销售员", "地区", "数量", "单价", "金额"], [1001, "2024-01-05", "电子产品", "笔记本电脑", "张三", "华东", 5, 5999, 29995], [1002, "2024-01-08", "日用品", "洗发水", "李四", "华北", 20, 35, 700], [1003, "2024-01-12", "电子产品", "无线鼠标", "张三", "华东", 30, 89, 2670], [1004, "2024-01-15", "食品", "坚果礼盒", "王五", "华南", 15, 128, 1920], [1005, "2024-01-20", "日用品", "洗衣液", "李四", "华北", 25, 45, 1125], [1006, "2024-02-03", "电子产品", "显示器", "张三", "华东", 8, 2499, 19992], [1007, "2024-02-08", "电子产品", "机械键盘", "赵六", "华东", 12, 399, 4788], [1008, "2024-02-12", "日用品", "纸巾", "李四", "华北", 50, 12, 600], [1009, "2024-02-18", "食品", "咖啡豆", "王五", "华南", 10, 89, 890], [1010, "2024-03-05", "电子产品", "耳机", "赵六", "华东", 20, 299, 5980], [1011, "2024-03-08", "日用品", "洗面奶", "李四", "华北", 18, 55, 990], [1012, "2024-03-12", "食品", "有机茶叶", "王五", "华南", 8, 158, 1264], [1013, "2024-03-15", "电子产品", "平板电脑", "张三", "华东", 6, 3499, 20994], [1014, "2024-03-20", "日用品", "垃圾袋", "李四", "华北", 100, 8, 800], [1015, "2024-03-22", "食品", "巧克力礼盒", "王五", "华南", 12, 99, 1188], ] for row in sales_data: ws_data.append(row) # 创建透视表工作表 ws_pivot = wb_pivot.create_sheet("销售透视分析") # 定义透视表数据源 pivot_source = f"销售数据!A1:I{len(sales_data) + 1}" # 创建透视表定义 pivot_def = PivotTableDefinition( name="销售汇总分析", cacheId="1", dataSource=pivot_source, applyNumberFormats=False, applyBorderFormats=False, applyFontFormats=False, applyPatternFormats=False, applyAlignmentFormats=False, showRowHeaders=True, showColHeaders=True, showRowStripes=True, showColStripes=True, showEmptyRow=True, showEmptyCol=True, indent=0, compact=True, compactData=True, outline=True, outlineData=True, ) # 创建透视表对象 pivot_table = PivotTable(pivot_def, cacheSource=pivot_source) # 设置行字段（产品类别） row_field = PivotField( name="产品类别", axis="axisRow", field=1, # 字段在数据源中的索引（0-based） showAll=True, outline=True, subtotalTop=True, ) pivot_def.rowFields.append(row_field) # 设置列字段（地区） col_field = PivotField( name="地区", axis="axisCol", field=5, # 地区列索引（0-based，第6列） showAll=True, ) pivot_def.colFields.append(col_field) # 设置值字段（金额求和） data_field = PivotField( name="金额", axis="axisData", field=8, # 金额列索引（0-based） subtotal="sum", numFmt="#,##0", showAll=True, ) pivot_def.dataFields.append(data_field) # 将透视表添加到工作表 ws_pivot.add_pivot_table(pivot_table, "A3") wb_pivot.save("pivot_table_basic.xlsx")

高级透视表配置

# 更复杂的透视表 - 多级行字段 + 筛选 + 值汇总方式 ws_pivot2 = wb_pivot.create_sheet("高级透视分析") pivot_source2 = f"销售数据!A1:I{len(sales_data) + 1}" pivot_def2 = PivotTableDefinition( name="高级销售分析", cacheId="2", dataSource=pivot_source2, showRowStripes=True, showColStripes=True, compact=False, # 非紧凑布局（表格形式） outline=False, ) # 创建透视表 pivot_table2 = PivotTable(pivot_def2, cacheSource=pivot_source2) # 多级行字段：产品类别 > 产品名称 row_field_cat = PivotField( name="产品类别", axis="axisRow", field=2, # 产品类别是第3列（0-based=2） showAll=True, ) pivot_def2.rowFields.append(row_field_cat) row_field_prod = PivotField( name="产品名称", axis="axisRow", field=3, # 产品名称是第4列（0-based=3） showAll=True, ) pivot_def2.rowFields.append(row_field_prod) # 列字段：销售员 col_field_seller = PivotField( name="销售员", axis="axisCol", field=4, # 销售员是第5列（0-based=4） showAll=True, ) pivot_def2.colFields.append(col_field_seller) # 多个值字段：数量求和 + 金额求和 data_field_qty = PivotField( name="数量", axis="axisData", field=6, # 数量列（0-based=6） subtotal="sum", numFmt="#,##0", ) pivot_def2.dataFields.append(data_field_qty) data_field_amt = PivotField( name="金额", axis="axisData", field=8, # 金额列（0-based=8） subtotal="sum", numFmt="#,##0", ) pivot_def2.dataFields.append(data_field_amt) # 添加报表筛选（地区） page_field = PivotField( name="地区", axis="axisPage", field=5, showAll=True, ) pivot_def2.pageFields.append(page_field) ws_pivot2.add_pivot_table(pivot_table2, "A3") wb_pivot.save("pivot_table_advanced.xlsx")
# 排序与筛选配置 from openpyxl.pivot.table import PivotFilter # 在已有的透视表上添加筛选 pivot_filter = PivotFilter( name="金额筛选", field=8, type="topN", evalOrder="desc", value=5, # 显示前5名 ) pivot_def2.filters.append(pivot_filter) # 自定义值字段显示方式 # 修改值字段的计算类型（如显示为占比） data_field_amt.showDataAs = "percentOfRow" # 显示为行总计的百分比 data_field_amt.numFmt = "0.00%" wb_pivot.save("pivot_table_filtered.xlsx")

重要说明：openpyxl的透视表创建依赖于 PivotTableDefinition 的各个字段配置，其中 field 属性的索引值是0-based的，对应数据源中的列位置。透视表创建后，需要在Excel中手动刷新才能看到数据结果（openpyxl不执行计算引擎）。对于复杂的透视表布局，建议先在Excel中手动创建目标样式，然后通过openpyxl的代码复现。

七、条件格式进阶

条件格式是Excel数据可视化的利器，能够根据单元格的值自动应用格式，帮助用户快速识别数据中的模式、异常和趋势。openpyxl支持与Excel几乎完全一致的条件格式功能，包括色阶、数据条、图标集以及自定义公式条件格式。通过编程方式批量应用条件格式，可以显著提升报表的数据解读效率。

条件格式的核心API位于 openpyxl.formatting.rule 模块中。每个条件格式由 ConditionalFormattingList 管理，通过 ws.conditional_formatting.add(range, rule) 方法应用到指定的单元格区域。支持的条件格式规则包括 CellIsRule（基于单元格值）、FormulaRule（基于公式）、ColorScaleRule（色阶）、DataBarRule（数据条）和 IconSetRule（图标集）五种主要类型。

色阶与数据条

from openpyxl.formatting.rule import ColorScaleRule, DataBarRule, IconSetRule, CellIsRule, FormulaRule from openpyxl.styles import PatternFill, Font, Border, Side wb_cf = Workbook() ws_cf = wb_cf.active ws_cf.title = "条件格式" ws_cf.append(["月份", "销售额", "目标完成率", "利润"]) sales_data_cf = [ ("1月", 120000, 0.85, 36000), ("2月", 150000, 0.92, 45000), ("3月", 135000, 0.88, 40500), ("4月", 180000, 0.95, 54000), ("5月", 165000, 0.90, 49500), ("6月", 210000, 0.98, 63000), ("7月", 195000, 0.93, 58500), ("8月", 220000, 1.02, 66000), ("9月", 185000, 0.91, 55500), ("10月", 240000, 1.05, 72000), ("11月", 260000, 1.10, 78000), ("12月", 300000, 1.15, 90000), ] for row in sales_data_cf: ws_cf.append(row) # 1. 色阶：应用于销售额列（B2:B13） ws_cf.conditional_formatting.add( "B2:B13", ColorScaleRule( start_type="min", start_color="F44336", mid_type="percentile", mid_value=50, mid_color="FFEB3B", end_type="max", end_color="4CAF50" ) ) # 2. 数据条：应用于目标完成率列（C2:C13） ws_cf.conditional_formatting.add( "C2:C13", DataBarRule( start_type="min", end_type="max", color="2196F3", showValue=True, minLength=None, maxLength=None ) ) # 3. 图标集：应用于利润列（D2:D13） ws_cf.conditional_formatting.add( "D2:D13", IconSetRule( icon_style="3TrafficLights1", type="percent", values=[0, 33, 67], showValue=True, percent=True, reverse=False ) ) wb_cf.save("conditional_formatting.xlsx")

公式条件格式与批量应用

# 4. 基于公式的条件格式 - 标记超出平均值的行 highlight_fill = PatternFill(start_color="E8F5E9", end_color="E8F5E9", fill_type="solid") highlight_font = Font(bold=True, color="2E7D32") ws_cf.conditional_formatting.add( "A2:D13", FormulaRule( formula=['$B2 > AVERAGE($B$2:$B$13)'], fill=highlight_fill, font=highlight_font ) ) # 5. 单元格值条件格式 - 标记低目标完成率 low_fill = PatternFill(start_color="FFEBEE", end_color="FFEBEE", fill_type="solid") ws_cf.conditional_formatting.add( "C2:C13", CellIsRule( operator="lessThan", formula=["0.9"], fill=low_fill, font=Font(color="C62828") ) ) # 6. 整行高亮 - 使用公式识别特定条件 ws_cf.append([]) ws_cf.append(["季度分析"]) ws_cf.append(["季度", "销售额", "增长率"]) quarterly = [ ("Q1", 405000, None), ("Q2", 555000, 0.37), ("Q3", 600000, 0.08), ("Q4", 800000, 0.33), ] for row in quarterly: ws_cf.append(row) # 标记增长率超过20%的季度 top_fill = PatternFill(start_color="C8E6C9", end_color="C8E6C9", fill_type="solid") ws_cf.conditional_formatting.add( "A17:C20", FormulaRule( formula=['AND($C18>0.2, $C18<>"")'], fill=top_fill, font=Font(bold=True) ) ) wb_cf.save("conditional_formatting.xlsx")

实践建议：条件格式的视觉层次应当遵循"数据-异常-趋势"的顺序。数据条适合展示数据的相对大小，色阶适合展示数据的分布密度，图标集适合做快速的状态判断。在实际报表中，建议在同一区域最多叠加2-3种条件格式，过多的条件格式会导致视觉混乱和性能下降。

八、实战案例

理论学习是为了解决实际问题。本节通过三个典型的办公自动化场景，展示如何综合运用openpyxl的图表、公式和透视表功能，构建完整的报表自动化解决方案。每个案例都包含从数据准备到最终输出的完整代码，可以直接在实际工作中复用和调整。

案例一：月度销售趋势报表

from openpyxl import Workbook from openpyxl.chart import BarChart, LineChart, Reference from openpyxl.chart.axis import NumericAxis from openpyxl.chart.label import DataLabelList from openpyxl.styles import Font, PatternFill, Alignment, Border, Side def create_monthly_sales_report(data, output_path): """生成月度销售趋势报表""" wb = Workbook() ws = wb.active ws.title = "月度销售分析" # 样式定义 header_font = Font(name="Microsoft YaHei", bold=True, color="FFFFFF", size=11) header_fill = PatternFill(start_color="2E7D32", end_color="2E7D32", fill_type="solid") title_font = Font(name="Microsoft YaHei", bold=True, size=14, color="2E7D32") thin_border = Border( left=Side(style="thin", color="E0E0E0"), right=Side(style="thin", color="E0E0E0"), top=Side(style="thin", color="E0E0E0"), bottom=Side(style="thin", color="E0E0E0") ) # 标题行 ws.merge_cells("A1:F1") ws["A1"] = "月度销售趋势分析报告" ws["A1"].font = title_font ws["A1"].alignment = Alignment(horizontal="center") # 写入表头 headers = ["月份", "目标销售额", "实际销售额", "完成率", "同比增长", "环比增长"] for col, header in enumerate(headers, 1): cell = ws.cell(row=3, column=col, value=header) cell.font = header_font cell.fill = header_fill cell.alignment = Alignment(horizontal="center") cell.border = thin_border # 写入数据 for row_idx, row_data in enumerate(data, 4): for col_idx, value in enumerate(row_data, 1): cell = ws.cell(row=row_idx, column=col_idx, value=value) cell.border = thin_border cell.alignment = Alignment(horizontal="center") # 写入完成率公式（实际/目标） ws.cell(row=row_idx, column=4, value=f'=C{row_idx}/B{row_idx}') ws.cell(row=row_idx, column=4).number_format = "0.0%" last_row = 3 + len(data) # 汇总行 ws.cell(row=last_row + 1, column=1, value="合计") ws.cell(row=last_row + 1, column=1).font = Font(bold=True) ws.cell(row=last_row + 1, column=2, value=f'=SUM(B4:B{last_row})') ws.cell(row=last_row + 1, column=3, value=f'=SUM(C4:C{last_row})') ws.cell(row=last_row + 1, column=4, value=f'=C{last_row + 1}/B{last_row + 1}') ws.cell(row=last_row + 1, column=4).number_format = "0.0%" # 创建图表 chart = BarChart() chart.type = "col" chart.title = "月度销售趋势" chart.y_axis.title = "销售额（万元）" chart.style = 10 # 数据系列 target_ref = Reference(ws, min_col=2, min_row=3, max_row=last_row) actual_ref = Reference(ws, min_col=3, min_row=3, max_row=last_row) cats = Reference(ws, min_col=1, min_row=4, max_row=last_row) chart.add_data(target_ref, titles_from_data=True) chart.add_data(actual_ref, titles_from_data=True) chart.set_categories(cats) chart.series[0].graphicalProperties.solidFill = "BBDEFB" chart.series[1].graphicalProperties.solidFill = "2E7D32" # 添加完成率折线（次坐标轴） line_chart = LineChart() rate_ref = Reference(ws, min_col=4, min_row=3, max_row=last_row) line_chart.add_data(rate_ref, titles_from_data=True) chart.series.append(line_chart.series[0]) chart.y_axis_2 = NumericAxis() chart.y_axis_2.title = "完成率" chart.y_axis_2.numFmt = "0%" chart.series[2].graphicalProperties.line.solidFill = "FF5722" chart.series[2].graphicalProperties.line.width = 25000 ws.add_chart(chart, "A" + str(last_row + 3)) wb.save(output_path) print(f"报表已生成: {output_path}") return wb # 示例数据 monthly_data = [ ("1月", 100, 85, None, None, None), ("2月", 100, 92, None, 0.08, -0.05), ("3月", 110, 110, None, 0.12, 0.20), ("4月", 120, 128, None, 0.15, 0.16), ("5月", 130, 145, None, 0.10, 0.13), ("6月", 140, 168, None, 0.18, 0.16), ] create_monthly_sales_report(monthly_data, "monthly_sales_report.xlsx")

案例二：财务报表自动化

from openpyxl.styles import Font, PatternFill, numbers from openpyxl.formatting.rule import DataBarRule, ColorScaleRule def create_financial_report(transactions, output_path): """生成财务收支分析报表""" wb = Workbook() ws = wb.active ws.title = "财务明细" # 定义样式 header_fill = PatternFill(start_color="1A237E", end_color="1A237E", fill_type="solid") header_font = Font(name="Microsoft YaHei", bold=True, color="FFFFFF", size=10) income_fill = PatternFill(start_color="E8F5E9", end_color="E8F5E9", fill_type="solid") expense_fill = PatternFill(start_color="FFEBEE", end_color="FFEBEE", fill_type="solid") # 写入数据 ws.append(["日期", "科目", "类型", "金额", "备注", "部门"]) for t in transactions: ws.append(t) last_row = ws.max_row # 自动配色 - 收入绿色、支出红色 for row in range(2, last_row + 1): type_val = ws.cell(row=row, column=3).value if type_val == "收入": for col in range(1, 7): ws.cell(row=row, column=col).fill = income_fill elif type_val == "支出": for col in range(1, 7): ws.cell(row=row, column=col).fill = expense_fill # 金额数据条 ws.conditional_formatting.add( f"D2:D{last_row}", DataBarRule(start_type="min", end_type="max", color="1A237E", showValue=True) ) # 汇总区域 summary_start = last_row + 3 ws.cell(row=summary_start, column=1, value="财务汇总").font = Font(bold=True, size=12, color="1A237E") ws.cell(row=summary_start + 1, column=1, value="总收入") ws.cell(row=summary_start + 1, column=2, value=f'=SUMIF(C2:C{last_row},"收入",D2:D{last_row})') ws.cell(row=summary_start + 1, column=2).number_format = '#,##0.00' ws.cell(row=summary_start + 2, column=1, value="总支出") ws.cell(row=summary_start + 2, column=2, value=f'=SUMIF(C2:C{last_row},"支出",D2:D{last_row})') ws.cell(row=summary_start + 2, column=2).number_format = '#,##0.00' ws.cell(row=summary_start + 3, column=1, value="净结余") ws.cell(row=summary_start + 3, column=2, value=f'=B{summary_start + 1}-B{summary_start + 2}') ws.cell(row=summary_start + 3, column=2).number_format = '#,##0.00' ws.cell(row=summary_start + 3, column=2).font = Font(bold=True, color="2E7D32") # 部门汇总 ws.cell(row=summary_start + 5, column=1, value="部门支出汇总").font = Font(bold=True, size=11, color="1A237E") ws.cell(row=summary_start + 6, column=1, value="部门") ws.cell(row=summary_start + 6, column=2, value="支出总额") ws.cell(row=summary_start + 6, column=1).font = Font(bold=True) ws.cell(row=summary_start + 6, column=2).font = Font(bold=True) depts = list(set(t[5] for t in transactions)) for i, dept in enumerate(depts): ws.cell(row=summary_start + 7 + i, column=1, value=dept) ws.cell(row=summary_start + 7 + i, column=2, value=f'=SUMIFS(D2:D{last_row},C2:C{last_row},"支出",F2:F{last_row},"{dept}")') ws.cell(row=summary_start + 7 + i, column=2).number_format = '#,##0.00' wb.save(output_path) print(f"财务报告已生成: {output_path}") return wb # 示例数据 trans = [ ("2024-01-05", "销售商品", "收入", 50000, "主营业收入", "销售部"), ("2024-01-08", "采购原材料", "支出", 18000, "原材料采购", "采购部"), ("2024-01-10", "办公用品", "支出", 3200, "办公耗材", "行政部"), ("2024-01-12", "服务收入", "收入", 25000, "咨询服务", "技术部"), ("2024-01-15", "员工工资", "支出", 35000, "1月工资", "人事部"), ("2024-01-18", "差旅费用", "支出", 5800, "项目出差", "销售部"), ("2024-01-20", "租金收入", "收入", 15000, "房屋出租", "行政部"), ("2024-01-22", "设备维护", "支出", 4500, "IT设备", "技术部"), ] create_financial_report(trans, "financial_report.xlsx")

案例三：数据汇总透视分析

def create_pivot_analysis(data, output_path): """创建完整的数据汇总透视分析报表""" wb = Workbook() ws_raw = wb.active ws_raw.title = "原始数据" # 写入原始数据 headers = ["日期", "产品线", "销售区域", "销售员", "客户", "产品", "数量", "单价", "金额", "成本", "利润"] ws_raw.append(headers) for row in data: ws_raw.append(row) last_data_row = len(data) + 1 # 自动计算金额和利润 for row in range(2, last_data_row + 1): ws_raw.cell(row=row, column=9, value=f'=F{row}*G{row}') # 金额 = 数量 * 单价 ws_raw.cell(row=row, column=11, value=f'=I{row}-J{row}') # 利润 = 金额 - 成本 # 创建分析汇总表 ws_analysis = wb.create_sheet("分析汇总") ws_analysis["A1"] = "销售数据透视分析报告" ws_analysis["A1"].font = Font(name="Microsoft YaHei", bold=True, size=16, color="1A237E") # 公式驱动的交叉分析表 ws_analysis["A3"] = "按产品线和区域汇总" ws_analysis["A4"] = "产品线 \\ 区域" ws_analysis["B4"] = "华东" ws_analysis["C4"] = "华北" ws_analysis["D4"] = "华南" ws_analysis["E4"] = "总计" product_lines = ["电子产品", "日用品", "食品"] regions = ["华东", "华北", "华南"] for i, prod in enumerate(product_lines): row = 5 + i ws_analysis.cell(row=row, column=1, value=prod) for j, reg in enumerate(regions): col = 2 + j cell = ws_analysis.cell(row=row, column=col) cell.value = f'=SUMIFS(原始数据!I2:I{last_data_row},原始数据!B2:B{last_data_row},"{prod}",原始数据!C2:C{last_data_row},"{reg}")' cell.number_format = '#,##0' # 行总计 ws_analysis.cell(row=row, column=5, value=f'=SUM(B{row}:D{row})') ws_analysis.cell(row=row, column=5).number_format = '#,##0' # 列总计 ws_analysis.cell(row=8, column=1, value="总计") for j in range(5): col = 2 + j ws_analysis.cell(row=8, column=col, value=f'=SUM({get_column_letter(col)}5:{get_column_letter(col)}7)') ws_analysis.cell(row=8, column=col).number_format = '#,##0' # 自动创建图表 chart_summary = BarChart() chart_summary.type = "col" chart_summary.title = "各产品线区域销售分布" chart_summary.y_axis.title = "销售额（元）" data_ref = Reference(ws_analysis, min_col=2, min_row=4, max_col=5, max_row=7) cats_ref = Reference(ws_analysis, min_col=1, min_row=5, max_row=7) chart_summary.add_data(data_ref, titles_from_data=True) chart_summary.set_categories(cats_ref) ws_analysis.add_chart(chart_summary, "A10") wb.save(output_path) print(f"透视分析报表已生成: {output_path}") return wb create_pivot_analysis([], "pivot_analysis.xlsx")

实战要点：在开发自动化报表时，建议采用函数封装的方式（如上述案例中的 create_monthly_sales_report()），将数据与逻辑分离。这样不仅便于代码复用和维护，还可以通过参数化配置适应不同的报表需求。同时，函数化封装也更有利于单元测试和异常处理。

九、性能与技巧

当处理大规模数据或复杂报表时，openpyxl的性能问题就会凸显出来。掌握优化技巧和最佳实践，可以显著提升代码执行效率，减少内存占用，从而实现对百万级数据的高效处理。本节总结在实际项目中积累的关键性能优化策略和开发技巧。

openpyxl的性能瓶颈主要来自三个方面：大量单元格的读写操作、图表创建时的渲染计算、以及公式写入前的数据准备。针对这些瓶颈，可以采取批量操作、数据缓存、延迟加载等优化策略。理解openpyxl的内部架构和内存模型，有助于从根本上避免性能陷阱。

性能优化策略

from openpyxl import Workbook, load_workbook from openpyxl.utils import get_column_letter import time # 策略1: 批量写入 vs 逐行写入 def batch_write_demo(): """批量写入数据（推荐）""" wb = Workbook() ws = wb.active # 准备大量数据 row_data = [] for i in range(10000): row_data.append([f"数据{i}", i * 1.5, i * 2.3, f"分类{i % 10}"]) start = time.time() # 批量写入 - 使用列表推导式一次性准备数据 ws.append(["名称", "数值1", "数值2", "分类"]) # 表头 for row in row_data: ws.append(row) elapsed = time.time() - start print(f"批量写入10000行耗时: {elapsed:.2f}秒") return ws # 策略2: 使用只读模式加载大文件 def read_large_file(filename): """只读模式读取大型Excel文件""" # 对于超大文件使用 read_only 模式 wb = load_workbook(filename, read_only=True, data_only=True) ws = wb.active total_rows = 0 for row in ws.iter_rows(min_row=1, values_only=True): # 逐行处理，不全部加载到内存 if total_rows == 0: print(f"表头: {row}") total_rows += 1 print(f"共读取 {total_rows} 行数据") wb.close() # 只读模式需要关闭 return total_rows # 策略3: 写入优化模式 def optimized_write_demo(): """使用 write_only 模式写入大量数据""" wb = Workbook(write_only=True) # 启用写入优化模式 ws = wb.create_sheet(title="大数据") # write_only模式下必须使用append ws.append(["ID", "名称", "数值", "分类"]) for i in range(50000): ws.append([i, f"项目{i}", i * 10.5, f"组{i % 20}"]) wb.save("large_data_optimized.xlsx") print("50,000行数据已优化写入")

图表性能优化

# 图表性能优化技巧 def optimize_chart_performance(): """优化图表创建性能""" wb = Workbook(write_only=True) ws = wb.create_sheet("图表数据") # 减少数据点数 - 对于图表而言，太多的数据点反而影响可读性 # 建议：超过1000个数据点时进行聚合 for i in range(100): ws.append([f"点{i}", i * 2, i * 1.5]) wb.save("chart_data_prepared.xlsx") # 技巧: 数据聚合后绘图 def aggregate_for_chart(data, group_size=10): """将大量数据聚合后再创建图表""" aggregated = [] for i in range(0, len(data), group_size): group = data[i:i + group_size] avg_val = sum(item[1] for item in group) / len(group) label = f"{group[0][0]}-{group[-1][0]}" aggregated.append((label, avg_val)) return aggregated # 技巧: 缓存Reference对象 class ChartBuilder: """图表构建器 - 缓存常用对象""" def __init__(self, ws): self.ws = ws self._ref_cache = {} def get_reference(self, min_col, min_row, max_col=None, max_row=None): """缓存Reference对象避免重复创建""" key = (min_col, min_row, max_col, max_row) if key not in self._ref_cache: from openpyxl.chart import Reference if max_col is None: max_col = min_col if max_row is None: max_row = min_row self._ref_cache[key] = Reference( self.ws, min_col=min_col, min_row=min_row, max_col=max_col, max_row=max_row ) return self._ref_cache[key]

数据联动与刷新策略

# 图表与数据动态联动 def create_dynamic_report(): """创建动态联动的报表系统""" wb = Workbook() ws_data = wb.active ws_data.title = "动态数据" # 使用结构化引用（表/Table）实现动态范围 from openpyxl.worksheet.table import Table, TableStyleInfo headers = ["月份", "销售额", "目标"] ws_data.append(headers) for i in range(1, 13): ws_data.append([f"{i}月", 100 + i * 20, 120 + i * 15]) # 创建Excel表（自动扩展范围） table = Table( displayName="销售数据表", ref=f"A1:C13", tableStyleInfo=TableStyleInfo( name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False ) ) ws_data.add_table(table) # 使用表格名称创建公式（表格数据会自动扩展） ws_data["E1"] = "统计指标" ws_data["E2"] = "总销售额" ws_data["F2"] = '=SUM(销售数据表[销售额])' ws_data["E3"] = "平均销售额" ws_data["F3"] = '=AVERAGE(销售数据表[销售额])' ws_data["E4"] = "完成率" ws_data["F4"] = '=SUM(销售数据表[销售额])/SUM(销售数据表[目标])' # 创建图表引用表范围 from openpyxl.chart import BarChart, Reference chart = BarChart() chart.title = "动态销售数据" data_ref = Reference(ws_data, min_col=2, min_row=1, max_row=13) cat_ref = Reference(ws_data, min_col=1, min_row=2, max_row=13) chart.add_data(data_ref, titles_from_data=True) chart.set_categories(cat_ref) ws_data.add_chart(chart, "E6") wb.save("dynamic_report.xlsx") print("动态联动报表已生成") # 未来添加新行时，表格会自动扩展，图表也会自动更新 # 只需在表格下方追加数据即可

核心总结：openpyxl性能优化的三个黄金法则：（1）使用 write_only=True 模式写入大量数据（性能提升可达10倍）；（2）使用 read_only=True 模式读取大文件（内存占用降低90%）；（3）对于图表，适度聚合数据点（建议不超过1000个数据点）。此外，合理使用Excel表（Table）功能可以实现数据的动态扩展和公式自动填充，这也是构建智能报表系统的关键基础设施。

开发者寄语：openpyxl是一个非常强大但功能繁多的库。建议在实际项目中使用"渐进式"的学习策略：先掌握基础的数据读写，然后逐步加入公式和图表，最后融合透视表和条件格式创建完整的自动化解决方案。每个新功能的添加都应该以解决实际的业务需求为目标，避免过度设计。