How to add new sheet to an existing .xlsx file every time python script is ran?

Question

TL;DR need python script to add new sheet to existing excel file (not overwrite) every time script is ran

I built a web scraping script that extracts prices from a website and pushes them to an excel file via pandas' ExcelWriter. In its current state, every time the prices are scraped, the file is overwritten with the latest scraped prices.

While this is effective at gathering latest prices, it would be nice if the script did not overwrite the .xlsx file, but instead stored each scrape as a sheet within the current excel file. I want my script to basically just add a new sheet to the current excel file every time the script is ran. I will name each sheet with the day in which the scraper ran.

Current code:

with pd.ExcelWriter('{file_path}') as writer:
    df.to_excel(writer, sheet_name='{date}')

I understand openpyxl is used to modify existing excel spreadsheets. However, my use with openpyxl is limited. Any pointers would be helpful. Let me know if any further context is needed.

Does this answer your question? https://stackoverflow.com/questions/46672207/add-worksheet-to-existing-excel-file-with-pandas — Nick ODell, Apr 09 '23 at 22:14

score 0 · Accepted Answer · answered Apr 10 '23 at 05:22

.. I want my script to basically just add a new sheet to the current excel file every time the script is ran.

You can use ExcelWriter in a append mode with an openpyxl engine :

from datetime import datetime

with pd.ExcelWriter(file_path, mode="a", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name=datetime.now().strftime("%Y%m%d_%H%M%S"))

NB: We use a timestamp ("%Y%m%d_%H%M%S") as the new sheetname to avoid overlapping names in case the script is run more than once in the same day.

Thank you! This seems to work as expected – Benjamin Parsons Apr 10 '23 at 15:13 — Benjamin Parsons, Apr 10 '23 at 15:13

How to add new sheet to an existing .xlsx file every time python script is ran?

1 Answers1