使用Pandas ExcelWriter实现跨工作表级联下拉菜单的问题

问题核心 #

单工作表级联下拉功能正常，但将引用数据移至独立工作表（Sheet2）时，因跨表引用格式错误导致文件损坏，且无法正确关联级联下拉的数据源。

解决方案 #

Excel跨表引用的正确格式为工作表名!单元格范围，而非错误的工作表名(公式)格式。以下是修正后的完整实现：

完整代码 #

import numpy as np
import pandas as pd

newVals = np.empty([100,4])
df = pd.DataFrame(newVals)
n_rows = df.shape[0]

# 初始化ExcelWriter
writer = pd.ExcelWriter('/trial/DF_New.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')

# 获取工作簿及工作表对象
workbook = writer.book
worksheet1 = writer.sheets['Sheet1']
# 创建并获取Sheet2用于存放引用数据
worksheet2 = workbook.add_worksheet('Sheet2')

# 设置Sheet1的解锁格式
unlocked = workbook.add_format({'locked': False})
worksheet1.set_column('B:F', 25, unlocked)

# --------------------------
# 在Sheet2中写入所有级联引用数据
# --------------------------
# 第一层级联数据源（对应Sheet1的B列）
worksheet2.write('I1', 'Cobra')
worksheet2.write('I2', 'Fault')
worksheet2.write('I3', 'IT')
worksheet2.write('I4', 'Quality')
worksheet2.write('I5', 'Misc')
worksheet2.write('I6', 'Alignment')

worksheet2.write('J1', 'Mamba')
worksheet2.write('J2', 'Fault')
worksheet2.write('J3', 'IT')
worksheet2.write('J4', 'Quality')
worksheet2.write('J5', 'Misc')
worksheet2.write('J6', 'Mamba_Lag')

worksheet2.write('K1', 'Python')
worksheet2.write('K2', 'Fault')
worksheet2.write('K3', 'IT')
worksheet2.write('K4', 'Quality')
worksheet2.write('K5', 'Misc')
worksheet2.write('K6', 'Viscosity')

# 第二层级联数据源（对应Sheet1的C列）
worksheet2.write('L1','Fault')
worksheet2.write('L2','Electrical_Setup')
worksheet2.write('L3','Fluidics_Issue')

worksheet2.write('M1','IT')
worksheet2.write('M2','No_Comms')
worksheet2.write('M3','PC_Issues')

worksheet2.write('N1','Quality')
worksheet2.write('N2','Internal_Quality')
worksheet2.write('N3','External_Quality')

worksheet2.write('O1','Misc')
worksheet2.write('O2','Personnel')
worksheet2.write('O3','Other')

worksheet2.write('P1','Alignment')
worksheet2.write('P2','Left Over')
worksheet2.write('P3','Right Over')

worksheet2.write('Q1','Viscosity')
worksheet2.write('Q2','50-75%')
worksheet2.write('Q3','>75%')

worksheet2.write('R1','Mamba_Lag')
worksheet2.write('R2','At_Setup')
worksheet2.write('R3','Other_Time')

# --------------------------
# 在Sheet1中设置级联下拉菜单
# --------------------------
# B列：第一级下拉
worksheet1.data_validation('B2:B'+str(1+n_rows), {'validate': 'list', 'source': ['Cobra', 'Mamba', 'Python']})

# C列：第二级下拉，引用Sheet2的数据
for i in range(n_rows):
    worksheet1.data_validation(
        f'C{2+i}',
        {
            'validate': 'list',
            # 关键：使用Sheet2!前缀指定跨表引用
            'source': f'=Sheet2!INDEX($I$2:$K$6, 0, MATCH($B${2+i}, $I$1:$K$1, 0))'
        }
    )

# D列：第三级下拉，引用Sheet2的数据
for i in range(n_rows):
    worksheet1.data_validation(
        f'D{2+i}',
        {
            'validate': 'list',
            # 关键：使用Sheet2!前缀指定跨表引用
            'source': f'=Sheet2!INDEX($L$2:$R$3, 0, MATCH($C${2+i}, $L$1:$R$1, 0))'
        }
    )

# 保护工作表
worksheet1.protect()

# 保存并关闭
writer.close()

关键修正点 #

1. 跨表引用格式：将错误的=Sheet2(INDEX(...))改为=Sheet2!INDEX(...)，!是Excel跨表引用的标准分隔符
2. Sheet2的创建：通过workbook.add_worksheet('Sheet2')创建独立工作表，确保能正常写入和引用数据
3. 公式字符串拼接：使用f-string替代传统字符串拼接，提升代码可读性和准确性

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