Python实现批量筛选Excel数据并标黄_Python

日常处理数据时，经常会遇到一种很典型的需求：手里有一个 txt 文件，里面记录了一批需要重点关注的数据；同时还有一个几万行的 excel 表，需要把这些数据在表格里找出来并标注。如果手动搜索，每条都要复制、查找、定位、上色，几十条还能忍，几百条甚至几万条就很低效。

这篇文章记录一个实际脚本的写法：读取 5.8.txt 中的目标数据，根据 excel 表里的 文件夹名 和 id 两个字段进行严格匹配，匹配到后把对应的 id 单元格标成黄色。

需求描述

5.8.txt 中每行是一条待匹配数据，格式类似：

动物_野生动物/animals_pets_2597
文字图像_装饰性素材/mechanical_mecha_assets_10353
游戏角色_奇幻类人角色/game_characters_1302

每一行用 / 分成两个字段：

前半段：文件夹名
后半段：id

excel 表 1.xlsx 中也有对应字段：

文件夹名
id

目标是：只有当 5.8.txt 中的文件夹名和 id 同时等于 excel 当前行的文件夹名和 id 时，才把该行的 id 单元格标黄。

这比只匹配尾号更安全。比如 game_characters_1302 可能在不同文件夹名下重复出现，如果只看 id 或数字尾号，就可能误标。

整体思路

脚本的处理流程可以拆成 5 步：

读取 5.8.txt，把每行解析成 (文件夹名, id) 二元组。
打开 xlsx 文件，找到工作表里的 文件夹名 列和 id 列。
逐行读取 excel 数据，拿当前行的 (文件夹名, id) 去目标集合里查询。
如果匹配成功，就给当前行的 id 单元格设置黄色样式。
把修改后的 excel 重新保存成一个新的 .xlsx 文件。

核心判断逻辑其实很简单：

if (folder_text, id_text) in pairs:
    id_cell.set("s", str(style_id))

这里的 pairs 是从 5.8.txt 里提前整理好的目标集合。用集合查询的好处是速度快，即使 excel 有几万行，也不需要一条一条嵌套搜索。

为什么不用手动搜索

如果 5.8.txt 有 396 条数据，excel 有 28123 行，手动搜索不仅慢，还容易出现这些问题：

漏搜
搜错字段
只搜尾号导致误匹配
同一个 id 出现在多个文件夹名下时标错
复制粘贴时混入空格

脚本化处理的优势是规则固定、可重复执行、结果可复查。

xlsx 文件的本质

这个脚本没有依赖 openpyxl，而是直接处理 .xlsx 的内部结构。

.xlsx 本质上是一个 zip 压缩包，里面放着一组 xml 文件。常见文件包括：

xl/workbook.xml
xl/worksheets/sheet1.xml
xl/sharedstrings.xml
xl/styles.xml

作用大概是：

workbook.xml 记录有哪些工作表
sheet1.xml 记录单元格内容和单元格位置
sharedstrings.xml 记录共享字符串
styles.xml 记录字体、边框、填充色等样式

所以脚本主要用到了 python 标准库里的：

import zipfile
from xml.etree import elementtree as et

zipfile 用来打开和重新打包 .xlsx，elementtree 用来解析和修改 xml。

读取 txt：把目标数据变成集合

脚本中负责解析 5.8.txt 的函数是：

def folder_id_pairs_from_txt(path):
    pairs = set()
    total = 0
    bad_lines = []
    for line in path(path).read_text(encoding="utf-8").splitlines():
        text = line.strip().strip("/")
        if not text:
            continue
        total += 1
        parts = text.split("/")
        if len(parts) != 2 or not parts[0] or not parts[1]:
            bad_lines.append(text)
            continue
        pairs.add((parts[0].strip(), parts[1].strip()))
    return pairs, total, bad_lines

这段代码做了几件事：

读取 txt 文件
去掉每行前后的空格和斜杠
用 / 分割成两个字段
把合法数据放进 set
把格式异常的数据记录到 bad_lines

为什么用 set？

因为集合适合做“是否存在”的判断：

("动物_野生动物", "animals_pets_2597") in pairs

这种查询速度很快，比用列表循环查找更适合大表。

读取 excel 表头：定位字段列

excel 里不应该写死“第 3 列是文件夹名、第 4 列是 id”，因为表格列顺序可能变化。更稳妥的做法是读取第一行表头，然后根据表头名称找列。

脚本里的函数：

def header_columns(header_row, shared_strings):
    columns = {}
    for cell in header_row.findall("main:c", ns):
        col, _ = split_cell_ref(cell.get("r"))
        name = cell_text(cell, shared_strings).strip()
        if col and name:
            columns[name] = col
    return columns

例如表头是：

批次 | 分类 | 文件夹名 | id | resolution

函数会得到类似结果：

