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.
Dataframe append syntaxYou type the name of the first dataframe, and then . append() to call the method. Then inside the parenthesis, you type the name of the second dataframe, which you want to append to the end of the first.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With