怎么使用Python在Excel中插入图表和公式

林舟
林舟
发布时间:2026-11-18 | 7人浏览过
原创

本文详细介绍如何使用Python的openpyxl库在Excel中插入常用计算公式与各类原生图表,包含完整操作步骤、底层计算机制解析、综合代码示例以及常见排错要点。

在日常办公自动化中,使用Python生成Excel报表不仅需要填入原始数据,还需要保留动态计算能力与直观的可视化图表。完成这项任务的核心途径是使用 openpyxl 库:它支持在不依赖本地Microsoft Excel软件的情况下,直接把公式表达式写入单元格,并利用其内置的图表模块(如 BarChart、LineChart)构建原生Excel图表。本文将围绕环境准备、公式写入规范、图表构建流程与避坑指南,完整演示具体的实现方法。

一、核心机制与整体操作流程

Python生成带有公式和图表的Excel文件主要经历五个阶段:建立或加载工作簿、按行列写入明细数据、将公式以特定文本格式赋给对应单元格、通过数据区域引用(Reference)定义图表范围,最后将图表对象挂载到工作表中并保存为 .xlsx 文件。整个过程无需在后台启动Excel进程,跨平台兼容性良好。

怎么使用Python在Excel中插入图表和公式
图 1:Python操作Excel公式与图表的阶段拆解与核心调用流

首先确保已安装 openpyxl 库。在终端中执行以下命令即可完成安装:

pip install openpyxl

二、在Excel中写入公式的实现与计算原理

在 openpyxl 中,给单元格插入公式与插入普通字符串完全一致,只需将以等号 = 开头的计算表达式直接赋值给单元格属性即可。

1. 常用公式赋值语法

以下示例演示如何写入求和、平均值、条件统计及跨列运算公式:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "销售明细"

# 写入测试数据
ws['A1'] = "单价"
ws['B1'] = "销量"
ws['C1'] = "总金额"

ws['A2'] = 15.5
ws['B2'] = 20
# 写入行内乘法公式
ws['C2'] = "=A2*B2"

ws['A3'] = 28.0
ws['B3'] = 10
ws['C3'] = "=A3*B3"

# 写入汇总行聚合公式
ws['A4'] = "合计"
ws['C4'] = "=SUM(C2:C3)"

# 写入平均值统计公式
ws['A5'] = "平均"
ws['C5'] = "=AVERAGE(C2:C3)"

wb.save("sales_formulas.xlsx")

2. 核心原理与常见疑惑:为什么写入后读出来是 None?

这是初学者最容易遇到的困惑:当使用 openpyxl 写入公式后,立刻使用 data_only=True 模式重新读取该单元格,读到的结果往往是 None,而不是公式计算后的数值。

原因在于:openpyxl 本身不内置 Excel 的公式计算引擎。Python在写入公式时,仅仅是将字符串写入到 xlsx 底层的 XML 文件(标签为 <f>SUM(...)</f>)。计算行为是在真实的使用场景中,由 Microsoft Excel、WPS 或 LibreOffice 打开文档时,其内置计算引擎实时运算并渲染出来的,同时会将运算结果保存在 <v> 缓存标签中。

怎么使用Python在Excel中插入图表和公式
图 2:Excel公式写入、计算求值与生命周期读取机制对比

实用建议: 如果你的后续业务流程要求立刻从生成的文件中读出实际计算数值,建议在Python脚本内部先完成数学运算(例如直接算好数值填入单元格),或者在写入公式的同时保留备份计算结果,不要指望 openpyxl 离线替你执行 =VLOOKUP() 或 =SUM()。

三、在Excel中创建并插入图表

openpyxl.chart 模块提供了丰富的图表类型,包括柱状图(BarChart)、折线图(LineChart)、饼图(PieChart)、散点图(ScatterChart)等。图表的核心逻辑是:通过 Reference 选定工作表中的数据区域和类别轴标签,配置图表外观,最后挂载到指定的起始单元格。

1. 图表对象模型与引用绑定

在创建图表时,最关键的是区分两个区域:

  1. 数据区域(Data Reference): 包含要绘制为柱子或线条的数值矩阵,通常可包含首行作为系列标题(titles_from_data=True)。
  2. 分类轴标签(Categories Reference): 对应横轴(X轴)上的业务维度(如日期、月份、姓名等)。
怎么使用Python在Excel中插入图表和公式
图 3:Excel单元格区域(Reference)与图表组件对应映射图

四、综合实战:同时插入明细、汇总公式与对比图表

下面给出一个完整的自动化脚本。该脚本创建一份包含部门销售明细的报表,利用公式自动计算合计与平均,并插入柱状图展示各部门季度业绩。

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.styles import Font, PatternFill, Alignment

