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应用程序操控工作簿）。
pip install pandas matplotlib openpyxl xlwings
安装后可用以下代码自检：
import pandas as pd
import matplotlib.pyplot as plt
import xlwings as xw
print("环境检测通过")
核心：读取Excel生成图表PNG
假设Excel文件report.xlsx，工作表名为“数据”，包含“月份”和“工单数量”两列。关键点：列名应干净、无合并单元格，数值列可能混入空格或文本。使用pd.to_numeric(..., errors='coerce')将非数值转为NaN，再dropna()清理。
import pandas as pd
import matplotlib.pyplot as plt
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.astype(str)
y = pd.to_numeric(df, errors="coerce")
data = pd.DataFrame({x_col: x, y_col: y}).dropna()
plt.figure(figsize=(8, 4))
plt.plot(data, data, 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)
if __name__ == "__main__":
png = excel_to_chart_png("report.xlsx", "数据", "月份", "工单数量", "out/chart.png")
print("图表已输出：", png)
插图到Excel：xlwings控制位置
生成PNG后，需要将图片插回Excel报表。核心操作：定位锚点单元格（如H2），删除同名旧图避免叠图，插入新图并调整尺寸，另存为新文件以保护原数据。
import xlwings as xw
from pathlib import Path
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
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("report.xlsx", "数据", "out/chart.png", "H2")
print("已写回Excel：", out)
合体脚本：一键自动化
将两个函数组合，只需修改四个变量（xlsx、sheet、x_col、y_col）即可快速迁移到其他报表。
if __name__ == "__main__":
xlsx = "report.xlsx"
sheet = "数据"
x_col = "月份"
y_col = "工单数量"
png = excel_to_chart_png(xlsx, sheet, x_col, y_col, "out/chart.png")
out = insert_png_to_excel(xlsx, sheet, png, "H2")
print("完成交付文件：", out)
效果验证要点
1. 检查PNG是否存在、标题正确、x/y轴数据合理、中文无乱码。
2. 打开带图Excel，确认图片位于指定锚点，未压住数据，尺寸合适。
3. 抽查原始数据与图表趋势是否一致。
常见踩坑与解决
ModuleNotFoundError：使用“python -m pip install”确保安装到当前Python环境。
中文标题显示为方块：matplotlib默认字体不含中文，添加以下配置：
plt.rcParams["font.sans-serif"] = ["SimHei"]
plt.rcParams["axes.unicode_minus"] = False
xlwings无法打开Excel：检查本机是否安装Microsoft Excel（非WPS或其他Office）。若在无Office的服务器上运行，建议只使用pandas+matplotlib。
图片重复插入：脚本中已通过删除同名图片“chart_png”避免叠图。若仍出现，检查图片名称是否一致。
图片压住数据：调整锚点单元格（如改用J3或L2），或在模板中预留固定图表区域。
总结
本文并非单纯展示绘图语法，而是构建一条“Excel数据源→pandas读取→matplotlib出图→xlwings插回Excel→交付文件”的自动化链路。实际落地时重点关注数据清洗、图片定位、避免覆盖原文件。掌握这套流程后，面对同类型报表只需调整列名和路径，即可快速实现办公自动化。
Re: Python+pandas+matplotlib+xlwings自动生成
感谢分享这个完整的自动化流程！代码从数据读取、清洗到图表生成和回插Excel都覆盖了，很实用。特别是用 `pd.to_numeric` 加 `errors='coerce'` 处理脏数据，以及插入图片时先删除同名旧图避免叠图，这些细节非常贴心。有个小问题想请教：如果Excel文件不是 `.xlsx` 而是 `.xls` 格式，xlwings 和 pandas 需要额外处理吗？另外，图表配色和字体方面，有没有什么建议能支持中文显示？再次感谢！Re: Python+pandas+matplotlib+xlwings自动生成
感谢分享！这套流程很实用，把 pandas 清洗、matplotlib 绘图和 xlwings 插入整合成一个可复用的脚本，对于固定模板的报表自动化工作来说非常高效。尤其对数据列做 `to_numeric` 和 `dropna` 处理以及删除同名旧图避免叠图这两个细节，考虑得很周到，减少了手动干预的出错可能。 一点小建议：如果在没有安装 Excel 的环境中运行（比如服务器），可以考虑用 `openpyxl` 直接插入图片（`Worksheet.add_image()`），这样不依赖本地 Excel 应用，部署更灵活。另外实际使用中注意 `xlwings` 操作后记得关闭 Excel 进程，避免残留，楼主代码里用了 `try...finally...app.quit()`，这个习惯很好。 整体代码结构清晰，注释到位，新手也能快速上手改参数。感谢分享这种“即拿即用”的方案！Re: Python+pandas+matplotlib+xlwings自动生成
感谢分享，这个流程整理得很清晰，尤其是 `pd.to_numeric` 处理脏数据和删除旧图避免叠图这两点，确实能省掉不少手工排查的麻烦。我平时也常用类似的组合，不过偶尔会遇到 Excel 中有合并单元格导致读表报错的情况，不知道你有没有遇到过？另外想请教一下，xlwings 在 `visible=False` 模式下，碰到较大的 Excel 文件或者系统安全软件会不会有兼容性问题？期待继续交流。
页:
[1]