How can we write new data to existing Excel spreadsheet

0 votes

I have a weekly procedure that generates a data frame with about 1,000 entries. In order to avoid having to read the spreadsheet again as the file gets larger, I would like to be able to add it to an existing sheet. I saw this answer here: Append existing excel sheet with new dataframe using python pandas. Sadly, it doesn't seem to be functioning well for me. I'm attempting to add the following dummy code to that already-existing file. Currently, it has two drawbacks: first, it overwrites the data rather than appending it. Second, even after the software has started, when I try to open the file, it will only let me do so in read-only mode. I can confirm that I'm also utilizing Pandas 1.4.

import pandas as pd


data = {'Name': ['Tom', 'Joseph', 'Krish', 'John'], 'Age': [20, 21, 19, 18]}
df = pd.DataFrame(data)


filename = "Testing Append Process.xlsx"
writer = pd.ExcelWriter(filename, engine="openpyxl", mode="a", if_sheet_exists="overlay")

df.to_excel(writer, index=False)
writer.save()
Oct 14, 2022 in Others by Kithuzzz
• 27,360 points
1,679 views

1 answer to this question.

0 votes

Please notes that the Testing Append Process.xlsx file has to be created before running this code.

from openpyxl import load_workbook
import pandas as pd

data = {'Name': ['Tom', 'Joseph', 'Krish', 'John'], 'Age': [20, 21, 19, 18]}
df = pd.DataFrame(data)

filename = "Testing Append Process.xlsx"
workbook = load_workbook(filename)
writer = pd.ExcelWriter(filename, engine='openpyxl')
writer.book = workbook
writer.sheets = {ws.title: ws for ws in workbook.worksheets}

df.to_excel(writer, startrow=writer.sheets['Sheet1'].max_row, index = False, header= False)

writer.close()

Returns the following if you will run the code twice.

enter image description here

answered Oct 14, 2022 by narikkadan
• 50,440 points

Related Questions In Others

0 votes
0 answers

Data Driven Framework -- how to read and write in excel sheet using Selenium WebDriver with java

I'm using this code to read something, ...READ MORE

Oct 31, 2022 in Others by Kithuzzz
• 27,360 points
105 views
0 votes
1 answer

How can I preserve the format while exporting data from excel to evernote

The contents for an Evernote note are ...READ MORE

answered Jan 5 in Others by narikkadan
• 50,440 points
39 views
0 votes
1 answer
0 votes
1 answer

How to import data from a HTML table on a website to excel?

Hello  To import any HTML file in excel there ...READ MORE

answered Feb 10, 2022 in Others by gaurav
• 22,940 points
494 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 3,299 views
0 votes
1 answer
0 votes
1 answer

How can I sort one set of data to match another set of data in Excel?

In addition, INDEX MATCH is a more ...READ MORE

answered Oct 29, 2022 in Others by narikkadan
• 50,440 points
92 views
0 votes
1 answer

How to save a new sheet in an existing excel file, using Pandas?

import pandas as pd import numpy as np path ...READ MORE

answered Dec 10, 2022 in Others by narikkadan
• 50,440 points
175 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP