-1

I have an Excel file named MasterFile. Within MasterFile I have multiple sheets with formulas. I would like to use the code below to update one sheet in MasterFile without overwriting any of my data or formulas.

Here is my code so far:

from bs4 import BeautifulSoup
import requests
import pandas as pd
from openpyxl import load_workbook

url = 'http://www.baseballpress.com/lineups'

soup = BeautifulSoup(requests.get(url).text, 'html.parser')

players = [i.text for i in soup.find_all('a', {'class': 'player-link'})]


my_dict = (players)

df = pd.DataFrame(pd.Series(my_dict))

writer = pd.ExcelWriter('my2nd_webscrape.xlsx')
df.to_excel(writer,'Sheet1')
writer.save()

I have found some info regarding this subject in How to write to an existing excel file without breaking formulas with openpyxl?, but I am not sure how to adjust my code.

jonrsharpe
  • 115,751
  • 26
  • 228
  • 437
Able Archer
  • 579
  • 5
  • 19
  • No need for pandas here. And you can prevent overwriting by just using a different filename when saving. – Charlie Clark Sep 10 '18 at 10:22
  • I appreciate the heads up @CharlieClark! Would it be possible for you to show me an example? I am new to this stuff. I will try my best to figure it out in the meantime. Thank you sir! – Able Archer Sep 10 '18 at 17:05

1 Answers1

0

Try this:

from bs4 import BeautifulSoup
import requests
import pandas as pd
from openpyxl import load_workbook

book = load_workbook('my2nd_webscrape.xlsx')
writer = pd.ExcelWriter('my2nd_webscrape.xlsx')
writer.book = book

url = 'http://www.baseballpress.com/lineups'
soup = BeautifulSoup(requests.get(url).text, 'html.parser')
players = [i.text for i in soup.find_all('a', {'class': 'player-link'})]
my_dict = (players)

df = pd.DataFrame(pd.Series(my_dict))
df.to_excel(writer,'Sheet1')
writer.save()
writer.close()
Himavanth
  • 400
  • 5
  • 15
  • I received an error... AttributeError: 'Workbook' object has no attribute 'add_worksheet'. I will look into this later today. Thanks for getting back to me @Himavanth! =) – Able Archer Sep 08 '18 at 18:24
  • Fyi - `xlwt` and `xlsxwriter` have methods `add_sheet` and `add_worksheet` respectively. Anyways, happy to help! – Himavanth Sep 09 '18 at 20:56