使用openpyxl修改Excel后下拉列表消失的解决方法求助

核心原因分析 #

1. 数据验证的存储层级：openpyxl中，数据验证（下拉列表）是工作表级对象，存储在工作表的data_validations集合中，而非单个单元格的属性。直接访问cell.data_validation必然报错，因为单元格本身没有这个属性。
2. 修改后验证丢失的常见原因：无需手动复制验证，只要加载工作簿时保留原有结构，修改单元格值不会删除数据验证。之前的错误操作反而可能破坏原有验证配置。

正确解决方案 #

方案1：直接修改原工作簿（保留原有数据验证） #

加载工作簿时默认会保留数据验证，只需正常修改单元格后保存即可，无需额外复制验证代码：

from openpyxl import load_workbook
import os

# 加载工作簿，默认data_only=False会保留公式、数据验证等结构
workbook = load_workbook(f"C:/Users/{os.getlogin()}/Documents/B&B Post/Grondenlijst 2024.xlsx")
worksheet = workbook['2023 Gronden - Pro forma']

# 查找目标行并修改单元格值
for row in worksheet.iter_rows(min_row=3, max_row=worksheet.max_row, min_col=1, max_col=worksheet.max_column):
    if row[2].value == bsn:
        # 修改单元格值，不会影响原有数据验证
        worksheet.cell(row=row[0].row, column=6, value=kolom_F)
        worksheet.cell(row=row[0].row, column=8, value=kolom_H)
        if row[4].value == 'Stukken ontvangen?':
            worksheet.cell(row=row[0].row, column=5, value='Ontvangstbevestiging?')

# 保存修改后的文件（建议另存为新文件避免覆盖原文件）
workbook.save(f"C:/Users/{os.getlogin()}/Documents/B&B Post/Grondenlijst 2024_updated.xlsx")

方案2：从原工作簿复制数据验证（适用于跨工作簿场景） #

如果需要将原工作簿的验证规则复制到新工作簿，需遍历工作表的data_validations集合，而非单个单元格：

from openpyxl import load_workbook
from openpyxl.worksheet.datavalidation import DataValidation
import os

# 加载原工作簿（来源）和目标工作簿
og_wb = load_workbook(f"C:/Users/{os.getlogin()}/Documents/B&B Post/Grondenlijst 2024.xlsx")
og_worksheet = og_wb['2023 Gronden - Pro forma']

target_wb = load_workbook(f"C:/Users/{os.getlogin()}/Documents/B&B Post/Grondenlijst 2024.xlsx")
target_worksheet = target_wb['2023 Gronden - Pro forma']

# 复制所有数据验证规则
for dv in og_worksheet.data_validations.dataValidation:
    dv_copy = DataValidation()
    # 复制验证规则的所有属性
    dv_copy.type = dv.type
    dv_copy.formula1 = dv.formula1
    dv_copy.formula2 = dv.formula2
    dv_copy.showDropDown = dv.showDropDown
    dv_copy.errorTitle = dv.errorTitle
    dv_copy.error = dv.error
    dv_copy.errorStyle = dv.errorStyle
    dv_copy.promptTitle = dv.promptTitle
    dv_copy.prompt = dv.prompt
    dv_copy.promptStyle = dv.promptStyle
    dv_copy.sqref = dv.sqref  # 保留原有的单元格应用范围
    
    # 添加到目标工作表
    target_worksheet.add_data_validation(dv_copy)

# 执行单元格修改操作...

# 保存目标工作簿
target_wb.save(f"C:/Users/{os.getlogin()}/Documents/B&B Post/Grondenlijst 2024_updated.xlsx")

关键注意事项 #

• 加载工作簿时不要设置data_only=True，否则会丢失公式和数据验证结构。
• 修改单元格值不会删除数据验证，只有手动清空验证规则或重建工作表才会导致丢失。
• 数据验证规则是按单元格范围批量设置的，无需逐个单元格复制，直接复制整个验证对象即可。

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