使用Pandas读取Excel文件时遇AttributeError问题求助

问题详情 #

使用Pandas提取xlsx文件数据时，原本通过pd.ExcelFile包装后读取的代码可正常运行，但直接调用pd.read_excel读取文件时触发AttributeError，报错信息为'ReadOnlyWorksheet' object has no attribute 'defined_names'。已安装openpyxl库，希望找到无需大量修改代码的解决方案。

原可运行代码 #

def read_data(DataFile):
    xlsLoad = pd.ExcelFile(DataFile)
    Bus = pd.read_excel(xlsLoad, 'bus').set_index('id')
    Gen = pd.read_excel(xlsLoad, 'gen').set_index('id')
    Line = pd.read_excel(xlsLoad, 'line').set_index('id')
    #id should be a column with unique information. 
    
    return Bus, Gen, Line


Bus, Gen, Line = read_data('RTS_Data.xlsx')
print("Data was read successfully.")
N=Bus.index
G=Gen.index
K=Line.index

修改后报错的代码片段 #

def read_data(DataFile):
    #xlsLoad = pd.ExcelFile.parse(DataFile)
    Bus = pd.read_excel(DataFile, 'bus').set_index('id')
    Gen = pd.read_excel(DataFile, 'gen').set_index('id')
    Line = pd.read_excel(DataFile, 'line').set_index('id')

完整报错栈 #

> AttributeError                            Traceback (most recent call last)
c:\Users\britt\Desktop\PythonLearn\PythonDCOPF\DCOPF.ipynb Cell 4 in <cell line: 3>()
      1 # Read Data
      2 #print(os.getcwd())
----> 3 Bus, Gen, Line = read_data('RTS_Data.xlsx')
      4 print("Data was read successfully.")
      5 N=Bus.index

c:\Users\britt\Desktop\PythonLearn\PythonDCOPF\DCOPF.ipynb Cell 4 in read_data(DataFile)
      1 def read_data(DataFile):
      2     #xlsLoad = pd.ExcelFile.parse(DataFile)
----> 3     Bus = pd.read_excel(DataFile, 'bus').set_index('id')
      4     Gen = pd.read_excel(DataFile, 'gen').set_index('id')
      5     Line = pd.read_excel(DataFile, 'line').set_index('id')

File c:\Users\britt\AppData\Local\Programs\Python\Python310\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 c:\Users\britt\AppData\Local\Programs\Python\Python310\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)),
...
--> 109     sheet.defined_names[name] = defn
    111 elif reserved == "Print_Titles":
    112     titles = PrintTitles.from_string(defn.value)

AttributeError: 'ReadOnlyWorksheet' object has no attribute 'defined_names'

解决方案 #

方案1：复用pd.ExcelFile对象（最小修改量） #

只需在read_data函数开头重新添加pd.ExcelFile的初始化代码，后续读取逻辑完全保留，几乎无需修改原有代码：

def read_data(DataFile):
    xlsLoad = pd.ExcelFile(DataFile)  # 仅添加这一行
    Bus = pd.read_excel(xlsLoad, 'bus').set_index('id')
    Gen = pd.read_excel(xlsLoad, 'gen').set_index('id')
    Line = pd.read_excel(xlsLoad, 'line').set_index('id')
    return Bus, Gen, Line

该方案利用pd.ExcelFile内部对文件的处理逻辑，避免直接调用pd.read_excel时触发的只读工作表问题。

方案2：指定openpyxl以可写模式加载工作簿 #

通过openpyxl手动加载工作簿并指定非只读模式，再传递给pd.read_excel：

from openpyxl import load_workbook

def read_data(DataFile):
    # 以可写模式加载工作簿，避免ReadOnlyWorksheet对象
    wb = load_workbook(DataFile, read_only=False)
    Bus = pd.read_excel(wb, 'bus').set_index('id')
    Gen = pd.read_excel(wb, 'gen').set_index('id')
    Line = pd.read_excel(wb, 'line').set_index('id')
    wb.close()  # 记得关闭工作簿
    return Bus, Gen, Line

方案3：调整openpyxl版本 #

部分新版本openpyxl与pandas的兼容性问题可能导致该报错，可尝试降级到稳定版本：

pip install openpyxl==3.0.10

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