BUG: Error when exporting empty DataFrame with MultiIndex on both axes to xlsx file · Issue #57696 · pandas-dev/pandas (original) (raw)

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 df = pd.DataFrame( data=[], index=pd.MultiIndex.from_tuples([], names=[0, 1]), columns=pd.MultiIndex.from_tuples([("A", "B")]), ) df.to_excel("tmp.xlsx")

Issue Description

The given example tries to write an empty dataframe with multiindex rows and columns to an Excel xlsx file.

Observed exception:

Traceback (most recent call last):
  File "example.py", line 7, in <module>
    df.to_excel("tmp.xlsx")
  File ".../lib/python3.10/site-packages/pandas/core/generic.py", line 2258, in to_excel
    formatter.write(
  File ".../lib/python3.10/site-packages/pandas/io/formats/excel.py", line 944, in write
    writer._write_cells(
  File ".../lib/python3.10/site-packages/pandas/io/excel/_xlsxwriter.py", line 261, in _write_cells
    for cell in cells:
  File ".../lib/python3.10/site-packages/pandas/io/formats/excel.py", line 881, in get_formatted_cells
    for cell in itertools.chain(self._format_header(), self._format_body()):
  File ".../lib/python3.10/site-packages/pandas/io/formats/excel.py", line 626, in _format_header_mi
    coloffset = len(self.df.index[0]) - 1
  File ".../lib/python3.10/site-packages/pandas/core/indexes/multi.py", line 2140, in __getitem__
    if level_codes[key] == -1:
IndexError: index 0 is out of bounds for axis 0 with size 0

This bug is almost identical to #46873 but for the case with multiindex columns.

Expected Behavior

Excel file written. No exception.

Installed Versions

INSTALLED VERSIONS

commit : ab2980ba52ae24db72765b3c4ae1d33ac8fad3f5 python : 3.10.13.final.0 python-bits : 64 OS : Linux OS-release : 6.7.6-arch1-1 Version : #1 SMP PREEMPT_DYNAMIC Fri, 23 Feb 2024 16:31:48 +0000 machine : x86_64 processor : byteorder : little LC_ALL : None LANG : en_US.UTF-8 LOCALE : en_US.UTF-8

pandas : 3.0.0.dev0+451.gab2980ba52.dirty

numpy : 1.26.4

pytz : 2024.1

dateutil : 2.9.0

setuptools : 69.1.1

pip : 24.0

Cython : 3.0.8

pytest : 8.0.2

hypothesis : 6.98.15

sphinx : 7.2.6

blosc : None

feather : None

xlsxwriter : 3.1.9

lxml.etree : 5.1.0

html5lib : 1.1

pymysql : 1.4.6

psycopg2 : 2.9.9

jinja2 : 3.1.3

IPython : 8.22.1

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.2.0

gcsfs : 2024.2.0

matplotlib : 3.8.3

numba : 0.59.0

numexpr : 2.9.0

odfpy : None

openpyxl : 3.1.2

pyarrow : 15.0.0

pyreadstat : 1.2.6

python-calamine : None

pyxlsb : 1.0.10

s3fs : 2024.2.0

scipy : 1.12.0

sqlalchemy : 2.0.27

tables : 3.9.2

tabulate : 0.9.0

xarray : 2024.2.0

xlrd : 2.0.1

zstandard : 0.22.0

tzdata : 2024.1

qtpy : None

pyqt5 : None