How to append a dataframe to an excel template?

Question

I have an xlsx template with some headers and other information, all I want is to fill that sheet with the dataframe without losing the contents of the template. Here's the code I'm using for that:

 writer = pd.ExcelWriter('xls/template.xlsx', engine='openpyxl')
 df.to_excel(writer, index=False, sheet_name='Sheet1',startrow=2,header=None)
 writer.save()

I read somewhere that, to use a template I must use openpyxl engine in the writer but even then the contents of the template are lost and the dataframe is written after 2 rows. The rows above had some headers before but now they are blank.

Answer 1

Just load the worksheet using openpyxl and write the dataframe into it.

import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows

wb= openpyxl.load_workbook('H:/your/dir/template.xlsx')
ws = wb.get_sheet_by_name('xyz')
rows = dataframe_to_rows(df)

for r_idx, row in enumerate(rows, 3):  #starts at 3 as you want to skip the first 2 rows
    for c_idx, value in enumerate(row, 1):
         ws.cell(row=r_idx, column=c_idx, value=value)