使用openpyxl为多工作表指定列设置数据验证遇多表失效求助

问题根源 #

原代码中复用了同一个DataValidation实例（比如data_2同时用于Tab1的第5列、Tab2的第8/9/23列），但openpyxl规定一个DataValidation对象只能属于一个工作表，重复添加到多个工作表会导致内部关联混乱，引发逻辑错误。

修正后的代码 #

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.utils import get_column_letter

# 匿名化选项列表
state_list_1 = ["State1", "State2", "State3"]  # 金融机构列表
state_list_2 = ["Yes", "No"]  # 是/否选项
state_list_3 = ["USD", "EUR", "CAD"]  # 货币代码

# 创建工作簿
workbook = Workbook()
# 移除默认的Sheet工作表（可选，避免冗余）
default_sheet = workbook['Sheet']
workbook.remove(default_sheet)
# 创建目标工作表
workbook.create_sheet('Tab 1')
workbook.create_sheet('Tab 2')
workbook.create_sheet('Tab 3')
workbook.create_sheet('List')

sheet_one = workbook['Tab 1']
sheet_two = workbook['Tab 2']
sheet_three = workbook['Tab 3']
sheet_four = workbook['List']

# 填充选项列表到List工作表
state_lists = [state_list_1, state_list_2, state_list_3]
for col_idx, state_list in enumerate(state_lists, start=1):
    for row_idx, state in enumerate(state_list, start=1):
        sheet_four.cell(row=row_idx, column=col_idx, value=state)

# 定义各工作表需要验证的列与对应规则映射
columns_to_validate = {
    'Tab 1': {5: '=List!$B:$B'},  # 对应state_list_2
    'Tab 2': {8: '=List!$B:$B', 9: '=List!$B:$B', 14: '=List!$C:$C', 15: '=List!$A:$A', 23: '=List!$B:$B'},
    'Tab 3': {6: '=List!$C:$C', 7: '=List!$C:$C', 8: '=List!$A:$A'}
}

# 为每个工作表的每列创建独立的数据验证并应用
for sheet_name, col_rules in columns_to_validate.items():
    sheet = workbook[sheet_name]
    # 预先设置需要验证的行数范围，这里指定到第100行（可按需调整）
    target_rows = range(3, 101)
    for col_num, formula in col_rules.items():
        # 为当前列创建独立的DataValidation实例
        dv = DataValidation(type="list", formula1=formula, allow_blank=True)
        sheet.add_data_validation(dv)
        col_letter = get_column_letter(col_num)
        # 批量添加单元格到验证规则
        for row in target_rows:
            cell = f"{col_letter}{row}"
            dv.add(sheet[cell])

# 保存工作簿
workbook.save('mwe.xlsx')

关键修改点 #

1. 移除默认工作表：原代码创建新表后会保留默认的Sheet，移除后避免冗余；
2. 独立创建DataValidation实例：不再复用全局的验证对象，为每个工作表的每列单独创建验证规则，彻底解决跨工作表关联冲突；
3. 明确验证行数范围：原代码中sheet.max_row初始为1，导致验证循环无法执行，这里直接指定目标行数范围；
4. 添加allow_blank=True：允许单元格为空，适配常规使用场景（可根据需求移除）。

优化小技巧 #

如果需要复用验证规则定义，可封装生成函数减少重复代码：

def create_list_validation(formula):
    return DataValidation(type="list", formula1=formula, allow_blank=True)

调用时替换为dv = create_list_validation(formula)即可。

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