python - 使用 openpyxl 2.6.2 读取 Excel 文件时出错（在 openpyxl 2.5.12 下工作）

Question

我编写了一个脚本来检查 Excel 文件是否包含工作表“预算”。这适用于 Python 3.7 和 openpyxl 2.5.12。但是，由于 Anaconda 将 openpyxl 更新到 2.6.2 版，它不再工作（请参阅下面的错误消息）。我在解决此错误消息时遇到问题。任何帮助表示赞赏。

# Import modules:
import os
import pandas as pd
from openpyxl import load_workbook
from pathlib import Path, PureWindowsPath

# Set correct path to Excel files:

excel_folder = PureWindowsPath("C:\\Users\\c3post\\Desktop\\Excel")
correct_excel_folder = Path(excel_folder)
print('Working directory: ', correct_excel_folder, '\n\n')

# Change current working directory so that opening relative paths will work:
os.chdir(correct_excel_folder)

# Add files to list:
files = os.listdir(correct_excel_folder)

# Filter 'xlsx' files and add them to new list:
files_xlsx = [f for f in files if f[-4:] == 'xlsx']

# Filter out xlsx files that do not contain the worksheet "Budget"
files_budget = []

for file in files_xlsx:
    wb = load_workbook(file)
    ws = wb.worksheets
    if 'Budget' in str(ws):
        files_budget.append(file)

print('Files that contain worksheet "Budget": ', files_budget, '\n\n')
print('Files that do not contain worksheet "Budget": ', set(files_xlsx) - set(files_budget))

错误信息：

---

KeyError                                  Traceback (most recent call last)
<ipython-input-2-fa1881e560e0> in <module>
     29 
     30 for file in files_xlsx:
---> 31     wb = load_workbook(file)
     32     ws = wb.worksheets
     33     if 'Budget' in str(ws):

~/anaconda3/lib/python3.7/site-packages/openpyxl/reader/excel.py in load_workbook(filename, read_only, keep_vba, data_only, keep_links)
    310     reader = ExcelReader(filename, read_only, keep_vba,
    311                         data_only, keep_links)
--> 312     reader.read()
    313     return reader.wb

~/anaconda3/lib/python3.7/site-packages/openpyxl/reader/excel.py in read(self)
    272         self.read_theme()
    273         apply_stylesheet(self.archive, self.wb)
--> 274         self.read_worksheets()
    275         self.parser.assign_names()
    276         if not self.read_only:

~/anaconda3/lib/python3.7/site-packages/openpyxl/reader/excel.py in read_worksheets(self)
    226                 ws._rels = rels
    227                 ws_parser = WorksheetReader(ws, fh, self.shared_strings, self.data_only)
--> 228                 ws_parser.bind_all()
    229 
    230             # assign any comments to cells

~/anaconda3/lib/python3.7/site-packages/openpyxl/worksheet/_reader.py in bind_all(self)
    401 
    402     def bind_all(self):
--> 403         self.bind_cells()
    404         self.bind_merged_cells()
    405         self.bind_hyperlinks()

~/anaconda3/lib/python3.7/site-packages/openpyxl/worksheet/_reader.py in bind_cells(self)
    324 
    325     def bind_cells(self):
--> 326         for idx, row in self.parser.parse():
    327             for cell in row:
    328                 style = self.ws.parent._cell_styles[cell['style_id']]

~/anaconda3/lib/python3.7/site-packages/openpyxl/worksheet/_reader.py in parse(self)
    148                 element.clear()
    149             elif tag_name == ROW_TAG:
--> 150                 row = self.parse_row(element)
    151                 element.clear()
    152                 yield row

~/anaconda3/lib/python3.7/site-packages/openpyxl/worksheet/_reader.py in parse_row(self, row)
    270         if keys != set(['r', 'spans']) and keys != set(['r']):
    271             # don't create dimension objects unless they have relevant information
--> 272             self.row_dimensions[attrs['r']] = attrs
    273 
    274         cells = [self.parse_cell(el) for el in row]

KeyError: 'r'

标签： pythonexcelopenpyxl