CODEWITHSUNDEEP
python
Kashyap
Kashyap

Reputation: 83

ValueError: Sheet 'Sheet1' already exists and if_sheet_exists is set to 'error'

I am trying to create an excel file of 3 columns: System Date, Time, Value on a webpage at that time.

Intention is to create a dataframe of the 3 values, every time the code runs, and append the dataframe to existing excel workbook (with one existing sheet).

I am able to create dataframe every time code runs, but when I try to append it to an excel file, it throws error:

ValueError: Sheet 'Sheet1' already exists and if_sheet_exists is set to 'error'

Can you please suggest, where am I going wrong.

# Importing Libraries
from datetime import datetime
import pandas as pd
import requests
from bs4 import BeautifulSoup
import openpyxl


#getting today's date amd formatting it
now = datetime.now()
Date = now.strftime ("%d/%m/%Y")
Time = now.strftime ("%H:%M")


# GET request to scrape. 'Page' variable to assign contents
page = requests.get("https://www.traderscockpit.com/?pageView=live-nse-advance-decline-ratio-chart")


# Create BeautifulSoup object to parse content
soup = BeautifulSoup(page.content, 'html.parser')


adv = soup.select_one('a:-soup-contains("Advanced:")').next_sibling.strip()
dec = soup.select_one('a:-soup-contains("Declined:")').next_sibling.strip()
ADratio = round(int(adv)/int(dec), 2)

df = pd.DataFrame({tuple([Date, Time, ADratio])})

#Load workbook and read last used row
path = r'C:\Users\kashk\OneDrive\Documents\ADratios.xlsx'

writer = pd.ExcelWriter (path, engine='openpyxl', mode = 'a') 
wb = openpyxl.load_workbook(path)
startrow = writer.sheets['Sheet1'].max_row

#Append data frame to existing table in existing sheet
df.to_excel (writer, sheet_name = 'Sheet1', index = False, header = False, startrow = startrow)

writer.save()
writer.close()

Upvotes: 3

Views: 6005

Answers (1)

Vungsovanreach KONG
Vungsovanreach KONG

Reputation: 352

A fast and easy solution would be upgrading your pandas > 1.4.0 since it provides a if_sheet_exists = 'overlay' Source

pd.ExcelWriter(path, engine='openpyxl', mode='a', if_sheet_exists='overlay')

If you don't want to upgrade your pandas, there is a way to work around by removing and re-write the sheet into the excel file. (Not recommended if you have a lot of records since it will be slow).

path, sheet_name = 'ADratios.xlsx' , 'Sheet 1'
df.columns = ['Date','Time','ADratio']
with pd.ExcelWriter(path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
    book = openpyxl.load_workbook(path, 'r')
    df_bak = pd.read_excel(path)
    writer.book = openpyxl.load_workbook(path)
    writer.book.remove(writer.book.worksheets[writer.book.sheetnames.index(sheet_name)])
    writer.sheets = {ws.title:ws for ws in writer.book.worksheets}
    pd.concat([df_bak, df], axis=0).to_excel(writer, sheet_name=sheet_name, index = False)

Upvotes: 5

Related Questions