OpenPyXL使用DataValidation添加长下拉列表报错的解决方法

Excel对直接写入数据验证formula1参数的内联列表字符串有255字符的长度限制，当你拼接的选项总长度超过该阈值时就会触发报错，选项较少时长度符合要求所以可以正常运行。

通过「独立工作表存储选项+区域引用」的方式实现大容量下拉列表，没有字符长度限制，操作步骤如下：

• 新建一个专门存储下拉选项的工作表，可设置为隐藏避免用户误改
• 将所有下拉选项按行写入该工作表的某一列
• 数据验证规则的formula1直接引用该选项所在的单元格区域即可

可运行代码示例 #

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

wb = Workbook()
# 业务使用的主工作表
main_sheet = wb.active

# 创建存储下拉选项的工作表
option_sheet = wb.create_sheet("hidden_options")
# 所有下拉选项放入列表即可
option_list = [
    "11111","22222","33333","44444","55555","66666","77777","88888","99999",
    "111110","122221","133332","144443","155554","166665","177776","188887","199998",
    # 剩余的所有选项全部补充到这个列表里
    "1122211"
]
# 写入选项到A列
for row_num, value in enumerate(option_list, start=1):
    option_sheet[f"A{row_num}"] = value

# 创建数据验证规则，引用选项区域
opt_count = len(option_list)
dv = DataValidation(
    type="list",
    formula1=f"=hidden_options!$A$1:$A${opt_count}",
    allow_blank=False
)
main_sheet.add_data_validation(dv)
# 绑定到目标单元格
dv.add('K5')

# 隐藏选项工作表，用户不可见
option_sheet.sheet_state = "hidden"

# 保存文件
wb.save("output.xlsx")

• 引用区域要使用带$的绝对引用，避免单元格位置变动时引用区域发生偏移
• 后续需要更新下拉选项时，直接修改hidden_options工作表内的内容即可，无需调整数据验证规则

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