TypeError when loading conditional formatting value objects (cfvo) with unexpected values

Issue #1385 resolved
Eiríkur Torfason created an issue

Steps to reproduce

  1. Create a new workbook in Microsoft Excel
  2. Select ‘Conditional Formatting’ → ‘Manage Rules…’
  3. Click the plus icon to add a new formatting rule (this should default to a 2-Colour Scale)
  4. Change the ‘Type’ of the Minimum to ‘Number’
  5. Enter a value that is NOT a number (e.g. =”<>100%”)
  6. Click ‘OK’
  7. Save the workbook
  8. Attempt to load the workbook using openpyxl

Observed behavior

The following error occurs

ws = openpyxl.load_workbook("openpyxl/tests/colorscale-type-error-repro.xlsx")
Traceback (most recent call last):
  File "/Users/eirikur/Projects/openpyxl/openpyxl/descriptors/base.py", line 55, in _convert
    value = expected_type(value)
ValueError: could not convert string to float: '"<>100%"'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "/Users/eirikur/Projects/openpyxl/openpyxl/reader/excel.py", line 314, in load_workbook
    reader.read()
  File "/Users/eirikur/Projects/openpyxl/openpyxl/reader/excel.py", line 279, in read
    self.read_worksheets()
  File "/Users/eirikur/Projects/openpyxl/openpyxl/reader/excel.py", line 227, in read_worksheets
    ws_parser.bind_all()
  File "/Users/eirikur/Projects/openpyxl/openpyxl/worksheet/_reader.py", line 421, in bind_all
    self.bind_cells()
  File "/Users/eirikur/Projects/openpyxl/openpyxl/worksheet/_reader.py", line 332, in bind_cells
    for idx, row in self.parser.parse():
  File "/Users/eirikur/Projects/openpyxl/openpyxl/worksheet/_reader.py", line 144, in parse
    dispatcher[tag_name](element)
  File "/Users/eirikur/Projects/openpyxl/openpyxl/worksheet/_reader.py", line 278, in parse_formatting
    cf = ConditionalFormatting.from_tree(element)
  File "/Users/eirikur/Projects/openpyxl/openpyxl/descriptors/serialisable.py", line 87, in from_tree
    obj = desc.expected_type.from_tree(el)
  File "/Users/eirikur/Projects/openpyxl/openpyxl/descriptors/serialisable.py", line 87, in from_tree
    obj = desc.expected_type.from_tree(el)
  File "/Users/eirikur/Projects/openpyxl/openpyxl/descriptors/serialisable.py", line 87, in from_tree
    obj = desc.expected_type.from_tree(el)
  File "/Users/eirikur/Projects/openpyxl/openpyxl/descriptors/serialisable.py", line 103, in from_tree
    return cls(**attrib)
  File "/Users/eirikur/Projects/openpyxl/openpyxl/formatting/rule.py", line 57, in __init__
    self.val = val
  File "/Users/eirikur/Projects/openpyxl/openpyxl/formatting/rule.py", line 36, in __set__
    super(ValueDescriptor, self).__set__(instance, value)
  File "/Users/eirikur/Projects/openpyxl/openpyxl/descriptors/base.py", line 67, in __set__
    value = _convert(self.expected_type, value)
  File "/Users/eirikur/Projects/openpyxl/openpyxl/descriptors/base.py", line 57, in _convert
    raise TypeError('expected ' + str(expected_type))
TypeError: expected <class 'float'>

Expected Behavior

The workbook loads successfully. The nonsensical colorscales are either ignored or the unexpected cfvo values replaced by fallback values (e.g. 0).

Comments (2)

  1. CharlieC

    I think the current behaviour is fine the constraints have been set to numbers and so junk should raise an exception. openpyxl won’t complain if the constraint has been set to a formula.

    Again, the specification is a bit sloppy here.

  2. Log in to comment