如何用Python在Excel多列添加下拉框？代码无下拉问题排查

我无法在Excel工作表中添加下拉框，代码运行无报错，但打开生成的Excel后，AC列和AF列均未出现下拉框。formula和formula_1是需要作为下拉选项的列表，对应AC列和AF列，寻求解决建议。

原代码 #

def formatContent(output_path):
  
    # based on above generated file, below actions is for adding data validity and adjust the formatting
    from openpyxl.worksheet.datavalidation import DataValidation
    from openpyxl import load_workbook
    from openpyxl.styles import Font, PatternFill, Alignment
    # import os
    
    # nowTime=datetime.datetime.now().strftime('%Y-%m-%d')  
    formula = '"Data Source Error,CRA Rotation,Methodology ,Rating_Level_Issues,NID Process,Pricing,Size,Local / Domestic Issuance,Confidential Rating,Client Choice,No response"'
    formula_1 = '"Juyong Baek,Kanchan  Arora,Tommy Kuang,Naoya Tsuruoka,Denis OSullivan,Eva Wu,Scarlett Lan,Xiao  Xiao,Kelly Lim,Sherman Chung,Benjamin  Fleming,Anthony  Foo,Peony Chan,Ray Mau,Genki Ikeda,Vicky Shen,Dongsheng Hong,John Birch,Valerie Lee,Yookyung Lee,Lee Wen En,Hoe Darren,Tom Ledgerwood"'
    wkbk = load_workbook(filename = output_path, data_only=False)
    
    for i in range(0,1):
        code = DataValidation(type="list",formula1=formula,allow_blank=True)
        code.error = 'No such item in the dropdown'
        code.errorTitle = 'Invalid Input'
        code.prompt = 'Please select an item from the dropdown'
        code.promptTitle = 'Select from the dropdown'
        wkbk[wkbk.sheetnames[i]].add_data_validation(code)
        code.add('AC2:AC3000')
        
        code_1 = DataValidation(type="list",formula1=formula_1,allow_blank=True)
        code_1.error = 'No such item in the dropdown'
        code_1.errorTitle = 'Invalid Input'
        code_1.prompt = 'Please select an item from the dropdown'
        code_1.promptTitle = 'Select from the dropdown'
        wkbk[wkbk.sheetnames[i]].add_data_validation(code_1)
        code_1.add('AF2:AF3000')
        
        # ft=Font(name="Calibli",size=7)
        ft=Font(name="Calibri",size=8)
        fill=PatternFill(start_color='CCCCCC',end_color='CCCCCC',fill_type="solid")
        align=Alignment(horizontal="center",vertical="center",wrap_text=True)
        row_nub=wkbk[wkbk.sheetnames[i]].max_row
        for j in range(1,row_nub+1):
            # MPS[MPS.sheetnames[i]].row_dimensions[j].height=25
            wkbk[wkbk.sheetnames[i]].row_dimensions[j]
            for k in range(1,34):
                wkbk[wkbk.sheetnames[i]].cell(row=j,column=k).font=ft
                wkbk[wkbk.sheetnames[i]].cell(row=1,column=k).font=ft
                wkbk[wkbk.sheetnames[i]].cell(row=1,column=k).fill=fill
                wkbk[wkbk.sheetnames[i]].cell(row=1,column=k).alignment=align
  
    wkbk.save(output_path)
    print("Process Completed.")

解决建议 #

1. 修正公式字符串的转义错误 #

代码中使用了"（HTML转义的双引号），这会导致Excel无法识别下拉列表的公式格式。直接用Python的双引号包裹选项列表即可，openpyxl会自动处理Excel所需的格式：

# 修正后的formula和formula_1
formula = '"Data Source Error,CRA Rotation,Methodology ,Rating_Level_Issues,NID Process,Pricing,Size,Local / Domestic Issuance,Confidential Rating,Client Choice,No response"'
formula_1 = '"Juyong Baek,Kanchan  Arora,Tommy Kuang,Naoya Tsuruoka,Denis OSullivan,Eva Wu,Scarlett Lan,Xiao  Xiao,Kelly Lim,Sherman Chung,Benjamin  Fleming,Anthony  Foo,Peony Chan,Ray Mau,Genki Ikeda,Vicky Shen,Dongsheng Hong,John Birch,Valerie Lee,Yookyung Lee,Lee Wen En,Hoe Darren,Tom Ledgerwood"'

2. 修正DataValidation的参数转义 #

同样，type="list"里的"要换成实际的双引号，改为type="list"。

3. 简化工作表引用（可选） #

将重复的wkbk[wkbk.sheetnames[i]]赋值给变量，提升代码可读性：

ws = wkbk.worksheets[i]
# 后续直接使用ws操作工作表
ws.add_data_validation(code)

4. 检查列范围的有效性 #

样式循环range(1,34)覆盖到第33列（AG列），AF列是第32列，在范围内，不会影响数据验证。若后续列数扩展，需注意调整循环范围。

修正后的完整代码 #

def formatContent(output_path):
  
    # based on above generated file, below actions is for adding data validity and adjust the formatting
    from openpyxl.worksheet.datavalidation import DataValidation
    from openpyxl import load_workbook
    from openpyxl.styles import Font, PatternFill, Alignment
    # import os
    
    # nowTime=datetime.datetime.now().strftime('%Y-%m-%d')  
    formula = '"Data Source Error,CRA Rotation,Methodology ,Rating_Level_Issues,NID Process,Pricing,Size,Local / Domestic Issuance,Confidential Rating,Client Choice,No response"'
    formula_1 = '"Juyong Baek,Kanchan  Arora,Tommy Kuang,Naoya Tsuruoka,Denis OSullivan,Eva Wu,Scarlett Lan,Xiao  Xiao,Kelly Lim,Sherman Chung,Benjamin  Fleming,Anthony  Foo,Peony Chan,Ray Mau,Genki Ikeda,Vicky Shen,Dongsheng Hong,John Birch,Valerie Lee,Yookyung Lee,Lee Wen En,Hoe Darren,Tom Ledgerwood"'
    wkbk = load_workbook(filename = output_path, data_only=False)
    
    for i in range(0,1):
        ws = wkbk.worksheets[i]
        code = DataValidation(type="list", formula1=formula, allow_blank=True)
        code.error = 'No such item in the dropdown'
        code.errorTitle = 'Invalid Input'
        code.prompt = 'Please select an item from the dropdown'
        code.promptTitle = 'Select from the dropdown'
        ws.add_data_validation(code)
        code.add('AC2:AC3000')
        
        code_1 = DataValidation(type="list", formula1=formula_1, allow_blank=True)
        code_1.error = 'No such item in the dropdown'
        code_1.errorTitle = 'Invalid Input'
        code_1.prompt = 'Please select an item from the dropdown'
        code_1.promptTitle = 'Select from the dropdown'
        ws.add_data_validation(code_1)
        code_1.add('AF2:AF3000')
        
        # ft=Font(name="Calibli",size=7)
        ft=Font(name="Calibri", size=8)
        fill=PatternFill(start_color='CCCCCC', end_color='CCCCCC', fill_type="solid")
        align=Alignment(horizontal="center", vertical="center", wrap_text=True)
        row_nub = ws.max_row
        for j in range(1, row_nub+1):
            # ws.row_dimensions[j].height=25 # 可以取消注释设置行高
            for k in range(1, 34):
                ws.cell(row=j, column=k).font = ft
                header_cell = ws.cell(row=1, column=k)
                header_cell.font = ft
                header_cell.fill = fill
                header_cell.alignment = align
  
    wkbk.save(output_path)
    print("Process Completed.")

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