如何用openpyxl移除Excel单元格原有数据验证并添加新验证？

我之前碰到过一模一样的问题！openpyxl确实没有在公开API里提供直接移除数据验证的方法，但我们可以通过操作工作表内部的data_validations集合来搞定这个问题。

核心思路 #

工作表的所有数据验证规则都存储在ws.data_validations列表中，每个规则的sqref属性记录了它应用的单元格范围。我们可以遍历这个列表，找到覆盖目标单元格的规则并删除，之后再添加新的验证规则就会生效了。

具体实现代码 #

首先，编写一个辅助函数来移除指定范围的原有数据验证：

from openpyxl.utils import get_column_letter

def remove_existing_data_validation(ws, target_range):
    # 反向遍历避免删除元素时索引错乱
    for idx in reversed(range(len(ws.data_validations))):
        dv_rule = ws.data_validations[idx]
        # 检查当前验证规则的范围是否与目标范围有重叠
        if dv_rule.sqref.intersects(target_range):
            del ws.data_validations[idx]

然后修改你的原有代码，先移除目标单元格的旧验证，再添加新规则：

# 构造需要处理的单元格范围（F3到最后一行）
target_cell_range = f"F3:F{ws.max_row}"

# 先移除该范围的所有原有数据验证
remove_existing_data_validation(ws, target_cell_range)

# 创建并添加新的数据验证规则
dv = DataValidation(type='list', formula="{0}!$B$2:$B$18".format(quote_sheetname('values')))
ws.add_data_validation(dv)

row = 3
while row <= ws.max_row:
    dv.add('F{}'.format(row))
    row += 1

额外小技巧 #

如果你不需要保留工作表中任何其他数据验证规则，可以直接用一行代码清空所有验证：

ws.data_validations.clear()

注意事项 #

• 确保在修改完验证规则后正确保存工作簿（wb.save('your_file.xlsx')）
• sqref.intersects()方法可以准确判断两个单元格范围是否有重叠，不用担心误删不相关的验证规则

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