Python 玩转 Excel 案例【第 19 期】

📌 案例说明

原始数据表（sales.xlsx）：

这是销售数据分析中非常常见的场景：需要快速识别高销售额记录，并对其进行可视化标注。

原始数据是销售员的业绩明细，我们需要：

1. 1. 读取 Excel 中的销售数据
2. 2. 自动定位销售额所在的列
3. 3. 将销售额大于 5000 的行用绿色背景标注
4. 4. 保存文件，让高业绩一目了然

在 Excel 中，这类需求通常使用条件格式功能手动设置，但如果文件需要反复生成、或需要批量处理多个文件，手动操作就变得繁琐且容易出错。

本期案例将跟大家一起学习如何用 Python 自动为 Excel 数据添加条件格式标注。

核心操作：动态列定位 + 条件判断 + 样式填充

• • 关键库：pandas、openpyxl、pathlib
• • 核心逻辑：用 pandas 处理数据，用 openpyxl 精确控制 Excel 样式

📜 完整代码

"""
销售数据标注 - 自动识别高销售额行并标绿
使用 pandas + openpyxl 实现类似 Excel 条件格式的功能
"""
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from pathlib import Path

file_path = Path('sales.xlsx')

# 生成并写入初始数据
df = pd.DataFrame({
    '销售员': ['张三', '李四', '王五', '赵六'],
    '销售额': [3000, 8000, 4500, 12000]
})

# 保存初始数据到文件
with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Sheet1', index=False)

# 加载工作簿并添加背景色
wb = load_workbook(file_path)
ws = wb['Sheet1']

# 定位销售额列
sales_col = next((col for col in range(1, ws.max_column + 1)
                  if ws.cell(1, col).value == '销售额'), None)
if not sales_col:
    raise ValueError("未找到'销售额'列")

# 批量标绿（从第2行开始，跳过表头）
green_fill = PatternFill(start_color='90EE90', end_color='90EE90', fill_type='solid')
for row in range(2, ws.max_row + 1):
    if ws.cell(row, sales_col).value > 5000:
        for col in range(1, ws.max_column + 1):
            ws.cell(row, col).fill = green_fill

wb.save(file_path)
print("✅ 高销售额行已标记完成")

运行结果（sales.xlsx）：

💡 结果分析：李四（8000）和赵六（12000）两行的背景色已变为绿色，高销售额一目了然。

第一步：导入所需库

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

代码解释：

• • import pandas as pd
  导入 pandas 库，这是 Python 数据分析的核心武器。它提供了 DataFrame 数据结构，方便创建和操作表格数据。
• • from openpyxl import load_workbook
  导入 openpyxl 的 load_workbook 函数，用于加载已有的 Excel 文件并操作其样式。
• • from openpyxl.styles import PatternFill
  导入 PatternFill 类，用于设置单元格的填充样式（背景色）。
• • from pathlib import Path
  导入 Path 类，这是 Python 3.4+ 推荐的处理文件路径的方式，比传统的 os.path 更加面向对象、代码更优雅。

💡 小贴士：pandas 适合数据处理，openpyxl 擅长样式控制，两者结合可以发挥各自优势。

第二步：创建并保存初始数据

file_path = Path('sales.xlsx')

# 生成并写入初始数据
df = pd.DataFrame({
    '销售员': ['张三', '李四', '王五', '赵六'],
    '销售额': [3000, 8000, 4500, 12000]
})

# 保存初始数据到文件
with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Sheet1', index=False)  

代码解释：

• • file_path = Path('sales.xlsx')
  使用 Path 创建文件路径对象，便于后续操作。
• • pd.DataFrame({...})
  创建一个 DataFrame（数据表），包含"销售员"和"销售额"两列数据。
• • with pd.ExcelWriter(...) as writer:
  使用上下文管理器 with 打开 Excel 写入器，确保文件操作完成后自动关闭。
• • engine='openpyxl'
  指定使用 openpyxl 引擎。pandas 本身不直接读写 Excel 文件，而是依赖第三方库作为"引擎"。本案例之所以必须指定 openpyxl，是因为后续需要用 load_workbook() 对文件进行样式修改，而 openpyxl 是唯一同时支持 pandas 写入和自身样式修改的引擎。如果不指定，可能默认使用其他引擎导致后续操作失败。
• • df.to_excel(writer, sheet_name='Sheet1', index=False)
  将 DataFrame 写入 Excel 的 Sheet1 工作表，index=False 表示不写入行号。

💡 小贴士：使用 with 语句是 Python 的最佳实践，即使发生异常也能保证文件正确关闭。

第三步：加载工作簿

wb = load_workbook(file_path)
ws = wb['Sheet1']

代码解释：

• • load_workbook(file_path)
  加载刚刚保存的 Excel 文件，返回一个 Workbook 对象。
• • ws = wb['Sheet1']
  获取名为"Sheet1"的工作表对象，后续的样式操作都在这个工作表上进行。

第四步：动态定位销售额列

sales_col = next((col for col in range(1, ws.max_column + 1)
                  if ws.cell(1, col).value == '销售额'), None)
