import openpyxl as xl
from datetime import date
workbook = xl.load_workbook('Schedule.xlsx')
sheet = workbook['Sheet1']
for column in range(2, sheet.max_column + 1):
for row in range(2, sheet.max_row + 1):
date1 = date.today()
day1 = date1.strftime("%A")
n = date1.weekday()
days = ['Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday']
day2 = days[n + 1]
cell = sheet.cell(row, column)
time = sheet.cell(row, 1).value
if sheet.cell(1, column).value == day1:
if cell.value != None:
reminder1 = f"{time}: {cell.value} "
else:
pass
elif sheet.cell(1, column).value == day2:
if cell.value != None:
reminder2 = f"{time}: {cell.value}"
else:
pass
else:
pass
print(f"""REMINDER:
Today:
{reminder1}
Tommorow:
{reminder2}""")
This is my code in python. It takes input from a spreadsheet("Schedule.xlsx") and checks if there are any task today or tommorow, if there are then it sends reminders. The spreadsheet The problem is if there are more than 1 tasks in a day, python only shows 1 task. (Sorry, I know the code is messy, I am new.) Thank you
I tried using for loops, expecting the same reminder to repeat if there are more than 1 tasks but it doesn't work. I also tried making the tasks elements of a list and then trying it but the code gets complex and it gets difficult to get the time along with the task.
This is a method you can use, may need to be adjusted depending on exactly what you want to do with the results.
Firstly some notes on your code
date, day1, day2. You can see in my example these are set along with other static items near the top of the code.cell.value != None you should not use equality operators. Use cell.value is None or cell.value is not None depending on which comparison you want.
Code operation
Since we know the today's day i.e. on the day this answer is written it is Wednesday, we know that we want to search column D in your Excel sheet.
This is static Mon is always B, Tue always C and Wed always D etc (unless you modify the layout of the sheet).
Therefore we know which column to run the loop on and there is no need to loop through columns B, C looking for the correct day. The cell values loop is set to loop only the required column using iter_rows
sheet.iter_rows(min_col=col, max_col=col, min_row=2, max_row=12)
min and max column are the column number which is the days' number plus 2 since columns start from 1. The row min is set to 2 to jump the Header row and max is 12 as this is again a set value with the rows covering the times from 11:00 AM to 9:00 PM.
Also it's only necessary to loop through todays' column, we use the cell offset attribute to simultaneously obtain the value from the tomorrows' column as we move down todays' column.
The found remindrs for today and tomorrow are then added to their own dictionary of dictionaries. These hold the reminder description and the time based on the cells current row number looked up from the dictionary time_dict.
Note this could also be done as a cell offset, use a negative number from the current column as the column= value to read the corresponding cell in column A
This results in two dictionaries the name of the day as the key and values as a sub dictionary where the time is key and reminder description is the value, e.g.
{'Wednesday': {'1:00 PM': 'Running checks on Project 5', '3:00 PM': 'Audit regarding Project 14'}}
Then it's just a matter of printing out the information or utilise as you need. I used a list, display_list to combine the two dictionaries to allow easy looping through the same print code.
Note
The code would fail if today is Sunday due to the need to wrap around from column H back to B. Don't know if this is required since it seems it may be a work calendar, or may be Friday needs to wrap to Monday for the 'tomorrow' schedule. Either way handling this can be included in the code.
import openpyxl as xl
from datetime import date
days_list = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
time_dict = {2: '11:00 AM', 3: '12:00 AM', 4: '1:00 PM', 5: '2:00 PM', 6: '3:00 PM', 7: '4:00 PM', 8: '5:00 PM',
9: '6:00 PM', 10: '7:00 PM', 11: '8:00 PM', 12: '9:00 PM'}
date = date.today().weekday()
day1 = days_list[date]
day2 = days_list[date + 1]
workbook = xl.load_workbook('Schedule.xlsx')
sheet = workbook['Sheet1']
### Create Dictionary for today and tomorrow
today_dict = {day1: {}}
tomorrow_dict = {day2: {}}
display_list = [today_dict, tomorrow_dict]
### Start loop at todays column from the expected position of today
col = 2 + date
for column in sheet.iter_rows(min_col=col, max_col=col, min_row=2, max_row=12):
### Checking cells in today's column
for cell in column:
### Today's column
if cell.value is not None:
today_dict[day1].update({time_dict[cell.row]:cell.value})
### Tomorrows column
elif cell.offset(column=1).value is not None:
tomorrow_dict[day2].update({time_dict[cell.row]:cell.offset(column=1).value})
### Print the reminders from the dictionaries
print('***Reminder***')
for dict in display_list:
for sched_day, sched_items in dict.items():
print(f'Schedule Day: {sched_day}')
for tm, item in sched_items.items():
print(f'Time: {tm} - {item}')
print('')
Output
using your example sheet with the items moved to Wed/Thu.
***Reminder***
Schedule Day: Wednesday
Time: 1:00 PM - Running checks on Project 5
Time: 3:00 PM - Audit regarding Project 14
Schedule Day: Thursday
Time: 2:00 PM - Meeting with General Manager