使用openpyxl修改数据验证后保存的Excel文件无法打开

运行Python脚本修改Excel数据验证规则后，保存的文件无法打开，一直处于加载状态。相关代码如下：

from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl import load_workbook
import copy
import re

excel_file_path = r"C:\Users\Downloads\Net_v2.7.xlsx"
wb  = load_workbook(excel_file_path,read_only=False)

def replace_dropdown(sheet, cell,dropdowntype):
    toRemove = []
    bit = []
    cell_pos = []
    if ':' in cell:
        cell_r = cell.split(":")
        col_names = list(map(chr,range(ord(cell_r[0][0]),ord(cell_r[1][0])+1)))
        for col in col_names:
            start_index,end_index = map(int,re.findall(r'\d+',cell))
            for i in range(int(start_index),int(end_index)+1):
                        cell1 = f'{col}{i}'
                        cell_pos.append(cell1)
    else:
        cell_pos.append(cell)
    for validation in sheet.data_validations.dataValidation:
        if validation.formula1  == dropdowntype:
                toRemove.append(validation)
                import ipdb
                ipdb.set_trace()
                break
    for ii in toRemove:
        sheet.data_validations.dataValidation.remove(ii)
    #here we get validation cells
    if len(toRemove)>0:
        dvd = toRemove
        dv =dvd[0]
        # dv = DataValidation(type = "list",formula1=dropdowntype,showDropDown=False)
        cell_range1 = f"{toRemove[0].sqref}"
        bit = cell_range1.split()
        cell_range = []
    # here we get validation cells
    if len(bit)>1:
        for i in bit:
            sheet_val = True
            if ':' in i:
                start_index,end_index = map(int,re.findall(r'\d+',i))
                start_col,end_col = re.findall(r'([a-zA-Z]+)',i)
                # for start_col in col_names:
                for i in range(int(start_index),int(end_index)+1):
                        cell1 = f'{start_col}{i}'
                        cell_range.append(cell1)
                        cell2 = f'{end_col}{i}'
                        cell_range.append(cell2)
            else:
                 cell_range.append(i)
    else:
        if len(bit) == 1:
            start_index,end_index = map(int,re.findall(r'\d+',bit[0]))
            for start_col in col_names:
                for i in range(int(start_index),int(end_index)+1):
                    cell1 = f'{start_col}{i}'
                    cell_range.append(cell1)
    # here we set validation on cells
    if len(toRemove)>0:
        dv.sqref = []
        import ipdb
        ipdb.set_trace()
        if sheet_val == False:
            for cell_cor in cell_range:
                if cell_cor not in cell_pos and cell_cor in dv:
                    sheet.add_data_validation(dv)
                    dv.add(sheet[cell_cor])
                else:
                    pass
        elif sheet_val == True:
            for cell_cor in cell_range:
                if cell_cor not in  cell_pos :
                    sheet.add_data_validation(dv)
                    dv.add(sheet[cell_cor])       

li1 = [
    {
    "T": "Network Issue",
    "v": 2.454547,
    "Tab Name": "thanked",
    "Change Type": "Change Dropdown",
    "cell_NameRG": "K447:K552",
    "column_name": "K",
    "Released in": None,
    "Old_v": "You jsut",
    "New_v": None
}]

for i in li1:
    sheet =i['Tab Name']
    cell = i["cell_NameRG"]
    old_v = i["Old_v"]
    new_v = i["New_v"]
    col_N = i["column_name"]
    replace_dropdown(wb[sheet],cell,old_v,new_v,col_N)

wb.save("task_1.xlsx")

问题原因 #

1. 参数不匹配：调用replace_dropdown时传入了5个参数，但函数定义仅接受3个参数，直接触发参数错误，导致文件保存不完整。
2. 变量未初始化：sheet_val仅在len(bit)>1的分支中定义，当bit长度为1或0时，该变量未定义，执行后续判断会抛出异常，破坏文件结构。
3. 重复添加数据验证：循环内多次调用sheet.add_data_validation(dv)，同一个数据验证对象被重复添加到工作表，造成Excel解析冲突。
4. 单元格范围处理错误：拆分sqref为单个单元格的逻辑有误，不仅增加文件体积，还可能导致无效的单元格引用；另外col_names在部分分支中未定义，触发NameError。
5. 未实现新规则替换：代码未实际将验证规则替换为new_v，仅移除旧规则后又重新添加旧规则，且new_v传入None，生成无效的验证规则。

修复方案 #

• 修正参数匹配：调整replace_dropdown函数定义，使其接受5个参数（sheet, cell, old_v, new_v, col_N），或修改调用逻辑，只传函数定义的3个参数。
• 初始化变量：在函数开头初始化sheet_val = False，避免未定义的情况。
• 避免重复添加：将sheet.add_data_validation(dv)移至循环外，仅执行一次，循环内仅调用dv.add()添加单元格。
• 优化范围处理：直接使用sqref的范围格式（如K447:K552），无需拆分为单个单元格，或使用openpyxl内置的范围处理方法。
• 实现新规则替换：如果需要替换为新下拉选项，创建新的DataValidation对象，设置formula1=new_v，再添加到目标单元格。

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