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')
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.
openpyxl to avoid OS specifics.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 oldPandas 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()
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()
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.
