使用openpyxl添加数据验证时Excel提示修复并移除验证的问题

问题原因 #

你的代码生成的数据验证公式不符合Excel的格式要求。Excel的列表型数据验证，要求选项列表必须被双引号包裹，格式示例为"选项1, 选项2, 选项3"。而你当前直接传入Possible Matches列的原始字符串，生成的公式缺少外层双引号，导致Excel无法正常解析，触发文件修复提示。

解决方案 #

给Possible Matches的字符串外层添加双引号，同时规避Python字符串的引号冲突：

• 可以用f'"{value}"'格式，外层用单引号包裹，内部用双引号包裹选项列表；
• 也可以用'"{}"'.format(value)，效果完全一致。

修正后的完整代码 #

from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.worksheet.datavalidation import DataValidation

workbook = load_workbook(filename='output_test.xlsx.xlsm')
worksheet = workbook['Sheet1']

for r, row in enumerate(dataframe_to_rows(df_excel, index=False, header=False), start=1):
    if row[2] == 100:
        for c, value in enumerate(row, start=1):
            worksheet.cell(row=r, column=c, value=value)
    else:
        for c, value in enumerate(row, start=1):
            if c == 2:
                # 给选项列表添加外层双引号，匹配Excel数据验证格式要求
                dv = DataValidation(type="list", formula1=f'"{value}"')
                worksheet.add_data_validation(dv)
                dv.add(worksheet.cell(row=r, column=c))
            else:
                worksheet.cell(row=r, column=c, value=value)

workbook.save('output_text.xlsx')

额外注意事项 #

如果Possible Matches列的内容中包含双引号，需要先对其转义（将单个双引号替换为两个双引号），否则Excel仍会解析失败。可添加如下处理逻辑：

escaped_value = value.replace('"', '""')
dv = DataValidation(type="list", formula1=f'"{escaped_value}"')

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