How to Write to an Existing Excel File Without Overwriting Data (Using Pandas)

Put Pandas Data Frame to Existing Excel sheet

It worked for me using xlwings. Keeping all data and style format.

import xlwings as xw
import pandas as pd

#create DF
df = pd.DataFrame([[7,2,3],[1,2,3]], columns=list('ABC'))

#load workbook
app = xw.App(visible=False)
wb = xw.Book('doc.xlsx')  
ws = wb.sheets['Sheet1']

#Update workbook at specified range
ws.range('A2').options(index=False).value = df

#Close workbook
wb.save()
wb.close()
app.quit()

[Result]

Data Frame is copied to specific Excel sheet Sheet1 to specific location cell A2, without losing any information. In this example, the chart is updated automatically referencing A2:C4.

Output to excel file without overwriting sheets

You should use ExcelWriter, it allows to open single .xlsx file and manupulate it.

import pandas as pd

# Initialize xlsx writer
writer = pd.ExcelWriter('output_file.xlsx', engine='xlsxwriter')
workbook = writer.book

df1 = pd.DataFrame({"a": [1,2,3],
                    "b": [1,2,3]})

df2 = pd.DataFrame({"c": [1,2,3],
                    "d": [1,2,3]})

df3 = pd.DataFrame({"e": [1,2,3],
                    "f": [1,2,3]})

df1.to_excel(writer,
             sheet_name="sheet1",
             startrow=0,
             startcol=0)

df2.to_excel(writer,
             sheet_name="sheet2",
             startrow=0,
             startcol=0)

df3.to_excel(writer,
             sheet_name="sheet3",
             startrow=0,
             startcol=0)

writer.save()

Append pandas dataframe to excelsheet, not overwrite it

with pd.ExcelWriter('target.xlsx', mode='a') as writer:  
    df.to_excel(writer, sheet_name='sheet_1')

Source: Pandas Dataframe to Excel

How do you write data in a specific cell in an excel file without overwriting data (using pandas)?

You cannot overwrite an excel file with XlsxWriter. However, if the whole content of the excel file ("input_file.xlsx") is only this table, you can read the old content in, add the new input and then write a new excel file ("output_file.xlsx") with the updated content:

import pandas as pd
df = pd.read_excel("input_file.xslx")
with pd.ExcelWriter("output_file.xlsx", mode="w", engine="xlsxwriter") as writer:
    stockSymbol = input("what stock do you want to add? ")
    new_row = {'Symbol': stockSymbol}
    df = df.append(new_row, ignore_index=True)
    df.to_excel(writer, index = False)
    

How to write to an existing excel file without over-writing existing data using pandas

Thanks Dijkgraaf. Your proposal worked.

Here is the full code for others (for future reference).

apologies for the font, couldnt set it properly. anyway hope below is to some use for someone in the future.

xlpath= "c:/projects/excelfile.xlsx"

df=pd.DataFrame() #creating a data frame before the for loop. (dataframe is empty before the for loop starts)

Url= www.your website.com 

for i in irange(1,10): 

       url= (urlA + str(i)) #this is url generator for pagination (to loop thru the page) 

       driver.get(url)  

       text=driver.find_element_by_xpath('/html/body/pre').text # gets text from site

       data=pd.DataFrame(eval(text)) #evalues the extracted text from site and converts to Pandas dataframe 

       df=df.append(data) #appends the dataframe (df) specificed before the for-loop and adds the new (data)

export_excel = df.to_excel(xlpath)  #exports consolidated dataframes (df) to excel

---

Related Topics