###通过excel课表生成ios日历订阅文件

import pandas as pd
from datetime import date, datetime, timedelta, timezone

######################################################

XLSX_PATH = 'week5.xlsx'
ICAL_PATH = 'export.ics'

day_one = date(2022, 12, 13)

#课程对应的时间
cols = {2: 1, 4: 3, 5: 3, 6: 3, 7: 3}

######################################################

tz_shanghai = timezone(timedelta(hours=8), 'Asia/Shanghai')

df = pd.read_excel(XLSX_PATH)
denan = df.fillna('空')
class_all = denan.to_dict('list')
del class_all['FAP-W5']
del class_all['Unnamed: 1']
del class_all['Unnamed: 3']
# for k,v in class_all.items
class_alls = []
for n in class_all:
    news_ids = []
    for id in class_all[n]:
        if id not in news_ids:
            news_ids.append(id)
    class_alls += news_ids
    class_alls_list = list(set(class_alls))
if 'Class Meeting' in class_alls_list:
    del class_alls_list[class_alls_list.index('Class Meeting')]
del class_alls_list[class_alls_list.index('空')]
classes = class_alls_list
ical = open(ICAL_PATH, 'w')

ical.write('BEGIN:VCALENDAR\n')
try:
    for day_offset, col in enumerate(cols):
        ssed = df.columns[cols[col]] #读取列名称
        ssds = df.loc[:, df.columns[cols[col]]]#读取整列series数据下标与值{(key:vlu),(key,vlu)}
        times = df.loc[:, df.columns[cols[col]]].dropna()

        day = day_one + timedelta(days=day_offset)
        sss = df.loc[:, df.columns[col]].dropna()#读取整列
        class_day = df.loc[:, df.columns[col]].dropna()
        class_day.pop(0)
        class_day_index = class_day.index
        for index in class_day_index:
            class_one = df.loc[index, df.columns[col]]
            ll = [cls for cls in classes if cls in class_one]
            class_one_choose = any(ll)
            if any([cls for cls in classes if cls in class_one]):

                term = df.loc[index, df.columns[col]].split('-')

                # ss=term.__len__()
                # if ( ss< 2):
                #     continue
                ical.write('BEGIN:VEVENT\n')

                time = ''
                # if not term.__len__() in [2,3,4]:
                if (term.__len__() == 1):
                    term1 = df.loc[index, df.columns[col]].split('：')
                    if (term1.__len__() == 2):
                        ical.write('SUMMARY:{}\n'.format(term1[0]))
                        ical.write('LOCATION:{}\n'.format(term1[1]))
                    if (term1.__len__() == 1):
                        term2 = df.loc[index, df.columns[col]].split('\n')
                        if (term2.__len__() == 2):
                            ical.write('SUMMARY:{}\n'.format(term2[0]))
                            ical.write('LOCATION:{}\n'.format(term2[1]))
                        if (term2.__len__() == 1):
                            ical.write('SUMMARY:{}\n'.format(term2[0]))
                            ical.write('LOCATION:{}\n'.format(term2[2]))


                if (term.__len__() == 2):
                    ical.write('SUMMARY:{}\n'.format(term[1]))

                if (term.__len__() == 3):
                    ical.write('SUMMARY:{}\n'.format(term[0]))
                    ical.write('LOCATION:{} {}\n'.format(term[2], term[1]))

                if (term.__len__() == 4):

                    if 'class' in term[1].lower():
                        ical.write('SUMMARY:{}\n'.format(term[0]))
                        ical.write('LOCATION:{} {}\n'.format(term[3], term[2]))
                    else:
                        ical.write('SUMMARY:{}\n'.format(term[1]))
                        ical.write('LOCATION:{} {}\n'.format(term[3], term[2]))

                for time_index in range(times.index.size):
                    lls = times.index[time_index]
                    if lls > index:
                        timess = times.loc[times.index[time_index - 1]]
                        time = times.loc[times.index[time_index - 1]].split('-')
                        break
                    elif index >= times.index[-1]:
                        time = times.loc[times.index[-1]].split('-')
                        break

                aa = time[0]
                dtstart_list = aa.split(':')
                dtend_list = time[1].split(':')

                dtstart = datetime(day.year, day.month, day.day, int(dtstart_list[0]), int(dtstart_list[1]),
                                   tzinfo=tz_shanghai)
                dtend = datetime(day.year, day.month, day.day, int(dtend_list[0]), int(dtend_list[1]), tzinfo=tz_shanghai)

                ical.write(f'DTSTART:{dtstart.astimezone(timezone.utc).strftime("%Y%m%dT%H%M%SZ")}\n')
                ical.write(f'DTEND:{dtend.astimezone(timezone.utc).strftime("%Y%m%dT%H%M%SZ")}\n')

                ical.write('END:VEVENT\n')
except Exception as e:
    a = e
    print(a)

ical.write('END:VCALENDAR\n')
ical.close()