0

I am trying to export a dataframe I've generated in Pandas to an Excel Workbook. I have been able to get that part working, but unfortunately no matter what I try, the dataframe goes into the workbook as a brand new worksheet.

What I am ultimately trying to do here is create a program that pulls API data from a website and imports it in an existing Excel sheet in order to create some sort of "live updating excel workbook". This means that the worksheet already has proper formatting, vba, and other calculated columns applied, and all of this would ideally stay the same except for the basic data in the dataframe I'm importing.

Anyway to go about this? Any direction at all would be quite helpful. Thanks.

Here is my current code:

file='testbook.xlsx'
writer = pd.ExcelWriter(file, engine = 'xlsxwriter')
df.to_excel(writer, sheet_name="Sheet1")
workbook = writer.book   
worksheet = writer.sheets["Sheet1")
writer.save
pizzleberry
  • 41
  • 5
  • 1
    https://stackoverflow.com/questions/47737220/append-dataframe-to-excel-with-pandas – BigBen Dec 23 '21 at 18:02
  • 1
    https://stackoverflow.com/questions/38074678/append-existing-excel-sheet-with-new-dataframe-using-python-pandas#38075046 – BigBen Dec 23 '21 at 18:03
  • 1
    https://stackoverflow.com/questions/54186519/appending-pandas-dataframe-to-existing-excel-document – BigBen Dec 23 '21 at 18:03
  • @BigBen these all look very helpful, and I will go ahead and read through all of these. Although, the one thing I'm seeing is that all of these deal with appending data. I neglected to say that I would like to overwrite a portion of my data in my sheets. For example, I want everything in A:5 - H:5 in my sheet to stay the same, but I would like to replace the data in A:6 - H: 1000 with my new data. Is this still possible with the append? – pizzleberry Dec 23 '21 at 18:38

1 Answers1

0

In case u have both existing excel file and DataFrame in same format then you can simply import your exiting excel file into another DataFrame and concat both the DataFrames then save into new excel or existing one.

df1["df"] = pd.read_excel('testbook.xlsx')
df2["df"] = 1#your dataFrame
df = pd.concat([df1, df2])
df.to_excel('testbook.xlsx')

There are multiple ways of doing it if you want to do it completely using pandas library this will work.

gilf0yle
  • 1,092
  • 3
  • 9