Python writing dataframe to excel - Check if tab exists and overwrite otherwise create

Question

The following code works correctly the first time creating tabs x1 and x2 and fills with data. When executed again for new data it doesn't check if the tabs already exist and adds new erroneous tabs x11 and x21.

import pandas as pd
import numpy as np
from openpyxl import load_workbook
import os

filename = os.path.dirname(os.path.abspath(__file__))+ '\\Test_excel.xlsx' # path to current directory adding file name

book = load_workbook(filename)
writer = pd.ExcelWriter(filename, engine='openpyxl')
writer.book = book

x1 = np.random.randn(10, 4)
df1 = pd.DataFrame(x1)

x2 = np.random.randn(20, 2)
df2 = pd.DataFrame(x2)

df1.to_excel(writer, sheet_name = 'x1')
df2.to_excel(writer, sheet_name = 'x2')
writer.save()
writer.close()

I need it to overwrite the existing tabs data if the tab already exists otherwise create the tab. Any ideas?

Answer 1

You asked a question that I also was going to run into soon in my own project. One solution is to look for the sheet and use the exception read_excel() raises. If there's no exception, then the sheet exists, so delete it. Here's the code I just tried out on Jupiter:

from xlrd import XLRDError

try:
    df = pd.read_excel( writer, sheet_name='x1')
except XLRDError: 
    print ("No sheet x1 found")
else:
    print ("Removing sheet")
    del book['x1']