BUG: read_excel multiindex head with holes

[PL-SalvadorFandino]

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

df = pd.read_excel(file, header=[0, 2], index_col=[0, 1])

# Sample contents for excel file:
# ,Name1,A1,B1
# ,,,		
# ,Name2,A2,B2
# IndexA,IndexB,,		
# ,IB0,0.98,3.68
# IA1,IB1,1.04,-0.04
# IA2,IB2,0.49,0.73

Issue Description

read_excel can't handle correctly lots of edge cases.

For instance:

• passing a header with row holes (header=[0,2]) and index_col, may fail to parse index names.
• passing lists to header and skiprows doesn't work when the rows to be skipped are before or between the header rows
• forward filling may copy data from the headers or other rows into the data rows.
• etc.

I am submitting a pull request with tests showing some of those issues and a patch. This bug report is for reference.

Expected Behavior

Any combination of arguments should be supported or otherwise the function must fail telling the user what is the unsupported combination.

Installed Versions

INSTALLED VERSIONS ------------------ commit : 272e69c python : 3.12.3.final.0 python-bits : 64 OS : Linux OS-release : 6.8.0-31-generic Version : #31-Ubuntu SMP PREEMPT_DYNAMIC Sat Apr 20 00:40:06 UTC 2024 machine : x86_64 processor : x86_64 byteorder : little LC_ALL : None LANG : en_US.UTF-8 LOCALE : en_US.UTF-8

pandas : 3.0.0.dev0+1053.g272e69ceaf
numpy : 1.26.4
pytz : 2024.1
dateutil : 2.9.0.post0
setuptools : 69.5.1
pip : 24.0
Cython : 3.0.10
pytest : 8.2.0
hypothesis : 6.102.4
sphinx : 7.3.7
blosc : None
feather : None
xlsxwriter : 3.2.0
lxml.etree : 5.2.2
html5lib : 1.1
pymysql : 1.4.6
psycopg2 : 2.9.9
jinja2 : 3.1.4
IPython : 8.23.0
pandas_datareader : None
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : 4.12.3
bottleneck : 1.3.8
fastparquet : 2024.2.0
fsspec : 2024.5.0
gcsfs : 2024.5.0
matplotlib : 3.9.0
numba : 0.59.1
numexpr : 2.10.0
odfpy : None
openpyxl : 3.1.2
pyarrow : 16.1.0
pyreadstat : 1.2.7
python-calamine : None
pyxlsb : 1.0.10
s3fs : 2024.5.0
scipy : 1.13.0
sqlalchemy : 2.0.29
tables : 3.9.2
tabulate : 0.9.0
xarray : 2024.5.0
xlrd : 2.0.1
zstandard : 0.22.0
tzdata : 2024.1
qtpy : 2.4.1
pyqt5 : None