如何用openpyxl为.xlsm文件实现Excel数据验证下拉列表？

一、OpenPyXL完全可以实现该需求 #

你的代码存在几个关键问题导致失败，修正后即可正常工作：

• 未打开原有xlsm文件，而是新建了空白工作簿，丢失原始数据
• DataFrame写入方式错误，不能直接将df.to_excel()指向openpyxl的worksheet对象
• 数据验证的列位置错误，需求中B列是下拉列表，你却设置到了A列
• 工作簿保存方法错误，openpyxl使用save()而非save_as()
• process_row函数的if分支未返回row，导致匹配分数100的行数据缺失

修正后的完整代码 #

from sqlupdate import data_frame_from_xlsx_range
import pandas as pd
from openpyxl import load_workbook
from openpyxl.worksheet.datavalidation import DataValidation

def read_data():
    df_names = data_frame_from_xlsx_range('excel_write_test.xlsm', 'names_to_match')
    df_db_pull = pd.read_excel('test_with_db_pull.xlsx')
    return df_names, df_db_pull

def process_row(row, df_db_pull):
    match_row = df_db_pull.loc[df_db_pull['original_name'] == row['Tracker_Name'], :]
    # 处理匹配结果为空的异常情况
    if match_row.empty:
        row['Possible Matches'] = ['Create New Database Entry']
        row['Match Score'] = 0
        return row
    
    top_score = match_row[['score_0', 'score_1', 'score_2', 'score_3', 'score_4']].max().values[0]
    if top_score == 100:
        row['Possible Matches'] = [match_row['match_name_0'].values[0]]
        row['Match Score'] = 100
    else:
        match_names = set(match_row[['match_name_0', 'match_name_1', 'match_name_2',
                                     'match_name_3', 'match_name_4']].values.ravel())
        # 过滤空值
        match_names = {x for x in match_names if pd.notnull(x)}
        match_names = list(match_names) + ['Create New Database Entry']
        row['Possible Matches'] = match_names
        row['Match Score'] = top_score
    
    return row

def create_dropdowns(df, original_file_path):
    # 打开原有xlsm文件，保留宏代码
    wb = load_workbook(original_file_path, keep_vba=True)
    ws = wb.active  # 可根据实际修改为指定工作表，如wb['Sheet1']
    
    # 写入表头
    ws['A1'] = 'Tracker_Name'
    ws['B1'] = 'Possible Matches'
    ws['C1'] = 'Match Score'
    
    # 逐行写入数据并设置格式
    for row_idx, (_, row_data) in enumerate(df.iterrows(), start=2):
        # 写入A列原始名称
        ws.cell(row=row_idx, column=1, value=row_data['Tracker_Name'])
        # 写入C列匹配分数
        ws.cell(row=row_idx, column=3, value=row_data['Match Score'])
        
        # 处理B列：固定值或下拉列表
        if row_data['Match Score'] == 100:
            ws.cell(row=row_idx, column=2, value=row_data['Possible Matches'][0])
        else:
            # 创建数据验证下拉列表
            dv = DataValidation(
                type="list",
                formula1=f'"{",".join(row_data["Possible Matches"])}"',
                allow_blank=True
            )
            ws.add_data_validation(dv)
            dv.add(ws.cell(row=row_idx, column=2))
    
    # 保存更新后的xlsm文件
    wb.save('excel_write_test_updated.xlsm')

def main():
    df_names, df_db_pull = read_data()
    
    # 初始化列并处理每行数据
    df_names['Possible Matches'] = None
    df_names['Match Score'] = None
    df_names = df_names.apply(process_row, df_db_pull=df_db_pull, axis=1)
    
    # 生成带下拉列表的xlsm文件
    create_dropdowns(df_names, 'excel_write_test.xlsm')

if __name__ == '__main__':
    main()

关键修正说明 #

1. 加载原有工作簿：使用load_workbook(..., keep_vba=True)打开目标xlsm文件，确保宏代码不丢失
2. 数据写入逻辑：手动逐行写入避免DataFrame与openpyxl的对象冲突
3. 数据验证创建：用openpyxl官方的DataValidation类生成下拉列表，通过formula1传入逗号分隔的选项字符串
4. 异常处理：增加匹配结果为空的分支，避免索引报错

二、针对XLSM文件的其他解决办法 #

如果OpenPyXL方案仍有问题，可选择以下两种方式：

1. 用Win32com直接操作Excel（Windows平台） #

调用本地Excel应用程序，完全支持xlsm的所有功能，包括宏和复杂数据验证：

import win32com.client as win32

def win32com_process():
    excel = win32.Dispatch("Excel.Application")
    excel.Visible = False  # 设为True可查看操作过程
    wb = excel.Workbooks.Open(r'C:\your\path\excel_write_test.xlsm')
    ws = wb.ActiveSheet
    
    # 这里可嵌入你的匹配逻辑，示例设置B2的下拉列表
    ws.Range("B2").Validation.Add(
        Type=3,
        AlertStyle=1,
        Operator=1,
        Formula1='"Option1,Option2,Create New Database Entry"'
    )
    
    wb.SaveAs(r'C:\your\path\excel_write_test_updated.xlsm')
    wb.Close()
    excel.Quit()

2. 先处理为XLSX再转换为XLSM #

用XlsxWriter生成xlsx文件后，通过以下方式转为xlsm：

• 手动用Excel打开xlsx文件，选择「另存为」→「Excel 启用宏的工作簿（*.xlsm）」
• 用openpyxl加载生成的xlsx文件，再用save(..., keep_vba=True)保存为xlsm（需确保模板xlsm有宏代码）

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