Pandas读取Excel触发TypeError，单表替换#N/A有效但全表处理崩溃

问题描述 #

使用Pandas读取Survey of Professional Forecasters.xlsx文件时触发TypeError，执行代码如下：

spf = pd.read_excel(
    "ds/us/survey_of_professional_forecasters_all.xlsx"
)

报错信息 #

TypeError                                 Traceback (most recent call last)
File D:\Programs\Python_Anaconda\lib\site-packages\openpyxl\descriptors\base.py:55, in _convert(expected_type, value)
     54 try:
---> 55     value = expected_type(value)
     56 except:

TypeError: an integer is required (got type datetime.date)

During handling of the above exception, another exception occurred:

TypeError                                 Traceback (most recent call last)
Input In [12], in <cell line: 1>()
----> 1 spf = pd.read_excel(
      2     "ds/us/survey_of_professional_forecasters_all.xlsx"
      3 )
      4 spf

File D:\Programs\Python_Anaconda\lib\site-packages\pandas\util\_decorators.py:211, in deprecate_kwarg.<locals>._deprecate_kwarg.<locals>.wrapper(*args, **kwargs)
    209     else:
    210         kwargs[new_arg_name] = new_arg_value
--> 211 return func(*args, **kwargs)

File D:\Programs\Python_Anaconda\lib\site-packages\pandas\util\_decorators.py:331, in deprecate_nonkeyword_arguments.<locals>.decorate.<locals>.wrapper(*args, **kwargs)
    325 if len(args) > num_allow_args:
    326     warnings.warn(
    327         msg.format(arguments=_format_argument_list(allow_args)),
    328         FutureWarning,
    329         stacklevel=find_stack_level(),
    330     )
--> 331 return func(*args, **kwargs)

File D:\Programs\Python_Anaconda\lib\site-packages\pandas\io\excel\_base.py:482, in read_excel(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, thousands, decimal, comment, skipfooter, convert_float, mangle_dupe_cols, storage_options)
    480 if not isinstance(io, ExcelFile):
    481     should_close = True
--> 482     io = ExcelFile(io, storage_options=storage_options, engine=engine)
    483 elif engine and engine != io.engine:
    484     raise ValueError(
    485         "Engine should not be specified when passing "
    486         "an ExcelFile - ExcelFile already has the engine set"
    487     )

File D:\Programs\Python_Anaconda\lib\site-packages\pandas\io\excel\_base.py:1695, in ExcelFile.__init__(self, path_or_buffer, engine, storage_options)
   1692 self.engine = engine
   1693 self.storage_options = storage_options
-> 1695 self._reader = self._engines[engine](self._io, storage_options=storage_options)

File D:\Programs\Python_Anaconda\lib\site-packages\pandas\io\excel\_openpyxl.py:557, in OpenpyxlReader.__init__(self, filepath_or_buffer, storage_options)
    547 """
    548 Reader using openpyxl engine.
    549 
   (...)
    554 {storage_options}
    555 """
    556 import_optional_dependency("openpyxl")
--> 557 super().__init__(filepath_or_buffer, storage_options=storage_options)

File D:\Programs\Python_Anaconda\lib\site-packages\pandas\io\excel\_base.py:545, in BaseExcelReader.__init__(self, filepath_or_buffer, storage_options)
    543 self.handles.handle.seek(0)
    544 try:
--> 545     self.book = self.load_workbook(self.handles.handle)
    546 except Exception:
    547     self.close()

File D:\Programs\Python_Anaconda\lib\site-packages\pandas\io\excel\_openpyxl.py:568, in OpenpyxlReader.load_workbook(self, filepath_or_buffer)
    565 def load_workbook(self, filepath_or_buffer: FilePath | ReadBuffer[bytes]):
    566     from openpyxl import load_workbook
--> 568     return load_workbook(
    569         filepath_or_buffer, read_only=True, data_only=True, keep_links=False
    570     )

File D:\Programs\Python_Anaconda\lib\site-packages\openpyxl\reader\excel.py:317, in load_workbook(filename, read_only, keep_vba, data_only, keep_links)
    290 """Open the given filename and return the workbook
    291 
    292 :param filename: the path to open or a file-like object
   (...)
    313 
    314 """
    315 reader = ExcelReader(filename, read_only, keep_vba,
    316                     data_only, keep_links)
--> 317 reader.read()
    318 return reader.wb

File D:\Programs\Python_Anaconda\lib\site-packages\openpyxl\reader\excel.py:279, in ExcelReader.read(self)
    277 self.read_strings()
    278 self.read_workbook()
--> 279 self.read_properties()
    280 self.read_theme()
    281 apply_stylesheet(self.archive, self.wb)

