how to append data frame to existed formulated excel file

Question

if u have a formulated excel file and now wants to append data frame by python then how..

I used this code but did not get output

mypath="C:\\Users\\egoyrat\\Desktop\\smt tracker\\Swap Manual.xlsx" book = load_workbook(mypath) ws= book['Main'] for row_data in now_append:enter code here ws.append(row_data)

Answer 1

If you write to the file by cells, you can do it. Below is the code... Assuming that, by formulated, you mean the cells have format (color, font, etc.) and you want to write data without changing the format of the cells)

import numpy as np
#Create random 10x3 dataframe
df = pd.DataFrame(np.random.randint(0,100,size=(10, 3)), columns=list('ABC'))

wb = openpyxl.load_workbook('output.xlsx', read_only=False)
ws=wb['Sheet2']

#Write header
for col in range(df.shape[1]):
    ws.cell(1, col+1).value = df.columns[col]

#Write the data
for row in range(df.shape[0]):
    for column in range(df.shape[1]):
        ws.cell(row=row+2, column=column+1).value = df.iloc[row,column]
wb.save('output.xlsx')
wb.close()

Output - Before writing

After writing your data

Option 2 - based on Charlie Clark's input

This does the same thing as above, but a little faster/better....

import numpy as np
from openpyxl.utils.dataframe import dataframe_to_rows

df = pd.DataFrame(np.random.randint(0,100,size=(10, 3)), columns=list('ABC'))

wb = openpyxl.load_workbook('output.xlsx', read_only=False)
ws=wb['Sheet2']
rows = 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('output.xlsx')
wb.close()