Python 玩转 Excel 案例【第 14 期】

📌 案例说明

原始数据表：

在上一期案例中，我们已经学会了如何计算排名并标记奖牌。但仅仅有文字标记还不够直观，如果能让前三名的整行数据都显示不同的背景色，数据就会更加一目了然！

目标任务：

本期案例将跟大家学习如何用 Python 给 Excel 表格添加颜色，让前三名的数据行自动染上金、银、铜色背景：

核心操作：颜色填充

• • 关键库：openpyxl 的 PatternFill
• • 核心逻辑：遍历数据行，根据奖牌列的内容给整行添加背景色

📜 完整代码

"""
销售业绩排名工具
功能：计算排名、标记奖牌、添加颜色
"""
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

# ========== 参数配置 ==========
file_path = "销售业绩表.xlsx"
output_file = "销售业绩排名.xlsx"

# 定义颜色
gold_fill = PatternFill(start_color="FFD700", end_color="FFD700", fill_type="solid")    # 金色
silver_fill = PatternFill(start_color="C0C0C0", end_color="C0C0C0", fill_type="solid")  # 银色
bronze_fill = PatternFill(start_color="CD7F32", end_color="CD7F32", fill_type="solid")  # 铜色

# 加载工作簿
wb = load_workbook(output_file)
ws = wb.active

# 找到奖牌列的列号
medal_col = None
for col_idx, cell in enumerate(ws[1], 1):
    if cell.value == "奖牌":
        medal_col = col_idx
        break

# 遍历数据行，给奖牌行加颜色
for row_idx in range(2, ws.max_row + 1):
    medal = ws.cell(row=row_idx, column=medal_col).value
    if medal and "金牌" in medal:
        for col in range(1, ws.max_column + 1):
            ws.cell(row=row_idx, column=col).fill = gold_fill
    elif medal and "银牌" in medal:
        for col in range(1, ws.max_column + 1):
            ws.cell(row=row_idx, column=col).fill = silver_fill
    elif medal and "铜牌" in medal:
        for col in range(1, ws.max_column + 1):
            ws.cell(row=row_idx, column=col).fill = bronze_fill


wb.save(output_file)

print(f"\n✅ 结果已保存：{output_file}")
print(f"   - 前3名已标记🥇🥈🥉背景色")

第一步：导入所需库

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

代码解释：

• • import pandas as pd
  导入 pandas 库。虽然本例主要用 openpyxl 操作颜色，但 pandas 会在其他部分使用，比如读取数据和计算排名。
• • from openpyxl import load_workbook
  导入 load_workbook 函数，用于加载已存在的 Excel 文件。注意这里用的是 load_workbook 而不是 Workbook，因为我们要操作的是已有文件，而不是创建新文件。
• • from openpyxl.styles import PatternFill
  导入 PatternFill 类，用于创建单元格填充样式。PatternFill 是 openpyxl 中专门设置背景色的工具。

💡 小贴士：openpyxl 主要用于 Excel 文件的格式操作（颜色、字体、边框等），而 pandas 擅长数据处理。两者结合使用可以发挥最大威力！

第二步：参数配置

file_path = "销售业绩表.xlsx"
output_file = "销售业绩排名.xlsx"

代码解释：

• • file_path
  源文件路径，这是原始数据文件。
• • output_file
  输出文件路径，这是已经包含排名和奖牌的结果文件。颜色填充操作就是基于这个文件进行的。

💡 小贴士：将参数放在代码开头，方便后续修改和维护。

第三步：定义颜色样式

# 定义颜色
gold_fill = PatternFill(start_color="FFD700", end_color="FFD700", fill_type="solid")    # 金色
silver_fill = PatternFill(start_color="C0C0C0", end_color="C0C0C0", fill_type="solid")  # 银色
bronze_fill = PatternFill(start_color="CD7F32", end_color="CD7F32", fill_type="solid")  # 铜色

PatternFill 详解

PatternFill 是 openpyxl 中用于设置单元格填充样式的类。

参数解析

颜色代码对照

fill_type 可选值

为什么这样写？

gold_fill = PatternFill(start_color="FFD700", end_color="FFD700", fill_type="solid")

• • 创建一个金色填充样式对象
• • 一次创建，多次使用（后面所有金牌行都复用这个对象）
• • 避免重复创建相同样式，提高性能
• • 颜色集中管理，修改时只需改一处

💡 小贴士：颜色代码前可以加 # 也可以不加，openpyxl 都能识别。例如 "#FFD700" 和 "FFD700" 效果相同。

第四步：加载工作簿和工作表

# 加载工作簿
wb = load_workbook(output_file)
ws = wb.active

代码解释：

• • load_workbook(output_file)
  加载已存在的 Excel 文件，返回一个 Workbook 对象。这里加载的是已经计算好排名和奖牌的文件。
