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

Sorted_Dataframe_1.to_excel(writer, sheet_name='Sheet1')
Sorted_Dataframe_2.to_excel(writer, sheet_name='Sheet2')

writer.save()
writer.close()

I have 2 DataFrames which I want to save to an existing excel named 'Output' to Sheet1 and Sheet2, respectively (see code). I am working in Sheet3 in excel itself, and every time I run the code, it deletes the Sheet3, and thus overwrites the whole excel. Is there any function to just 'update' sheets 1 and 2 but not touch the other sheets that I do not recall by the function in the 'Output' xlsx?

Ja123
  • 73
  • 6

2 Answers2

1

Can you set the mode to 'a' (for append). See the Pandas ExcelWriter docs.

writer = pd.ExcelWriter('Output.xlsx', mode='a', engine='xlsxwriter')
mullinscr
  • 1,668
  • 1
  • 6
  • 14
  • I tried.. It gives me an error: raise ValueError("Append mode is not supported with xlsxwriter!") ValueError: Append mode is not supported with xlsxwriter! – Ja123 Feb 08 '21 at 10:15
  • Try removing the engine keyword -- unless you specifically want to use the xlsxWriter engine? – mullinscr Feb 08 '21 at 10:19
  • Did you try `writer = pd.ExcelWriter('Output.xlsx', mode='a')` – mullinscr Feb 08 '21 at 11:31
1

Xlsxwriter can only write new excel files from scratch, it cannot append to existing ones. This is explained here:

It cannot read or modify existing Excel XLSX files.

openpyxl supports reading from and appending to existing files, so you should set

with pd.ExcelWriter('output.xlsx',
                    mode='a', engine ='openpyxl') as writer:  
    df.to_excel(writer, sheet_name='Sheet_name_3')

This comes from the docs, but I have added engine ='openpyxl' without which I am not sure it would work.

A very similar question was here: Append a sheet to an existing excel file using openpyxl

Pythonista anonymous
  • 8,140
  • 20
  • 70
  • 112
  • Ok this works thanks!! Only problem: if I run the code again, it adds new "sheet12" and "sheet21" besides the already existing sheet1 and sheet2 from the export of python. Is there anyway to be sure that it overwrites the Sheet1 and sheet2 and not add new ones? @pythonista anonymous – Ja123 Feb 08 '21 at 13:12
  • Sorry, I am not familiar enough with openpyxl. But if you find a solution please do post it here - I'd be interested, too – Pythonista anonymous Feb 08 '21 at 13:19