Copy pandas dataframe to excel using openpyxl

Question

I have some complicated formating saved in a template file into which I need to save data from a pandas dataframe. Problem is when I use pd.to_excel to save to this worksheet, pandas overwrites the formatting. Is there a way to somehow 'paste values' form the df into the worksheet? I am using pandas 0.17

import openpyxl import pandas as pd wb= openpyxl.load_workbook('H:/template.xlsx') sheet = wb.get_sheet_by_name('spam') sheet.title = 'df data' wb.save('H:/df_out.xlsx')  xlr = pd.ExcelWriter('df_out.xlsx') df.to_excel(xlr, 'df data') xlr.save() 

Answer 1

openpyxl 2.4 comes with a utility for converting Pandas Dataframes into something that openpyxl can work with directly. Code would look a bit like this:

from openpyxl.utils.dataframe import dataframe_to_rows rows = dataframe_to_rows(df)  for r_idx, row in enumerate(rows, 1):     for c_idx, value in enumerate(row, 1):          ws.cell(row=r_idx, column=c_idx, value=value) 

You can adjust the start of the enumeration to place the cells where you need them.

See openpyxl documentation for more information.