Write a pandas df into Excel and save it into a copy

Question

I have a pandas dataframe and I want to open an existing excel workbook containing formulas, copying the dataframe in a specific set of columns (lets say from column A to column H) and save it as a new file with a different name.

The idea is to update an existing template, populate it with the dataframe in a specified set of column and then save a copy of the Excel file with a different name.

Any idea?

What I have is:

  import pandas
  from openpyxl import load_workbook

 book = load_workbook('Template.xlsx')
 writer = pandas.ExcelWriter('Template.xlsx', engine='openpyxl') 
 writer.book = book
 writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

 df.to_excel(writer)

 writer.save()

i would suggest first make a copy of the 'Template.xlsx' file and then work with that copy instead of "saving with different name" — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Mar 11, 2016 at 13:34

Sam Davey · Accepted Answer · 2017-09-26 13:30:45Z

4

The below should work, assuming that you are happy to copy into column A. I don't see a way to write into the sheet starting in a different column (without overwriting anything).

The below incorporates @MaxU's suggestion of copying the template sheet before writing to it (having just lost a few hours' work on my own template workbook to pd.to_excel)

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

template_file = 'Template.xlsx' # Has a header in row 1 already
output_file = 'Result.xlsx' # What we are saving the template as

# Copy Template.xlsx as Result.xlsx
copyfile(template_file, output_file)

# Read in the data to be pasted into the termplate
df = pd.read_csv('my_data.csv') 

# Load the workbook and access the sheet we'll paste into
wb = load_workbook(output_file)
ws = wb.get_sheet_by_name('Existing Result Sheet') 

# Selecting a cell in the header row before writing makes append()
#  start writing to the following line i.e. row 2
ws['A1']
# Write each row of the DataFrame
# In this case, I don't want to write the index (useless) or the header (already in the template)
for r in dataframe_to_rows(df, index=False, header=False):
    ws.append(r)

wb.save(output_file)

answered Sep 26, 2017 at 13:30

Sam Davey

4014 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam Davey Over a year ago

Here's the answer for starting in a particular column: stackoverflow.com/questions/36657288/… Replace the for loop in the above with the nested for loop in the linked example.

MaxU - stand with Ukraine · Accepted Answer · 2016-03-11 16:02:26Z

0

try this:

df.to_excel(writer, startrow=10, startcol=1, index=False, engine='openpyxl')

Pay attention at startrow and startcol parameters

answered Mar 11, 2016 at 16:02

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Collectives™ on Stack Overflow

Write a pandas df into Excel and save it into a copy

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related