CODEWITHSUNDEEP
pythonpandas
Tengis
Tengis

Reputation: 2809

Pandas: can not write to excel file

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.

Upvotes: 12

Views: 24659

Answers (4)

Naor Zakk
Naor Zakk

Reputation: 86

An update to pandas should slove this.

pip install -U pandas

It works for pandas version 0.17+

Upvotes: 1

slushy
slushy

Reputation: 3357

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.

Upvotes: 10

pbamba
pbamba

Reputation: 303

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.

Upvotes: 11

Tal Peretz
Tal Peretz

Reputation: 515

For fast solution replace this

xcell.style = xcell.style.copy(**style_kwargs)

with

pass

At pandas/io/excel.py line 778.

openpyxl upgraded their api and pandas also need to be updated for support openpyxl.

Upvotes: 8

Related Questions