[FIXED] Saving multiple pandas dataframes to Excel file with Styleframe, each one on a different sheet

Issue

I am trying to save a number of pandas dataframes to excel. I want them all in the same excel file, but with each one on a different sheet.

This is what I have at the moment. This is just saving all my dataframes to separate files. I have used StyleFrame. Some of the values in these dataframes have \n. When I write to excel, I want it to register this as a new line within each cell.

Using StyleFrame was the only solution I could find.

file_path = "path_to_save_files"
StyleFrame(df1).to_excel(file_path + '/df1.xlsx').save()
StyleFrame(df2).to_excel(file_path + '/df2.xlsx').save()
StyleFrame(df3).to_excel(file_path + '/df3.xlsx').save()

This is what I've tried. This would normally work:

writer = pd.ExcelWriter(f'{file_path}/output.xlsx', engine='xlsxwriter')

StyleFrame(df1).to_excel(writer, sheet_name = "sheet1")
StyleFrame(df2).to_excel(writer, sheet_name = "sheet2")
StyleFrame(df3).to_excel(writer, sheet_name = "sheet3")

However, because I'm using StyleFrame, I get an error:

AttributeError: 'Worksheet' object has no attribute 'sheet_view'

Any suggestions?

Solution

Is there any particular reason you are using xlsxwriter as an engine? styleframe only supports openpyxl, so this will work:

writer = pd.ExcelWriter(f'{file_path}/output.xlsx', engine='openpyxl')
df1 = pd.DataFrame({'a':[1]})
df2 = pd.DataFrame({'b':[2]})
StyleFrame(df1).to_excel(writer, sheet_name="sheet1")
StyleFrame(df2).to_excel(writer, sheet_name="sheet2")

writer.save()

Note that you do have to explicitly call writer.save() (just like when working with pandas.DataFrame directly).

There is a shortcut for pd.ExcelWriter(f'{file_path}/output.xlsx', engine='openpyxl'):

writer = StyleFrame.ExcelWriter(f'{file_path}/output.xlsx')

Answered By - DeepSpace