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

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

需求描述

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

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

每一行用 / 分成兩個字段：

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

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

文件夾名
id

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

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

整體思路

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

1. 讀取 5.8.txt，把每行解析成 (文件夾名, id) 二元組。
2. 打開 xlsx 文件，找到工作表里的 文件夾名 列和 id 列。
3. 逐行讀取 Excel 數(shù)據(jù)，拿當(dāng)前行的 (文件夾名, id) 去目標集合里查詢。
4. 如果匹配成功，就給當(dāng)前行的 id 單元格設(shè)置黃色樣式。
5. 把修改后的 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 條數(shù)據(jù)，Excel 有 28123 行，手動搜索不僅慢，還容易出現(xiàn)這些問題：

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

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

xlsx 文件的本質(zhì)

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

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

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

作用大概是：

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

所以腳本主要用到了 Python 標準庫里的：

import zipfile
from xml.etree import ElementTree as ET

zipfile 用來打開和重新打包 .xlsx，ElementTree 用來解析和修改 XML。

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

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

為什么用 set？

因為集合適合做“是否存在”的判斷：

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

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

讀取 Excel 表頭：定位字段列

Excel 里不應(yīng)該寫死“第 3 列是文件夾名、第 4 列是 id”，因為表格列順序可能變化。更穩(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ù)會得到類似結(jié)果：

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

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

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

如果表頭不存在，腳本會直接報錯，而不是悄悄處理錯列。

匹配并標黃

真正執(zhí)行標黃的函數(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("沒有找到表頭為 文件夾名 的字段")
    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：把當(dāng)前行的單元格按列號整理成字典
• (folder_text, id_text) in pairs：兩個字段同時嚴格匹配
• id_cell.set("s", str(style_id))：給 id 單元格設(shè)置黃色樣式

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

寫入黃色樣式

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

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

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

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

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

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

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="包含目標數(shù)據(jù)的 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

參數(shù)含義：

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

Python 基礎(chǔ)語法說明

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

1. 導(dǎo)入模塊

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

import 用來導(dǎo)入模塊。from ... import ... 表示只導(dǎo)入模塊中的某個對象。

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

2. 定義函數(shù)

def cell_text(cell, shared_strings):
    return ""

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

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. 循環(huán)

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

for 用來遍歷列表、集合、文件行等對象。

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

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

6. continue

if not text:
    continue

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

這里用于跳過空行。

7. set 集合

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

set 是集合，特點是：

• 自動去重
• 適合快速判斷某個元素是否存在

判斷是否存在：

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

8. tuple 元組

(folder_text, id_text)

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

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

9. dict 字典

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

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

比如通過表頭名稱找列號：

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)式，可以理解為快速生成一個字典：

• key：單元格所在列號
• value：單元格對象

寫成普通循環(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. 正則表達式

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

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

這段用于把 D12 拆成：

• 列：D
• 行：12

其中：

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

12. with 上下文管理器

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

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

腳本里還用了臨時目錄：

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

處理結(jié)束后，臨時目錄會自動刪除。

13. f-string

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

f-string 是 Python 中常用的字符串格式化方式。大括號里的變量會被替換成實際值。

14. 程序入口

if __name__ == "__main__":
    main()

這是 Python 腳本的標準入口寫法。

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

為什么要輸出新文件

腳本默認不直接覆蓋原 Excel，而是輸出一個新文件：

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

這樣做的好處是：

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

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

踩坑記錄

這個需求看起來簡單，但實際有幾個容易踩坑的地方。

1. 只匹配尾號容易誤標

比如：

Game_Characters_1302
Game_Characters_11302

如果只用“包含 1302”來判斷，就會把后者也誤標。

2. 只匹配 id 也可能不夠

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

所以最終采用：文件夾名嚴格相等 + id嚴格相等

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

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

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

shared_strings = read_shared_strings(src)

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

4. 表頭不能寫死列號

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

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

小結(jié)

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

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

真正需要注意的是數(shù)據(jù)匹配規(guī)則。對于批量標注任務(wù)，規(guī)則越明確，越不容易誤標。相比手動搜索，腳本化處理可以把幾百條甚至幾萬條數(shù)據(jù)的篩選和標注穩(wě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
標注行數(shù)：xxx
輸出文件：1_folder_id_yellow.xlsx

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

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

最新評論