Save list of DataFrames to multisheet Excel spreadsheet

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?

124425 次浏览
小开
最佳答案

你应该使用熊猫自己的 ExcelWriter类:

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

然后，save_xls函数将按照预期的方式工作:

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)
小开

如果有人需要一个使用数据框架字典的示例:

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

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

小开

如果数据框中有一些不支持的字符类型，有时会出现问题(编写包含 unicode 的 Excel 文件)。为了克服这个问题，我们可以使用‘ Xlsxwriter’软件包，如下所示:

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

我得到的错误是“非法字符错误”

行之有效的代码:

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

提交答案