Python+pandas+matplotlib+xlwings自动生成

脚本专家

在日常办公中，从Excel提取数据并生成图表是常见需求。手工重复操作效率低，容易出错。本文提供一套完整的Python自动化流程：读取Excel数据，使用pandas清洗，matplotlib绘制图表保存为PNG，最后通过xlwings将图表插回Excel指定位置。整个过程可复用，只需修改文件名和列名即可适应不同报表。

适用场景与前提
该方法适合Excel表结构固定、数据定期更新的场景，如工单趋势、销售统计、巡检汇总等。注意：xlwings依赖本机Microsoft Excel应用，因此推荐在Windows办公电脑上运行。如果仅需生成PNG，可只用pandas+matplotlib；需要交付带图表的Excel文件则必须安装xlwings。

环境准备
需要安装四个核心库：pandas（读取清洗Excel数据）、matplotlib（绘图并保存PNG）、openpyxl（pandas读取.xlsx的底层引擎）、xlwings（通过Excel应用程序操控工作簿）。

2. pip install pandas matplotlib openpyxl xlwings

复制代码

安装后可用以下代码自检：

2. import pandas as pd
3. import matplotlib.pyplot as plt
4. import xlwings as xw
5. print("环境检测通过")

复制代码

核心：读取Excel生成图表PNG
假设Excel文件report.xlsx，工作表名为“数据”，包含“月份”和“工单数量”两列。关键点：列名应干净、无合并单元格，数值列可能混入空格或文本。使用pd.to_numeric(..., errors='coerce')将非数值转为NaN，再dropna()清理。

2. import pandas as pd
3. import matplotlib.pyplot as plt
4. from pathlib import Path
6. def excel_to_chart_png(xlsx_path, sheet_name, x_col, y_col, out_png):
7.     df = pd.read_excel(xlsx_path, sheet_name=sheet_name)
8.     x = df[x_col].astype(str)
9.     y = pd.to_numeric(df[y_col], errors="coerce")
10.     data = pd.DataFrame({x_col: x, y_col: y}).dropna()
11.     plt.figure(figsize=(8, 4))
12.     plt.plot(data[x_col], data[y_col], marker="o")
13.     plt.title(f"{y_col}趋势图")
14.     plt.xlabel(x_col)
15.     plt.ylabel(y_col)
16.     plt.xticks(rotation=30)
17.     plt.grid(axis="y", linestyle="dashed", alpha=0.4)
18.     plt.tight_layout()
19.     out_png = Path(out_png)
20.     out_png.parent.mkdir(parents=True, exist_ok=True)
21.     plt.savefig(out_png, dpi=200)
22.     plt.close()
23.     return str(out_png)
25. if __name__ == "__main__":
26.     png = excel_to_chart_png("report.xlsx", "数据", "月份", "工单数量", "out/chart.png")
27.     print("图表已输出：", png)

复制代码

插图到Excel：xlwings控制位置
生成PNG后，需要将图片插回Excel报表。核心操作：定位锚点单元格（如H2），删除同名旧图避免叠图，插入新图并调整尺寸，另存为新文件以保护原数据。

2. import xlwings as xw
3. from pathlib import Path
5. def insert_png_to_excel(xlsx_path, sheet_name, png_path, anchor_cell="H2", out_xlsx=None):
6.     xlsx_path = str(Path(xlsx_path).resolve())
7.     png_path = str(Path(png_path).resolve())
8.     if out_xlsx is None:
9.         p = Path(xlsx_path)
10.         out_xlsx = str(p.with_name(p.stem + "_已插图" + p.suffix))
11.     app = xw.App(visible=False, add_book=False)
12.     try:
13.         wb = app.books.open(xlsx_path)
14.         sht = wb.sheets[sheet_name]
15.         cell = sht.range(anchor_cell)
16.         # 删除同名旧图，避免重复叠图
17.         for pic in list(sht.pictures):
18.             if pic.name == "chart_png":
19.                 pic.delete()
20.         sht.pictures.add(png_path, name="chart_png", left=cell.left, top=cell.top)
21.         pic = sht.pictures["chart_png"]
22.         pic.width = 520
23.         pic.height = 320
24.         wb.save(out_xlsx)
25.         wb.close()
26.     finally:
27.         app.quit()
28.     return out_xlsx
30. if __name__ == "__main__":
31.     out = insert_png_to_excel("report.xlsx", "数据", "out/chart.png", "H2")
32.     print("已写回Excel：", out)

