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?

Answer 1

Use pandas ExcelWriter class:

from pandas import ExcelWriter

# from pandas.io.parsers import ExcelWriter

After this save_xls function works fine:

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)

        writer.save()