Spreadsheetgen

Automatically creating the schedule for the year

The following python script is able to generate the yearly schedule for a given year.

```
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()
```

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.