Python 实现 Excel 数据精美绘图：xlrd + Matplotlib 实战

实验课 · 课后扩展学习
关键词：xlrd、Matplotlib、LaTeX 公式、坐标点透明化、数据可视化

很多人做数据分析，第一步就卡在「怎么把 Excel 里的数据画得好看」。本实验用纯 Python 走通一条最小闭环：

  1. 用 xlrd 读取 Excel 数据
  2. 用 Matplotlib 绘制二维图像
  3. 在图上显示 LaTeX 风格公式
  4. 让坐标点透明化（alpha 通道）

全程不依赖 Excel 软件，代码可在服务器无界面环境（CI / 云开发机）批量跑，输出可直接用于报告、博客与课件。

〇、实验提示词与工具链（脱敏）

本实验源自一份课后扩展学习任务，以下为脱敏后的提示词原文（已隐去云开发机 IP、SSH 密码、Gitee 账号密码与 Token）：

实验：Python 实现 Excel 数据精美绘图
需加入课程后学习。
知识点：

  1. 使用 xlrd 扩展包读取 Excel 数据
  2. 使用 Matplotlib 绘制二维图像
  3. 显示 LaTeX 风格公式
  4. 坐标点处透明化
    基于云开发机（atomcode）开发代码，源代码推送到 Gitee 仓库。

使用工具一览（Tools）：

类别 工具 / 库 用途 (Purpose)
开发环境 云开发机 atomcode（无 GUI，Linux） 代码编写与运行
运行时 Python 3.13 + 虚拟环境 venv 隔离依赖 (isolation)
读取 xlrd 2.x 读取 .xls Excel
造数 xlwt 生成示例 .xls
计算 numpy 多项式拟合 polyfit
绘图 matplotlib (Agg 后端) 折线 / 散点 / 公式
版本控制 Git + Gitee 源码托管与推送

源码仓库地址（公开）：

https://gitee.com/LiaCin/python-excel-plot

一、整体架构

数据从 Excel 流向一张精美 PNG 图的链路如下：

┌────────────┐    xlrd     ┌────────────┐   numpy    ┌──────────────┐
│ sample.xls │ ─────────▶ │  内存二维表 │ ─────────▶ │  数值/拟合    │
│ (Excel数据)│  open_book │ (行×列)     │  polyfit   │  (x, y, 公式) │
└────────────┘            └────────────┘            └──────┬───────┘
                                                           │
                                              Matplotlib   │  plot / scatter
                                                           ▼
                                                   ┌──────────────┐
                                                   │  result.png   │
                                                   │ (二维精美图)  │
                                                   └──────────────┘

核心分工（中英文对照）：

环节 主用库 职责 (Responsibility)
读取 xlrd Excel → Python 列表/数组
计算 numpy 数值处理、多项式拟合
绘图 matplotlib 折线与散点、公式、样式
造数 xlwt 生成示例 .xls（仅教学用）

二、环境准备

pip install xlrd xlwt matplotlib numpy
组件 版本建议 说明
Python 3.8+ 本文 3.13 验证通过
xlrd 2.0+ 仅支持 .xls（见第四节踩坑）
xlwt 1.3+ 写 .xls，用于生成示例数据
matplotlib 3.x 绘图引擎
numpy 1.2x 拟合与数组运算

代码已在云开发机（无 GUI）与本地 Windows 双重验证。

三、步骤一：xlrd 读取 Excel 数据

xlrd 的 API 非常直白：打开工作簿 → 取表 → 按单元格读值。

import xlrd

def read_excel(path):
    book = xlrd.open_workbook(path)      # 打开工作簿 Workbook
    sheet = book.sheet_by_index(0)       # 取第 1 个 sheet（下标从 0 起）
    months, series_a, series_b = [], [], []
    for r in range(1, sheet.nrows):      # nrows=行数，跳过表头 row0
        months.append(str(sheet.cell_value(r, 0)))
        series_a.append(float(sheet.cell_value(r, 1)))
        series_b.append(float(sheet.cell_value(r, 2)))
    return months, series_a, series_b

关键方法（中英文对照）：

方法 含义 备注
open_workbook(path) 打开 Excel 文件 返回 Book 对象
sheet_by_index(i) 按下标取表 也可用 sheet_by_name("月度销量")
sheet.nrows / ncols 行数 / 列数 用于循环边界
cell_value(r, c) 读 (r,c) 单元格 行列均从 0 计数

