Python (openpyxl) : Put data from one excel file to another (template file) & save it with another name while retaining the template

Question

I have a template excel file named as template.xlsx which has a number of sheets. I would like to copy data from a seperate .csv file into the first sheet of template.xlsx (named as data) and save the new file as result.xlsx while retaining the original template file.

I want to paste the data starting from the second row in the data sheet of template.xlsx

This is the code I have developed so far

import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows
import openpyxl
from shutil import copyfile

template_file = 'template.xlsx' # Has a header in row 1 already which needs to be skipped while pasting data but it should be there in the output file
output_file = 'result.xlsx' 

copyfile(template_file, output_file)
df = pd.read_csv('input_file.csv') #The file which is to be pasted in the template

wb = openpyxl.load_workbook(output_file)
ws = wb.get_sheet_by_name('data') #Getting the sheet named as 'data'

for r in dataframe_to_rows(df, index=False, header=False):
   ws.append(r)

 wb.save(output_file)

I am not able to get the desired output

The template file (with an extra row) on the left and the input file (data to be copied to the template) on the right, look like this

Shorthorn

Answer 1

There's not really any need to use the shutil module, as you could just use openpyxl.load_workbook to load your template and then save by a different name.

Additionally, ws.append(r) inside your for loop will append to the existing data taken form template.xlsx, and it sounds like you only want to keep the header.

I've provided a fully reproducible example below that creates template.xlsx for demonstration purposes. Then it loads template.xlsx adds new data to it and saves as result.xlsx.

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.chart import PieChart, Reference, Series
import pandas as pd

template_file = 'template.xlsx'
output_file = 'result.xlsx'

#This part creates a workbook called template.xlsx with a sheet called 'data' and sheet called 'second_sheet'
writer = pd.ExcelWriter('template.xlsx', engine='openpyxl') 
wb  = writer.book
df = pd.DataFrame({'Pie': ["Cream", "Cherry", "Banoffee", "Apple"],
                  'Sold': [2, 2, 1, 4]})

df.to_excel(writer, index=False, sheet_name='data', startrow=1)
ws = writer.sheets['data']
ws['A1'] = 1
ws['B1'] = 2

ch = PieChart()
labels = Reference(ws, min_col=1, min_row=3, max_row=6)
data = Reference(ws, min_col=2, min_row=3, max_row=6)
ch.series = (Series(data),)
ch.title = "Pies sold"
ws.add_chart(ch, "D2")

ws = wb.create_sheet("Second_sheet")
ws['A1'] = 'This Sheet will not be overwitten'

wb.save(template_file)

#Now we load workbook called template.xlsx modify the 'data' sheet and save under a new name
#template.xlsx has not been modified

df_new = pd.DataFrame({'different_name': ["Blueberry", "Pumpkin", "Mushroom", "Turnip"],
                  'different_numbers': [4, 6, 2, 1]})

wb = load_workbook(template_file)

ws = wb['data'] #Getting the sheet named as 'data'

rows = dataframe_to_rows(df_new, index=False, header=False)

for r_idx, row in enumerate(rows, 1):
    for c_idx, value in enumerate(row, 1):
         ws.cell(row=r_idx+2, column=c_idx, value=value)

wb.save(output_file)

Expected Output:

---

EDIT:

1. Change wb.get_sheet_by_name('data') (deprecated) to wb['data'].

Dodecagon

I have a header (extra row) in my template file (which I want to keep) but I want to paste my data (from a .csv file) starting from the second row. I have edited my question with the pictures so that it becomes easier to understand how my files look like. Additionally, this will give some insight – Shorthorn

Made a couple of changes based on your comment. The only important change is that I've shifted everything down one row in the creation of result.xlsx. So ws.cell(row=r_idx+1.... was changed to ws.cell(row=r_idx+2..... – Dodecagon

The data is getting pasted however the charts and graphs based on the new pasted data are not being generated. Does the module not support this feature? – Shorthorn

The module does support this feature. I've updated the answer to put a chart in there. You might want to make sure you're using the latest version released by doing this in command line $ python -m pip install --upgrade openpyxl – Dodecagon

Thanks. But, my chart is in the other sheet (Let's just assume Second_sheet in your case). secondly, I am on windows – Shorthorn