Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to append a dataframe to an excel template?

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.

like image 712
Faizan Ali Avatar asked Jul 12 '18 07:07

Faizan Ali


People also ask

How do you append a DataFrame in Python?

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.


1 Answers

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)
like image 100
ReKx Avatar answered Oct 14 '22 02:10

ReKx