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?
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)
writer.save()