CODEWITHSUNDEEP
pythonexcelpython-3.xpandas
iraciv94
iraciv94

Reputation: 840

Saving Pandas series on existing excel worksheet

I am trying to export some values from python to excel. In the first part I want to setup the sheet and save it in a xlsx file. After that I want to overwrite the same worksheet by adding a pandas series.

My issue comes from the fact that when I run my code, I obtain a brand new worksheet with the name 'Name1' instead instead of putting the Series on the existing sheet named "Name" previously.

import pandas as pd
from openpyxl import Workbook
from openpyxl import load_workbook

#Page setup
wb = Workbook()
ws0 = wb.active
ws0.title = "Name"
ws0.sheet_properties.tabColor = "1072BA"
ws0['B1'] = "AAA"
wb.save('Example.xlsx')
wb.close()

#Insert pandas series on existing worksheet
path = r".\Example.xlsx"
book = load_workbook('Example.xlsx')
writer = pd.ExcelWriter(path, engine = 'openpyxl')
writer.book = book

r1y = pd.Series([1,3,5,13,6,8])
df1y = pd.DataFrame(r1y)

df1y.to_excel(writer, 'Name', header=False, startcol = 0, startrow = 1)
writer.save()
writer.close()

What am I doing wrong?

Thanks in advance

Upvotes: 2

Views: 1146

Answers (2)

user15258196
user15258196

Reputation: 1

I have found the answer to this problem in a different website, which I report here. I have not come up with this answer myself, so I want to credit who did: https://cmsdk.com/python/insert-pandas-dataframe-into-existing-excel-worksheet-with-styling.html

from openpyxl import load_workbook, Workbook
import pandas as pd
df = pd.DataFrame(data=["20-01-2018",4,9,16,25,36])
path = 'filepath.xlsx'
writer = pd.ExcelWriter(path, engine='openpyxl')
writer.book = load_workbook(path)
**writer.sheets = dict((ws.title,ws) for ws in writer.book.worksheets)**
df.to_excel(writer,sheet_name="Sheet1", startrow=2,index=False, header=False)
writer.save()

What did the job for me was the row highlighted in bold. I hope it can be of help to you!

Upvotes: 0

Vityata
Vityata

Reputation: 43595

Obviously to_excel creates a new worksheet and does not want to write in an already created one. You are already having one, which is called Name, thus the new one is Name1. If you do not have a sheet MyOwnName this will crete one and write the data there:

df1y.to_excel(writer, 'MyOwnName', header=False, startcol = 0, startrow = 1)

Although I could not find anything mentioning it in the documentation: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_excel.html

Upvotes: 1

Related Questions