百万级Excel匹配文档列表内容并标绿行的性能优化求助

问题场景 #

你有包含约3000条文档路径的列表，示例如下：

C:\\folder\\somepath\\1234_456_2.pdf
C:\\folder\\somepath\\whatever\\5932194_123.pdf
C:\\folder\\somepath\\2022_10_10_5932194_123.pdf
C:\\folder\\somepath\\January\\123_5932192.pdf
C:\\folder\\somepath\\whatever\\123_59321911_1234.pdf
C:\\folder\\somepath\\whatever\\123_5932197.pdf

同时有一个含100万行数据的Excel文件，其中Relevant Col.列的值若出现在任意文档路径中，需要将对应整行背景色设为绿色。原openpyxl实现方案在大文件下速度极慢，以下提供优化后的openpyxl方案和更高效的Pandas实现方案。

一、优化openpyxl的实现方案 #

原代码的性能瓶颈 #

1. 每次循环重复调用get_column_letter计算列名，产生冗余开销
2. 将整个路径列表转为字符串str(the_list)做匹配，每次判断都需扫描超长字符串，效率极低
3. 逐单元格单独操作，openpyxl单单元格IO本身耗时较高

优化后的代码 #

import re
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.utils import get_column_letter

# 1. 从文档路径中提取所有目标ID，存入集合（集合的in操作是O(1)，远快于列表/字符串）
pattern = re.compile(r'(\d{6,})')  # 根据实际ID长度调整正则，示例匹配6位及以上数字
target_ids = set()
for path in the_list:
    matches = pattern.findall(path)
    target_ids.update(matches)

# 2. 加载Excel文件
wb = load_workbook('your_source_file.xlsx')
sheet = wb.active

# 3. 定位目标列（找到Relevant Col.所在列）
target_col_idx = None
for cell in sheet[1]:  # 遍历表头行
    if cell.value == "Relevant Col.":
        target_col_idx = cell.column
        break

if not target_col_idx:
    print("未找到目标列")
    exit()

# 4. 定义绿色填充样式（复用对象，避免重复创建）
green_fill = PatternFill("solid", fgColor="92D050")

# 5. 批量遍历行，设置匹配行的背景色
for row in sheet.iter_rows(min_row=2, max_row=sheet.max_row):
    # 获取当前行的目标列单元格值
    relevant_value = str(row[target_col_idx - 1].value).strip()
    if relevant_value in target_ids:
        # 给整行所有单元格设置背景色
        for cell in row:
            cell.fill = green_fill

# 保存结果
wb.save('your_output_file.xlsx')

优化说明 #

• 用正则提取所有路径中的ID并存入集合，将匹配操作的时间复杂度从O(n)降至O(1)
• 仅遍历表头一次定位目标列，避免循环所有列的冗余操作
• 复用PatternFill对象，减少内存开销
• 使用iter_rows批量遍历行，降低单单元格访问的IO次数

二、用Pandas实现（百万级数据首选） #

Pandas对批量数据处理的效率远高于openpyxl，适合处理百万级行数的场景。

方案1：结合Styler快速生成样式 #

import re
import pandas as pd

# 1. 提取目标ID集合
pattern = re.compile(r'(\d{6,})')
target_ids = set()
for path in the_list:
    matches = pattern.findall(path)
    target_ids.update(matches)

# 2. 读取Excel文件
df = pd.read_excel('your_source_file.xlsx')

# 3. 定义行样式函数：匹配的行设置绿色背景
def highlight_matching(row):
    if str(row['Relevant Col.']) in target_ids:
        return ['background-color: #92D050'] * len(row)
    return [''] * len(row)

# 4. 应用样式并导出
styled_df = df.style.apply(highlight_matching, axis=1)
styled_df.to_excel('your_final_output.xlsx', engine='openpyxl', index=False)

方案2：Pandas处理数据+openpyxl精细控制样式 #

如果需要更复杂的样式调整，可以先用Pandas标记匹配行，再用openpyxl设置样式：

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

# 1. 提取目标ID集合
pattern = re.compile(r'(\d{6,})')
target_ids = set()
for path in the_list:
    matches = pattern.findall(path)
    target_ids.update(matches)

# 2. 读取Excel并标记匹配行
df = pd.read_excel('your_source_file.xlsx')
df['is_match'] = df['Relevant Col.'].astype(str).isin(target_ids)

# 3. 导出临时文件
df.to_excel('temp_file.xlsx', index=False)

# 4. 用openpyxl加载临时文件，设置样式
wb = load_workbook('temp_file.xlsx')
sheet = wb.active
green_fill = PatternFill("solid", fgColor="92D050")

# 遍历行设置背景色
for row_num in range(2, sheet.max_row + 1):
    if sheet.cell(row=row_num, column=sheet.max_column).value:
        for col_num in range(1, sheet.max_column):
            sheet.cell(row=row_num, column=col_num).fill = green_fill

# 删除标记列
sheet.delete_cols(sheet.max_column)
wb.save('your_final_output.xlsx')

方案优势 #

• Pandas的矢量运算可以在几秒内完成百万行的匹配判断，远快于openpyxl的逐行遍历
• 代码更简洁，逻辑清晰，维护成本低

内容的提问来源于stack exchange，提问作者Squary94