子类化Pandas与Openpyxl读取Excel跳过删除线行报错求助

问题背景 #

需要解析并合并数百个Excel表格，要求跳过带有删除线格式的条目。此前基于@Henry Yik提供的子类化_OpenpyxlReader和CustomExcelFile的代码稳定运行一年以上，但10月初pandas库更新后运行报错：TypeError: CustomReader.get_sheet_data() takes 3 positional arguments but 4 were given。尝试修改CustomReader类及恢复旧环境均未解决，当前临时方案是使用pandas=1.4.1的独立conda环境，现寻求适配新版库的修复方案。

原代码 #

import pandas as pd
from pandas.io.excel._openpyxl import _OpenpyxlReader
from pandas._typing import Scalar
from typing import List
from pandas.io.excel._odfreader import _ODFReader
from pandas.io.excel._xlrd import _XlrdReader

class CustomReader(_OpenpyxlReader):
    def get_sheet_data(self, sheet, convert_float: bool) -> List[List[Scalar]]:
        data = []
        for row in sheet.rows:
            first = row[1] # 仅检查该单元格的删除线格式
            if first.value is not None and first.font.strike: continue
            else:
                data.append([self._convert_cell(cell, convert_float) for cell in row])
        return data

class CustomExcelFile(pd.ExcelFile):
    _engines = {"xlrd": _XlrdReader, "openpyxl": CustomReader, "odf": _ODFReader}

报错栈信息 #

excel = CustomExcelFile(r"excel_file_name.xlsx", engine="openpyxl")
df = excel.parse()

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In [12], line 17
     15     file.decrypt(decrypted)   
     16     excel = CustomExcelFile(decrypted, engine = "openpyxl")
---> 17     data = excel.parse(usecols="A:M", index = 0, header = 0)


File /home/dev/anaconda3/envs/lwo_datasci/lib/python3.10/site-packages/pandas/io/excel/_base.py:1734, in ExcelFile.parse(self, sheet_name, header, names, index_col, usecols, squeeze, converters, true_values, false_values, skiprows, nrows, na_values, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, **kwds)
   1700 def parse(
   1701     self,
   1702     sheet_name: str | int | list[int] | list[str] | None = 0,
   (...)
   1721     **kwds,
   1722 ) -> DataFrame | dict[str, DataFrame] | dict[int, DataFrame]:
   1723     """
   1724     Parse specified sheet(s) into a DataFrame.
   1725 
   (...)
   1732         DataFrame from the passed in Excel file.
   1733     """
-> 1734     return self._reader.parse(
   1735         sheet_name=sheet_name,
   1736         header=header,
   1737         names=names,
   1738         index_col=index_col,
   1739         usecols=usecols,
   1740         squeeze=squeeze,
   1741         converters=converters,
   1742         true_values=true_values,
   1743         false_values=false_values,
   1744         skiprows=skiprows,
   1745         nrows=nrows,
   1746         na_values=na_values,
   1747         parse_dates=parse_dates,
   1748         date_parser=date_parser,
   1749         thousands=thousands,
   1750         comment=comment,
   1751         skipfooter=skipfooter,
   1752         convert_float=convert_float,
   1753         mangle_dupe_cols=mangle_dupe_cols,
   1754         **kwds,
   1755     )

File /home/dev/anaconda3/envs/lwo_datasci/lib/python3.10/site-packages/pandas/io/excel/_base.py:765, in BaseExcelReader.parse(self, sheet_name, header, names, index_col, usecols, squeeze, dtype, true_values, false_values, skiprows, nrows, na_values, verbose, parse_dates, date_parser, thousands, decimal, comment, skipfooter, convert_float, mangle_dupe_cols, **kwds)
    762     sheet = self.get_sheet_by_index(asheetname)
    764 file_rows_needed = self._calc_rows(header, index_col, skiprows, nrows)
---> 765 data = self.get_sheet_data(sheet, convert_float, file_rows_needed)
    766 if hasattr(sheet, "close"):
    767     # pyxlsb opens two TemporaryFiles
    768     sheet.close()

TypeError: CustomReader.get_sheet_data() takes 3 positional arguments but 4 were given
> TypeError: CustomReader.get_sheet_data() takes 3 positional arguments
> but 4 were given

修复方案 #

新版本pandas对get_sheet_data方法新增了file_rows_needed参数，导致自定义方法的签名与父类不匹配。只需修改CustomReader的get_sheet_data方法，添加该参数即可（无需在逻辑中使用）：

修改后的代码 #

import pandas as pd
from pandas.io.excel._openpyxl import _OpenpyxlReader
from pandas._typing import Scalar
from typing import List
from pandas.io.excel._odfreader import _ODFReader
from pandas.io.excel._xlrd import _XlrdReader

class CustomReader(_OpenpyxlReader):
    # 新增file_rows_needed参数，保持与父类方法签名一致
    def get_sheet_data(self, sheet, convert_float: bool, file_rows_needed) -> List[List[Scalar]]:
        data = []
        for row in sheet.rows:
            first = row[1] # 仅检查该单元格的删除线格式
            if first.value is not None and first.font.strike: 
                continue
            data.append([self._convert_cell(cell, convert_float) for cell in row])
        return data

class CustomExcelFile(pd.ExcelFile):
    _engines = {"xlrd": _XlrdReader, "openpyxl": CustomReader, "odf": _ODFReader}

说明 #

• 新版本pandas的BaseExcelReader类中，get_sheet_data方法新增file_rows_needed参数用于优化行数读取逻辑
• 自定义方法必须保持与父类一致的参数列表，否则会触发参数不匹配的TypeError
• 新增的参数无需在业务逻辑中使用，仅需在方法签名中声明即可，完全不影响原有的删除线判断逻辑

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