Pandas.read_excel()无法正确读取部分Excel文件，返回相同异常输出

我是一名新手开发者，使用pandas.read_excel()批量读取大量Excel文件时，多数文件可正常读取，但部分文件会返回完全相同的异常输出。排查后发现，这些异常文件中都存在名为“Macro1”的隐藏工作表，正是该工作表导致了异常输出。

代码示例 #

import pandas as pd

files = """AllExcels\NH1 CE A+C 20W AI 20th Oct 2023.xls
AllExcels\33WPD B003 B005 A+C BOM update.xls
AllExcels\33WPD B003 B005 Single C BOM(2).xls
AllExcels\35W B011 A+C BOM.xls
AllExcels\35W B011 Single C BOM.xls
AllExcels\CE 20W  A+C Rev.xls
AllExcels\CE 20W  A+C.xls
AllExcels\CE NH 1 QC 18W April 07, 2023 (1).xls
AllExcels\CE NH 2 QC 18W April 07, 2023 (1).xls
AllExcels\CE NH 2 QC 18W May 08，2023.xls
AllExcels\CE NH1 A+C 20W March 29, 2023.xls
AllExcels\NH 1 CE QC 18W BOM 20th Oct 2023.xls
AllExcels\NH1 CE A+C 20W AI 20th Oct 2023.xls"""
filesList = files.splitlines()
allSheets = {}
num1 = 0
num2 = 0
num3 = 0
for file in filesList:
    file_read = pd.read_excel(file, sheet_name=None)
    num2 = 0
    for sheet_name, sheet in file_read.items():
        sheet_name = "page" + str(num3)
        allSheets[sheet_name] = sheet
        print(sheet)
        num2 += 1
        num3 += 1
    num1 += 1

异常输出示例（所有异常文件输出完全一致） #

Unnamed: 0
0         1.0
1         NaN
2         NaN
3         NaN
4         0.0
5         1.0

解决方案 #

方案1：直接过滤“Macro1”工作表 #

在遍历读取到的工作表时，跳过名为“Macro1”的表：

import pandas as pd

files = """AllExcels\NH1 CE A+C 20W AI 20th Oct 2023.xls
AllExcels\33WPD B003 B005 A+C BOM update.xls
AllExcels\33WPD B003 B005 Single C BOM(2).xls
AllExcels\35W B011 A+C BOM.xls
AllExcels\35W B011 Single C BOM.xls
AllExcels\CE 20W  A+C Rev.xls
AllExcels\CE 20W  A+C.xls
AllExcels\CE NH 1 QC 18W April 07, 2023 (1).xls
AllExcels\CE NH 2 QC 18W April 07, 2023 (1).xls
AllExcels\CE NH 2 QC 18W May 08，2023.xls
AllExcels\CE NH1 A+C 20W March 29, 2023.xls
AllExcels\NH 1 CE QC 18W BOM 20th Oct 2023.xls
AllExcels\NH1 CE A+C 20W AI 20th Oct 2023.xls"""
filesList = files.splitlines()
allSheets = {}
num1 = 0
num2 = 0
num3 = 0
for file in filesList:
    file_read = pd.read_excel(file, sheet_name=None)
    num2 = 0
    for sheet_name, sheet in file_read.items():
        # 跳过Macro1工作表
        if sheet_name == "Macro1":
            continue
        sheet_name = "page" + str(num3)
        allSheets[sheet_name] = sheet
        print(sheet)
        num2 += 1
        num3 += 1
    num1 += 1

方案2：跳过所有隐藏工作表 #

如果存在其他隐藏工作表也可能引发问题，可以借助openpyxl判断工作表状态，只读取非隐藏表：

import pandas as pd
from openpyxl import load_workbook

files = """AllExcels\NH1 CE A+C 20W AI 20th Oct 2023.xls
AllExcels\33WPD B003 B005 A+C BOM update.xls
AllExcels\33WPD B003 B005 Single C BOM(2).xls
AllExcels\35W B011 A+C BOM.xls
AllExcels\35W B011 Single C BOM.xls
AllExcels\CE 20W  A+C Rev.xls
AllExcels\CE 20W  A+C.xls
AllExcels\CE NH 1 QC 18W April 07, 2023 (1).xls
AllExcels\CE NH 2 QC 18W April 07, 2023 (1).xls
AllExcels\CE NH 2 QC 18W May 08，2023.xls
AllExcels\CE NH1 A+C 20W March 29, 2023.xls
AllExcels\NH 1 CE QC 18W BOM 20th Oct 2023.xls
AllExcels\NH1 CE A+C 20W AI 20th Oct 2023.xls"""
filesList = files.splitlines()
allSheets = {}
num1 = 0
num2 = 0
num3 = 0
for file in filesList:
    # 加载工作簿判断工作表状态
    wb = load_workbook(file, read_only=True)
    # 筛选非隐藏工作表
    valid_sheets = [name for name in wb.sheetnames if wb[name].sheet_state != 'hidden']
    # 只读取有效工作表
    file_read = pd.read_excel(file, sheet_name=valid_sheets)
    num2 = 0
    for sheet_name, sheet in file_read.items():
        sheet_name = "page" + str(num3)
        allSheets[sheet_name] = sheet
        print(sheet)
        num2 += 1
        num3 += 1
    num1 += 1

方案3：指定读取目标工作表 #

如果你明确知道每个文件需要读取的工作表名称，可以直接在read_excel中指定sheet_name参数，避免读取无关工作表：

# 假设目标工作表名为"BOM"
file_read = pd.read_excel(file, sheet_name="BOM")

内容的提问来源于stack exchange，提问作者J.Ruffalo