四、步骤二：Matplotlib 绘制二维图像

采用「面向对象」写法（fig, ax = plt.subplots()），比 plt.plot 全局写法更易精细控制。

import matplotlib
matplotlib.use("Agg")                     # 无 GUI 环境必须切到 Agg 后端
import matplotlib.pyplot as plt
import numpy as np

fig, ax = plt.subplots(figsize=(10, 6), dpi=150)
ax.plot(x, a, "-", color="#2c7be5", lw=2.2, label="产品线 A（实测）")
ax.plot(x, b, "-", color="#e63757", lw=2.2, label="产品线 B（实测）")
ax.set_title("Excel 销量数据可视化", fontsize=14, fontweight="bold")
ax.grid(True, ls=":", alpha=0.5)
ax.legend(loc="upper left", frameon=False)
fig.tight_layout()
fig.savefig("result.png", dpi=150)

常用参数：

参数 作用 示例
figsize 画布尺寸（英寸） (10, 6)
dpi 分辨率 150 清晰，300 印刷级
color 线条/点颜色 "#2c7be5" 十六进制
lw 线宽 (line width) 2.2
label 图例文本 配合 legend()
ls 线型 (linestyle) "-" 实线，":" 点线，"--" 虚线

五、步骤三：显示 LaTeX 风格公式

Matplotlib 内置 mathtext 引擎，用 $...$ 包裹即按 LaTeX 语法渲染（无需本机装 LaTeX）。

import numpy as np

coeff = np.polyfit(x, a, 2)               # 二次多项式拟合 y = ax²+bx+c
a2, b2, c2 = coeff
formula = r"$y = %.3f\,x^2 %+.3f\,x %+.3f$" % (a2, b2, c2)

# 把公式放在图内左上角，带圆角底框
ax.text(0.03, 0.92, "拟合公式 (产品 A)：\n" + formula,
        transform=ax.transAxes, fontsize=12, color="#27a567",
        bbox=dict(boxstyle="round,pad=0.4", fc="#eafaf1", ec="#27a567", alpha=0.9))

mathtext 常用符号：上标 ^、下标 _、\, 加窄空格、\frac{a}{b} 分数、\sqrt{x} 根号。

进阶：若需完整 LaTeX（更精致字体），设 plt.rcParams["text.usetex"] = True，但要求系统已安装 LaTeX 发行版（如 TeX Live），服务器上通常没有，故本实验用 mathtext。

六、步骤四：坐标点透明化

「透明化」就是给散点加 alpha 通道（0=全透明，1=不透明）。重叠区域会因半透明叠加而自然变深，既美观又能体现数据密度。

ax.scatter(x, a, s=70, color="#2c7be5", alpha=0.45,
           edgecolors="white", linewidths=1.2, zorder=3)
ax.scatter(x, b, s=70, color="#e63757", alpha=0.45,
           edgecolors="white", linewidths=1.2, zorder=3)
参数 作用 说明
alpha 不透明度 0.45 让点半透明
s 点大小 (size) 单位面积
edgecolors 描边色 白色描边提升精致感
zorder 图层顺序 数值大者在上

七、完整代码

仓库结构：

python-excel-plot/
├── make_sample_data.py   # 生成 sample.xls / sample.xlsx 示例数据
├── excel_plot_demo.py    # 基础版：讲清四个知识点
├── excel_plot_pro.py     # 改进版：字体探测 + xlsx + 参数化 + SVG
├── sample.xls            # 示例数据（.xls）
├── sample.xlsx           # 示例数据（.xlsx）
├── result.png            # 输出位图
└── result.svg            # 输出矢量图

主脚本已整合上述四步，运行：

python make_sample_data.py     # 先造数据
python excel_plot_demo.py      # 再绘图，生成 result.png

八、真实输出

运行后得到的拟合公式与图像如下（脱敏环境实测）：

拟合公式: y = -0.585 x^2 + 9.495 x + 137.066
result.png 已生成（含 LaTeX 风格公式 + 透明坐标点）

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

可以看到：两条实测折线清晰，半透明散点让每个月的观测值「浮」在线上方而不抢戏；左上角绿色圆框内即 LaTeX 风格二次拟合公式。

九、踩坑记录

