first of all, this post is the first piece of the solution, where you should specify startrow=
:
Append existing excel sheet with new dataframe using python pandas
you might also consider header=False
.
so it should look like:
df1.to_excel(writer, startrow = 2,index = False, Header = False)
if you want it to automatically get to the end of the sheet and append your df then use:
startrow = writer.sheets['Sheet1'].max_row
and if you want it to go over all of the sheets in the workbook:
for sheetname in writer.sheets:
df1.to_excel(writer,sheet_name=sheetname, startrow=writer.sheets[sheetname].max_row, index = False,header= False)
btw: for the writer.sheets
you could use dictionary comprehension (I think it’s more clean, but that’s up to you, it produces the same output):
writer.sheets = {ws.title: ws for ws in book.worksheets}
so full code will be:
import pandas
from openpyxl import load_workbook
book = load_workbook('test.xlsx')
writer = pandas.ExcelWriter('test.xlsx', engine="openpyxl")
writer.book = book
writer.sheets = {ws.title: ws for ws in book.worksheets}
for sheetname in writer.sheets:
df1.to_excel(writer,sheet_name=sheetname, startrow=writer.sheets[sheetname].max_row, index = False,header= False)
writer.save()