• • wb.active
  获取当前活动的工作表。如果文件只有一个工作表，就是那个工作表；如果有多个，默认返回第一个。

Workbook 和 Worksheet 的关系：

Excel 文件 (.xlsx)
    └── Workbook 对象 (wb)          # 代表整个文件
        └── Worksheet 对象 (ws)      # 代表一个工作表
            └── Cell 对象            # 代表单元格

💡 小贴士：如果需要操作指定名称的工作表，可以用 wb["工作表名称"]。

第五步：找到奖牌列的列号

# 找到奖牌列的列号
medal_col = None
for col_idx, cell in enumerate(ws[1], 1):
    if cell.value == "奖牌":
        medal_col = col_idx
        break

代码详解

ws[1] 的含义

• • ws[1] 表示工作表的第一行（标题行）
• • 在 openpyxl 中，用索引获取行，ws[1] 是第一行，ws[2] 是第二行，以此类推
• • 返回的是一个元组，包含该行所有单元格对象

enumerate(ws[1], 1) 的用法

enumerate(ws[1], 1)  # 从 1 开始编号

• • enumerate 函数用于遍历序列时同时获取索引和值
• • 第二个参数 1 表示索引从 1 开始计数（因为 Excel 列号从 1 开始）
• • 等价于 enumerate(ws[1], start=1)，显式写出 start= 可读性更好

执行过程示意：

标题行内容：["姓名", "部门", "销售额", "公司排名", "部门排名", "奖牌"]
            ↓ enumerate(ws[1], 1)
(1, 单元格"姓名") → 不是"奖牌"，继续
(2, 单元格"部门") → 不是"奖牌"，继续
(3, 单元格"销售额") → 不是"奖牌"，继续
(4, 单元格"公司排名") → 不是"奖牌"，继续
(5, 单元格"部门排名") → 不是"奖牌"，继续
(6, 单元格"奖牌") → 是"奖牌"！medal_col = 6，结束循环

为什么需要找列号？

Excel 表格的列位置可能不固定，通过查找标题名称来确定列号，让代码更健壮。即使以后在奖牌列前面插入新列，代码依然能正确工作。

💡 小贴士：这种方法比硬编码列号（如 column=6）更灵活，推荐使用。

第六步：遍历数据行并填充颜色

# 遍历数据行，给奖牌行加颜色
for row_idx in range(2, ws.max_row + 1):
    medal = ws.cell(row=row_idx, column=medal_col).value
    if medal and "金牌" in medal:
        for col in range(1, ws.max_column + 1):
            ws.cell(row=row_idx, column=col).fill = gold_fill
    elif medal and "银牌" in medal:
        for col in range(1, ws.max_column + 1):
            ws.cell(row=row_idx, column=col).fill = silver_fill
    elif medal and "铜牌" in medal:
        for col in range(1, ws.max_column + 1):
            ws.cell(row=row_idx, column=col).fill = bronze_fill

6.1 外层循环：遍历数据行

for row_idx in range(2, ws.max_row + 1):

• • range(2, ws.max_row + 1)：从第 2 行开始遍历到最后一行
• • 为什么要加 1？因为 range() 是左闭右开区间，不包含结束值
• • 第 1 行是标题行，不需要处理

示例：如果 ws.max_row = 51

• • range(2, 52) 遍历：2, 3, 4, ..., 51（共 50 行数据）

6.2 获取奖牌值

medal = ws.cell(row=row_idx, column=medal_col).value

• • ws.cell(row=row_idx, column=medal_col)：获取指定单元格对象
• • .value：获取单元格的值，如 "🥇 金牌"、"🥈 银牌" 或空字符串

6.3 条件判断

if medal and "金牌" in medal:

• • medal and ...：先判断 medal 不为空（避免 None 值报错）
• • "金牌" in medal：判断字符串中是否包含"金牌"字样

为什么用 in 而不是 ==？

奖牌列的值是 "🥇 金牌"，包含表情符号和文字。用 in 可以匹配文字部分，更加灵活。即使以后表情符号变了，代码仍然有效。

6.4 内层循环：填充整行

for col in range(1, ws.max_column + 1):
    ws.cell(row=row_idx, column=col).fill = gold_fill

• • range(1, ws.max_column + 1)：从第 1 列遍历到最后一列
• • ws.cell(row=row_idx, column=col).fill = gold_fill：给每个单元格设置填充颜色

为什么要遍历所有列？

因为要给整行添加背景色，所以需要依次设置该行每个单元格的 fill 属性。

执行过程示意（金牌行）：

第 3 行是金牌（row_idx=3）
    ↓
col=1：A3单元格.fill = 金色
col=2：B3单元格.fill = 金色
col=3：C3单元格.fill = 金色
...
col=6：F3单元格.fill = 金色

6.5 ws.max_row 和 ws.max_column 详解

这两个属性是 openpyxl 中非常有用的工具：