{
    "批次": "a",
    "分类": "b",
    "文件夹名": "c",
    "id": "d",
    "resolution": "e",
}

后续就可以这样拿到目标列：

folder_col = columns.get("文件夹名")
id_col = columns.get("id")

如果表头不存在，脚本会直接报错，而不是悄悄处理错列。

匹配并标黄

真正执行标黄的函数是：

def highlight_folder_id_sheet(xml_bytes, shared_strings, pairs, style_id):
    root = et.fromstring(xml_bytes)
    sheet_data = root.find("main:sheetdata", ns)
    if sheet_data is none:
        return xml_bytes, 0

    rows = sheet_data.findall("main:row", ns)
    if not rows:
        return xml_bytes, 0

    columns = header_columns(rows[0], shared_strings)
    folder_col = columns.get("文件夹名")
    id_col = columns.get("id")
    if not folder_col:
        raise runtimeerror("没有找到表头为 文件夹名 的字段")
    if not id_col:
        raise runtimeerror("没有找到表头为 id 的字段")

    marked = 0
    for row in rows[1:]:
        cells_by_col = {
            split_cell_ref(cell.get("r"))[0]: cell
            for cell in row.findall("main:c", ns)
        }
        folder_cell = cells_by_col.get(folder_col)
        id_cell = cells_by_col.get(id_col)
        if folder_cell is none or id_cell is none:
            continue
        folder_text = cell_text(folder_cell, shared_strings).strip()
        id_text = cell_text(id_cell, shared_strings).strip()
        if (folder_text, id_text) in pairs:
            id_cell.set("s", str(style_id))
            marked += 1

    return et.tostring(root, encoding="utf-8", xml_declaration=true), marked

这段逻辑重点有三个：

rows[1:]：跳过表头，从第二行开始处理
cells_by_col：把当前行的单元格按列号整理成字典
(folder_text, id_text) in pairs：两个字段同时严格匹配
id_cell.set("s", str(style_id))：给 id 单元格设置黄色样式

这里标黄的是 id 单元格，而不是整行。如果想标整行，可以把当前行的所有单元格都设置同一个样式。

写入黄色样式

excel 的颜色样式保存在 xl/styles.xml 中。脚本通过 ensure_yellow_style 往样式表里追加一个黄色填充样式：

fill = et.element(f"{{{ns['main']}}}fill")
pattern = et.subelement(fill, f"{{{ns['main']}}}patternfill", {"patterntype": "solid"})
et.subelement(pattern, f"{{{ns['main']}}}fgcolor", {"rgb": "ffffff00"})
et.subelement(pattern, f"{{{ns['main']}}}bgcolor", {"indexed": "64"})

其中：

ffffff00 表示黄色
patterntype="solid" 表示纯色填充

追加样式后，函数会返回新样式的编号：

return et.tostring(root, encoding="utf-8", xml_declaration=true), style_id

后面给单元格设置样式时，就使用这个 style_id。

命令行参数设计

脚本使用 argparse 支持命令行参数：

parser = argparse.argumentparser(description="mark xlsx rows whose trailing number appears in 5.8.txt.")
parser.add_argument("xlsx", help="要标注的 xlsx 文件")
parser.add_argument("-o", "--output", help="输出文件名，默认在原文件名后加 _marked")
parser.add_argument("--txt", default="5.8.txt", help="包含目标数据的 txt")
parser.add_argument("--yellow-folder-id", action="store_true", help="按 txt 的 文件夹名/id 两个字段严格匹配")

这样脚本就可以直接在终端运行：

python3 mark_xlsx_by_suffix.py 1.xlsx -o 1_folder_id_yellow.xlsx --yellow-folder-id

参数含义：

1.xlsx 输入 excel 文件
-o 1_folder_id_yellow.xlsx 输出 excel 文件
--yellow-folder-id 启用文件夹名 + id 严格匹配模式

python 基础语法说明

下面整理一下这个脚本里出现的 python 基础语法。

1. 导入模块

import zipfile
import tempfile
from pathlib import path
from xml.etree import elementtree as et

import 用来导入模块。from ... import ... 表示只导入模块中的某个对象。

as et 是起别名，后面可以用更短的 et 来代替 elementtree。

2. 定义函数

def cell_text(cell, shared_strings):
    return ""

def 用来定义函数。括号里是参数，函数内部通过 return 返回结果。

3. 字符串处理

text = line.strip().strip("/")
parts = text.split("/")

常见方法：

strip()      去掉字符串两端空白
strip("/")   去掉字符串两端的 /
split("/")   按 / 分割字符串

4. 条件判断

if not text:
    continue
elif args.yellow_id:
    ...
else:
    ...

python 使用 if / elif / else 做条件判断。

not text 表示字符串为空时成立。

5. 循环

