dataframe to_excel issue with openpyxl > 3.0.1

[mhooreman]

Hello,

When exporting a data frame to excel on a python setup using openpyxl 3.0.2, we have the error TypeError: got invalid input value of type <class 'xml.etree.ElementTree.Element'>, expected string or Element.

Rolling back to 3.0.1 solves the issue.

Thanks

Code Sample

With openpyxl 3.0.2

>>> import openpyxl
>>> openpyxl.__version__
'3.0.2'
>>> import pandas as pd
>>> pd.DataFrame(dict(x=range(10))).to_excel('test.xlsx')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  [...]
  File "src/lxml/serializer.pxi", line 1652, in lxml.etree._IncrementalFileWriter.write
TypeError: got invalid input value of type <class 'xml.etree.ElementTree.Element'>, expected string or Element

With openpyxl 3.0.1

>>> import openpyxl
>>> openpyxl.__version__
'3.0.1'
>>> import pandas as pd
>>> pd.DataFrame(dict(x=range(10))).to_excel('test.xlsx')
>>> 

Output of pd.show_versions()

INSTALLED VERSIONS
------------------
commit           : None
python           : 3.7.4.final.0
python-bits      : 64
OS               : Linux
OS-release       : 4.15.0-72-generic
machine          : x86_64
processor        : x86_64
byteorder        : little
LC_ALL           : None
LANG             : en_US.UTF-8
LOCALE           : en_US.UTF-8

pandas           : 0.25.3
numpy            : 1.17.4
pytz             : 2019.3
dateutil         : 2.8.1
pip              : 19.3.1
setuptools       : 41.4.0
Cython           : 0.29.14
pytest           : None
hypothesis       : None
sphinx           : 2.2.2
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : 4.4.2
html5lib         : None
pymysql          : None
psycopg2         : 2.8.4 (dt dec pq3 ext lo64)
jinja2           : 2.10.3
IPython          : 7.10.1
pandas_datareader: None
bs4              : 4.8.1
bottleneck       : 1.3.1
fastparquet      : None
gcsfs            : None
lxml.etree       : 4.4.2
matplotlib       : 3.1.2
numexpr          : 2.7.0
odfpy            : None
openpyxl         : 3.0.2
pandas_gbq       : None
pyarrow          : None
pytables         : None
s3fs             : None
scipy            : 1.3.3
sqlalchemy       : None
tables           : None
xarray           : None
xlrd             : 1.2.0
xlwt             : None
xlsxwriter       : None

[TomAugspurger]

Is this likely an issue pandas or with openpyxl?

[mhooreman]

Thanks @TomAugspurger
It’s a very good question, which I was not able to reply to. Sorry

[jbrockmendel]

This is an openpyxl issue, xref #29862

[WillAyd]

Looks like that was fixed on openpyxl 3.0 branch yesterday - @mhooreman any chance you can install that version of openpyxl and see if it fixes the issue?

[mhooreman]

@WillAyd, installed pip's pandas and openpyxl from https://bitbucket.org/openpyxl/openpyxl/branch/3.0 on a dedicated venv.

It works. Thanks a lot.

Remark: in the bitbucket, openpyxl was still flagged 3.0.2