Save list of DataFrames to multisheet Excel spreadsheet

Question

How can I export a list of DataFrames into one Excel spreadsheet?
The docs for to_excel state:

Notes
If passing an existing ExcelWriter object, then the sheet will be added to the existing workbook. This can be used to save different DataFrames to one workbook

writer = ExcelWriter('output.xlsx')
df1.to_excel(writer, 'sheet1')
df2.to_excel(writer, 'sheet2')
writer.save()

Following this, I thought I could write a function which saves a list of DataFrames to one spreadsheet as follows:

from openpyxl.writer.excel import ExcelWriter
def save_xls(list_dfs, xls_path):
    writer = ExcelWriter(xls_path)
    for n, df in enumerate(list_dfs):
        df.to_excel(writer,'sheet%s' % n)
    writer.save()

However (with a list of two small DataFrames, each of which can save to_excel individually), an exception is raised (Edit: traceback removed):

AttributeError: 'str' object has no attribute 'worksheets'

Presumably I am not calling ExcelWriter correctly, how should I be in order to do this?

score 182 · Accepted Answer · edited Mar 08 '22 at 09:31

182

You should be using pandas own ExcelWriter class:

from pandas import ExcelWriter
# from pandas.io.parsers import ExcelWriter

Then the save_xls function works as expected:

def save_xls(list_dfs, xls_path):
    with ExcelWriter(xls_path) as writer:
        for n, df in enumerate(list_dfs):
            df.to_excel(writer,'sheet%s' % n)

edited Mar 08 '22 at 09:31

xjcl

12,848
6
67
89

answered Jan 08 '13 at 23:27

Andy Hayden

359,921
101
625
535

why not just `df.to_excel('file_path')`? – root Jan 08 '13 at 23:30
12

How are you finding the speed of this? I tried to do the same thing yesterday and found that writing a dataframe with 2000 columns to an .xlsx file was taking about 16s per 100 rows on a decent workstation with solid state drive. Some quick profiling with %prun in ipython showed this to be due to the XML processing. In the end I got the data inte Excel by going via CSV because the ExcelWriter speed was prohibitively slow. – snth Jan 09 '13 at 08:15
6

Still as slow in 2018. – stmax Feb 19 '18 at 13:11
2

You can also use `ExcelWriter` as a context manager. `with ExcelWriter(xls_path) as writer: df.to_excel(writer, sheet_name)` – BallpointBen Jan 25 '19 at 20:32
2

Thanks Andy. Would you mind explaining the `'sheet%s' % n` bit please? What does it do and how does it work? – Bowen Liu Apr 12 '19 at 15:12
2

@BowenLiu That's just naming the sheets to sheet1, sheet2, etc. – xiaomy May 16 '19 at 19:23
2

This seems to overwrite an existing file. How does one write and keep existing file? Thank you – Confounded Jul 25 '20 at 17:37
I had to leave out writer.save() for this to work for me. – ENIAC-6 Oct 28 '21 at 13:18
1

`writer.save()` is already called when the context manager exits, don't manually call it again. – xjcl Mar 08 '22 at 09:32
Is there a way you could amend your code so that the sheet tabs are named according to the dataframe please? – alexmathios Jul 25 '23 at 14:50

score 39 · Answer 2 · edited Sep 09 '22 at 13:13

39

In case anyone needs an example using a dictionary of dataframes:

from pandas import ExcelWriter

def save_xls(dict_df, path):
    """
    Save a dictionary of dataframes to an excel file, 
    with each dataframe as a separate page
    """

    writer = ExcelWriter(path)
    for key in dict_df.keys():
        dict_df[key].to_excel(writer, sheet_name=key)

    writer.save()

example: save_xls(dict_df = my_dict, path = '~/my_path.xls')

edited Sep 09 '22 at 13:13

Marco Cerliani

21,233
3
49
54

answered Jun 29 '18 at 17:13

Jared Marks

948
8
15

This really saved my day. But there is one thing I don't understand although it worked. What does the part `'%s' % key` do? Would you mind explaining it? Thanks! – Bowen Liu Apr 12 '19 at 15:11
@BowenLiu that takes the dictionary key value, and uses it for the page name in the excel sheet. '%s' is a placeholder that is filled in with "key". Hope that helps. – Jared Marks Apr 14 '19 at 18:16
2

It worked great for me, just one little addition: add `writer.close()` at the end. I have python 3.7 and windows 10 setup, Microsoft Excel would not save the changes made to file because python would be accessing it. – hru_d Mar 16 '21 at 22:08

score 0 · Answer 3 · answered Dec 10 '20 at 11:10

Sometimes there can be issues(Writing an excel file containing unicode), if there are some non supporting character type in the data frame. To overcome it we can use 'xlsxwriter' package as in below case:

for below code:

from pandas import ExcelWriter
import xlsxwriter
writer = ExcelWriter('notes.xlsx')
for key in dict_df:
        data[key].to_excel(writer, key,index=False)
writer.save()

I got the error as "IllegalCharacterError"

The code that worked:

%pip install xlsxwriter
from pandas import ExcelWriter
import xlsxwriter
writer = ExcelWriter('notes.xlsx')
for key in dict_df:
        data[key].to_excel(writer, key,index=False,engine='xlsxwriter')
writer.save()

Save list of DataFrames to multisheet Excel spreadsheet

3 Answers3

Linked

Related