日常處理數(shù)據(jù)時(shí)，經(jīng)常會(huì)遇到一種很典型的需求：手里有一個(gè) txt 文件，里面記錄了一批需要重點(diǎn)關(guān)注的數(shù)據(jù)；同時(shí)還有一個(gè)幾萬(wàn)行的 Excel 表，需要把這些數(shù)據(jù)在表格里找出來(lái)并標(biāo)注。如果手動(dòng)搜索，每條都要復(fù)制、查找、定位、上色，幾十條還能忍，幾百條甚至幾萬(wàn)條就很低效。

這篇文章記錄一個(gè)實(shí)際腳本的寫法：讀取 5.8.txt 中的目標(biāo)數(shù)據(jù)，根據(jù) Excel 表里的 文件夾名 和 id 兩個(gè)字段進(jìn)行嚴(yán)格匹配，匹配到后把對(duì)應(yīng)的 id 單元格標(biāo)成黃色。

需求描述

5.8.txt 中每行是一條待匹配數(shù)據(jù)，格式類似：

動(dòng)物_野生動(dòng)物/Animals_Pets_2597
文字圖像_裝飾性素材/Mechanical_Mecha_Assets_10353
游戲角色_奇幻類人角色/Game_Characters_1302

每一行用 / 分成兩個(gè)字段：

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

Excel 表 1.xlsx 中也有對(duì)應(yīng)字段：

文件夾名
id

目標(biāo)是：只有當(dāng) 5.8.txt 中的 文件夾名 和 id 同時(shí)等于 Excel 當(dāng)前行的 文件夾名 和 id 時(shí)，才把該行的 id 單元格標(biāo)黃。

這比只匹配尾號(hào)更安全。比如 Game_Characters_1302 可能在不同文件夾名下重復(fù)出現(xiàn)，如果只看 id 或數(shù)字尾號(hào)，就可能誤標(biāo)。

整體思路

腳本的處理流程可以拆成 5 步：

1. 讀取 5.8.txt，把每行解析成 (文件夾名, id) 二元組。
2. 打開(kāi) xlsx 文件，找到工作表里的 文件夾名 列和 id 列。
3. 逐行讀取 Excel 數(shù)據(jù)，拿當(dāng)前行的 (文件夾名, id) 去目標(biāo)集合里查詢。
4. 如果匹配成功，就給當(dāng)前行的 id 單元格設(shè)置黃色樣式。
5. 把修改后的 Excel 重新保存成一個(gè)新的 .xlsx 文件。

核心判斷邏輯其實(shí)很簡(jiǎn)單：

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

這里的 pairs 是從 5.8.txt 里提前整理好的目標(biāo)集合。用集合查詢的好處是速度快，即使 Excel 有幾萬(wàn)行，也不需要一條一條嵌套搜索。

為什么不用手動(dòng)搜索

如果 5.8.txt 有 396 條數(shù)據(jù)，Excel 有 28123 行，手動(dòng)搜索不僅慢，還容易出現(xiàn)這些問(wèn)題：

• 漏搜
• 搜錯(cuò)字段
• 只搜尾號(hào)導(dǎo)致誤匹配
• 同一個(gè) id 出現(xiàn)在多個(gè)文件夾名下時(shí)標(biāo)錯(cuò)
• 復(fù)制粘貼時(shí)混入空格

腳本化處理的優(yōu)勢(shì)是規(guī)則固定、可重復(fù)執(zhí)行、結(jié)果可復(fù)查。

xlsx 文件的本質(zhì)

這個(gè)腳本沒(méi)有依賴 openpyxl，而是直接處理 .xlsx 的內(nèi)部結(jié)構(gòu)。

.xlsx 本質(zhì)上是一個(gè) zip 壓縮包，里面放著一組 XML 文件。常見(jiàn)文件包括：

• xl/workbook.xml
• xl/worksheets/sheet1.xml
• xl/sharedStrings.xml
• xl/styles.xml

作用大概是：

• workbook.xml       記錄有哪些工作表
• sheet1.xml         記錄單元格內(nèi)容和單元格位置
• sharedStrings.xml  記錄共享字符串
• styles.xml         記錄字體、邊框、填充色等樣式

所以腳本主要用到了 Python 標(biāo)準(zhǔn)庫(kù)里的：

import zipfile
from xml.etree import ElementTree as ET

zipfile 用來(lái)打開(kāi)和重新打包 .xlsx，ElementTree 用來(lái)解析和修改 XML。

讀取 txt：把目標(biāo)數(shù)據(jù)變成集合

腳本中負(fù)責(zé)解析 5.8.txt 的函數(shù)是：

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 文件
• 去掉每行前后的空格和斜杠
• 用 / 分割成兩個(gè)字段
• 把合法數(shù)據(jù)放進(jìn) set
• 把格式異常的數(shù)據(jù)記錄到 bad_lines

為什么用 set？

因?yàn)榧线m合做“是否存在”的判斷：

