Write Dataframe to excel with template-CodePudding

I am trying to write my dataframe to excel. I am able to write the data using pandas.

df.to_excel(r'Path where the exported excel file will be stored\File Name.xlsx', index = False)

But the excel I am trying to write contain some template which look something like this.

Whenever I try to write the df values to excel using df.to_excel it always remove the template and write is there way I can write the data below the template in excel.

Any suggestions?

CodePudding user response：

Hi I am able to solve this using below code.

from openpyxl import load_workbook
path = "Excel.xlsx"
book = load_workbook(path)
writer = pandas.ExcelWriter("Excel.xlsx", engine='openpyxl')
writer.book = book
writer.sheets = {ws.title: ws for ws in book.worksheets}
df.to_excel(writer, startrow=writer.sheets['Sheet1'].max_row, index = False,header= False
writer.save()

CodePudding user response：

When you do df.to_excel(), it by default writes the file. If you want to update the file, first you need to read it and then append data from another file/operations to the template file.

import pandas as pd

df_temp = pd.read_excel('template.xlsx')

df_temp = pd.concat([df_temp, df]) 

df_temp.to_excel('template.xlsx')