Copy pandas dataframe to excel using openpyxl

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_rowsrows = 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.

I slightly modified @CharlieClark's great answer to avoid the index (which is not there in the original Excel file). Here is a ready-to-run code:

import pandas as pdfrom openpyxl.utils.dataframe import dataframe_to_rowsfrom openpyxl import load_workbookwb = load_workbook('test.xlsx')  # load as openpyxl workbook; useful to keep the original layout                                 # which is discarded in the following dataframedf = pd.read_excel('test.xlsx')  # load as dataframe (modifications will be easier with pandas API!)ws = wb.activedf.iloc[1, 1] = 'hello world'    # modify a few thingsrows = dataframe_to_rows(df, index=False)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)wb.save('test2.xlsx')

Here is the solution for you using clipboard:

import openpyxlimport pandas as pdimport clipboard as clp#Copy dataframe to clipboarddf.to_clipboard()#paste the clipboard to a valirablecells = clp.paste()#split text in varialble as rows and columnscells = [x.split() for x in cells.split('\n')]#Open the work bookwb= openpyxl.load_workbook('H:/template.xlsx')#Get the Sheetsheet = wb.get_sheet_by_name('spam')sheet.title = 'df data'#Paste clipboard values to the sheetfor i, r in zip(range(1,len(cells)), cells):    for j, c in zip(range(1,len(r)), r):        sheet.cell(row = i, column = j).value = c#Save the workbookwb.save('H:/df_out.xlsx')