0

I am trying to clear all data in a sheet in excel and replace it with a pandas DataFrame. This sheet is then used in a pivot table in another sheet. First, what is the best way to replace the data using openpyxl from a pandas DataFrame. Then is it possible to refresh the pivot tables to the new data range?

I found some a few question here and here, but the following doesn't seem to overwrite the data of an existing sheet in an excel file.

df = pd.read_csv(PATH)

book = xls.load_workbook(PATH)
writer = pd.ExcelWriter(PATH, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

df.to_excel(writer,sheet_name='SHEET_NAME',index=False,encoding='utf8')
writer.save()

openpyxl has some documentaion about overwriting single cell and row values, but not an entire sheet of data.

Thanks for the help!

bkeesey
  • 466
  • 4
  • 12
  • 1
    I belive to actually refresh a pivot table (or calculate anything) you need to open Excel and make it do that. AFAIK all these excel libraries only read and write, they do not calculate. – dim_voly Jul 01 '21 at 17:46
  • 1
    Thanks, that's also what I have mainly found other than using VBA or win32com which is a bit more cumbersome. Do you know a solution to just clearing a sheet and adding new data to it? I can then just manually update the pivot tables? – bkeesey Jul 01 '21 at 19:02
  • Openpyxl can tell Excel to refresh the pivot. But that is not the problem. If you want to replace data in a particular sheet then that is easy with openpyxl. – Charlie Clark Jul 02 '21 at 09:29
  • @CharlieClark great, would you be able to tell me how to clear all data in a sheet and replace it with data from a pandas DataFrame using openpyxl? Thanks! – bkeesey Jul 02 '21 at 13:34

0 Answers0