("動(dòng)物_野生動(dòng)物", "Animals_Pets_2597") in pairs

這種查詢速度很快，比用列表循環(huán)查找更適合大表。

讀取 Excel 表頭：定位字段列

Excel 里不應(yīng)該寫死“第 3 列是文件夾名、第 4 列是 id”，因?yàn)楸砀窳许樞蚩赡茏兓８€(wěn)妥的做法是讀取第一行表頭，然后根據(jù)表頭名稱找列。

腳本里的函數(shù)：

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

函數(shù)會(huì)得到類似結(jié)果：

{
    "批次": "A",
    "分類": "B",
    "文件夾名": "C",
    "id": "D",
    "resolution": "E",
}

后續(xù)就可以這樣拿到目標(biāo)列：

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

如果表頭不存在，腳本會(huì)直接報(bào)錯(cuò)，而不是悄悄處理錯(cuò)列。

匹配并標(biāo)黃

真正執(zhí)行標(biāo)黃的函數(shù)是：

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("沒(méi)有找到表頭為 文件夾名 的字段")
    if not id_col:
        raise RuntimeError("沒(méi)有找到表頭為 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

這段邏輯重點(diǎn)有三個(gè)：

• rows[1:]：跳過(guò)表頭，從第二行開(kāi)始處理
• cells_by_col：把當(dāng)前行的單元格按列號(hào)整理成字典
• (folder_text, id_text) in pairs：兩個(gè)字段同時(shí)嚴(yán)格匹配
• id_cell.set("s", str(style_id))：給 id 單元格設(shè)置黃色樣式

這里標(biāo)黃的是 id 單元格，而不是整行。如果想標(biāo)整行，可以把當(dāng)前行的所有單元格都設(shè)置同一個(gè)樣式。

寫入黃色樣式

Excel 的顏色樣式保存在 xl/styles.xml 中。腳本通過(guò) ensure_yellow_style 往樣式表里追加一個(gè)黃色填充樣式：

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" 表示純色填充

追加樣式后，函數(shù)會(huì)返回新樣式的編號(hào)：

return ET.tostring(root, encoding="utf-8", xml_declaration=True), style_id

后面給單元格設(shè)置樣式時(shí)，就使用這個(gè) style_id。

命令行參數(shù)設(shè)計(jì)

腳本使用 argparse 支持命令行參數(shù)：

parser = argparse.ArgumentParser(description="Mark xlsx rows whose trailing number appears in 5.8.txt.")
parser.add_argument("xlsx", help="要標(biāo)注的 xlsx 文件")
parser.add_argument("-o", "--output", help="輸出文件名，默認(rèn)在原文件名后加 _marked")
parser.add_argument("--txt", default="5.8.txt", help="包含目標(biāo)數(shù)據(jù)的 txt")
parser.add_argument("--yellow-folder-id", action="store_true", help="按 txt 的 文件夾名/id 兩個(gè)字段嚴(yán)格匹配")

這樣腳本就可以直接在終端運(yùn)行：

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

參數(shù)含義：

• 1.xlsx                         輸入 Excel 文件
• -o 1_folder_id_yellow.xlsx     輸出 Excel 文件
• --yellow-folder-id             啟用 文件夾名 + id 嚴(yán)格匹配模式

Python 基礎(chǔ)語(yǔ)法說(shuō)明

下面整理一下這個(gè)腳本里出現(xiàn)的 Python 基礎(chǔ)語(yǔ)法。

1. 導(dǎo)入模塊

import zipfile
import tempfile
from pathlib import Path
from xml.etree import ElementTree as ET

import 用來(lái)導(dǎo)入模塊。from ... import ... 表示只導(dǎo)入模塊中的某個(gè)對(duì)象。

as ET 是起別名，后面可以用更短的 ET 來(lái)代替 ElementTree。

2. 定義函數(shù)

def cell_text(cell, shared_strings):
    return ""

def 用來(lái)定義函數(shù)。括號(hào)里是參數(shù)，函數(shù)內(nèi)部通過(guò) return 返回結(jié)果。

3. 字符串處理

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

常見(jiàn)方法：

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

4. 條件判斷

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

Python 使用 if / elif / else 做條件判斷。

not text 表示字符串為空時(shí)成立。

5. 循環(huán)

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

for 用來(lái)遍歷列表、集合、文件行等對(duì)象。

腳本里經(jīng)常遍歷：

• txt 的每一行
• Excel 的每一行
• 當(dāng)前行里的每個(gè)單元格

6. continue

if not text:
    continue

continue 表示跳過(guò)本輪循環(huán)，直接處理下一條數(shù)據(jù)。

這里用于跳過(guò)空行。

7. set 集合

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

set 是集合，特點(diǎn)是：

• 自動(dòng)去重
• 適合快速判斷某個(gè)元素是否存在

判斷是否存在：

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

8. tuple 元組

(folder_text, id_text)

這是一個(gè)二元組。這里用它表示一條完整匹配條件：文件夾名 + id

這樣可以避免只匹配其中一個(gè)字段導(dǎo)致誤標(biāo)。

9. dict 字典

columns = {
    "文件夾名": "C",
    "id": "D",
}

字典是鍵值對(duì)結(jié)構(gòu)，適合通過(guò)名稱找值。

比如通過(guò)表頭名稱找列號(hào)：

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

10. 列表推導(dǎo)和字典推導(dǎo)

腳本里有這樣的寫法：

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

這是字典推導(dǎo)式，可以理解為快速生成一個(gè)字典：

• key：?jiǎn)卧袼诹刑?hào)
• value：?jiǎn)卧駥?duì)象

