Spreadsheetgen: Difference between revisions
Jump to navigation
Jump to search
Created page with "=== 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 fir..." |
m fix: use a constant for the year rather than a hardcoded value |
||
| (2 intermediate revisions by one other user not shown) | |||
| Line 1: | Line 1: | ||
=== Automatically creating the schedule for the year === | === Automatically creating the schedule for the year === | ||
The following python script is able to generate the yearly schedule for a given year. | The following python script is able to generate the yearly schedule for a given year. | ||
< | <nowiki> | ||
``` | ``` | ||
from datetime import datetime, timedelta | from datetime import datetime, timedelta | ||
| Line 7: | Line 7: | ||
import datetime as dt | import datetime as dt | ||
import xlsxwriter | import xlsxwriter | ||
YEAR = 2022 | |||
workbook = xlsxwriter.Workbook('demo.xlsx') | workbook = xlsxwriter.Workbook('demo.xlsx') | ||
| Line 17: | Line 19: | ||
def get_last_date_of_month(year, month): | def get_last_date_of_month(year, month): | ||
if month == 12: | if month == 12: | ||
last_date = datetime(year, month, 31) | last_date = datetime(year, month, 31) | ||
else: | else: | ||
last_date = datetime(year, month + 1, 1) + timedelta(days=-1) | last_date = datetime(year, month + 1, 1) + timedelta(days=-1) | ||
return last_date | return last_date | ||
| Line 28: | Line 28: | ||
worksheet = workbook.add_worksheet(f'{start.strftime("%B")} {start.year}') | worksheet = workbook.add_worksheet(f'{start.strftime("%B")} {start.year}') | ||
worksheet.set_column('A:H', 20) | worksheet.set_column('A:H', 20) | ||
worksheet.write('A1',f'{start.strftime("%B")} {start.year}',bold) | worksheet.write('A1',f'{start.strftime("%B")} {start.year}',bold) | ||
worksheet.write('A2','Date',bold) | worksheet.write('A2','Date',bold) | ||
worksheet.write('B2','Start',bold) | worksheet.write('B2','Start',bold) | ||
| Line 61: | Line 59: | ||
for i in range(1, 13): | for i in range(1, 13): | ||
first= get_first_date_of_month( | first= get_first_date_of_month(YEAR, i) | ||
last = get_last_date_of_month( | last = get_last_date_of_month(YEAR, i) | ||
add_month(workbook,first,last) | add_month(workbook,first,last) | ||
workbook.close() | workbook.close() | ||
``` | ``` | ||
</ | </nowiki> | ||
To update this for any given year, replace the | To update this for any given year, simply replace the YEAR = 2024 with the proper year. | ||
When you run the program, a file called `demo.xlsx` will be created in the current working directory, this is the yearly schedule. | When you run the program, a file called `demo.xlsx` will be created in the current working directory, this is the yearly schedule. | ||
[[Category:Infrastructure]][[Category:Responsibility]] | |||
Latest revision as of 15:49, 7 September 2024
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
YEAR = 2022
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(YEAR, i)
last = get_last_date_of_month(YEAR, i)
add_month(workbook,first,last)
workbook.close()
```
To update this for any given year, simply replace the YEAR = 2024 with the proper year.
When you run the program, a file called `demo.xlsx` will be created in the current working directory, this is the yearly schedule.