使用pandas读取xlsx文件时遇PatternFill.__init__参数错误求助

问题场景 #

作为pandas新手，尝试读取非空xlsx文件，代码如下：

import pandas as pd
df = pd.read_excel(r"C:\Users\HP\Desktop\NAME.xlsx", "Sheet1")
print(df)

预期输出表格数据，却触发以下错误：

Traceback (most recent call last):
  File "C:\Users\HP\Desktop\concept practicing in python\pandas practicing.py", line 21, in <module>
    df = pd.read_excel(r"C:\Users\HP\Desktop\NAME.xlsx", "Sheet1")
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\HP\PycharmProjects\pythonProject5\venv\Lib\site-packages\pandas\io\excel\_base.py", line 478, in read_excel
    io = ExcelFile(io, storage_options=storage_options, engine=engine)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\HP\PycharmProjects\pythonProject5\venv\Lib\site-packages\pandas\io\excel\_base.py", line 1513, in __init__
    self._reader = self._engines[engine](self._io, storage_options=storage_options)
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\HP\PycharmProjects\pythonProject5\venv\Lib\site-packages\pandas\io\excel\_openpyxl.py", line 549, in __init__
    super().__init__(filepath_or_buffer, storage_options=storage_options)
  File "C:\Users\HP\PycharmProjects\pythonProject5\venv\Lib\site-packages\pandas\io\excel\_base.py", line 540, in __init__
    self.book = self.load_workbook(self.handles.handle)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\HP\PycharmProjects\pythonProject5\venv\Lib\site-packages\pandas\io\excel\_openpyxl.py", line 560, in load_workbook
    return load_workbook(
           ^^^^^^^^^^^^^^
  File "C:\Users\HP\PycharmProjects\pythonProject5\venv\Lib\site-packages\openpyxl\reader\excel.py", line 346, in load_workbook
    reader.read()
  File "C:\Users\HP\PycharmProjects\pythonProject5\venv\Lib\site-packages\openpyxl\reader\excel.py", line 299, in read
    apply_stylesheet(self.archive, self.wb)
  File "C:\Users\HP\PycharmProjects\pythonProject5\venv\Lib\site-packages\openpyxl\styles\stylesheet.py", line 198, in apply_stylesheet
    stylesheet = Stylesheet.from_tree(node)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\HP\PycharmProjects\pythonProject5\venv\Lib\site-packages\openpyxl\styles\stylesheet.py", line 103, in from_tree
    return super(Stylesheet, cls).from_tree(node)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\HP\PycharmProjects\pythonProject5\venv\Lib\site-packages\openpyxl\descriptors\serialisable.py", line 83, in from_tree
    obj = desc.from_tree(el)
          ^^^^^^^^^^^^^^^^^^
  File "C:\Users\HP\PycharmProjects\pythonProject5\venv\Lib\site-packages\openpyxl\descriptors\sequence.py", line 94, in from_tree
    return [self.expected_type.from_tree(el) for el in node]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\HP\PycharmProjects\pythonProject5\venv\Lib\site-packages\openpyxl\descriptors\sequence.py", line 94, in <listcomp>
    return [self.expected_type.from_tree(el) for el in node]
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\HP\PycharmProjects\pythonProject5\venv\Lib\site-packages\openpyxl\styles\fills.py", line 64, in from_tree
    return PatternFill._from_tree(child)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\HP\PycharmProjects\pythonProject5\venv\Lib\site-packages\openpyxl\styles\fills.py", line 102, in _from_tree
    return cls(**attrib)
           ^^^^^^^^^^^^^
TypeError: PatternFill.__init__() got an unexpected keyword argument 'extLst'

解决方案 #

这个错误源于旧版本的openpyxl无法解析Excel文件中包含的扩展样式（extLst是扩展样式节点），可按以下方式解决：

• 升级openpyxl到最新版本：
  执行以下命令更新依赖：

  pip install --upgrade openpyxl
  

  更新后重新运行读取代码即可。

• 更换读取引擎：
  如果升级后问题仍存在，可尝试使用其他Excel读取引擎：

  • 使用pyxlsb引擎（需先安装）：

    pip install pyxlsb
    

    修改代码指定引擎：

    import pandas as pd
    df = pd.read_excel(r"C:\Users\HP\Desktop\NAME.xlsx", "Sheet1", engine='pyxlsb')
    print(df)
    

  • 若文件是xls格式，可使用xlrd引擎（注意xlrd 2.0+版本不再支持xlsx，需安装旧版：pip install xlrd==1.2.0）：

    df = pd.read_excel(r"C:\Users\HP\Desktop\NAME.xlsx", "Sheet1", engine='xlrd')
    

• 临时处理文件：
  将目标Excel文件重新另存为新的xlsx文件（保存时选择"仅保存数据"或清除特殊格式），再尝试读取。

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