复制代码

合体脚本：一键自动化
将两个函数组合，只需修改四个变量（xlsx、sheet、x_col、y_col）即可快速迁移到其他报表。

2. if __name__ == "__main__":
3.     xlsx = "report.xlsx"
4.     sheet = "数据"
5.     x_col = "月份"
6.     y_col = "工单数量"
7.     png = excel_to_chart_png(xlsx, sheet, x_col, y_col, "out/chart.png")
8.     out = insert_png_to_excel(xlsx, sheet, png, "H2")
9.     print("完成交付文件：", out)

复制代码

效果验证要点
1. 检查PNG是否存在、标题正确、x/y轴数据合理、中文无乱码。
2. 打开带图Excel，确认图片位于指定锚点，未压住数据，尺寸合适。
3. 抽查原始数据与图表趋势是否一致。

常见踩坑与解决
ModuleNotFoundError：使用“python -m pip install”确保安装到当前Python环境。
中文标题显示为方块：matplotlib默认字体不含中文，添加以下配置：

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

复制代码

xlwings无法打开Excel：检查本机是否安装Microsoft Excel（非WPS或其他Office）。若在无Office的服务器上运行，建议只使用pandas+matplotlib。
图片重复插入：脚本中已通过删除同名图片“chart_png”避免叠图。若仍出现，检查图片名称是否一致。
图片压住数据：调整锚点单元格（如改用J3或L2），或在模板中预留固定图表区域。

总结
本文并非单纯展示绘图语法，而是构建一条“Excel数据源→pandas读取→matplotlib出图→xlwings插回Excel→交付文件”的自动化链路。实际落地时重点关注数据清洗、图片定位、避免覆盖原文件。掌握这套流程后，面对同类型报表只需调整列名和路径，即可快速实现办公自动化。

热心网友4

感谢分享这个完整的自动化流程！代码从数据读取、清洗到图表生成和回插Excel都覆盖了，很实用。特别是用 `pd.to_numeric` 加 `errors='coerce'` 处理脏数据，以及插入图片时先删除同名旧图避免叠图，这些细节非常贴心。有个小问题想请教：如果Excel文件不是 `.xlsx` 而是 `.xls` 格式，xlwings 和 pandas 需要额外处理吗？另外，图表配色和字体方面，有没有什么建议能支持中文显示？再次感谢！

热心网友4

感谢分享！这套流程很实用，把 pandas 清洗、matplotlib 绘图和 xlwings 插入整合成一个可复用的脚本，对于固定模板的报表自动化工作来说非常高效。尤其对数据列做 `to_numeric` 和 `dropna` 处理以及删除同名旧图避免叠图这两个细节，考虑得很周到，减少了手动干预的出错可能。 一点小建议：如果在没有安装 Excel 的环境中运行（比如服务器），可以考虑用 `openpyxl` 直接插入图片（`Worksheet.add_image()`），这样不依赖本地 Excel 应用，部署更灵活。另外实际使用中注意 `xlwings` 操作后记得关闭 Excel 进程，避免残留，楼主代码里用了 `try...finally...app.quit()`，这个习惯很好。 整体代码结构清晰，注释到位，新手也能快速上手改参数。感谢分享这种“即拿即用”的方案！

热心网友4

感谢分享，这个流程整理得很清晰，尤其是 `pd.to_numeric` 处理脏数据和删除旧图避免叠图这两点，确实能省掉不少手工排查的麻烦。我平时也常用类似的组合，不过偶尔会遇到 Excel 中有合并单元格导致读表报错的情况，不知道你有没有遇到过？另外想请教一下，xlwings 在 `visible=False` 模式下，碰到较大的 Excel 文件或者系统安全软件会不会有兼容性问题？期待继续交流。