BUG: cannot open xlsx file with active filter

[azhu26]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd;

pd.read_excel(excel_file, sheet_name=sheet, header=0);

Issue Description

I recently upgraded pandas to the latest 2.0.1 version. the problem appears after this upgrade: if the input excel file has an active filter (filtered on the header row and set some values to be filtered out). then the read_excel will throw exception: value must be either numerical or a string containing a wildcard. the filtered values contain blank cell.

if leaving the filter with "select all", then there is no exception from read_excel. so this is definitely from the new version of pandas. my previous version is 1.2.3 and I never had this problem before. (but the same issue may be raised in earlier version but don't know for sure)

Expected Behavior

the read_excel should be able to proceed without raising exception. if it can honor the excel filter, that is better.

Installed Versions

2.0.1

[topper-123]

Thanks for the report, @azhu26.

We need a working example in order to look into this. Can you upload a excel file with this issue.

[topper-123]

Can you also make the title more descriptive.

[azhu26]

thank you for looking into this.

The problem occurs when the filter on "blank" cells, see attached example below.

Also, not sure how to change the title; when open the ticket to report, not aware a place to input the title though.

Please let me know if anything that I can help.

test-filter.xlsx

[topper-123]

Ok thanks @azhu26.

I tried this on the dev branch with openpyxl v.3.0.10 and everything worked as intended. I then tried upgrading to openpyxl v3.1.2 and I got the error you describe ValueError: Value must be either numerical or a string containing a wildcard.

I then tried open the file directly in openpyxl:

• using v3.1.2: failed with message: ValueError: Unable to read workbook: could not read worksheets from ../test-filter.xlsx. This is most probably because the workbook source files contain some invalid XML. Please see the exception for more details.
• using v.3.0.10: it opened without any issue.

So this is likely an openpyxl issue on v.3.1, and not a pandas issue. Can you verify that the file can be opened if you downgrade openpyxl to v.3.0.

[azhu26]

Hi, Terji: thank you very much.

Yes, I can confirm: after downgrading openpyxl to 3.0.10, the exception disappeared and everything works smoothly.

[topper-123]

Ok, I'll close this issue then.

Can you bring up this issue with the openpyxl people, to see if this is an issues with openpyxl?

[rhshadrach]

Related: #51557, #51392

[azhu26]

Hi, Terji:

thanks. Yes, there is an existing open issue on openpyxl and I added my comments there. Thanks again for quick efforts.

[whuwangyong]

related: versions 3.1.0 and up fail to open files saved with active filters