openpyxl批量添加data_validation致Excel崩溃问题求助

你的问题根源在于为每行单独创建一个DataValidation对象，当行数超过6万时，会生成数十万独立的数据验证条目，这会让Excel的XML结构臃肿不堪，超出处理能力，最终导致文件损坏、验证丢失——不管是openpyxl的处理效率还是Excel本身的负载，都扛不住这种写法。

正确的做法：单个DataValidation对象覆盖整个区域 #

你不需要循环每行创建验证，只需要创建一个DataValidation实例，直接把整个目标区域（G3:G1048576）添加进去，利用Excel的相对引用特性自动适配每行的公式：

from openpyxl.worksheet.datavalidation import DataValidation

# 假设已获取目标worksheet对象
dv = DataValidation(type="list", formula1='=INDIRECT("Prefix."&E3)')
# 直接添加G列从第3行开始的所有单元格
dv.add('G3:G1048576')
worksheet.add_data_validation(dv)

为什么这么做能解决问题？ #

• 只生成1个DataValidation对象，Excel文件的XML结构会非常简洁，不会出现数十万冗余条目，文件体积和加载压力骤降。
• 公式里的E3是相对引用，当应用到G列的不同行时，Excel会自动把E3替换成对应行的单元格（比如G4对应E4，G5对应E5），完全符合你原本的需求。

额外注意事项 #

• 确保你的Prefix.开头的命名范围都正确定义，每个E列单元格的值都能匹配到有效的命名区域，否则下拉列表会显示为空或报错。
• 如果需要排除空白行，可以在公式里加判断，比如=IF(E3="",,INDIRECT("Prefix."&E3))，避免空值导致的下拉异常。

内容的提问来源于stack exchange，提问作者Trung Hieu Nguyen