How to update an existing excel spreadsheet with data from dataframe-CodePudding

I need to update an existing excel spreadsheet with data from dataframe 'CF'. I try this code but nothing happens and I don't get an error. Could you suggest what could be wrong?

import openpyxl as ox
def update_spreadsheet(path : str ='/a123.xlsx', _df = CF, starcol : int = 1, startrow : int = 1, sheet_name : str ="TDSheet"):
    wb = ox.load_workbook(path)
    for ir in range(0, len(_df)):
        for ic in range(0, len(_df.iloc[ir])):
            wb[sheet_name].cell(startrow   ir, starcol   ic).value = _df.iloc[ir][ic]
    wb.save(path)

CodePudding user response：

The reason you did not see any response/error is because you created a function, but did not call it. I have updated the function code and called it to update a sheet with some random data. Let me know if you face any issues.

Code

import openpyxl as ox
import numpy as np
CF = pd.DataFrame() # Had to define this as you kept CF as default. But, would suggest you don't have _df=CF, but rather just _df

def update_spreadsheet(path:str ='/a123.xlsx', _df=CF, startcol:int=1, startrow:int=1, sheet_name:str ="TDSheet"):
    wb = ox.load_workbook(path)
    ws=wb[sheet_name]
    for row in range(0, _df.shape[0]): #For each row in the dataframe
        for col in range(0, _df.shape[1]): #For each column in the dataframe
            ws.cell(row = startrow   row, column = startcol   col).value = _df.iat[row, col]
    wb.save(path)

#Dummy data for CF
CF = pd.DataFrame(np.random.randint(0,1000,size=(10, 3)))

#Call the defined function
update_spreadsheet('./a123.xlsx', CF, 3, 20, 'Sheet1') #Write to sheet1 starting from row 20 and column 3 / column C