0

I want to export dataframe C to an existing excel, saving it in a new tab. I tried:

writer=pd.ExcelWriter(r'H:\test.xlsx', engine='xlsxwriter')
C.to_excel(writer,sheet_name='c') 
writer.save()

(Notice: Excel test is an existing and previously saved Excel, with different tables A, B in tabs a, b, which I want to keep)

This code however deletes the existing excel and creates a new one with only the tab "c". I want to keep the existing tabs "a" and "b".

What is the right way?

ps: I tried suggestion on link "How to save a new sheet in an existing excel file, using Pandas? " as follows:

writer=pd.ExcelWriter(r'H:\C.xlsx', engine='xlsxwriter')
y_pred_all2.to_excel(writer,sheet_name='cc')
writer.save()
writer.close()
from openpyxl import load_workbook
path=r'H:\C.xlsx'
book=load_workbook(path)
writer=pd.ExcelWriter(path, engine='xlsxwriter')
writer.book=book
C.to_excel(writer,sheet_name='returns')

This gives me error: AttributeError: 'Workbook' object has no attribute 'add_worksheet'

Rick
  • 79
  • 9
  • Does this answer your question? [How to save a new sheet in an existing excel file, using Pandas?](https://stackoverflow.com/questions/42370977/how-to-save-a-new-sheet-in-an-existing-excel-file-using-pandas) – Xukrao Jan 06 '20 at 19:06
  • I tried suggestion on link "How to save a new sheet in an existing excel file, using Pandas? " as follows: writer=pd.ExcelWriter(r'H:\C.xlsx', engine='xlsxwriter') y_pred_all2.to_excel(writer,sheet_name='cc') writer.save() writer.close() from openpyxl import load_workbook path=r'H:\C.xlsx' book=load_workbook(path) writer=pd.ExcelWriter(path, engine='xlsxwriter') writer.book=book C.to_excel(writer,sheet_name='returns') This gives me error: AttributeError: 'Workbook' object has no attribute 'add_worksheet' – Rick Jan 06 '20 at 19:19
  • 1
    Do you have to use xlsxwriter? It sounds like this might be a limitation in that library, according to this [answer](https://stackoverflow.com/a/18850144/10682164). According to pandas [docs](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.ExcelWriter.html#pandas.ExcelWriter), the default for ExcelWriter is openpyxl which should be able to do this. – totalhack Jan 06 '20 at 19:57
  • Indeed. Using writer=pd.ExcelWriter(path,engine='openpyxl') works. Thanks a lot. – Rick Jan 06 '20 at 22:47
  • Great, moved my comment to an answer. – totalhack Jan 07 '20 at 14:09

1 Answers1

1

According to this answer this is a limitation in the xlsxwriter engine. The pandas docs show the default for ExcelWriter is to use openpyxl which should be able to do what you are looking for. If you want to be explicit about the engine being used, this should work:

writer = pd.ExcelWriter(path, engine='openpyxl')
totalhack
  • 2,298
  • 17
  • 23