ValueError:工作表“Sheet1”已经存在，if_sheet_exists设置为“error”
ValueError:工作表“Sheet1”已经存在，if_sheet_exists设置为“error”
提问于 2022-03-17 03:32:48
我试图创建一个excel文件的3列:系统日期，时间，价值在网页上的时候。
目的是在每次代码运行时创建这3个值的数据，并将其附加到现有的excel工作簿(带有一个现有工作表)。
每次代码运行时，我都能够创建dataframe，但是当我试图将其附加到excel文件时，它会引发错误：
ValueError:工作表“Sheet1”已经存在，if_sheet_exists设置为“error”
你能告诉我，我哪里出错了吗？
# 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()回答 1
Stack Overflow用户
回答已采纳
发布于 2022-03-17 07:47:38
一个快速而简单的解决方案是升级您的熊猫> 1.4.0，因为它提供了一个if_sheet_exists = 'overlay' 来源
pd.ExcelWriter(path, engine='openpyxl', mode='a', if_sheet_exists='overlay')如果您不想升级您的熊猫，有一种方法是通过删除和重写工作表到excel文件。(如果你有很多记录，那就不推荐了，因为它会很慢)。
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)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71506787
复制相关文章
相似问题
腾讯云开发者
