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](https://foss.heptapod.net/openpyxl/openpyxl/-/blob/branch/3.1/openpyxl/worksheet/filters.py#L173) 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: https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1741 ## Examples ### Workbook In the attached workbook are three worksheets, `numeric`, `string` and `boolean` with some filtered tables. [filter-examples.xlsx](/uploads/33e078b7e36021c5e3b8204c99dd371f/filter-examples.xlsx) ### 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: ```python 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(): ```
issue