Spreadsheetgen

Automatically creating the schedule for the year

The following python script is able to generate the yearly schedule for a given year. <markdown> ``` from datetime import datetime, timedelta from dateutil.rrule import rrule, DAILY, TH, SA import datetime as dt import xlsxwriter

workbook = xlsxwriter.Workbook('demo.xlsx') bold = workbook.add_format({'bold': True})

def get_first_date_of_month(year, month):

   first_date = datetime(year, month, 1)
   return first_date

def get_last_date_of_month(year, month):

   if month == 12:
       last_date = datetime(year, month, 31)
   else:
       last_date = datetime(year, month + 1, 1) + timedelta(days=-1)

   return last_date

def add_month(workbook,start, end):

   worksheet = workbook.add_worksheet(f'{start.strftime("%B")} {start.year}')
   worksheet.set_column('A:H', 20)

   worksheet.write('A1',f'{start.strftime("%B")} {start.year}',bold)

   worksheet.write('A2','Date',bold)
   worksheet.write('B2','Start',bold)
   worksheet.write('C2','Start',bold)
   worksheet.write('D2','Event',bold)
   worksheet.write('E2','Volunteer 1',bold)
   worksheet.write('F2','Volunteer 2',bold)
   worksheet.write('G2','Volunteer 3',bold)
   worksheet.write('H2','Volunteer 4',bold)
   results = rrule(DAILY,
           dtstart = dt.datetime(start.year,start.month,start.day),
           until = dt.datetime(end.year,end.month,end.day),
           byweekday = (TH, SA),
   )
   i = 2
   for result in results:
       col = xlsxwriter.utility.xl_col_to_name(i)
       date = str(result).split(' ')[0]
       if result.isoweekday() == 4:
           worksheet.write(i, 0, date)
           worksheet.write(i, 1, '6:00PM')
           worksheet.write(i, 2, '8:00PM')
           worksheet.write(i, 3, 'Repairsday')
       elif result.isoweekday() == 6:
           worksheet.write(i, 0, date)
           worksheet.write(i, 1, '2:00PM')
           worksheet.write(i, 2, '6:00PM')
           worksheet.write(i, 3, 'Open Hours')
       i += 1

for i in range(1, 13):

   first= get_first_date_of_month(2022, i)
   last = get_last_date_of_month(2022, i)
   add_month(workbook,first,last)

workbook.close() ``` </markdown> To update this for any given year, replace the arguments to `get_first_date_of_month(2022,i)` and `get_last_date_of_month(2022,i)` with the desired years.

When you run the program, a file called `demo.xlsx` will be created in the current working directory, this is the yearly schedule.