Unable to parse workbooks with `customFilter` for empty/non-empty numerical columns (3.1.0)
(Breaking this into a new issue as I noticed the previous ticket had been closed as non-reproducible.)
Affected Versions
openpyxl@3.1.0
Overview
While parsing a workbook with a customFilter on a TRUE/FALSE field (i.e. numeric), an exception is thrown.
| Traceback (most recent call last): |
|----------------------------------------------------------------------------------------------------------------------|
| File "/usr/local/lib/python3.10/site-packages/openpyxl/reader/excel.py", line 301, in read |
| self.read_worksheets() |
| File "/usr/local/lib/python3.10/site-packages/openpyxl/reader/excel.py", line 261, in read_worksheets |
| table = Table.from_tree(xml) |
| File "/usr/local/lib/python3.10/site-packages/openpyxl/descriptors/serialisable.py", line 87, in from_tree |
| obj = desc.expected_type.from_tree(el) |
| File "/usr/local/lib/python3.10/site-packages/openpyxl/descriptors/serialisable.py", line 87, in from_tree |
| obj = desc.expected_type.from_tree(el) |
| File "/usr/local/lib/python3.10/site-packages/openpyxl/descriptors/serialisable.py", line 87, in from_tree |
| obj = desc.expected_type.from_tree(el) |
| [Previous line repeated 1 more time] |
| File "/usr/local/lib/python3.10/site-packages/openpyxl/descriptors/serialisable.py", line 103, in from_tree |
| return cls(**attrib) |
| File "/usr/local/lib/python3.10/site-packages/openpyxl/worksheet/filters.py", line 184, in __init__ |
| self.val = val |
| File "/usr/local/lib/python3.10/site-packages/openpyxl/worksheet/filters.py", line 165, in __set__ |
| raise ValueError("Value must be either numerical or a string containing a wildcard") |
| ValueError: Value must be either numerical or a string containing a wildcard |
...snip...
| File "/usr/local/lib/python3.10/site-packages/<...redacted...>.py", line 143, in _load_workbook |
| return load_workbook(filename=filename, data_only=True, read_only=False) |
| File "/usr/local/lib/python3.10/site-packages/openpyxl/reader/excel.py", line 346, in load_workbook |
| reader.read() |
| File "/usr/local/lib/python3.10/site-packages/openpyxl/reader/excel.py", line 307, in read |
| raise ValueError( |
| ValueError: Unable to read workbook: could not read worksheets from /tmp/tmpbrvz_pih.xlsx. |It appears the customFilter regex expression is too strict in some cases. I've included an example workbook with various filters enabled. I believe the issue was introduce to fix this issue with custom filters: #1741 (closed)
Examples
Workbook
In the attached workbook are three worksheets, numeric, string and boolean with some filtered tables.
XML
A specific example of the xml which is making us choke is here, this is used to filter a boolean column is empty/not empty. This example can be found in filter-examples/xl/tables/table3.xml which contains the boolean filters.
<customFilter operator="notEqual" val=" "/>Potential fixes
A solution could be to extend the existing regex to include a pattern for one or more space characters +
pattern = re.compile(r"\d+|^\*.+|^.+\*| +$")Alternatively, you could try and avoid the regex and change the logic to be:
def __set__(self, instance, value):
if isinstance(value, str) and not value.lstrip("-").isnumeric():
self.expected_type = str
super().__set__(instance, value)Where we are testing if the stripped value, to avoid negatives, is a numeric string.
Apologies for not setting up a MR, I'm not very experienced with heptapod/mecurial, but I've included a potential solution in diff form.
output of $ hg diff
diff -r 1dfe7c40371f openpyxl/worksheet/filters.py
--- a/openpyxl/worksheet/filters.py Mon Feb 13 17:44:42 2023 +0100
+++ b/openpyxl/worksheet/filters.py Wed Feb 15 12:01:09 2023 +0000
@@ -155,15 +155,15 @@
Excel uses wildcards for string matching
"""
- pattern = re.compile(r"\d+|^\*.+|^.+\*$")
+ pattern = re.compile(r"\d+|^\*.+|^.+\*| +$")
expected_type = float
def __set__(self, instance, value):
if isinstance(value, str):
m = self.pattern.match(value)
if not m:
- raise ValueError("Value must be either numerical or a string containing a wildcard")
- if "*" in value:
+ raise ValueError("Value must be either numerical, blank or a string containing a wildcard")
+ if "*" in value or not value.strip():
self.expected_type = str
super().__set__(instance, value)
diff -r 1dfe7c40371f openpyxl/worksheet/tests/test_filters.py
--- a/openpyxl/worksheet/tests/test_filters.py Mon Feb 13 17:44:42 2023 +0100
+++ b/openpyxl/worksheet/tests/test_filters.py Wed Feb 15 12:01:09 2023 +0000
@@ -285,6 +285,14 @@
fut = CustomFilter.from_tree(node)
assert fut == CustomFilter(val="K*", operator="equal")
+ def test_blank_filter(self, CustomFilter):
+ src = """
+ <customFilter val=" " operator="equal" />
+ """
+ node = fromstring(src)
+ fut = CustomFilter.from_tree(node)
+ assert fut == CustomFilter(val=" ", operator="equal")
+
@pytest.fixture
def CustomFilters():