注意：max_row 和 max_column 返回的是有数据的最后位置，如果中间有空行或空列，不会影响结果。

第七步：保存文件

wb.save(output_file)

print(f"\n✅ 结果已保存：{output_file}")
print(f"   - 前3名已标记🥇🥈🥉背景色")

代码解释：

• • wb.save(output_file)：将修改后的工作簿保存到文件
• • 如果文件已存在，会覆盖原文件
• • 建议保存到不同文件名，避免数据丢失

运行结果预览

运行代码后，销售业绩排名.xlsx 中的前三名会被自动染上颜色：

效果特点：

• • 金牌行整行显示金色背景
• • 银牌行整行显示银色背景
• • 铜牌行整行显示铜色背景
• • 其他行保持原有样式

📚 本期核心知识点

📍 知识点 1：PatternFill 创建填充样式

from openpyxl.styles import PatternFill

# 创建纯色填充对象
gold_fill = PatternFill(start_color="FFD700", end_color="FFD700", fill_type="solid")

• • start_color 和 end_color：颜色代码（十六进制）
• • fill_type="solid"：纯色填充
• • 一次创建，多次使用，提高性能

📍 知识点 2：load_workbook() 加载已有文件

from openpyxl import load_workbook

wb = load_workbook("文件名.xlsx")  # 加载整个文件
ws = wb.active                      # 获取活动工作表

• • 用于打开已存在的 Excel 文件进行修改
• • 区别于 Workbook()（创建新文件）

📍 知识点 3：ws[行号] 获取行对象

first_row = ws[1]           # 获取第一行（标题行）
all_cells = ws[1]           # 返回该行所有单元格的元组

• • ws[1] 表示第一行，ws[2] 表示第二行
• • 返回的是该行所有单元格的元组对象

📍 知识点 4：enumerate() 同时获取索引和值

for col_idx, cell in enumerate(ws[1], 1):
    # col_idx: 列号（1,2,3...）
    # cell: 单元格对象

• • 第二个参数控制起始索引
• • 等价于 enumerate(ws[1], start=1)

📍 知识点 5：ws.cell() 访问单元格

# 获取单元格
cell = ws.cell(row=2, column=3)

# 获取单元格值
value = ws.cell(row=2, column=3).value

# 设置单元格样式
ws.cell(row=2, column=3).fill = gold_fill

• • row：行号（从 1 开始）
• • column：列号（从 1 开始）
• • 可以链式操作获取或设置属性

📍 知识点 6：ws.max_row 和 ws.max_column

last_row = ws.max_row      # 有数据的最大行号
last_col = ws.max_column   # 有数据的最大列号

• • 自动检测工作表的数据范围
• • 常用于循环遍历的边界控制

📍 知识点 7：range(start, stop) 的边界特性

range(2, ws.max_row + 1)   # 包含最后一行
range(2, ws.max_row)       # 不包含最后一行（错误写法）

• • range() 是左闭右开区间，不包含 stop 值
• • 要包含结束值，需要 stop + 1

🔄 本案例核心流程

加载已排名的 Excel 文件
        ↓
获取活动工作表
        ↓
查找"奖牌"列的列号（遍历标题行）
        ↓
遍历数据行（从第 2 行开始）
        ↓
读取奖牌列的值
        ↓
判断奖牌类型（金牌/银牌/铜牌）
        ↓
遍历该行的所有列
        ↓
为每个单元格设置对应颜色
        ↓
保存文件

💡 常见问题解答

Q1：为什么要先查找奖牌列，而不是直接写死列号？

A：如果以后在表格前面插入新列，写死列号的代码就会出错。通过查找标题名称来定位列，代码更健壮、更灵活。

Q2：为什么用 "金牌" in medal 而不是 medal == "🥇 金牌"？

A：因为奖牌列的值包含表情符号，不同系统显示可能不同。用 in 判断文字部分更稳定，即使表情符号变了也能正确识别。

Q3：为什么遍历行时用 range(2, ws.max_row + 1) 而不是直接写数字？

A：因为数据行数可能变化，用 ws.max_row 可以自动适应不同长度的数据表，无需手动修改代码。

Q4：给整行填充颜色为什么要遍历所有列？

A：Excel 中每个单元格的样式是独立的，必须逐个设置。目前我没有找到直接给整行设置颜色的快捷方法。

Q5：PatternFill 创建的颜色对象可以重复使用吗？

A：可以！这正是将颜色对象定义为变量的好处。一次创建，所有需要该颜色的单元格都使用同一个对象，节省内存。

📦 资源获取提示

关注「码农自习室」，后台回复关键词 PythonExcel案例，即可获取本文完整代码及配套练习数据集，一起动手掌握高效数据操作的核心技巧！

❤️ 支持我们

如果本文对你有帮助，欢迎点赞 + 关注 + 点亮小红心，你的支持就是我们持续创作的最大动力～