如何读取Excel中带数据验证（下拉列表）列的内容并导出

我需要读取多个Excel文件，处理数据后导出到新文件，但原文件中带数据验证（下拉列表）的3列，导出后数据丢失。我不需要保留下拉列表，只需要捕获这些列的显示内容。

原代码 #

folder_iter = Path(r"C:\my\folder\path").iterdir() 

#FOLDER LOOP
for folder in folder_iter:
    path_folder = Path(pasta)
    for folder_sec in path_folder.iterdir():
        path_folder_sec = Path(folder_sec)
        for planilha in Path(path_folder_sec).glob("*.xlsx"): 
            path_sheet = Path(path_sheet)
            sheet_citacao = pd.read_excel(path_sheet,sheet_name='CITAÇÃO ',index=False)
            sheet_penhora = pd.read_excel(path_sheet,sheet_name='PENHORA ',index=False)
            

            sheet_citacao.CDPASTA    = sheet_citacao.CDPASTA.astype(int) 
            sheet_citacao['PRAZO']   =   pd.to_datetime(sheet_citacao.PRAZO, format='%d-%m-%Y')
            sheet_citacao['PRAZO']   =   pd.to_datetime(sheet_citacao['PRAZO'])
            sheet_citacao['DTAJUIZ'] = pd.to_datetime(sheet_citacao['DTAJUIZ'])
        
        #FINAL CONCATENATION
        concat_final = pd.ExcelWriter('CITACAO_PENHORA_FULL.xlsx',engine='xlsxwriter')
        sheet_citacao.to_excel(concat_final, sheet_name='CITAÇÃO')
        sheet_penhora.to_excel(concat_final, sheet_name='PENHORA')
        
        concat_final.save()

问题现象 #

原Excel中带数据验证的列显示：

导出后的列数据显示（丢失下拉列表选中内容）：

解决方案 #

原因分析 #

pandas默认的read_excel无法正确读取下拉列表单元格的显示文本，因为这类单元格可能存储的是关联索引或公式，而非显示的内容。改用openpyxl并设置data_only=True，可以直接读取单元格的显示值。

修正后的代码 #

from pathlib import Path
import pandas as pd
from openpyxl import load_workbook

# 初始化空DataFrame用于收集所有文件的数据
all_citacao = pd.DataFrame()
all_penhora = pd.DataFrame()

folder_iter = Path(r"C:\my\folder\path").iterdir() 

# 遍历根目录下的文件夹
for folder in folder_iter:
    # 遍历子文件夹
    for folder_sec in folder.iterdir():
        # 遍历子文件夹中的所有xlsx文件
        for planilha in folder_sec.glob("*.xlsx"): 
            # 使用openpyxl读取Excel，data_only=True获取单元格显示值
            wb = load_workbook(planilha, data_only=True)
            
            # 处理「CITAÇÃO」工作表
            ws_citacao = wb['CITAÇÃO ']
            # 将工作表内容转为DataFrame
            df_citacao = pd.DataFrame(ws_citacao.values)
            # 设置第一行为列名
            df_citacao.columns = df_citacao.iloc[0]
            # 去掉表头行，重置索引
            df_citacao = df_citacao[1:].reset_index(drop=True)
            
            # 数据类型转换（保留原逻辑）
            df_citacao['CDPASTA'] = df_citacao['CDPASTA'].astype(int)
            # 日期转换添加errors参数，避免格式错误导致崩溃
            df_citacao['PRAZO'] = pd.to_datetime(df_citacao['PRAZO'], format='%d-%m-%Y', errors='coerce')
            df_citacao['DTAJUIZ'] = pd.to_datetime(df_citacao['DTAJUIZ'], errors='coerce')
            
            # 将当前文件的数据合并到总DataFrame
            all_citacao = pd.concat([all_citacao, df_citacao], ignore_index=True)
            
            # 处理「PENHORA」工作表，逻辑同上
            ws_penhora = wb['PENHORA ']
            df_penhora = pd.DataFrame(ws_penhora.values)
            df_penhora.columns = df_penhora.iloc[0]
            df_penhora = df_penhora[1:].reset_index(drop=True)
            
            all_penhora = pd.concat([all_penhora, df_penhora], ignore_index=True)

# 导出最终合并后的文件
with pd.ExcelWriter('CITACAO_PENHORA_FULL.xlsx', engine='xlsxwriter') as concat_final:
    all_citacao.to_excel(concat_final, sheet_name='CITAÇÃO', index=False)
    all_penhora.to_excel(concat_final, sheet_name='PENHORA', index=False)

关键修改点 #

1. 读取方式替换：用openpyxl的load_workbook(..., data_only=True)读取单元格显示值，确保下拉列表选中的文本被正确捕获。
2. 路径变量修正：原代码中Path(pasta)、Path(path_sheet)是无效变量，直接使用遍历得到的Path对象即可。
3. 数据收集逻辑优化：原代码每次循环都会覆盖导出文件，改为收集所有文件数据后再合并导出，避免数据丢失。
4. 容错处理：日期转换添加errors='coerce'，遇到格式错误的单元格会转为NaT，避免程序中断。

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