如何用openpyxl检测Excel工作表中含错误的单元格？

针对你已加载的工作表，下面分不同错误类型给出具体检测方法：

1. 检测公式计算错误（如#DIV/0!、#VALUE!等） #

Excel中公式计算出错时，单元格的data_type会标记为'e'，对应的value是具体错误代码。遍历所有单元格即可筛选这类情况：

from openpyxl import load_workbook

wb = load_workbook("path/to/workbook.xlsx", data_only=False)  # 必须保留公式，不能只读计算结果
ws = wb.worksheets[0]

formula_errors = []
for row in ws.iter_rows():
    for cell in row:
        if cell.data_type == 'e':
            formula_errors.append((cell.coordinate, cell.value))

print("公式错误单元格：", formula_errors)

2. 检测N/A类错误（#N/A、#NA()返回值） #

这类错误可能是公式返回或手动输入的，直接检查单元格值即可：

na_errors = []
for row in ws.iter_rows():
    for cell in row:
        # 匹配手动输入的#N/A，或公式返回的#N/A
        if cell.value == '#N/A' or (cell.data_type == 'f' and '#N/A' in str(cell.value)):
            na_errors.append((cell.coordinate, cell.value))

print("N/A错误单元格：", na_errors)

3. 检测数据验证错误 #

openpyxl无法直接读取Excel界面标记的验证错误（如红色感叹号），但可以读取单元格的验证规则，手动校验值是否符合规则。以下是常见规则的验证示例：

validation_errors = []

for row in ws.iter_rows():
    for cell in row:
        dv = cell.data_validation
        if not dv:
            continue  # 无验证规则，跳过
        
        cell_value = cell.value
        valid = True
        
        # 整数范围验证
        if dv.type == 'whole':
            min_val = dv.formula1
            max_val = dv.formula2
            if min_val and cell_value < int(min_val):
                valid = False
            if max_val and cell_value > int(max_val):
                valid = False
        
        # 下拉列表验证
        elif dv.type == 'list':
            allowed_values = dv.formula1.split(',')
            if str(cell_value) not in allowed_values:
                valid = False
        
        # 可扩展其他规则：小数、日期、文本长度等
        elif dv.type == 'decimal':
            min_val = dv.formula1
            max_val = dv.formula2
            if min_val and cell_value < float(min_val):
                valid = False
            if max_val and cell_value > float(max_val):
                valid = False
        
        if not valid:
            validation_errors.append((cell.coordinate, f"不符合{dv.type}类型规则"))

print("数据验证错误单元格：", validation_errors)

4. 汇总所有错误 #

可以整合上述逻辑，一次性返回所有错误单元格：

all_errors = []

for row in ws.iter_rows():
    for cell in row:
        # 公式错误检测
        if cell.data_type == 'e':
            all_errors.append((cell.coordinate, f"公式错误：{cell.value}"))
            continue
        
        # N/A错误检测
        if cell.value == '#N/A' or (cell.data_type == 'f' and '#N/A' in str(cell.value)):
            all_errors.append((cell.coordinate, "N/A错误"))
            continue
        
        # 数据验证错误检测
        dv = cell.data_validation
        if dv:
            valid = True
            if dv.type == 'whole':
                min_val = dv.formula1
                max_val = dv.formula2
                if min_val and cell.value < int(min_val):
                    valid = False
                if max_val and cell.value > int(max_val):
                    valid = False
            elif dv.type == 'list':
                allowed_values = dv.formula1.split(',')
                if str(cell.value) not in allowed_values:
                    valid = False
            
            if not valid:
                all_errors.append((cell.coordinate, f"数据验证错误：{dv.type}"))

print("所有错误单元格：", all_errors)

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