Python实现Excel数据自动生成图表的代码实现

1. 问题背景：为什么要用 Python 从 Excel 自动生成图表

在日常办公里，Excel 做图表并不难，真正麻烦的是重复做图。比如每周都要从报表里取一列数据，生成趋势图，再把图表放回 Excel 或汇报文件里。一次两次还能手工点，次数多了就会变成纯体力劳动。

本文的目标不是单纯演示一段绘图代码，而是把“读取 Excel → 生成图表 → 插回 Excel → 保存交付文件”整理成一条可复用的自动化流程。

从技术上看，这里其实是把 Excel 的数据能力、pandas 的读取清洗能力、matplotlib 的绘图能力、xlwings 的 Excel 操控能力串成一条链路。

这张图展示了本文的整体目标：把 Excel 数据通过 Python 自动转换成可交付的图表结果。

从这张图中可以看出，本文的重点不是“单独画一张图”，而是让 Excel 报表自动生成图表。这才是办公自动化真正有价值的地方：减少重复操作，提高交付稳定性。

读完本文后，你应该能带走三件事：

• 知道如何用 pandas 读取 Excel 数据
• 知道如何用 matplotlib 生成图表 PNG
• 知道如何用 xlwings 把图表插回 Excel 指定位置

2. 适用场景与限制条件

这个方法特别适合“数据在 Excel 中，但图表需要自动生成”的场景。例如工单趋势图、销售趋势图、设备维修数量统计、月度报表图、巡检结果汇总图等。

典型场景包括：

• 每天或每周定期生成同一类报表图
• Excel 表结构固定，只是数据不断更新
• 需要把生成的图表插回 Excel，方便领导或业务同事打开查看
• 需要减少手工插图、调图、保存文件的重复操作

但要注意：本文使用的 xlwings 是通过本机 Excel 应用来操作工作簿，因此更适合 Windows 办公电脑环境。如果你是在没有 Office 的纯服务器上运行，xlwings 插图这一步可能无法正常执行。

推荐做法：如果只是读取 Excel 并生成 PNG，pandas + matplotlib 就够了；如果要把 PNG 插回 Excel 并保持办公交付格式，再使用 xlwings。

3. 环境准备：先把依赖装稳，不然后面全是坑

这一节用到 4 个核心库：pandas、matplotlib、openpyxl、xlwings。它们各自负责的事情不一样，不要混在一起理解。

依赖库	作用
pandas	读取 Excel 数据、清洗数据、整理 DataFrame
openpyxl	作为 pandas 读取 .xlsx 文件时常用的底层引擎
matplotlib	根据数据生成图表，并保存为 PNG 图片
xlwings	打开 Excel，把图表图片插回指定 Sheet 和单元格位置

安装命令如下：

pip install pandas matplotlib openpyxl xlwings

这张图展示了本文所需依赖的安装环境和 Python 图表自动化基础组件。

从这张图中可以看出，环境准备不是随便装几个库就结束了。pandas 负责读数据，matplotlib 负责出图，openpyxl 负责 Excel 文件解析，xlwings 负责和 Excel 应用交互。少一个环节，整条自动化链路就不完整。

安装完成后，可以用下面的代码快速自检：

import pandas as pd
import matplotlib.pyplot as plt
import xlwings as xw

print("环境检测通过")

如果这里报错，先不要急着写业务代码。环境没稳定之前，后面的错误很容易被误判成代码问题。

4. 核心原理：读取 Excel → 生成图表 PNG

先把最核心的一步单独拆出来：从 Excel 读取数据，然后用 matplotlib 生成一张图表 PNG。只要这一步能跑通，后面 插回 Excel 就只是交付格式的问题。

假设 Excel 文件名叫 report.xlsx，工作表名叫 数据，里面有两列：

月份	工单数量
2026-01	120
2026-02	98
2026-03	135

这里的关键点是：列名要干净、数据结构要稳定、x 轴和 y 轴要能一一对应。如果列名有多余空格、合并单元格、表头不在第一行，脚本就需要额外处理。

