Overwrite an excel sheet with pandas dataframe without affecting other sheets

Question

I want to overwrite an existing sheet in an excel file with Pandas dataframe but don't want any changes in other sheets of the same file. How this can be achieved. I tried below code but instead of overwriting, it is appending the data in 'Sheet2'.

import pandas as pd
from openpyxl import load_workbook

book = load_workbook('sample.xlsx')
writer = pd.ExcelWriter('sample.xlsx', engine = 'openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df.to_excel(writer, 'sheet2', index = False)
writer.save()

Parvathirajan Natarajan · Accepted Answer · 2022-10-13T09:06:02.513

21

I didn't find any other option other than this, this would be a quick solution for you.

I believe still there's no direct way to do this, correct me if I'm wrong. That's the reason we need to play with these logical ways.

import pandas as pd

def write_excel(filename,sheetname,dataframe):
    with pd.ExcelWriter(filename, engine='openpyxl', mode='a') as writer: 
        workBook = writer.book
        try:
            workBook.remove(workBook[sheetname])
        except:
            print("Worksheet does not exist")
        finally:
            dataframe.to_excel(writer, sheet_name=sheetname,index=False)
            writer.save()

df = pd.DataFrame({'Col1':[1,2,3,4,5,6], 'col2':['foo','bar','foobar','barfoo','foofoo','barbar']})

write_excel('PRODUCT.xlsx','PRODUCTS',df)

Let me know if you found this helpful, or ignore it if you need any other better solution.

edited Oct 13 '22 at 09:06

answered Jun 28 '20 at 07:30

Parvathirajan Natarajan

1,240
15
29

I tried this but it deleted all other sheets in the file – prashant Jun 28 '20 at 08:58
No, Certainly not. I tried. Did you pass the correct sheet name? – Parvathirajan Natarajan Jun 28 '20 at 09:00
Works perfectly fine .... An update just remove `writer.save()` from the function as it's not part of the public API – Osama Hussein Aug 30 '23 at 08:37

score 9 · Answer 2 · answered Dec 16 '21 at 21:33

9

Similar to Gavaert's answer... For Pandas 1.3.5, add the 'if_sheet_exists="replace"' option:

import pandas as pd

with pd.ExcelWriter("file.xlsx", engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
    df.to_excel(writer, 'Logs', index=False)

answered Dec 16 '21 at 21:33

SoloDolo

174
1
5

I get this error io.UnsupportedOperation: truncate – Greencolor Dec 18 '21 at 20:54
and it creates the copy of Logs1 – Greencolor Dec 18 '21 at 20:58

score 1 · Answer 3 · answered Jun 24 '21 at 21:30

1

Since Pandas version 1.3.0 on_sheet_exists is an option of ExcelWriter. It can be used as such:

import pandas as pd

with pd.ExcelWriter("my_sheet.xlsx",engine="openpyxl",mode="a",on_sheet_exists="replace") as writer:
    pd.write_excel(writer,df)

Since none of the ExcelWriter methods or properties are public, it is advised to not use them.

answered Jun 24 '21 at 21:30

Fee

177
1
3

if_sheet_exists is the correct parameter – Abdul Haseeb Apr 25 '22 at 19:36

123bmc · Answer 4 · 2023-03-21T11:54:15.347

I know this is an old question, but I faced the same issue recently and answers are not worked for me and here is how I solved the issue:

import pandas as pd
import xlwings as xw

filename = 'path\to\the\file.xlsx'
wb = xw.Book(filename)
wb.activate()
wb.app.screen_updating = False #xl will open and freeze
wb.app.display_alerts = False
wb.app.calculation = 'manual'

ws = wb.sheets['Sheet Name To Change']
ws.value = <DataFrameName>


wb.activate()
wb.app.screen_updating = True
wb.app.display_alerts = True
wb.app.calculation = 'automatic'
wb.save()

Let me know if you found this helpful, I am not very familiar with answering so please correct any kind of error.

Overwrite an excel sheet with pandas dataframe without affecting other sheets

4 Answers4

Linked

Related