import xlrd, xlwt

rb = xlrd.open_workbook('xle.xlsx')
sheet = rb.sheet_by_index(0)

wb = xlwt.Workbook()
ws = wb.add_sheet('Летово', cell_overwrite_ok=True)

vals = [sheet.row_values(rownum) for rownum in range(sheet.nrows)]
data = dict()
fam = {"Ильин": "vladimir.iljin@letovo.ru", "Кобцева":"likobtseva@gmail.com", "Гуровиц":"vladimir.gurovic@letovo.ru", "Завриев":"organickloud@gmail.com", "Труфанов": "trufanov.p@gmail.com", "Лавренов": "kolya@lavrenov.net", "Власов": "ilyavlasov82@yandex.ru"}
for i in range(5, len(vals)):
    dea = vals[i][1]
    start = vals[i][4]
    result = vals[i][2]
    date = vals[i][5]
    worke = vals[i][6]
    if date == "":
        continue
    if vals[i][0] == "Основные блоки" or str(type(date)) == "<class 'str'>" or str(type(start)) == "<class 'str'>":
        continue
    if date != "":
        dely = xlrd.xldate_as_tuple(vals[i][5], 0)
        a = str(dely[1])
        b = str(dely[2])
        if len(b) == 1:
            b = "0" + b
        if len(a) == 1:
            a = "0" + a
        dela = str(dely[0]) + a + b
    print(type(vals[i][4]), date, dea)
    if start != "#######":
        dely = xlrd.xldate_as_tuple(vals[i][4], 0)
        a = str(dely[1])
        b = str(dely[2])
        if len(b) == 1:
            b = "0" + b
        if len(a) == 1:
            a = "0" + a
        start = str(dely[0]) + a + b
    if worke == "":
        continue;
    if worke[-1] == ',':
        worke = worke[:-1]
    worke = list(map(str, worke.split(", ")))
    for worker in worke:
        if dea != "" and date != "" and worker != "":
            if worker in data.keys():
                data[worker].append([dela, dea, result, start])
            else:
                data[worker] = []
                data[worker].append([dela, dea, result, start])

            data[worker] = sorted(data[worker])
k = 1
ws.write(0, 0, "TASKLIST")
ws.write(0, 1, "TASK")
ws.write(0, 2, "DESCRIPTION")
ws.write(0, 3, "ASSIGN TO")
ws.write(0, 4, "START DATE")
ws.write(0, 5, "DUE DATE")
ws.write(0, 6, "PRIORITY")
ws.write(0, 7, "ESTIMATED TIME")
ws.write(0, 8, "TAGS")
ws.write(0, 9, "STATUS")
ws.write(1, 0, "Список задач")
print(data.keys())
for i in data.keys():
    if i in fam:
        for j in data[i]:
            k += 1
            ws.write(k, 0, "Список задач")
            ws.write(k, 1, j[1])
            ws.write(k, 2, j[2])

            ws.write(k, 4, j[3])
            ws.write(k, 5, j[0])
            ws.write(k, 3, fam[i])
            ws.write(k, 8, i)
            ws.write(k, 9, "Active")
wb.save('Список задач.xls')