Openpyxl 无法读取 xlsx 文件，但如果我保存该文件，它会打开 [英] Openpyxl can't read xlsx file, but if i save the file, it opens

问题描述

所以，我试图用这一行用 openpyxl 打开一个 excel 文件

wb_bs = openpyxl.load_workbook(filename=filepath)

并得到这个错误:

<预><代码>C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\styles\stylesheet.py:214: UserWarning: Workbook contains no default style, apply openpyxl's default警告(工作簿不包含默认样式，应用openpyxl的默认样式")回溯(最近一次调用最后一次):wb_bs = openpyxl.load_workbook(filename=url_nova, data_only=True)文件C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\reader\excel.py"，load_workbook 中的第 315 行读者阅读()文件C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\reader\excel.py"，第 280 行，读取self.read_worksheets()文件C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\reader\excel.py"，第 228 行，在 read_worksheetsws_parser.bind_all()文件C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_reader.py"，第 434 行，在 bind_allself.bind_cells()文件C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_reader.py"，第 337 行，在 bind_cells对于 idx，self.parser.parse() 中的行:文件C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_reader.py"，第 149 行，解析obj = prop[1].from_tree(元素)文件C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\descriptors\serialisable.py"，第 87 行，在 from_treeobj = desc.expected_type.from_tree(el)文件C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\descriptors\serialisable.py"，第 103 行，在 from_tree返回 cls(**属性)类型错误:__init__() 得到了一个意外的关键字参数地址"PS C:\Users\T-Gamer\Python 程序\cmtrat\Cmtrat Helper>&C:/Users/T-Gamer/AppData/Local/Programs/Python/Python38-32/python.exec:/Users/T-Gamer/Pythonprograms/cmtrat/Cmtrat Helper/excel_scripts/ostest.py"C:\Users\T-Gamer\Python 程序\cmtrat\Cmtrat Helper\excel_scripts\copias\diario_padrao.xlsxC:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\styles\stylesheet.py:214: UserWarning: Workbook contains no default style, apply openpyxl's default警告(工作簿不包含默认样式，应用openpyxl的默认样式")回溯(最近一次调用最后一次):wb_bs = openpyxl.load_workbook(filename=url_nova, data_only=True)文件C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\reader\excel.py"，load_workbook 中的第 315 行读者阅读()文件C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\reader\excel.py"，第 280 行，读取self.read_worksheets()文件C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\reader\excel.py"，第 228 行，在 read_worksheetsws_parser.bind_all()文件C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_reader.py"，第 434 行，在 bind_allself.bind_cells()文件C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_reader.py"，第 337 行，在 bind_cells对于 idx，self.parser.parse() 中的行:文件C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_reader.py"，第 149 行，解析obj = prop[1].from_tree(元素)文件C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\descriptors\serialisable.py"，第 87 行，在 from_treeobj = desc.expected_type.from_tree(el)文件C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\descriptors\serialisable.py"，第 103 行，在 from_tree返回 cls(**属性)类型错误:__init__() 得到了一个意外的关键字参数地址"

事情是:

• 如果我创建 .xlsx 文件，它会打开
• 如果我从该特定来源(我需要的来源)下载文件并尝试立即打开它，则会产生错误.
• 如果我在打开并保存 .xlsx 文件(无更改)后运行代码，它会起作用.

我想这与 excel 版本冲突有关，但我已经尝试了所有方法，但似乎没有任何效果.

openpyxl==3.0.5蟒蛇==3.8.5

解决方案

这似乎是一个关于某些 Excel 文件的 Openpyxl 错误，报告在这里:https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1071

不幸的是，似乎没有修复方法，只有您找到的解决方法:

<块引用>

在刚刚发布的 Excel 版本(版本 1803(内部版本 9126.2259 即点即用))中，Microsoft 修改了超链接在 Excel 文件中的存储方式.作为解决方法，您可以尝试在 Google 表格或 LibreOffice 中打开并重新保存文件.我在数据验证方面遇到了类似的问题.
来源

So, I tried to open an excel file with openpyxl with this line

wb_bs = openpyxl.load_workbook(filename=filepath)

And got this error:

C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\styles\stylesheet.py:214: UserWarning: Workbook contains no default style, apply openpyxl's default
  warn("Workbook contains no default style, apply openpyxl's default")
Traceback (most recent call last):
    wb_bs = openpyxl.load_workbook(filename=url_nova, data_only=True)
  File "C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\reader\excel.py", line 315, in load_workbook
    reader.read()
  File "C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\reader\excel.py", line 280, in read
    self.read_worksheets()
  File "C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\reader\excel.py", line 228, in read_worksheets
    ws_parser.bind_all()
  File "C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_reader.py", line 434, in bind_all
    self.bind_cells()
  File "C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_reader.py", line 337, in bind_cells
    for idx, row in self.parser.parse():
  File "C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_reader.py", line 149, in parse
    obj = prop[1].from_tree(element)
  File "C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\descriptors\serialisable.py", line 87, in from_tree
    obj = desc.expected_type.from_tree(el)
  File "C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\descriptors\serialisable.py", line 103, in from_tree
    return cls(**attrib)
TypeError: __init__() got an unexpected keyword argument 'address'
PS C:\Users\T-Gamer\Python programs\cmtrat\Cmtrat Helper> & C:/Users/T-Gamer/AppData/Local/Programs/Python/Python38-32/python.exe "c:/Users/T-Gamer/Python programs/cmtrat/Cmtrat Helper/excel_scripts/ostest.py"
C:\Users\T-Gamer\Python programs\cmtrat\Cmtrat Helper\excel_scripts\copias\diario_padrao.xlsx
C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\styles\stylesheet.py:214: UserWarning: Workbook contains no default style, apply openpyxl's default
  warn("Workbook contains no default style, apply openpyxl's default")
Traceback (most recent call last):
    wb_bs = openpyxl.load_workbook(filename=url_nova, data_only=True)
  File "C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\reader\excel.py", line 315, in load_workbook
    reader.read()
  File "C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\reader\excel.py", line 280, in read
    self.read_worksheets()
  File "C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\reader\excel.py", line 228, in read_worksheets
    ws_parser.bind_all()
  File "C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_reader.py", line 434, in bind_all
    self.bind_cells()
  File "C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_reader.py", line 337, in bind_cells
    for idx, row in self.parser.parse():
  File "C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_reader.py", line 149, in parse
    obj = prop[1].from_tree(element)
  File "C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\descriptors\serialisable.py", line 87, in from_tree
    obj = desc.expected_type.from_tree(el)
  File "C:\Users\T-Gamer\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\descriptors\serialisable.py", line 103, in from_tree
    return cls(**attrib)
TypeError: __init__() got an unexpected keyword argument 'address'

The thing is:

• If I create the .xlsx file, it opens
• If I download the file from this specific source(the one I need) and try to open it straight away, it generates the error.
• If I run the code after I open and save the .xlsx file(no changes), it works.

I suppose it has something to do with excel version conflict, but I've tried everything and nothing seems to work.

openpyxl==3.0.5 python==3.8.5

解决方案

This seems to be an Openpyxl bug regarding some Excel files, reported here: https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1071

Unfortunately it looks like there's no fix, just the workaround you've found:

In the just released version of Excel (Version 1803 (Build 9126.2259 Click-to-Run)), Microsoft has modified the way hyperlinks are stored in Excel files. As a workaround, you could try opening and re-saving the file in Google Sheets or LibreOffice. I am experiencing similar issues with data validation.
Source

这篇关于Openpyxl 无法读取 xlsx 文件，但如果我保存该文件，它会打开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！