for line in path(path).read_text(encoding="utf-8").splitlines():
    ...

for 用来遍历列表、集合、文件行等对象。

脚本里经常遍历：

txt 的每一行
excel 的每一行
当前行里的每个单元格

6. continue

if not text:
    continue

continue 表示跳过本轮循环，直接处理下一条数据。

这里用于跳过空行。

7. set 集合

pairs = set()
pairs.add((folder, id_value))

set 是集合，特点是：

自动去重
适合快速判断某个元素是否存在

判断是否存在：

if (folder_text, id_text) in pairs:
    ...

8. tuple 元组

(folder_text, id_text)

这是一个二元组。这里用它表示一条完整匹配条件：文件夹名 + id

这样可以避免只匹配其中一个字段导致误标。

9. dict 字典

columns = {
    "文件夹名": "c",
    "id": "d",
}

字典是键值对结构，适合通过名称找值。

比如通过表头名称找列号：

folder_col = columns.get("文件夹名")
id_col = columns.get("id")

10. 列表推导和字典推导

脚本里有这样的写法：

cells_by_col = {
    split_cell_ref(cell.get("r"))[0]: cell
    for cell in row.findall("main:c", ns)
}

这是字典推导式，可以理解为快速生成一个字典：

key：单元格所在列号
value：单元格对象

写成普通循环大概是：

cells_by_col = {}
for cell in row.findall("main:c", ns):
    col = split_cell_ref(cell.get("r"))[0]
    cells_by_col[col] = cell

11. 正则表达式

脚本中用 re 处理单元格引用和数字尾号：

m = re.match(r"([a-z]+)(\d+)$", ref or "")

这段用于把 d12 拆成：

列：d
行：12

其中：

[a-z]+ 匹配一个或多个大写字母
\d+ 匹配一个或多个数字
$ 匹配字符串结尾

12. with 上下文管理器

with zipfile.zipfile(xlsx_path, "r") as src:
    ...

with 可以自动管理资源。文件用完后会自动关闭，不需要手动调用 close()。

脚本里还用了临时目录：

with tempfile.temporarydirectory() as tmp:
    ...

处理结束后，临时目录会自动删除。

13. f-string

print(f"标注行数：{marked_count}")

f-string 是 python 中常用的字符串格式化方式。大括号里的变量会被替换成实际值。

14. 程序入口

if __name__ == "__main__":
    main()

这是 python 脚本的标准入口写法。

含义是：当这个文件被直接运行时，执行 main()；如果被其他 python 文件导入，则不自动执行。

为什么要输出新文件

脚本默认不直接覆盖原 excel，而是输出一个新文件：

python3 mark_xlsx_by_suffix.py 1.xlsx -o 1_folder_id_yellow.xlsx --yellow-folder-id

这样做的好处是：

原始数据不会被破坏
结果可以和原文件对比
如果规则写错，可以重新生成

处理数据时，这是一个很重要的习惯。

踩坑记录

这个需求看起来简单，但实际有几个容易踩坑的地方。

1. 只匹配尾号容易误标

比如：

game_characters_1302
game_characters_11302

如果只用“包含 1302”来判断，就会把后者也误标。

2. 只匹配 id 也可能不够

同一个 id 可能出现在不同 文件夹名 下。只匹配 id 时，可能会标到不属于目标分类的数据。

所以最终采用：文件夹名严格相等 + id严格相等

3. xlsx 的字符串不一定直接在单元格里

excel 为了节省空间，经常把字符串放在 sharedstrings.xml 里，单元格中只保存字符串索引。

所以脚本需要先读取共享字符串：

shared_strings = read_shared_strings(src)

再通过 cell_text() 还原单元格真实文本。

4. 表头不能写死列号

如果写死第 3 列和第 4 列，表格一旦调整列顺序，脚本就会处理错列。

根据表头名称找列，更稳。

小结

这个脚本的核心并不复杂，本质上就是：

txt 解析成目标集合
excel 逐行读取
两个字段严格匹配
匹配成功就设置黄色样式
重新保存为新 xlsx

真正需要注意的是数据匹配规则。对于批量标注任务，规则越明确，越不容易误标。相比手动搜索，脚本化处理可以把几百条甚至几万条数据的筛选和标注稳定地压缩到几秒钟完成。

完整运行命令：

python3 mark_xlsx_by_suffix.py 1.xlsx -o 1_folder_id_yellow.xlsx --yellow-folder-id

当终端输出：

txt 行数：396
提取文件夹名/id：396
标注行数：xxx
输出文件：1_folder_id_yellow.xlsx

就说明脚本已经完成处理。

以上就是python实现批量筛选excel数据并标黄的详细内容，更多关于python批量筛选excel数据的资料请关注代码网其它相关文章！

Python实现批量筛选Excel数据并标黄

2026年05月11日 • Python •我要评论