def generate_quarterly_report(output_filename="quarterly_report.xlsx"):
    wb = Workbook()
    ws = wb.active
    ws.title = "季度销售业绩"

    # 1. 写入表头及基础数据
    headers = ["季度", "华东大区", "华北大区", "华南大区"]
    ws.append(headers)

    rows = [
        ["一季度", 120, 95, 110],
        ["二季度", 150, 115, 130],
        ["三季度", 180, 140, 160],
        ["四季度", 220, 185, 205],
    ]
    for row in rows:
        ws.append(row)

    # 2. 插入计算公式(第6行为合计,第7行为平均值)
    ws['A6'] = "总计"
    ws['B6'] = "=SUM(B2:B5)"
    ws['C6'] = "=SUM(C2:C5)"
    ws['D6'] = "=SUM(D2:D5)"

    ws['A7'] = "季度平均"
    ws['B7'] = "=AVERAGE(B2:B5)"
    ws['C7'] = "=AVERAGE(C2:C5)"
    ws['D7'] = "=AVERAGE(D2:D5)"

    # 简单样式修饰:表头背景与加粗
    header_fill = PatternFill(start_color="345E82", end_color="345E82", fill_type="solid")
    header_font = Font(name="微软雅黑", size=11, bold=True, color="FFFFFF")
    for cell in ws[1]:
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = Alignment(horizontal="center")

    # 3. 构造柱状图
    chart = BarChart()
    chart.type = "col"             # "col" 表示纵向柱状图,"bar" 表示横向条形图
    chart.style = 10               # 内置配色方案序号
    chart.title = "各区域季度销售业绩走势"
    chart.y_axis.title = "销售额(万元)"
    chart.x_axis.title = "业务季度"
    chart.width = 16               # 图表宽度(单位:厘米)
    chart.height = 10              # 图表高度(单位:厘米)

    # 4. 绑定数据源(注意:仅引用明细行 B1:D5,不要把汇总行加进图表)
    data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=5)
    categories = Reference(ws, min_col=1, min_row=2, max_row=5)
    
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(categories)

    # 5. 将图表添加到工作表的指定锚点单元格
    ws.add_chart(chart, "F2")

    # 保存文件
    wb.save(output_filename)
    print(f"报表生成完毕,文件已保存至:{output_filename}")

if __name__ == "__main__":
    generate_quarterly_report()

代码要点说明

  • 数据范围截取: 在设置 data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=5) 时,行范围只选到第 5 行。如果错误地选到了第 6 行(总计),汇总数值会大幅拉高Y轴刻度,导致明细数据的柱子严重被压缩。
  • 图表尺寸调节: chart.width 和 chart.height 默认单位为厘米,可根据数据量灵活调整,避免标题或X轴标签因图表太小而重叠拥挤。
  • 单元格锚定位置: ws.add_chart(chart, "F2") 表示图表左上角对齐 F2 单元格。根据数据列宽度,将其放置在数据右侧或数据下方均可。

五、常见避坑要点与排错检查表

在实际工程开发中,插入公式和图表经常遇到细节错误,可参考下方的对照排查规范。

怎么使用Python在Excel中插入图表和公式
图 4:Python自动化操作Excel公式与图表的常见问题与排查指南

关键排错清单速查

问题分类 常见错误表现 核心诱因 标准处理方法
公式大写规范 单元格出现 #NAME? 报错 公式函数名使用了小写或全角符号,如 =sum(a1:a10) 始终使用大写英文函数名与半角标点,如 =SUM(A1:A10)
公式离线读取 Python读取返回 None openpyxl不带计算引擎,未经Excel打开保存无缓存值 在Python生成时同步写入数值,或由Excel/LibreOffice先执行计算
图表系列识别 图例显示为数字或“系列1” titles_from_data 设置为True但数据切片未包含标题行 min_row 需从表头所在的第1行开始截取
X轴标签错位 柱状图底部横轴显示 1, 2, 3 未调用 chart.set_categories() 绑定业务标签列 使用 Reference 选定类别列并显式调用 set_categories
文件写入权限 PermissionError: Errno 13 输出的 Excel 文件被本地 Excel 软件保持占用锁定 关闭同名工作簿后再运行脚本,或在文件名中加入动态时间戳

六、总结

使用Python在Excel中插入图表和公式,核心在于掌握 openpyxl 的单元格公式赋值方式和 Reference 区域切片机制。写入公式时,牢记公式字符串以 = 开头、英文大写函数名以及离线不计算的特性;创建图表时,理清“系列数值区域”与“X轴分类标签区域”的划分,配置合适的尺寸和挂载坐标。遵循这套标准流程,即可轻松实现自动化数据汇总与专业可视化图表输出。

本站声明

本文内容仅供参考,不代表本站观点。转载或引用请注明出处。如发现内容问题,请通过联系我们页面反馈。

Python 相关产品和应用

全部下载

专题列表