CODEWITHSUNDEEP
pythonopenpyxl
Serdia
Serdia

Reputation: 4426

How to load data into excel template into particular sheet name

I have a template file Template.xlsx. It has 2 sheets: "Monthly Premium" and "Monthly Losses"

Then I am creating dataframe df. How can I simply save df into "Monthly Premium" sheet?

I thought something like this would work but it creates a new sheet name.

import pandas as pd
from openpyxl import load_workbook

#sample df
df = pd.DataFrame()
df["A"] = [1,2,3,4,5]
df["B"] = ["A", "B", "C", "D", "E"]
#print(df)

path = r'C:\My\Path\Template1.xlsx'
book = load_workbook(path)
writer = pd.ExcelWriter(path, engine='openpyxl')
writer.book = book
df.to_excel(writer, sheet_name='Monthly Premium')

writer.save()
writer.close()

Upvotes: 0

Views: 44

Answers (1)

Mike67
Mike67

Reputation: 11342

To save a dataframe to an existing worksheet, you must set the worksheet collection in the excel writer.

Try this code:

###### create test file ######

from openpyxl import load_workbook, Workbook
wb = Workbook()
ws = wb.active
ws.title='Monthly Premium'  # rename default sheet
ws = wb.create_sheet('Monthly Losses')  # add sheet
wb.save(r'Template1.xlsx') 
  
######### main script #########

import pandas as pd
from openpyxl import load_workbook

#sample df
df = pd.DataFrame()
df["A"] = [1,2,3,4,5]
df["B"] = ["A", "B", "C", "D", "E"]
#print(df)

path = r'Template1.xlsx'
book = load_workbook(path)
writer = pd.ExcelWriter(path, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)  # prevent new sheet
df.to_excel(writer, sheet_name='Monthly Premium')

writer.save()
writer.close()

Output

Excel

Upvotes: 1

Related Questions