Pandas: can not write to excel file

Question

Trying this example from the documentation

writer = ExcelWriter('output.xlsx')
df1.to_excel(writer,'Sheet1')
df2.to_excel(writer,'Sheet2')
writer.save()

I found out that I can not write to an excel file with the error

TypeError: copy() got an unexpected keyword argument 'font'

I'm using Panda 0.16 on a Mac pro.

EDIT: writing to an xls file worked just fine. I do not insist in having an xlsx file, just wonder why it does not work.

Answer 1

As per their documentation, pandas depends

on openpyxl version 1.6.1 or higher, but lower than 2.0.0

The last openpyxl version lower than 2.0.0 being version 1.8.6, you should simply remove your current openpyxl version and run:

pip install openpyxl==1.8.6

if your using pip, or find an equivalent way to install this specific version.

Answer 2

If you don't care whether the headers have borders around them and bold font, and you don't want to restrict the version of openpyxl, the quickest way is to overwrite the header_style dictionary to be None.

If you also have dates or datetimes, you must also explicitly set the workbook's date and datetime formats to None:

from datetime import datetime
import pandas as pd
pd.core.format.header_style = None  # <--- Workaround for header formatting

dt = datetime.now()
d = datetime.date(datetime.now())
df1 = pd.DataFrame([{'c1': 'alpha', 'c2': 1}, {'c1': 'beta', 'c2': 2}])
df2 = pd.DataFrame([{'c1': dt, 'c2': d}, {'c1': dt, 'c2': d}])

with pd.ExcelWriter('output.xlsx') as writer:
    writer.date_format = None # <--- Workaround for date formatting
    writer.datetime_format = None  # <--- this one for datetime
    df1.to_excel(writer,'Sheet1')
    df2.to_excel(writer,'Sheet2')

Not clear why the keyword arguments never make it through the openpyxl deprecation wrapper...but they don't. In addition if you're formatting any other cells use the new openpyxl api.

All of these problems go away if you have Anaconda, by the way.