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.

Please show the exact code that caused the error, and more of the error trace — iayork, May 07 '15 at 16:33

score 11 · Answer 1 · answered May 21 '15 at 15:37

11

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.

answered May 21 '15 at 15:37

pbamba

303
3
7

3

I installed `openpyxl==1.8.6` . My Pandas version is `pandas===0.16.0-294-g45f69cd`. This does not help. The problem remains. Now I really need the xlsx format because of the max lines limitation.. – Tengis Jul 20 '15 at 09:30

slushy · Answer 2 · 2015-09-12T19:33:25.137

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.

This worked for a simple example, but then I tried it on a df with dates in it and got `TypeError: copy() got an unexpected keyword argument 'number_format'` — Nathan Lloyd, Sep 12 '15 at 16:05
Wow. The pandas writer explicitly overrides null arguments for the formatting to make them default, rather than taking a default value and letting the user override. wtf. I'll edit the answer. — slushy, Sep 12 '15 at 18:56

score 8 · Accepted Answer · answered May 18 '15 at 13:38

8

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.

answered May 18 '15 at 13:38

Tal Peretz

515
1
5
15

1

Oh no, you are not supposed to change library files. Better changer version – chhantyal Jul 14 '15 at 15:15
As i said. Fast but dirty. – Tal Peretz Jul 21 '15 at 09:40
1

Although hacking a library works, it is definitely evil. – ChuckCottrill Apr 29 '17 at 04:22

score 1 · Answer 4 · edited Dec 21 '16 at 18:25

1

An update to pandas should slove this.

pip install -U pandas

It works for pandas version 0.17+

edited Dec 21 '16 at 18:25

yiming

67
4

answered May 07 '16 at 08:58

Naor Zakk

86
3

Pandas: can not write to excel file

4 Answers4

Linked