Put Pandas Data Frame to Existing Excel sheet

Question

Is there any way to put data frame into existing Excel sheet. I have a Data Frame that I need to copy into specific Excel sheet to specific location in the sheet, to cell A2, so it will mirror(paste) the whole data frame. Starting from selected cell(like the the picture) and including cells below. In this way I do not have to assign values to specific cell one by one.

df = pd.DataFrame([[1,2,3],[1,2,3]], columns=list('ABC')

Answer 1

How to write data to an arbitrary location in an existing Excel workbook

openpyxl 3.0.10 pandas 1.5.1

Let's see this workflow:

import pandas as pd
import openpyxl

# create a dummy data frame
df = pd.DataFrame({
    'one': [*'abcd'],
    'two': [*'wxyz'],
})

# write data into a new file
file_name = 'test_openpyxl.xlsx'
df.to_excel(file_name)

# NOTE: stop here and see the data in Excel,
#       close the file before continuing

# create a writer with parameters mode and if_sheet_exists
writer = pd.ExcelWriter(
    path=file_name,
    engine='openpyxl',
    mode='a',                   # append data to the file
    if_sheet_exists='overlay'   # keep previous data on existing sheets
)

# update data and write changes to the file
df['two'] = df['two'].str.upper()
df.to_excel(
    writer,                    # use openpyxl writer
    sheet_name='Sheet1',       # to update data on the existing sheet
    columns=['two'],           # in this only columns
    header=False,              # with no headers
    index=False,               # and no index printed
    startrow=1,                # at this specific position 
    startcol=2,                # with rows and cols numbering from 0
)    

writer.close()                 # file is saved by default when close

This method is equally applicable in both Linux and Windows. Note that append mode is not supported with xlsxwriter, so we have to use openpyxl to update existing data. It's also best to avoid working with a file in both Python and Excel at the same time.

The key parameter is if_sheet_exists with mode='a', which can be:

• error raise a ValueError.
• new Create a new sheet, with a name determined by the engine.
• replace Delete the contents of the sheet before writing to it.
• overlay Write contents to the existing sheet without removing the old

Answer 2

You can check, if the given sheet exist and you can delete the existing sheet and add a new one.

import xlwings as xw
def df_to_excel_util(excel,sheet_to_dataFrame_map):

    with xw.App(visible=False) as app:
        wb = app.books.open(excel)            
        current_sheets = [sheet.name for sheet in wb.sheets]
        
        for sheet_name in sheet_to_dataFrame_map.keys():
            if sheet_name in  current_sheets:
                wb.sheets[sheet_name].delete()
            
            new_sheet = wb.sheets.add(after=wb.sheets.count)
            new_sheet.range('A1').value = sheet_to_dataFrame_map.get(sheet_name)
            new_sheet.name = sheet_name
        wb.save()

Answer 3

Pandas allows you to write a dataframe to excel and specify the column and row number to start from.

So in your case, you can mention

df.to_excel(writer, sheet_name='Sheet1', header=None, index=False,
         startcol=1, startrow=2)

The sheet_name needs to be updated accordingly

---

So you entire code could look like:

import pandas as pd
from openpyxl import load_workbook

fn = r'C:\YourFolder\doc.xlsx'
book = load_workbook(fn)

df = pd.DataFrame([[1,2,3],[1,2,3]], columns=list('ABC'))

writer = pd.ExcelWriter(fn, engine='openpyxl')

writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

df.to_excel(writer, sheet_name='Sheet1', header=None, index=False,
             startcol=1, startrow=2)

writer.save()

Answer 4

It worked for me using xlwings. Keeping all data and style format.

import xlwings as xw
import pandas as pd

#create DF
df = pd.DataFrame([[7,2,3],[1,2,3]], columns=list('ABC'))

#load workbook
app = xw.App(visible=False)
wb = xw.Book('doc.xlsx')  
ws = wb.sheets['Sheet1']

#Update workbook at specified range
ws.range('A2').options(index=False).value = df

#Close workbook
wb.save()
wb.close()
app.quit()

[Result]

Data Frame is copied to specific Excel sheet Sheet1 to specific location cell A2, without losing any information. In this example, the chart is updated automatically referencing A2:C4.

Answer 5

You can to read the excel file withpd.read_excel(), copy the firs row to your DataFrame and save the DataFrame as excel file with a same name.