CODEWITHSUNDEEP
pythonpython-3.xpandasdataframeopenpyxl
Faizan Ali
Faizan Ali

Reputation: 1013

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.

Upvotes: 6

Views: 6624

Answers (2)

Charlie Wang
Charlie Wang

Reputation: 1

An example can be:

test_df = pd.read_excel('xls/template.xlsx', sheet_name='Sheet1')
test_df = test_df.append(df, ignore_index=True)
writer = pd.ExcelWriter('xls/template.xlsx', engine='openpyxl')
test_df.to_excel(writer, sheet_name='Sheet1')

Upvotes: 0

ReKx
ReKx

Reputation: 1066

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)

Upvotes: 4

Related Questions