BUG: read_excel with openpyxl and missing dimension
State: closed

- [x] closes #38956
- [x] closes #39001
- [x] tests added / passed
- [x] Ensure all linting tests pass, see [here](https://pandas.pydata.org/pandas-docs/dev/development/contributing.html#code-standards) for how to run them
- [x] whatsnew entry

Targeted for 1.2.2 because this resolves an issue that occurs from the change of default engine from xlrd to openpyxl in 1.2.

Labels:
name: Bug
name: IO Excel
description: read_excel, to_excel

Comments:
~~Didn't read the discussion from #39001; I'm thinking now this is probably the wrong thing to do.~~

This approach can be used along with calling `reset_dimension` to resolve #39001 without the performance hit, see https://github.com/pandas-dev/pandas/issues/39001#issuecomment-770277269.
This can probably be a follow up issue, but running this patch on the `sample.xlsx` file of #38956 results in different shapes of dataframes with engine=xlrd vs engine=openpyxl. This is NOT caused by this patch however.

openpyxl generates 39 rows vs 9 rows for xlrd. The first 9 rows seem equivalent for both. The latter rows with openpyxl are all NaNs. 

Agreed @asishm - I think that is the cause for #39181 as well, and my guess is that adding a call to ``reset_dimension`` will fix as well. However, I need to test this. If it is, I think it makes sense to add them all here as they are all the same core issue (openpyxl being given no/incorrect dimension information).
My previous comment was not correct - if there are empty cells then we still get null values coming through and need to trim, e.g. the following would result in a DataFrame with rows 7 through 17 np.nan. I've modified the added `dimension_large.xlsx` to include this phenomenon as well.

```
<row r="6" customFormat="false" ht="13.8" hidden="false" customHeight="false" outlineLevel="0" collapsed="false">
  <c r="A6" s="3" t="n">
    <v>3</v>
  </c>
  <c r="B6" s="4" t="n">
    <v>6</v>
  </c>
  <c r="C6" s="4" t="n">
    <v>9</v>
  </c>
</row>
<row r="16">
  <c r="B16" s="7"/>
  <c r="C16" s="7"/>
</row>
<row r="17">
  <c r="B17" s="7"/>
  <c r="C17" s="7"/>
</row>
```
I've reverted the fixes for #39181 as they are of a different nature than just dimension information and opened #39547
Current minimum version for openpyxl is 2.6.0, this patch would need 2.6.1 (reset_dimensions doesn't exist before this, accesses protected members). Is increasing the minimum version here okay to do for 1.2.2? cc @jreback @simonjayhawkins 
> Current minimum version for openpyxl is 2.6.0, this patch would need 2.6.1 (reset_dimensions doesn't exist before this, accesses protected members). Is increasing the minimum version here okay to do for 1.2.2? cc @jreback @simonjayhawkins

yep that's fine (just update all the locations, install, show_versions, and put it in the whatsnew note)
can you rebase
@jreback I've merged master and increased the minimum version for openpyxl.
> pandas/io/excel/_openpyxl.py:543: error: Unsupported operand types for + ("List[Union[Union[str, int, float, bool], Union[Any, Any, Any, Any]]]" and "List[str]")  [operator]

:-<
Thanks @jreback, turns out that this needs openpyxl 3.0.0. Prior versions still suffer from not reading the full excel file even after calling `reset_dimension`.
can we *easily* fallback to behavior if older version of openpyxl is installed? (just because this is a minor release)
Yes - good idea and I think it should be easy enough. Because I don't understand what happened between openpyxl 2.6.4 and 3.0.0 (there are no release notes for the 3.0.0 that I can find), my thought is to not modify behavior if openpyxl < 3.0.0. If that is the case, then what should be done with the minimum version here? Keep at 2.6.0 until pandas 1.3 (or 2.0)?
> Yes - good idea and I think it should be easy enough. Because I don't understand what happened between openpyxl 2.6.4 and 3.0.0 (there are no release notes for the 3.0.0 that I can find), my thought is to not modify behavior if openpyxl < 3.0.0. If that is the case, then what should be done with the minimum version here? Keep at 2.6.0 until pandas 1.3 (or 2.0)?

yes exactly so we don't actually bump the min in 1.2.2 (or just bump to 2.6.1) and just fallback (even if slow / whatever), and make the bump for 1.3
@jreback - changes made. One thing I noticed is that the minimum version in `_optional.py` was incorrect - I've updated it to 2.6.0 - uncertain if this needs a line in the whatsnew.
thanks @rhshadrach 

happy to have the min version bump for 1.3
@meeseeksdev backport 1.2.x
Owee, I'm MrMeeseeks, Look at me.

There seem to be a conflict, please backport manually. Here are approximate instructions:

1. Checkout backport branch and update it.

```
$ git checkout 1.2.x
$ git pull
```

2. Cherry pick the first parent branch of the this PR on top of the older branch:
```
$ git cherry-pick -m1 e1a9b787cd16e714c57a758f353b6eda9cdcee9b
```

3. You will likely have some merge/cherry-pick conflict here, fix them and commit:

```
$ git commit -am 'Backport PR #39486: BUG: read_excel with openpyxl and missing dimension'
```

4. Push to a named branch :

```
git push YOURFORK 1.2.x:auto-backport-of-pr-39486-on-1.2.x
```

5. Create a PR against branch 1.2.x, I would have named this PR:

> "Backport PR #39486 on branch 1.2.x"

And apply the correct labels and milestones.

Congratulation you did some good work ! Hopefully your backport PR will be tested by the continuous integration and merged soon!

If these instruction are inaccurate, feel free to [suggest an improvement](https://github.com/MeeseeksBox/MeeseeksDev).