if not sales_col:
    raise ValueError("未找到'销售额'列")

这是本期案例的核心亮点——无需硬编码列号，自动找到"销售额"在哪一列。

4.1 为什么要动态定位？

传统的写法往往假设"销售额"在第 2 列：

# 不推荐的硬编码写法
if ws.cell(row, 2).value > 5000:

但如果数据源发生变化，比如：

• • 增加了"日期"列，"销售额"变成了第 3 列
• • 列顺序被调整

硬编码的代码就会出错。动态定位则能适应这些变化，代码更健壮。

4.2 生成器表达式详解

(col for col in range(1, ws.max_column + 1)
 if ws.cell(1, col).value == '销售额')

这是一个生成器表达式，逐列检查第 1 行（表头）的值是否等于"销售额"。

• • range(1, ws.max_column + 1)：从第 1 列遍历到最后一列（openpyxl 的列索引从 1 开始）
• • ws.cell(1, col).value：获取第 1 行、第 col 列的单元格值
• • if ... == '销售额'：筛选出表头为"销售额"的列号

4.3 next() 函数的作用

sales_col = next(..., None)

• • next(生成器, 默认值)：获取生成器的第一个元素
• • 如果找到"销售额"列，返回列号
• • 如果遍历完都没找到，返回 None

4.4 异常处理

if not sales_col:
    raise ValueError("未找到'销售额'列")

如果 sales_col 为 None，说明文件中没有"销售额"列，抛出明确的错误提示。

💡 小贴士：next(生成器, None) 这种写法是 Pythonic 风格，比用 for 循环 + break 更简洁优雅。

第五步：设置绿色填充样式

green_fill = PatternFill(start_color='90EE90', end_color='90EE90', fill_type='solid')

代码解释：

• • PatternFill
  openpyxl 中用于定义单元格填充样式的类。
• • start_color='90EE90'
  填充的起始颜色，十六进制颜色码 #90EE90 是一种浅绿色。
• • end_color='90EE90'
  填充的结束颜色，与起始颜色相同表示纯色填充。
• • fill_type='solid'
  填充类型为"纯色"。

常用颜色码参考：

第六步：批量标注高销售额行

for row in range(2, ws.max_row + 1):
    if ws.cell(row, sales_col).value > 5000:
        for col in range(1, ws.max_column + 1):
            ws.cell(row, col).fill = green_fill

代码解释：

• • for row in range(2, ws.max_row + 1):
  从第 2 行开始遍历到最后一行的数据行（第 1 行是表头，跳过）。
• • if ws.cell(row, sales_col).value > 5000:
  检查当前行"销售额"列的值是否大于 5000。
• • for col in range(1, ws.max_column + 1):
  如果条件满足，遍历该行的所有列。
• • ws.cell(row, col).fill = green_fill
  为当前单元格设置绿色填充样式。

执行过程示意：

第七步：保存文件

wb.save(file_path)
print("✅ 高销售额行已标记完成")

代码解释：

• • wb.save(file_path)
  将修改后的工作簿保存回原文件。
• • print(...)
  输出成功提示，让用户知道程序执行完成。

📚 本期核心知识点

📍 知识点 1：pathlib.Path 文件路径处理

from pathlib import Path
file_path = Path('sales.xlsx')

为什么用 pathlib 而不是 os.path？

💡 小贴士：Python 3.4 引入 pathlib，官方推荐用于新项目。

📍 知识点 2：next() + 生成器表达式

sales_col = next((col for col in range(1, max_col+1)
                  if condition), None)

• • 作用：从生成器中获取第一个满足条件的元素
• • 优势：简洁、高效、Pythonic
• • 对比传统写法：

# 传统写法（繁琐）
sales_col = None
for col in range(1, ws.max_column + 1):
    if ws.cell(1, col).value == '销售额':
        sales_col = col
        break

📍 知识点 3：PatternFill 单元格填充

fill = PatternFill(start_color='90EE90', 
                   end_color='90EE90', 
                   fill_type='solid')

📍 知识点 4：openpyxl 行列索引

ws.cell(row, col)

• • 重要：openpyxl 的行列索引从 1 开始，不是从 0 开始
• • ws.cell(1, 1) 表示第 1 行第 1 列（即 A1 单元格）
• • 这与 Python 的 0-based 索引习惯不同，需要特别注意

🔄 本案例核心流程

① 创建数据 → ② 保存 Excel → ③ 加载工作簿 → ④ 定位目标列 → ⑤ 设置样式 → ⑥ 条件判断标注 → ⑦ 保存文件

创建销售数据 (DataFrame)
        ↓
保存为 Excel 文件 (ExcelWriter)
        ↓
加载工作簿 (load_workbook)
        ↓
动态定位销售额列 (next + 生成器)
        ↓
定义绿色填充样式 (PatternFill)
        ↓
遍历数据行，条件满足则整行标绿
        ↓
保存并输出成功提示

🗳️ 点单时间到！ 🗳️

下期写什么？
你来定，我来写。

评论区见！👇

📦 资源获取提示

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

❤️ 支持我们

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