这张图展示了从 Excel 表格读取数据，并通过 Python 生成图表文件的过程。

从这张图中可以看出，Excel 不是最终结果，而是数据来源；PNG 图表才是后续插入报表、博客、汇报材料的中间成果。这个设计很稳，因为 PNG 是通用格式，后续无论写回 Excel、插入 Word，还是上传 CSDN，都比较方便。

下面是第一段核心代码：读取 Excel 并生成图表 PNG。

import pandas as pd
import matplotlib.pyplot as plt
from pathlib import Path

def excel_to_chart_png(
    xlsx_path: str,
    sheet_name: str,
    x_col: str,
    y_col: str,
    out_png: str
):
    # 1. 读取 Excel 数据
    df = pd.read_excel(xlsx_path, sheet_name=sheet_name)

    # 2. 取出 x 轴和 y 轴数据
    x = df[x_col].astype(str)
    y = pd.to_numeric(df[y_col], errors="coerce")

    # 3. 组合并清理空值
    data = pd.DataFrame({
        x_col: x,
        y_col: y
    }).dropna()

    # 4. 绘制折线图
    plt.figure(figsize=(8, 4))
    plt.plot(data[x_col], data[y_col], marker="o")

    plt.title(f"{y_col}趋势图")
    plt.xlabel(x_col)
    plt.ylabel(y_col)

    plt.xticks(rotation=30)
    plt.grid(axis="y", linestyle="dashed", alpha=0.4)
    plt.tight_layout()

    # 5. 保存图片
    out_png = Path(out_png)
    out_png.parent.mkdir(parents=True, exist_ok=True)
    plt.savefig(out_png, dpi=200)
    plt.close()

    return str(out_png)

if __name__ == "__main__":
    png = excel_to_chart_png(
        xlsx_path="report.xlsx",
        sheet_name="数据",
        x_col="月份",
        y_col="工单数量",
        out_png="out/chart.png"
    )
    print("图表已输出：", png)

这段代码最值得注意的是 pd.to_numeric(..., errors="coerce")。真实 Excel 中经常会出现数字列夹杂空格、文本、异常字符的情况，直接画图可能报错。这里先转成数值，无法转换的内容变成空值，再通过 dropna() 清掉。

这一步的本质是：先把 Excel 中“看起来像数字”的内容，转换成 Python 真正能计算和绘图的数值。

5. 把图表插回 Excel：xlwings 控制插图位置

前面已经能生成 PNG 了，但在真实办公里，很多时候不能只给一张图片，还要把图表插回 Excel 报表里，让业务同事或领导直接打开一个文件就能看到数据和图表。

这一步使用 xlwings 完成，核心动作是：

• 打开 Excel 工作簿
• 定位目标 Sheet
• 定位插图锚点单元格，例如 H2
• 删除旧图，避免重复叠图
• 插入新图
• 调整宽高
• 保存为新文件

这张图展示了使用 xlwings 将生成的图表插入到 Excel 指定单元格位置。

从这张图中可以看出，插图不是“随便丢到 Excel 里”，而是要有明确锚点。比如把图片放到 H2，就可以避免覆盖原始数据区域，也方便后续形成固定报表模板。

下面是把 PNG 插回 Excel 的代码：

import xlwings as xw
from pathlib import Path

def insert_png_to_excel(
    xlsx_path: str,
    sheet_name: str,
    png_path: str,
    anchor_cell: str = "H2",
    out_xlsx: str | None = None
):
    xlsx_path = str(Path(xlsx_path).resolve())
    png_path = str(Path(png_path).resolve())

    if out_xlsx is None:
        p = Path(xlsx_path)
        out_xlsx = str(p.with_name(p.stem + "_已插图" + p.suffix))

    app = xw.App(visible=False, add_book=False)

    try:
        wb = app.books.open(xlsx_path)
        sht = wb.sheets[sheet_name]

        cell = sht.range(anchor_cell)

        # 删除同名旧图，避免重复插入后图表叠在一起
        for pic in list(sht.pictures):
            if pic.name == "chart_png":
                pic.delete()

        sht.pictures.add(
            png_path,
            name="chart_png",
            left=cell.left,
            top=cell.top
        )

        pic = sht.pictures["chart_png"]
        pic.width = 520
        pic.height = 320

        wb.save(out_xlsx)
        wb.close()

    finally:
        app.quit()

    return out_xlsx

