How can I export a list of DataFrames into one Excel spreadsheet?
The docs for
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')
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
from pandas import ExcelWriter # from pandas.io.parsers import ExcelWriter
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()
In case anyone needs an example of how to do this with 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 seperate page """ writer = ExcelWriter(path) for key in dict_df: dict_df[key].to_excel(writer, key) writer.save()
save_xls(dict_df = my_dict, path = '~/my_path.xls')