pandas writing to excel sheet deleting other sheets in file

Question

I have simple code to export python dataframe to existing excel file with sheets but the writer keep deleting the existing sheet from the file

read = pd.ExcelFile('Saw_Load.xlsx')
print(read.sheet_names)
writer = pd.ExcelWriter('Saw_Load.xlsx')
result.to_excel(writer,'saw', index = False)
read2 = pd.ExcelFile('Saw_Load.xlsx')
print(read2.sheet_names)
writer.save()

Here is the output i am getting

['saw', 'Pivot']
['saw']

We can clearly see before to_excel function was used there were 2 sheets (saw,Pivot). After there is only one 'saw'

It could be a simple fix in formula but couldn't seem to find anything that works. Any help will be appreciated

Thanks

Possible duplicate of [How to write to an existing excel file without overwriting data (using pandas)?](https://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandas) — BruceWayne, Oct 18 '18 at 16:16
@BruceWayne Thanks I tried the solution in that sheet and it worked although it seems to corrupt the file and remove some special formatting from other tabs. eg it worked with pivot table but corrupted the file when slicer was added. — Shri, Oct 18 '18 at 21:25

Btc Sources · Accepted Answer · 2020-05-28T09:23:44.950

10

Your problem is that you're not writing again the old sheets that the book contains. Let's say that you need to write it from scratch again, but no to execute to_excel again but just specify the workbook.

This happens beacause xlsxwriter creates a new file, so the old one is erased.

You can do it by using writer.book and writer.sheets objects.

excelBook = load_workbook(filename)
with pd.ExcelWriter(filename, engine='xlsxwriter') as writer:
    # Save your file workbook as base
    writer.book = excelBook
    writer.sheets = dict((ws.title, ws) for ws in excelBook.worksheets)

    # Now here add your new sheets
    result.to_excel(writer,'saw', index = False)

    # Save the file
    writer.save()

Note: please notice that I've used load_workbook from openpyxl, but you can use Excelfile without it and reproduce it with just minor changes.

edited May 28 '20 at 09:23

answered Apr 05 '19 at 11:56

Btc Sources

1,912
2
30
58

I'm facing the same issue as the OP. I adopted your code, but got the following error: *AttributeError: 'Workbook' object has no attribute 'add_format'*. I'm writing into a test xlsx worbook that has three tabs including the one I'm writing into. No other formatting added. Thanks. – SModi Dec 30 '20 at 11:38
I think you're having a problem with the selected engine. Check this other question: https://stackoverflow.com/questions/59794843/xlsxwriter-error-attributeerror-workbook-object-has-no-attribute-add-format – Btc Sources Dec 30 '20 at 11:45
that is correct. I switched to openpyxl and it worked following the solution here which is similar to yous: https://stackoverflow.com/questions/39049148/python-update-dataframe-to-existing-excel-sheet-without-overwriting-contents-on. thanks – SModi Dec 30 '20 at 11:54

score 3 · Answer 2 · answered Apr 15 '20 at 16:49

3

Try this:

with pd.ExcelWriter('Saw_load.xlsx', engine="openpyxl", mode="a") as writer:
    result.to_excel(writer, sheet_name="name_of_sheet")

Hope this helps!

answered Apr 15 '20 at 16:49

Kriticoder

103
1
6

1

I haven't had success getting this to intelligently add data. With write mode 'a', i get an extra sheet e.g. Sheet1 even if it already exists. With write mode 'w', it deletes any previous information. – illan Mar 02 '21 at 17:59

Bash · Answer 3 · 2023-06-29T17:54:00.983

0

You can use the if_sheet_exists parameter with values of either replace or overlay.

with pd.ExcelWriter('file.xlsx', engine="openpyxl", mode="a" if_sheet_exists="overlay") as writer:
    df.to_excel(writer, sheet_name="name_of_sheet")

edited Jun 29 '23 at 17:54

answered Sep 30 '22 at 08:27

Bash

47
1
10

1

I believe it is "if_sheet_exists", not "if_sheets_exists" – Hùng Nguyễn Jun 21 '23 at 18:42

score -2 · Answer 4 · answered Oct 18 '18 at 16:15

-2

Try this (although I'm not sure where result came from):

read = pd.ExcelFile('Saw_Load.xlsx')
print(read.sheet_names)
_writer = pd.ExcelWriter('Saw_Load.xlsx')
result.to_excel(_writer,'saw', index = False)

Idea from here:

If passing an existing ExcelWriter object, then the sheet will be added to the existing workbook.

answered Oct 18 '18 at 16:15

BruceWayne

22,923
15
65
110

1

Sorry, I forgot to add the writer command, I had the writer command in my original file. Still got the same output – Shri Oct 18 '18 at 18:38

pandas writing to excel sheet deleting other sheets in file

4 Answers4

Related