Save list of DataFrames to multisheet Excel spreadsheet

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()

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

from pandas import ExcelWriterdef 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:        dict_df[key].to_excel(writer, key)    writer.save()

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

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 ExcelWriterimport xlsxwriterwriter = 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 xlsxwriterfrom pandas import ExcelWriterimport xlsxwriterwriter = ExcelWriter('notes.xlsx')for key in dict_df:        data[key].to_excel(writer, key,index=False,engine='xlsxwriter')writer.save()