个人主页杨利杰YJlio❄️个人专栏《Sysinternals实战教程》 《Windows PowerShell 实战》 《WINDOWS教程》 《IOS教程》《微信助手》 《锤子助手》 《Python》 《Kali Linux》《那些年未解决的Windows疑难杂症》让复杂的事情更简单让重复的工作自动化《超简单用 Python 让 Excel 飞起来》读书笔记第7章 案例02 在 Python 中导入 Excel 数据制作简单的图表1. 问题背景为什么要用 Python 从 Excel 自动生成图表2. 适用场景与限制条件3. 环境准备先把依赖装稳不然后面全是坑4. 核心原理读取 Excel → 生成图表 PNG5. 把图表插回 Excelxlwings 控制插图位置6. 合体脚本一键完成“读 Excel → 出图 → 插回 Excel”7. 效果验证不要只看“有没有报错”7.1 验证 PNG 是否生成7.2 验证 Excel 是否成功插图7.3 验证数据是否一致8. 常见问题与踩坑记录8.1 ModuleNotFoundError找不到 pandas / matplotlib / xlwings8.2 中文标题显示为方块8.3 xlwings 打不开 Excel8.4 图片重复插入8.5 图片位置压住原始数据9. 总结提升这节真正要掌握的是“自动化交付链路”1. 问题背景为什么要用 Python 从 Excel 自动生成图表在日常办公里Excel 做图表并不难真正麻烦的是重复做图。比如每周都要从报表里取一列数据生成趋势图再把图表放回 Excel 或汇报文件里。一次两次还能手工点次数多了就会变成纯体力劳动。这一节对应《超简单用 Python 让 Excel 飞起来》第 7 章案例 02在 Python 中导入 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 并生成 PNGpandas matplotlib 就够了如果要把 PNG 插回 Excel 并保持办公交付格式再使用 xlwings。3. 环境准备先把依赖装稳不然后面全是坑这一节用到 4 个核心库pandas、matplotlib、openpyxl、xlwings。它们各自负责的事情不一样不要混在一起理解。依赖库作用pandas读取 Excel 数据、清洗数据、整理 DataFrameopenpyxl作为 pandas 读取.xlsx文件时常用的底层引擎matplotlib根据数据生成图表并保存为 PNG 图片xlwings打开 Excel把图表图片插回指定 Sheet 和单元格位置安装命令如下pipinstallpandas matplotlib openpyxl xlwings这张图展示了本文所需依赖的安装环境和 Python 图表自动化基础组件。从这张图中可以看出环境准备不是随便装几个库就结束了。pandas 负责读数据matplotlib 负责出图openpyxl 负责 Excel 文件解析xlwings 负责和 Excel 应用交互。少一个环节整条自动化链路就不完整。安装完成后可以用下面的代码快速自检importpandasaspdimportmatplotlib.pyplotaspltimportxlwingsasxwprint(环境检测通过)如果这里报错先不要急着写业务代码。环境没稳定之前后面的错误很容易被误判成代码问题。4. 核心原理读取 Excel → 生成图表 PNG先把最核心的一步单独拆出来从 Excel 读取数据然后用 matplotlib 生成一张图表 PNG。只要这一步能跑通后面插回 Excel 就只是交付格式的问题。假设 Excel 文件名叫report.xlsx工作表名叫数据里面有两列月份工单数量2026-011202026-02982026-03135这里的关键点是列名要干净、数据结构要稳定、x 轴和 y 轴要能一一对应。如果列名有多余空格、合并单元格、表头不在第一行脚本就需要额外处理。整体流程可以理解为下面这样读取 Excel 文件选择需要的列清洗数据matplotlib 绘制图表保存为 PNG 图片这张图展示了从 Excel 表格读取数据并通过 Python 生成图表文件的过程。从这张图中可以看出Excel 不是最终结果而是数据来源PNG 图表才是后续插入报表、博客、汇报材料的中间成果。这个设计很稳因为 PNG 是通用格式后续无论写回 Excel、插入 Word还是上传 CSDN都比较方便。下面是第一段核心代码读取 Excel 并生成图表 PNG。importpandasaspdimportmatplotlib.pyplotaspltfrompathlibimportPathdefexcel_to_chart_png(xlsx_path:str,sheet_name:str,x_col:str,y_col:str,out_png:str):# 1. 读取 Excel 数据dfpd.read_excel(xlsx_path,sheet_namesheet_name)# 2. 取出 x 轴和 y 轴数据xdf[x_col].astype(str)ypd.to_numeric(df[y_col],errorscoerce)# 3. 组合并清理空值datapd.DataFrame({x_col:x,y_col:y}).dropna()# 4. 绘制折线图plt.figure(figsize(8,4))plt.plot(data[x_col],data[y_col],markero)plt.title(f{y_col}趋势图)plt.xlabel(x_col)plt.ylabel(y_col)plt.xticks(rotation30)plt.grid(axisy,linestyledashed,alpha0.4)plt.tight_layout()# 5. 保存图片out_pngPath(out_png)out_png.parent.mkdir(parentsTrue,exist_okTrue)plt.savefig(out_png,dpi200)plt.close()returnstr(out_png)if__name____main__:pngexcel_to_chart_png(xlsx_pathreport.xlsx,sheet_name数据,x_col月份,y_col工单数量,out_pngout/chart.png)print(图表已输出,png)这段代码最值得注意的是pd.to_numeric(..., errorscoerce)。真实 Excel 中经常会出现数字列夹杂空格、文本、异常字符的情况直接画图可能报错。这里先转成数值无法转换的内容变成空值再通过dropna()清掉。这一步的本质是先把 Excel 中“看起来像数字”的内容转换成 Python 真正能计算和绘图的数值。5. 把图表插回 Excelxlwings 控制插图位置前面已经能生成 PNG 了但在真实办公里很多时候不能只给一张图片还要把图表插回 Excel 报表里让业务同事或领导直接打开一个文件就能看到数据和图表。这一步使用 xlwings 完成核心动作是打开 Excel 工作簿定位目标 Sheet定位插图锚点单元格例如H2删除旧图避免重复叠图插入新图调整宽高保存为新文件这张图展示了使用 xlwings 将生成的图表插入到 Excel 指定单元格位置。从这张图中可以看出插图不是“随便丢到 Excel 里”而是要有明确锚点。比如把图片放到H2就可以避免覆盖原始数据区域也方便后续形成固定报表模板。下面是把 PNG 插回 Excel 的代码importxlwingsasxwfrompathlibimportPathdefinsert_png_to_excel(xlsx_path:str,sheet_name:str,png_path:str,anchor_cell:strH2,out_xlsx:str|NoneNone):xlsx_pathstr(Path(xlsx_path).resolve())png_pathstr(Path(png_path).resolve())ifout_xlsxisNone:pPath(xlsx_path)out_xlsxstr(p.with_name(p.stem_已插图p.suffix))appxw.App(visibleFalse,add_bookFalse)try:wbapp.books.open(xlsx_path)shtwb.sheets[sheet_name]cellsht.range(anchor_cell)# 删除同名旧图避免重复插入后图表叠在一起forpicinlist(sht.pictures):ifpic.namechart_png:pic.delete()sht.pictures.add(png_path,namechart_png,leftcell.left,topcell.top)picsht.pictures[chart_png]pic.width520pic.height320wb.save(out_xlsx)wb.close()finally:app.quit()returnout_xlsxif__name____main__:outinsert_png_to_excel(xlsx_pathreport.xlsx,sheet_name数据,png_pathout/chart.png,anchor_cellH2)print(已写回 Excel,out)这里一定要注意不要默认覆盖原文件。尤其是在真实办公数据里脚本如果写错位置可能会覆盖原始数据或破坏模板。更稳的做法先另存为一个新文件例如report_已插图.xlsx确认图表位置、大小、数据都正确后再决定是否覆盖原文件。6. 合体脚本一键完成“读 Excel → 出图 → 插回 Excel”单独看每一段代码不难真正有价值的是把它们组合成一个可以复用的自动化脚本。这样以后换一个 Excel 文件只需要改文件名、Sheet 名、x 轴列名、y 轴列名就可以直接生成交付版报表。这张图展示了完整的一键交付流程读取 Excel 数据、生成图表 PNG、插入 Excel、保存最终文件。从这张图中可以看出完整自动化的重点是流程闭环。如果只生成图片还不算真正交付如果只插图但没有数据清洗也容易出现错误图表。稳定的方案应该把读取、清洗、出图、插入、保存全部串起来。完整流程如下准备 Excel 文件pandas 读取数据选择 x/y 字段清洗数值列matplotlib 生成 PNGxlwings 打开 Excel插入图片到指定单元格另存为交付文件人工打开验证结果下面是合体后的完整版本importpandasaspdimportmatplotlib.pyplotaspltimportxlwingsasxwfrompathlibimportPathdefexcel_to_chart_png(xlsx_path,sheet_name,x_col,y_col,out_png):dfpd.read_excel(xlsx_path,sheet_namesheet_name)xdf[x_col].astype(str)ypd.to_numeric(df[y_col],errorscoerce)datapd.DataFrame({x_col:x,y_col:y}).dropna()plt.figure(figsize(8,4))plt.plot(data[x_col],data[y_col],markero)plt.title(f{y_col}趋势图)plt.xlabel(x_col)plt.ylabel(y_col)plt.xticks(rotation30)plt.grid(axisy,linestyledashed,alpha0.4)plt.tight_layout()out_pngPath(out_png)out_png.parent.mkdir(parentsTrue,exist_okTrue)plt.savefig(out_png,dpi200)plt.close()returnstr(out_png)definsert_png_to_excel(xlsx_path,sheet_name,png_path,anchor_cellH2,out_xlsxNone):xlsx_pathstr(Path(xlsx_path).resolve())png_pathstr(Path(png_path).resolve())ifout_xlsxisNone:pPath(xlsx_path)out_xlsxstr(p.with_name(p.stem_已插图p.suffix))appxw.App(visibleFalse,add_bookFalse)try:wbapp.books.open(xlsx_path)shtwb.sheets[sheet_name]cellsht.range(anchor_cell)forpicinlist(sht.pictures):ifpic.namechart_png:pic.delete()sht.pictures.add(png_path,namechart_png,leftcell.left,topcell.top)picsht.pictures[chart_png]pic.width520pic.height320wb.save(out_xlsx)wb.close()finally:app.quit()returnout_xlsxif__name____main__:xlsxreport.xlsxsheet数据x_col月份y_col工单数量pngexcel_to_chart_png(xlsx_pathxlsx,sheet_namesheet,x_colx_col,y_coly_col,out_pngout/chart.png)outinsert_png_to_excel(xlsx_pathxlsx,sheet_namesheet,png_pathpng,anchor_cellH2)print(完成交付文件,out)实际落地时主要修改下面 4 个变量即可xlsxreport.xlsxsheet数据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-mpipinstallpandas matplotlib openpyxl xlwings8.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并保存成业务同事能直接打开的文件才算真正具备办公自动化价值。我建议后续写类似脚本时始终按三个层次来设计数据层数据从哪里来字段是否稳定数值是否需要清洗图表层用什么图表达标题、坐标轴、大小是否清晰交付层图表输出到哪里是 PNG、Excel、Word还是博客配图。最容易翻车的地方不是绘图语法而是数据源不规范、图片插入位置不固定、Excel 进程没有关闭、原始文件被覆盖。所以这篇笔记最值得沉淀的不是代码本身而是这套“读数据、出图、写回、验证”的标准流程。返回顶部