[BUG] read_excel: fixes handling of multi index header and other corner cases
State: closed

- [x] closes #58898
- [x] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [x] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [x] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints) to new arguments/methods/functions.
- [x] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.


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

Comments:
Hi, I can't see why check `pre-commit.ci - pr` is failing!
pre-commit.ci autofix
Looks like I don't have the permissions to do it, but you should be able to autofix by commenting `pre-commit.ci autofix`
pre-commit.ci autofix
This pull request is stale because it has been open for thirty days with no activity. Please [update](https://pandas.pydata.org/pandas-docs/stable/development/contributing.html#updating-your-pull-request) and respond to this comment if you're still interested in working on this.
I have rebased my branch on top of the current  "main" branch, because I was having some issues with the test suite unrelated to my code.
Now, ixmap is not coerced into a list in order to reduce the overhead when `skiprows` is None or an integer.
pre-commit.ci autofix

pre-commit.ci autofix
> This pull request is stale because it has been open for thirty days with no activity. Please [update](https://pandas.pydata.org/pandas-docs/stable/development/contributing.html#updating-your-pull-request) and respond to this comment if you're still interested in working on this.

not stale!
It would be great if a fix for this issue could be implemented quickly as it is fundamental to properly reading in data from excel files. I would suggest that this should be VERY HIGH PRIORITY.
Note, I just tried [https://github.com/PL-SalvadorFandino/pandas/tree/holes](https://github.com/PL-SalvadorFandino/pandas/tree/holes) and it does NOT solve the following issue for me:

Consider an excel sheet with the following content (three multiindex header rows with a blank row separating rows 1 and 3):
```
Group, A, A, B, B
ID, 0, 1, 0, 1
 , , , , 
X, Y, Y, Y, Y
100, 0, 1, 2, 3
200, 4, 5, 6, 7
```

When reading in the above excel sheet using `pandas.read_excel` I get mangled data. For example,
```python
pd.read_excel(..., index_col=0, header=[0,1,3], skiprows = lambda row: row not in [0,1,3,4,5])
```
gives the following output (index_col missing last data row, and other columns missing last header row):
```
Group, A, A, B, B
ID, 0, 1, 0, 1
X, 0, 1, 2, 3
200, 4, 5, 6, 7
```

Thinking that header=[...] indices might refer to row indices after removing skipped rows, I tried:
```python
pd.read_excel(..., index_col=0, header=[0,1,2], skiprows = lambda row: row not in [0,1,3,4,5])
```
which gives the following output (index_col missing last header row, and other columns ok):
```
Group, A, A, B, B
ID, 0, 1, 0, 1
 , Y, Y, Y, Y
100, 0, 1, 2, 3
200, 4, 5, 6, 7
```

Note that if the index_col is ignored, then everything works if one assumes header=[...] denotes row indices after removing skipped rows (I find this HUGELY non-intuitive):
```python
pd.read_excel(..., header=[0,1,2], skiprows = lambda row: row not in [0,1,3,4,5])
```
which gives the correct output (albeit the desired index column is now a regular column):
```
Group, A, A, B, B
ID, 0, 1, 0, 1
X, Y, Y, Y, Y
100, 0, 1, 2, 3
200, 4, 5, 6, 7
```

I have the suspicion that index_col and other columns may be treating the indices passed in header=[...] differently (e.g., row indices as in the excel sheet or row indices after removing skipped rows)? If so, I STRONGLY suggest that all row indices should be relative to the excel file, not the remaining rows after extracting the desired rows.
Thanks for the pull request, but it appears to have gone stale. If interested in continuing, please merge in the main branch, address any review comments and/or failing tests, and we can reopen.