# 坑 现象 解决
1 xlrd 读不了 .xlsx xlrd.biffh.XLRDError: Excel xlsx file; not supported xlrd≥2.0 只支持 .xls；.xlsx 改用 openpyxl，或先转 .xls
2 中文变方块 标题/标签显示 □□□ 设 rcParams["font.sans-serif"]=["Microsoft YaHei","SimHei"]
3 负号变方块 - 显示成 □ 加 rcParams["axes.unicode_minus"]=False
4 服务器报 cannot connect to X server 无 GUI 环境直接 plt.show() 崩 开头 matplotlib.use("Agg") 并改用 savefig
5 图被截断 标签/标题被边缘裁掉 用 fig.tight_layout() 自动留白
6 透明点被线盖住 看不到散点 给 scatter 设 zorder 大于 plot

十、知识点对照表

实验知识点 落地的核心 API 所在章节
1. xlrd 读取 Excel open_workbook / sheet_by_index / cell_value 三
2. Matplotlib 二维绘图 subplots / plot / grid / legend 四
3. LaTeX 风格公式 mathtext + $...$ + ax.text 五
4. 坐标点透明化 scatter(..., alpha=0.45) 六

十一、小结与延伸

本实验用约 60 行代码打通了「Excel → 精美图」的闭环，四个知识点各司其职：
xlrd 负责把表格搬进内存，numpy 做拟合，matplotlib 负责呈现，mathtext 让公式有学术质感，alpha 让散点既清晰又不喧宾夺主。

可延伸方向：

  • 多 sheet 批量读取 → 一键出多图；
  • 用 openpyxl 支持 .xlsx（xlrd 的现代替代）；
  • 用 plt.style.use("ggplot") 切换主题；
  • 接入 pandas 做数据清洗后再绘图。

十二、进阶改进：让代码更稳更通用

基础版（第三~六节）重在讲清四个知识点，但直接用于生产仍有短板：中文字体硬编码、只认 .xls、参数写死、只出位图。改进版脚本 excel_plot_pro.py 一次性补齐四点。

12.1 字体自动探测（告别方块）

不再写死 "Microsoft YaHei"，而是扫描系统已装字体，按候选清单自动挑第一个可用中文字体；都找不到才告警，避免部署机缺字体时直接方块。

import matplotlib.font_manager as fm
def pick_cjk_font():
    candidates = ["Microsoft YaHei", "SimHei", "PingFang SC",
                  "Noto Sans CJK SC", "WenQuanYi Micro Hei", "DejaVu Sans"]
    installed = {f.name for f in fm.fontManager.ttflist}
    return next((n for n in candidates if n in installed), None)

12.2 统一读取接口：.xls / .xlsx 通吃

按扩展名分派：.xls 走 xlrd，.xlsx 走 openpyxl（xlrd≥2.0 已不支持 xlsx 的现代替代）。

def read_excel(path):
    if path.lower().endswith(".xls"):   return _read_xls(path)   # xlrd
    if path.lower().endswith(".xlsx"):  return _read_xlsx(path)  # openpyxl
    raise ValueError("仅支持 .xls / .xlsx")

12.3 参数化 + 异常处理

用 argparse 把输入 / 输出 / 拟合阶数 / DPI 全部外置，并对「读取失败」「点数不足」等场景给出友好报错而非堆栈。

python excel_plot_pro.py -i sample.xlsx -d 2 -D 300 --svg
# -i 输入  -d 拟合阶数  -D DPI  --svg 额外导出矢量图

12.4 矢量导出（SVG）

位图放大易糊，博客 / 论文更适合 SVG。加 --svg 即同时落盘 result.png 与 result.svg。

改进项 基础版 改进版
中文字体 写死，缺字体即方块 自动探测回退
Excel 格式 仅 .xls .xls + .xlsx
参数 硬编码 命令行可配
容错 无 异常捕获 + 退出码
输出 仅 PNG PNG + SVG

改进版已与基础版同步验证：读取 sample.xls 与 sample.xlsx 得到完全一致的结果（R²=0.4814），并成功导出 SVG。

完整源码已开源至 Gitee，欢迎 clone 改成你自己的业务数据：

https://gitee.com/LiaCin/python-excel-plot

# python # matplotlib
Logo
亚马逊云科技技术品牌专区

更多推荐

cover

Hugging Face Optimum 源码静态评测：模型部署优化框架的工程结构与验证边界

avatar 亚马逊云科技技术品牌专区
cover

智能软件工程AI4SE（一）——智能需求工程

avatar 亚马逊云科技技术品牌专区
cover

车载OBD的6路摄像头同时传，画面卡？选对物联网卡立马流畅回传

avatar 亚马逊云科技技术品牌专区