CODEWITHSUNDEEP
pythonpython-3.xpandas
nomad
nomad

Reputation: 983

xlsxwriter overwrites the previous sheet

I am trying to put a pivot table on one column and detail on the second sheet. But the detail sheet is being overwritten by xlsxwriter.

pivot = pd.pivot_table(df2, index=['Customer_Name'], values=['Billed_Net_Value', 'Sales_Order_Net_Value', 'Open_Amount'], aggfunc=np.sum)
writer = pd.ExcelWriter('SAP.xlsx', engine='xlsxwriter')
pivot.to_excel(writer, sheet_name='Pivot')
writer.save()

filename = 'SAP.xlsx'
workbook = xlsxwriter.Workbook(filename)
worksheet = workbook.add_worksheet('Detail')

Upvotes: 3

Views: 3942

Answers (2)

jmcnamara
jmcnamara

Reputation: 41644

In your example the Workbook() constructor creates a new file which overwrites the file created in Pandas.

If you want to add other dataframes to the workbook you can call to_excel() with other worksheet names, like this:

writer = pd.ExcelWriter('SAP.xlsx', engine='xlsxwriter')

pivot.to_excel(writer, sheet_name='Pivot')
df2.to_excel(writer, sheet_name='Sheet2')
writer.save()

If you want to add a worksheet using the XlsxWriter apis you can do it as follows:

writer = pd.ExcelWriter('SAP.xlsx', engine='xlsxwriter')

pivot.to_excel(writer, sheet_name='Pivot')

workbook  = writer.book
worksheet = workbook.add_worksheet('Detail')
writer.save()

See also Working with Pandas in the XlsxWriter docs.

Upvotes: 2

mechanical_meat
mechanical_meat

Reputation: 169494

I think you can do this simply by postponing the .save() call:

pivot = pd.pivot_table(df2, index=['Customer_Name'], values=['Billed_Net_Value', 'Sales_Order_Net_Value', 'Open_Amount'], aggfunc=np.sum)
writer = pd.ExcelWriter('SAP.xlsx', engine='xlsxwriter')
pivot.to_excel(writer, sheet_name='Pivot')
df2.to_excel(writer, sheet_name='Detail')
writer.save()

Upvotes: 2

Related Questions