Changing row in Excel file when row is filled

Question

I'm coding a simple timer using Tkinter. I want my code to save the time the timer is showing when the stop button is activated in an Excel file. The timer works fine but the data is stored in just one row in the Excel file. I want it to change row when e.g. 5 cells in a row is filled.

I've been trying using conditional for loop. E.G. I tried to increment min_row with 1 every time the cell values is not None but it's not working.

Could someone tell me what I'm doing wrong?

# ***** IMPORTS *****
import tkinter as tk
from openpyxl import Workbook
from openpyxl import load_workbook



running = False
# time variables initially set to 0
hours, minutes, seconds, total_time = 0, 0, 0, 0

def start():
    global running
    if not running:
        update()
        running = True

# pause function
def pause():
    global running
    if running:
        # cancel updating of time using after_cancel()
        stopwatch_label.after_cancel(update_time)
        running = False

# reset function
def reset():
    global running
    if running:
        # cancel updating of time using after_cancel()
        stopwatch_label.after_cancel(update_time)
        running = False
    # set variables back to zero
    global hours, minutes, seconds
    hours, minutes, seconds = 0, 0, 0
    # set label back to zero
    stopwatch_label.config(text='00:00:00')

# update stopwatch function
def update():
    # update seconds with (addition) compound assignment operator
    global hours, minutes, seconds, total_time
    seconds += 1
    total_time += 1
    if seconds == 60:
        minutes += 1
        seconds = 0
    if minutes == 60:
        hours += 1
        minutes = 0
    # format time to include leading zeros
    hours_string = f'{hours}' if hours > 9 else f'0{hours}'
    minutes_string = f'{minutes}' if minutes > 9 else f'0{minutes}'
    seconds_string = f'{seconds}' if seconds > 9 else f'0{seconds}'
    # update timer label after 1000 ms (1 second)
    stopwatch_label.config(text=hours_string + ':' + minutes_string + ':' + seconds_string)

    global update_time
    update_time = stopwatch_label.after(1000, update)
    
def excel_update():
    dest_filename = r"C:\Users\abbas\OneDrive\Desktop\Prog\Flex	ider.xlsx"
    #dest_filename = r"C:\Users\abbas\OneDrive\Desktop\Prog\Flex	est.xlsx"
    wb = load_workbook(dest_filename)
    ws = wb.active
    next_row = 1
    
    cell_range = ws.iter_rows(min_row=next_row, max_row=3, max_col=2)
    #cell_range = ws["A1":"B3"]
    
    for rows in cell_range:        
        for columns in rows:
            if columns.value[columns] is None:
                columns.value[columns] = total_time
                          
        break
    
    wb.save(dest_filename)   
    
# ***** WIDGETS *****
# create main window
root = tk.Tk()
#root.geometry('450x200')
root.resizable(width=False, height=False)
root.title('Stopwatch')

# label to display time
stopwatch_label = tk.Label(text='00:00:00', font=('Arial', 20))
stopwatch_label.pack()

# start, pause, reset, stop buttons
start_button = tk.Button(text='start', height=3, width=5, font=('Arial', 10), command=start)
start_button.pack(side=tk.LEFT)

stop_button = tk.Button(text='stop', height=3, width=5, font=('Arial', 10), command=lambda:[root.quit(), excel_update()])
stop_button.pack(side=tk.LEFT)

pause_button = tk.Button(text='pause', height=3, width=5, font=('Arial', 10), command=pause)
pause_button.pack(side=tk.LEFT)

reset_button = tk.Button(text='reset', height=3, width=5, font=('Arial', 10), command=reset)
reset_button.pack(side=tk.LEFT)

# ***** MAINLOOP *****
root.mainloop()

user_355230 · Answer

Based on the code in your updated question, I think changing the excel_update() function as shown below will make it do what you want.

It searches through the columns of the rows from min_row to max_row looking for an empty cell. If it finds one it assigns the time value to it and raises a StopIteration exception to break out of the nested for loops and terminate the search. The exception is then simply ignored since it's part of normal processing.

from tkinter.messagebox import showerror

def excel_update():
def excel_update():
    dest_filename = r"C:\Users\abbas\OneDrive\Desktop\Prog\Flex	ider.xlsx"
    wb = load_workbook(dest_filename)
    ws = wb.active

    # Put value in next empty cell in specified region (if one exists).
    try:
        for row in ws.iter_rows(min_row=1, max_row=3, max_col=3):
            for cell in row:
                if cell.value is None:
                    cell.value = total_time
                    raise StopIteration
        else:  # No exception raised.
            showerror('Error!', 'No empty cells left for storing timer time!')
    except StopIteration:
        pass

    wb.save(dest_filename)  # Update file.

Changing row in Excel file when row is filled

1 Answers