A script to convert class table to ICS format, designed for SJTU SPEIT curriculum.

ClassTableToCS.py

# %%
from openpyxl import load_workbook
import pandas as pd
import numpy as np
import re
from icalendar import Calendar, Event, vText
from pytz import timezone
from datetime import datetime, timedelta
from typing import Tuple

# %%
SHEET_NAMES = ['W1-8', 'W9-16']
FILENAME = 'file.xlsx'
CONFIG_TIMETABLE_COL_IDX = 1
CONFIG_DATE_ROW_IDX = 1
CONFIG_WEEDDAY_NAMES = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday']
TZ = timezone('Asia/Shanghai')

# %%
def is_selected(value: str) -> Tuple[bool, str]:
    _pattern_ie = re.compile('\n?.*\(.*IE.*\).*')
    _pattern_me = re.compile('\n?.*\(.*ME.*\).*')
    _pattern_epe = re.compile('\n?.*\(.*EPE.*\).*')

    _match_ie = re.findall(_pattern_ie, str(value))
    _match_me = re.findall(_pattern_me, str(value))
    _match_epe = re.findall(_pattern_epe, str(value))
    _is_match_all = ((len(_match_ie) == 0) and (len(_match_me) == 0) and (len(_match_epe) == 0)) and value

    if len(_match_ie) > 0 or _is_match_all:
        if _is_match_all:
            return True, str(value).strip('\n')
        else:
            return True, str(_match_ie[0]).strip('\n')
    else:
        return False, ''

# %%
# f = lambda x: (len(re.findall(re.compile('\([.]*IE[.]*\)'), str(x))) > 0)
wb = load_workbook(FILENAME)
raw_context = []

for SHEET_NAME in SHEET_NAMES:
    data_np = np.array(pd.DataFrame(wb[SHEET_NAME].values))
    weekday_col_idx = np.sort(np.concatenate([np.where(data_np == day)[1] for day in CONFIG_WEEDDAY_NAMES]))  # Weekdays
    timetable = data_np[:, CONFIG_TIMETABLE_COL_IDX]  # global timetable

    for weekday in weekday_col_idx:
        schedule = data_np[:, weekday]
        for idx, value in enumerate(schedule):
            res = is_selected(value)
            if res[0]:
                period = timetable[idx]
                # print(f"{value}/{period}/{schedule[CONFIG_DATE_ROW_IDX]}")
                raw_context.append({
                    "value": res[1],
                    "period": timetable[idx],
                    "date": schedule[CONFIG_DATE_ROW_IDX],
                })

# %%
events = []
for target in raw_context:
    summary = target['value']
    _date = target['date']
    try:
        dtstart_str = target['value'].split(' ')[0].split('-')[0]
        dtend_str = target['value'].split(' ')[0].split('-')[1]
        dtstart = _date + timedelta(hours=int(dtstart_str.split('h')[0]),
                                    minutes=int(dtstart_str.split('h')[1]) if dtstart_str.split('h')[1] != '' else 0)
        dtend = _date + timedelta(hours=int(dtend_str.split('h')[0]),
                                  minutes=int(dtend_str.split('h')[1]) if dtend_str.split('h')[1] != '' else 0)
    except Exception as err:
        if target['period'] == None:
            print(target, 'filtered')
            continue
        else:
            print(target, 'converted')
            dtstart_str = target['period'].split('-')[0]
            dtstart = _date + timedelta(hours=int(dtstart_str.split(':')[0]), minutes=int(dtstart_str.split(':')[1]))
            dtend_str = target['period'].split('-')[1]
            dtend = _date + timedelta(hours=int(dtend_str.split(':')[0]), minutes=int(dtend_str.split(':')[1]))
    location_str = target['value'].split('-')[-1]

    event = Event()
    event.add('summary', summary)
    event.add('dtstart', dtstart.astimezone(TZ))
    event.add('dtend', dtend.astimezone(TZ))
    event['location'] = vText(location_str)
    events.append(event)

cal = Calendar()
for event in events:
    cal.add_component(event)

# %%
with open('out.ics', 'wb') as f:
    f.write(cal.to_ical())