Skip to content

Add information to the ValueError when loading xlsx fails

Anthony Hayward requested to merge topic/3.1/error-info-on-load into branch/3.1

Information added to ValueError output:

  • Name of file that failed to load. Could be helpful if a failing script loads multiple xslx files.
  • Identify which step of the loading process caused the error.
  • Show the element name in the stylesheet that was invalid, and also the invalid value, not just the permitted values.

Error output

In this example, c:\temp\bad.xlsx contains invalid <u val="1"/> as described in #1766 (closed)

Before

openpyxl.load_workbook(r"c:\temp\bad.xlsx")

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\code\oss\openpyxl\openpyxl\reader\excel.py", line 322, in load_workbook
    reader.read()
  File "C:\code\oss\openpyxl\openpyxl\reader\excel.py", line 286, in read
    apply_stylesheet(self.archive, self.wb)
  File "C:\code\oss\openpyxl\openpyxl\styles\stylesheet.py", line 198, in apply_stylesheet
    stylesheet = Stylesheet.from_tree(node)
  File "C:\code\oss\openpyxl\openpyxl\styles\stylesheet.py", line 103, in from_tree
    return super(Stylesheet, cls).from_tree(node)
  File "C:\code\oss\openpyxl\openpyxl\descriptors\serialisable.py", line 83, in from_tree
    obj = desc.from_tree(el)
  File "C:\code\oss\openpyxl\openpyxl\descriptors\sequence.py", line 85, in from_tree
    return [self.expected_type.from_tree(el) for el in node]
  File "C:\code\oss\openpyxl\openpyxl\descriptors\sequence.py", line 85, in <listcomp>
    return [self.expected_type.from_tree(el) for el in node]
  File "C:\code\oss\openpyxl\openpyxl\styles\fonts.py", line 109, in from_tree
    return super(Font, cls).from_tree(node)
  File "C:\code\oss\openpyxl\openpyxl\descriptors\serialisable.py", line 103, in from_tree
    return cls(**attrib)
  File "C:\code\oss\openpyxl\openpyxl\styles\fonts.py", line 87, in __init__
    self.u = u
  File "C:\code\oss\openpyxl\openpyxl\descriptors\nested.py", line 35, in __set__
    super(Nested, self).__set__(instance, value)
  File "C:\code\oss\openpyxl\openpyxl\descriptors\base.py", line 143, in __set__
    super(NoneSet, self).__set__(instance, value)
  File "C:\code\oss\openpyxl\openpyxl\descriptors\base.py", line 128, in __set__
    raise ValueError(self.__doc__)
ValueError: Value must be one of {'double', 'singleAccounting', 'doubleAccounting', 'single'}

After

openpyxl.load_workbook(r"c:\temp\bad.xlsx")

Traceback (most recent call last):
  File "C:\code\oss\openpyxl\openpyxl\reader\excel.py", line 293, in read
    apply_stylesheet(self.archive, self.wb)
  File "C:\code\oss\openpyxl\openpyxl\styles\stylesheet.py", line 198, in apply_stylesheet
    stylesheet = Stylesheet.from_tree(node)
  File "C:\code\oss\openpyxl\openpyxl\styles\stylesheet.py", line 103, in from_tree
    return super(Stylesheet, cls).from_tree(node)
  File "C:\code\oss\openpyxl\openpyxl\descriptors\serialisable.py", line 83, in from_tree
    obj = desc.from_tree(el)
  File "C:\code\oss\openpyxl\openpyxl\descriptors\sequence.py", line 85, in from_tree
    return [self.expected_type.from_tree(el) for el in node]
  File "C:\code\oss\openpyxl\openpyxl\descriptors\sequence.py", line 85, in <listcomp>
    return [self.expected_type.from_tree(el) for el in node]
  File "C:\code\oss\openpyxl\openpyxl\styles\fonts.py", line 109, in from_tree
    return super(Font, cls).from_tree(node)
  File "C:\code\oss\openpyxl\openpyxl\descriptors\serialisable.py", line 103, in from_tree
    return cls(**attrib)
  File "C:\code\oss\openpyxl\openpyxl\styles\fonts.py", line 87, in __init__
    self.u = u
  File "C:\code\oss\openpyxl\openpyxl\descriptors\nested.py", line 35, in __set__
    super(Nested, self).__set__(instance, value)
  File "C:\code\oss\openpyxl\openpyxl\descriptors\base.py", line 147, in __set__
    super(NoneSet, self).__set__(instance, value)
  File "C:\code\oss\openpyxl\openpyxl\descriptors\base.py", line 132, in __set__
    raise ValueError(message)
ValueError: Value for 'u' must be one of {'doubleAccounting', 'single', None, 'double', 'singleAccounting'}, but '1' was found

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\code\oss\openpyxl\openpyxl\reader\excel.py", line 334, in load_workbook
    data_only, keep_links)
  File "C:\code\oss\openpyxl\openpyxl\reader\excel.py", line 302, in read
    self.archive.close()
ValueError: Failed to load Excel file: could not read stylesheet from C:\temp\bad.xlsx

I've added pytests and checked that it works on Python 3.6 as well as 3.9.

--HG-- branch : 3.1

Merge request reports