if __name__ == "__main__":
    out = insert_png_to_excel(
        xlsx_path="report.xlsx",
        sheet_name="数据",
        png_path="out/chart.png",
        anchor_cell="H2"
    )
    print("已写回 Excel：", out)

这里一定要注意：不要默认覆盖原文件。尤其是在真实办公数据里，脚本如果写错位置，可能会覆盖原始数据或破坏模板。

更稳的做法：先另存为一个新文件，例如 report_已插图.xlsx，确认图表位置、大小、数据都正确后，再决定是否覆盖原文件。

6. 合体脚本：一键完成“读 Excel → 出图 → 插回 Excel”

单独看每一段代码不难，真正有价值的是把它们组合成一个可以复用的自动化脚本。这样以后换一个 Excel 文件，只需要改文件名、Sheet 名、x 轴列名、y 轴列名，就可以直接生成交付版报表。

这张图展示了完整的一键交付流程：读取 Excel 数据、生成图表 PNG、插入 Excel、保存最终文件。

从这张图中可以看出，完整自动化的重点是流程闭环。如果只生成图片，还不算真正交付；如果只插图但没有数据清洗，也容易出现错误图表。稳定的方案应该把读取、清洗、出图、插入、保存全部串起来。

下面是合体后的完整版本：

import pandas as pd
import matplotlib.pyplot as plt
import xlwings as xw
from pathlib import Path

def excel_to_chart_png(xlsx_path, sheet_name, x_col, y_col, out_png):
    df = pd.read_excel(xlsx_path, sheet_name=sheet_name)

    x = df[x_col].astype(str)
    y = pd.to_numeric(df[y_col], errors="coerce")

    data = pd.DataFrame({
        x_col: x,
        y_col: y
    }).dropna()

    plt.figure(figsize=(8, 4))
    plt.plot(data[x_col], data[y_col], marker="o")

    plt.title(f"{y_col}趋势图")
    plt.xlabel(x_col)
    plt.ylabel(y_col)

    plt.xticks(rotation=30)
    plt.grid(axis="y", linestyle="dashed", alpha=0.4)
    plt.tight_layout()

    out_png = Path(out_png)
    out_png.parent.mkdir(parents=True, exist_ok=True)

    plt.savefig(out_png, dpi=200)
    plt.close()

    return str(out_png)

def insert_png_to_excel(xlsx_path, sheet_name, png_path, anchor_cell="H2", out_xlsx=None):
    xlsx_path = str(Path(xlsx_path).resolve())
    png_path = str(Path(png_path).resolve())

    if out_xlsx is None:
        p = Path(xlsx_path)
        out_xlsx = str(p.with_name(p.stem + "_已插图" + p.suffix))

    app = xw.App(visible=False, add_book=False)

    try:
        wb = app.books.open(xlsx_path)
        sht = wb.sheets[sheet_name]

        cell = sht.range(anchor_cell)

        for pic in list(sht.pictures):
            if pic.name == "chart_png":
                pic.delete()

        sht.pictures.add(
            png_path,
            name="chart_png",
            left=cell.left,
            top=cell.top
        )

        pic = sht.pictures["chart_png"]
        pic.width = 520
        pic.height = 320

        wb.save(out_xlsx)
        wb.close()

    finally:
        app.quit()

    return out_xlsx

if __name__ == "__main__":
    xlsx = "report.xlsx"
    sheet = "数据"
    x_col = "月份"
    y_col = "工单数量"

    png = excel_to_chart_png(
        xlsx_path=xlsx,
        sheet_name=sheet,
        x_col=x_col,
        y_col=y_col,
        out_png="out/chart.png"
    )

    out = insert_png_to_excel(
        xlsx_path=xlsx,
        sheet_name=sheet,
        png_path=png,
        anchor_cell="H2"
    )

    print("完成交付文件：", out)

 实际落地时，主要修改下面 4 个变量即可：

