I'm trying to tell python to insert "No work" in certain amount of cells, depending on how many days has passed since the codes was run.

So basically, let imagine that the code is run on a Friday and then a few day passes before it's started again. I want python to understand the number of days that has passed and insert "No work" in x numbers of cells in a excel file.

I've been using datetime module to calculate the number of days the code has been inactive in the "excel_update" function but find it very difficult to skip corresponding number of days in the for loop.

Can someone please tell me how this is done and explain you'r reasoning?

# ***** IMPORTS *****
import tkinter as tk
from unittest import skip
from openpyxl import Workbook
from openpyxl import load_workbook
import datetime as dt


# ***** VARIABLES *****
# use a boolean variable to help control state of time (running or not running)
running = False
# time variables initially set to 0
hours, minutes, seconds, total_time = 0, 0, 0, 0

# ***** NOTES ON GLOBAL *****
# global will be used to modify variables outside functions
# another option would be to use a class and subclass Frame

# ***** FUNCTIONS *****
# start, pause, and reset functions will be called when the buttons are clicked
# start function
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)
    # after each second (1000 milliseconds), call update function
    # use update_time variable to cancel or pause the time using after_cancel
    global update_time
    update_time = stopwatch_label.after(1000, update)


# Fill in the excel file with times   
def excel_update():
    global hours, minutes, seconds, total_time
    dest_filename = r"C:\Users\abbas\OneDrive\Desktop\Prog\Flex\tider.xlsx"
    wb = load_workbook(dest_filename)
    ws = wb.active
    
    days = [0]
    todays_date = dt.datetime.today()
    the_day = todays_date.day
    days.append(the_day)
    time_difference = the_day - days[-2]
    #time_difference = 4
    
    # Put value in next empty cell in specified region (if there is one).
    try:
        for row in ws.iter_rows(min_row=1, max_row=3, max_col=3):
            for cell in row: 
                if time_difference >=2:
                    next 
                if cell.value is None:
                    cell.value = f"{hours} h and {minutes} min"
                    raise StopIteration
    except StopIteration:
        pass

    wb.save(dest_filename)  # Update file.
  
 
# ***** 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 *****
# run app
root.mainloop()

CodePudding user response：

What I see in your code is that you are just inserting an hour/min text entry in the Excel cell when clicking the stop button (after clicking start button); f"{hours} h and {minutes} min".

So when you run the app again there is just the 'Xh and Ymin' entry in the cell. When excel_update is called it;

1. Creates a list 'date' with value 0
2. Gets todays date assigned to 'todays_date'
3. Gets the current dates day and appends that to the list 'date'
4. Calculates 'time_difference' as date[-2] which will always be the first element you assigned when creating the list, subtracted from the current date i.e. will always be the current date (The list only has two elements so [-2] is always the first element you assigned at creation)

Either way the main point is you would not be able to calculate the time difference without a record of when the last entry was made. You probably need to include the current date/time as well in another cell either as a single 'last update' value or per entry made, whichever works best.

Then when running update_excel again read the 'last update' date in order to get the date and this would be the first entry in your date list. Bear in mind that such a calculation is too simplistic however as it doesn't account for the next month(s) so you should use the full date, i.e. if you run it on April 30 then May 6 the days difference is not 6 - 30.

CodePudding user response：

You are looking for continue.