I'm new to python and I want to save the current date and time to excel every 5 seconds using python but it only saves 1 I added print(nowDate)to see if the scheduler is working and it is but it wont save to excel. I want it to save continuously as long the script is running.

This is what I get

This is what I want

import datetime
import schedule
import time

from openpyxl import Workbook

def my_function ():
   now = datetime.datetime.now()
   nowDate = now.strftime('%Y-%m-%d %H:%M:%S')
   wb = Workbook()
   sheet1 = wb.active
   sheet1.title = 'sampleSheet'
   sheet1.cell(row=1, column=1).value = nowDate
   wb.save(filename='test.xlsx')
   print(nowDate)

schedule.every(5).seconds.do(my_function)

while 1:
   schedule.run_pending()
   time.sleep(1)

CodePudding user response：

Just create a index variable (row_no) outside the function to point to the current row where the new data is going to be inserted.

Create the workbook object outside the function. In your case, every time the function is getting called and a new workbook object is getting created.

I've commented the edits.

Here's the code:-

import datetime
import schedule
import time

from openpyxl import Workbook

row_no = 1 # new code
wb = Workbook() # taken outside from my_function


def my_function ():
   global row_no # new code
   global wb # new code
   now = datetime.datetime.now()
   nowDate = now.strftime('%Y-%m-%d %H:%M:%S')
   sheet1 = wb.active
   sheet1.title = 'sampleSheet'
   sheet1.cell(row=row_no, column=1).value = nowDate # row=row_no
   row_no  = 1 # new code
   wb.save(filename='test.xlsx')
   print(nowDate)

schedule.every(5).seconds.do(my_function)

while 1:
   schedule.run_pending()
   time.sleep(1)