寫成普通循環(huán)大概是：

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. 正則表達(dá)式

腳本中用 re 處理單元格引用和數(shù)字尾號(hào)：

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

這段用于把 D12 拆成：

• 列：D
• 行：12

其中：

• [A-Z]+   匹配一個(gè)或多個(gè)大寫字母
• \d+      匹配一個(gè)或多個(gè)數(shù)字
• $        匹配字符串結(jié)尾

12. with 上下文管理器

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

with 可以自動(dòng)管理資源。文件用完后會(huì)自動(dòng)關(guān)閉，不需要手動(dòng)調(diào)用 close()。

腳本里還用了臨時(shí)目錄：

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

處理結(jié)束后，臨時(shí)目錄會(huì)自動(dòng)刪除。

13. f-string

print(f"標(biāo)注行數(shù)：{marked_count}")

f-string 是 Python 中常用的字符串格式化方式。大括號(hào)里的變量會(huì)被替換成實(shí)際值。

14. 程序入口

if __name__ == "__main__":
    main()

這是 Python 腳本的標(biāo)準(zhǔn)入口寫法。

含義是：當(dāng)這個(gè)文件被直接運(yùn)行時(shí)，執(zhí)行 main()；如果被其他 Python 文件導(dǎo)入，則不自動(dòng)執(zhí)行。

為什么要輸出新文件

腳本默認(rèn)不直接覆蓋原 Excel，而是輸出一個(gè)新文件：

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

這樣做的好處是：

• 原始數(shù)據(jù)不會(huì)被破壞
• 結(jié)果可以和原文件對(duì)比
• 如果規(guī)則寫錯(cuò)，可以重新生成

處理數(shù)據(jù)時(shí)，這是一個(gè)很重要的習(xí)慣。

踩坑記錄

這個(gè)需求看起來(lái)簡(jiǎn)單，但實(shí)際有幾個(gè)容易踩坑的地方。

1. 只匹配尾號(hào)容易誤標(biāo)

比如：

Game_Characters_1302
Game_Characters_11302

如果只用“包含 1302”來(lái)判斷，就會(huì)把后者也誤標(biāo)。

2. 只匹配 id 也可能不夠

同一個(gè) id 可能出現(xiàn)在不同 文件夾名 下。只匹配 id 時(shí)，可能會(huì)標(biāo)到不屬于目標(biāo)分類的數(shù)據(jù)。

所以最終采用：文件夾名嚴(yán)格相等 + id嚴(yán)格相等

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

Excel 為了節(jié)省空間，經(jīng)常把字符串放在 sharedStrings.xml 里，單元格中只保存字符串索引。

所以腳本需要先讀取共享字符串：

shared_strings = read_shared_strings(src)

再通過(guò) cell_text() 還原單元格真實(shí)文本。

4. 表頭不能寫死列號(hào)

如果寫死第 3 列和第 4 列，表格一旦調(diào)整列順序，腳本就會(huì)處理錯(cuò)列。

根據(jù)表頭名稱找列，更穩(wěn)。

小結(jié)

這個(gè)腳本的核心并不復(fù)雜，本質(zhì)上就是：

• txt 解析成目標(biāo)集合
• Excel 逐行讀取
• 兩個(gè)字段嚴(yán)格匹配
• 匹配成功就設(shè)置黃色樣式
• 重新保存為新 xlsx

真正需要注意的是數(shù)據(jù)匹配規(guī)則。對(duì)于批量標(biāo)注任務(wù)，規(guī)則越明確，越不容易誤標(biāo)。相比手動(dòng)搜索，腳本化處理可以把幾百條甚至幾萬(wàn)條數(shù)據(jù)的篩選和標(biāo)注穩(wěn)定地壓縮到幾秒鐘完成。

完整運(yùn)行命令：

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

當(dāng)終端輸出：

txt 行數(shù)：396
提取 文件夾名/id：396
標(biāo)注行數(shù)：xxx
輸出文件：1_folder_id_yellow.xlsx

就說(shuō)明腳本已經(jīng)完成處理。

以上就是Python實(shí)現(xiàn)批量篩選Excel數(shù)據(jù)并標(biāo)黃的詳細(xì)內(nèi)容，更多關(guān)于Python批量篩選Excel數(shù)據(jù)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論