File D:\Programs\Python_Anaconda\lib\site-packages\openpyxl\reader\excel.py:173, in ExcelReader.read_properties(self)
    171 if ARC_CORE in self.valid_files:
    172     src = fromstring(self.archive.read(ARC_CORE))
--> 173     self.wb.properties = DocumentProperties.from_tree(src)

File D:\Programs\Python_Anaconda\lib\site-packages\openpyxl\descriptors\serialisable.py:103, in Serialisable.from_tree(cls, node)
    100     else:
    101         attrib[tag] = obj
--> 103 return cls(**attrib)

File D:\Programs\Python_Anaconda\lib\site-packages\openpyxl\packaging\core.py:104, in DocumentProperties.__init__(self, category, contentStatus, keywords, lastModifiedBy, lastPrinted, revision, version, created, creator, description, identifier, language, modified, subject, title)
    102 self.creator = creator
    103 self.lastModifiedBy = lastModifiedBy
--> 104 self.modified = modified
    105 self.created = created
    106 self.title = title

File D:\Programs\Python_Anaconda\lib\site-packages\openpyxl\descriptors\base.py:268, in DateTime.__set__(self, instance, value)
    266     except ValueError:
    267         raise ValueError("Value must be ISO datetime format")
--> 268 super(DateTime, self).__set__(instance, value)

File D:\Programs\Python_Anaconda\lib\site-packages\openpyxl\descriptors\nested.py:35, in Nested.__set__(self, instance, value)
     32         raise ValueError("Tag does not match attribute")
     34     value = self.from_tree(value)
---> 35 super(Nested, self).__set__(instance, value)

File D:\Programs\Python_Anaconda\lib\site-packages\openpyxl\descriptors\base.py:67, in Convertible.__set__(self, instance, value)
     64 def __set__(self, instance, value):
     65     if ((self.allow_none and value is not None)
     66         or not self.allow_none):
--> 67         value = _convert(self.expected_type, value)
     68     super(Convertible, self).__set__(instance, value)

File D:\Programs\Python_Anaconda\lib\site-packages\openpyxl\descriptors\base.py:57, in _convert(expected_type, value)
     55         value = expected_type(value)
     56     except:
---> 57         raise TypeError('expected ' + str(expected_type))
     58 return value

TypeError: expected <class 'datetime.datetime'>

当前现状：手动在单个工作表中替换所有#N/A为空值后可正常读取，但批量处理所有工作表时程序崩溃，需彻底解决方法。

解决方案 #

1. 更换Excel读取引擎 #

报错根源是openpyxl无法解析文件属性中的日期格式，改用其他引擎绕过该问题：

• 使用pyxlsb引擎（支持.xlsx/.xlsb文件，需先安装pip install pyxlsb）：

spf = pd.read_excel(
    "ds/us/survey_of_professional_forecasters_all.xlsx",
    engine="pyxlsb"
)

• 使用xlrd引擎（仅支持.xls文件，需安装xlrd==1.2.0，高版本xlrd不再支持.xlsx）：

spf = pd.read_excel(
    "ds/us/survey_of_professional_forecasters_all.xlsx",
    engine="xlrd"
)

2. 修复Excel文件的文档属性 #

文件的创建/修改日期格式不符合openpyxl预期，手动修复：

1. 打开目标Excel文件，点击文件→信息→属性→高级属性
2. 在摘要和统计标签页中，重新设置创建时间和修改时间，确保为完整的「日期+时间」格式
3. 保存文件后再用Pandas读取

3. 批量替换工作表中的#N/A #

用openpyxl遍历所有工作表，提前替换#N/A为空值后保存：

import pandas as pd
from openpyxl import load_workbook

file_path = "ds/us/survey_of_professional_forecasters_all.xlsx"
fixed_path = file_path.replace(".xlsx", "_fixed.xlsx")

# 加载工作簿并批量处理
wb = load_workbook(file_path, read_only=False, data_only=True)
for sheet_name in wb.sheetnames:
    ws = wb[sheet_name]
    # 遍历所有单元格替换#N/A
    for row in ws.iter_rows():
        for cell in row:
            if cell.value == "#N/A":
                cell.value = None

wb.save(fixed_path)
wb.close()

# 读取修复后的文件
spf = pd.read_excel(fixed_path)

4. 第三方库预处理文件 #

用pyexcel重新保存文件，自动修复格式问题（需安装pip install pyexcel pyexcel-xlsx）：

from pyexcel import get_book

file_path = "ds/us/survey_of_professional_forecasters_all.xlsx"
fixed_path = file_path.replace(".xlsx", "_fixed.xlsx")

# 读取并重新保存文件
book = get_book(file_name=file_path)
book.save_as(fixed_path)

# 读取修复后的文件
spf = pd.read_excel(fixed_path)

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