BUG: ExcelFile.parse sets sheet.max_row and sheet.max_column to None

[behrenhoff]

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

# /// script
# dependencies = [
#     "openpyxl",
#     "pandas",
# ]
# ///
import pandas as pd

# Assuming "some_file.xlsx" exists and contains a sheet "Sheet1" with
# some data, for example cell A1="A Header" and cell A2=1234

with pd.ExcelFile("some_file.xlsx") as excel_file:
    ws = excel_file.book["Sheet1"]

    dim_before = (ws.max_row, ws.max_column)
    print(f"Dimensions of Sheet1: {dim_before=}")

    df = excel_file.parse(sheet_name=ws.title)

    dim_after = (ws.max_row, ws.max_column)

    print(f"Dimensions of Sheet1: {dim_after=}")

assert dim_before == dim_after  # assert fails!

# Expect same values
# But we get dim_before=(2, 1) or whatever is in the file
# and dim_after=(None, None) - unexpectedly dimensions are now None

Issue Description

When using Pandas to read tables from Excel worksheets, the parse function messes with the internal dimension variables - it sets max_row and max_column to None.

This is caused by pandas/io/excel/_openpyxl.py lines 610 + 611 (the last 2 in the following snippet):

    def get_sheet_data(
        self, sheet, file_rows_needed: int | None = None
    ) -> list[list[Scalar]]:
        if self.book.read_only:
            sheet.reset_dimensions()

The reset_dimensions call resets the values.

Why is this call done, is it necessary? On read-only sheets only?! Can those two lines be removed? Unfortunately there is no comment as to why they are needed. Removing them fixes the issue for me - but would that break otherwise? In any case, I do not expect a "read" operation to change max_row and max_column.

The problem is that I need to read multiple tables from different sheets. So I usually first try to find the position of the tables in the file using openpyxl code on the excel_file.book, then I read the table using excel_file.parse with corresponding parameters (in particular skiprows and skipfooter) And afterwards I still read more stuff from the same sheet. [Excel files from customers can be a big PITA, customers like to put multiple tables one one sheet or add hidden sheets, hidden rows&cols - that would almost be a feature request: read_excel(..., read_hidden_cols=False)]

It came as a surprise that the dimensions are lost after reading.

This is an issue for openpyxl / xlsx files only: when reading an old-style xls file with xlrd instead, the sheet dimension can be found via book.sheet_by_name("Sheet1").nrows and ncols - and they do not change after reading.

Expected Behavior

Dimensions should be left alone.

Installed Versions

Limited to relevant parts

INSTALLED VERSIONS ------------------ python : 3.12.3 pandas : 2.3.3 openpyxl : 3.1.5

[rhshadrach]

Thanks for the report. This was added in #39486. The issue is that these dimensions can be unreliable, so to improve the robustness of pandas we instruct openpyxl to ignore them. I agree that having this impact the user's handle is a downside and desired to fix, but I don't think we should do so if it makes our reading less robust.

Further investigations and PRs to fix are welcome!

[behrenhoff]

Ok, thanks for the info. That was not the answer I was hoping for as this means I need to modify my code and stop using max_row and max_column!
On the other hand, I guess I did not come across any broken file so far - and one could also consider this a bug elsewhere then.

I see two options:
a) just close this - and I'll modify my code to deal with this
b) if I am not mistaken, there is just one function where the change to None actually matters - and that is the for row_number, row in enumerate(sheet.rows) in get_sheet_data (line 615) (i.e. in the iteration itself, ultimately boils down to ReadOnlyWorksheet._cells_by_row ). So another option would be to save the old values before calling sheet.reset_dimensions() and restoring them after the loop. That feels a bit "hacky" tbh.