Openpyxl: 'ValueError: Max value is 14' 使用 load_workbook 时 [英] Openpyxl: 'ValueError: Max value is 14' when using load_workbook

问题描述

我试图打开一个 excel 文件，在该文件中我需要将数据框插入某些工作表，而其他工作表则不理会.当我在其他 excel 文件上测试它时，该脚本工作正常.当我在我真正需要的地方使用它时，我收到一条错误消息.

I have attempted to open an excel file in which I need to insert dataframes to certain sheets, while leaving other sheets alone. The script works fine when I tested it on other excel files. When I use it on the one I actually need, I get an error message.

脚本如下:

from openpyxl import load_workbook
book = load_workbook(self.directory)

Self.directory 指的是我的文件位置.正如您在回溯中看到的那样，尝试执行 load_workbook() 时它已经在这一行失败，并给出以下错误消息:

Self.directory refers to my file location. As you can see in the traceback, it fails already at this line when trying to execute load_workbook(), and gives the following error message:

值错误:最大值为 14

ValueError: Max value is 14

这是相关的回溯(我离开了以虚拟环境文件夹virtual"开头的目录位置):

Here is the relevant traceback (I left the directory locations starting with the virtual environment folder 'virtual'):

"""
book = load_workbook(self.directory)
virtual\lib\site-packages\openpyxl\reader\excel.py", line 217, in load_workbook
shared_strings = read_string_table(archive.read(strings_path))
virtual\lib\site-packages\openpyxl\reader\strings.py", line 22, in read_string_table
text = Text.from_tree(node).content
virtual\lib\site-packages\openpyxl\descriptors\serialisable.py", line 84, in from_tree
obj = desc.expected_type.from_tree(el)
virtual\lib\site-packages\openpyxl\descriptors\serialisable.py", line 84, in from_tree
obj = desc.expected_type.from_tree(el)
virtual\lib\site-packages\openpyxl\styles\fonts.py", line 110, in from_tree
return super(Font, cls).from_tree(node)
virtual\lib\site-packages\openpyxl\descriptors\serialisable.py", line 100, in from_tree
return cls(**attrib)
virtual\lib\site-packages\openpyxl\cell\text.py", line 114, in __init__
self.family = family
virtual\lib\site-packages\openpyxl\descriptors\nested.py", line 36, in __set__ 6, in __set__
super(Nested, self).__set__(instance, value)
virtual\lib\site-packages\openpyxl\descriptors\base.py", line 110, in __set__ , in __set__ 
super(Min, self).__set__(instance, value)
virtual\lib\site-packages\openpyxl\descriptors\base.py", line 89, in __set__ in __set__
raise ValueError('Max value is {0}'.format(self.max))
ValueError: Max value is 14
"""

我意识到我使用的 excelfile 超出了 self.max 的限制.

I realized that the excelfile I was using is over the limit of self.max.

我尝试自己筛选 openpyxl 脚本，但我无法弄清楚 self.max 指的是什么，或者如何更改我的 Excel 文件以便我可以加载工作簿.

I tried sifting through the the openpyxl scripts myself, but I could not manage to figure out what self.max refers to, or how I can change my Excel File so that I can load the workbook.

谁能指出我正确的方向?

Can anyone point me to the right direction?

提前致谢！

推荐答案

这是为我修复此错误的方法.我编辑了 lib\site-packages\openpyxl\descriptors\base.py 并在类 Max 的第 86 行之后添加了一条打印语句，如下所示:

Here's what fixed this error for me. I edited lib\site-packages\openpyxl\descriptors\base.py and added a print statement after line 86 in class Max like so:

def __set__(self, instance, value):
    if ((self.allow_none and value is not None)
        or not self.allow_none):
        value = _convert(self.expected_type, value)
        if value > self.max:
            print(f"value is {value}")
            raise ValueError('Max value is {0}'.format(self.max))
    super(Max, self).__set__(instance, value)

这打印了 34 的值，显然高于最大值 14(这是一个字体系列值).

This printed the value of 34 which is obviously higher than the max value of 14 (it's a font family value).

然后，我保存了一份扩展名为 .zip 的 Excel 电子表格副本，提取了所有 XML 文件，然后使用 grep 搜索 val=34".这让我找到了 3 个以某种方式具有 font-family=34 的单元格.我在 Excel 中将字体更改为其他字体，保存电子表格，然后将其更改回原始字体 (Arial) 并保存.
在这一切之后，错误消失了.

I then saved a copy of my Excel spreadsheet with a .zip extension, extracted all of the XML files, and then used grep searching for val="34". This led me to 3 cells which somehow had font-family=34 in them. I changed the font to something else in Excel, saved the spreadsheet, then changed it back to the original font (Arial) and saved.
After all of this, the error was gone.

这篇关于Openpyxl: 'ValueError: Max value is 14' 使用 load_workbook 时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！