xlsx = "report.xlsx"
sheet = "数据"
x_col = "月份"
y_col = "工单数量"

这个脚本的价值在于：它把一次性操作变成了可复用模板。下一次换成“维修数量”“销售金额”“资产统计”“巡检次数”，只要字段结构一致，就能快速迁移。

7. 效果验证：不要只看“有没有报错”

很多新手写自动化脚本，只要控制台没有报错，就以为成功了。这个判断不够严谨。Excel 自动化脚本的验证至少要看三个层面。

7.1 验证 PNG 是否生成

首先确认 out/chart.png 是否存在，并且打开后图表内容正常。

重点检查：

• 标题是否正确
• x 轴是否对应月份或分类
• y 轴数值是否合理
• 图表是否被裁切
• 中文是否乱码

7.2 验证 Excel 是否成功插图

打开输出文件，例如 report_已插图.xlsx，检查目标工作表的 H2 附近是否出现图表。

重点检查：

• 图片是否插入到指定位置
• 是否覆盖了原始数据
• 图片大小是否合适
• 是否出现多张重复叠图

7.3 验证数据是否一致

最后不要忽略数据核对。至少抽查几行 Excel 原始数据，看图表趋势是否和源数据一致。

如果数据源不对，图表画得再漂亮也没有意义。

8. 常见问题与踩坑记录

8.1 ModuleNotFoundError：找不到 pandas / matplotlib / xlwings

这种问题一般是库没有安装，或者安装到了另一个 Python 环境里。

推荐用下面这种方式安装，更能保证安装到当前 Python 环境：

python -m pip install pandas matplotlib openpyxl xlwings

8.2 中文标题显示为方块

matplotlib 默认字体不一定支持中文，图表标题、坐标轴可能显示为方块。可以加上中文字体配置：

plt.rcParams["font.sans-serif"] = ["SimHei"]
plt.rcParams["axes.unicode_minus"] = False

这里的本质是 matplotlib 找不到合适的中文字体，所以需要显式指定。

8.3 xlwings 打不开 Excel

如果报 COM 异常、找不到 Excel、启动失败，优先检查本机是否安装 Microsoft Excel。

xlwings 不是纯文件读写库，它是通过 Excel 应用程序来操作工作簿。所以没有 Office 的服务器环境，不适合直接使用本文的插图方案。

8.4 图片重复插入

如果每运行一次脚本就多一张图，说明没有删除旧图。本文代码中通过图片名称 chart_png 删除旧图，再插入新图，避免重复叠图。

8.5 图片位置压住原始数据

这种情况通常是锚点单元格设置不合理。例如 H2 附近没有足够空白区域，可以改成 J3、L2 等更靠右的位置。

建议预留一块固定图表区域，这样模板稳定，脚本也更好维护。

9. 总结提升：这节真正要掌握的是“自动化交付链路”

这一节的核心，不是记住某一个函数，而是理解一条完整的办公自动化交付链路：

Excel 数据源 → pandas 读取 → matplotlib 出图 → PNG 文件 → xlwings 插回 Excel → 输出交付文件

如果只会 plt.plot()，那只是会画图；如果能把图表自动插回 Excel，并保存成业务同事能直接打开的文件，才算真正具备办公自动化价值。

我建议后续写类似脚本时，始终按三个层次来设计：

1. 数据层：数据从哪里来，字段是否稳定，数值是否需要清洗；
2. 图表层：用什么图表达，标题、坐标轴、大小是否清晰；
3. 交付层：图表输出到哪里，是 PNG、Excel、Word，还是博客配图。

最容易翻车的地方不是绘图语法，而是数据源不规范、图片插入位置不固定、Excel 进程没有关闭、原始文件被覆盖。

所以这篇笔记最值得沉淀的不是代码本身，而是这套“读数据、出图、写回、验证”的标准流程。