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"}
l = 0
for i in range(5, len(vals)):
    ws.write(i - 4, 0, "Список задач")
    dea = vals[i][1]
    start = vals[i][4]
    result = vals[i][2]
    date = vals[i][5]
    worke = vals[i][6]
    block = vals[i][0]
    #print(type(date), date, type(start))
    if worke != "" and worke[-1] == ',':
        worke = worke[:-1]
    worke = list(map(str, worke.split(", ")))
    for j in worke:
        if j not in fam and j != '':
            continue
    if str(type(date)) != "<class 'str'>":
        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
        date = str(dely[0]) + a + b
    #print(type(vals[i][4]), date, dea)
    if str(type(start)) != "<class 'str'>":
        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 l == 0:
        ws.write(i - 4, 1, dea)
    else:
        ws.write(i - 4, 1, '-' * (l + 1) + " " + dea)
    if block != '':
        if block.count('.') == 3:
            block = '-- ' + block
            l = 2
            #print(block)
            ws.write(i - 4, 1, block)
        elif block.count('.') == 2:
            block = "- " + block
            #print(block)
            l = 1
            ws.write(i - 4, 1, block)
        elif block.count('.') == 1:
            l = 0
            ws.write(i - 4, 1, block)
    ws.write(i - 4, 2, result)
    a = []
    afam = []
    for j in worke:
        if j in fam:
            a.append(fam[j])
            afam.append(j)
    b = ', '.join(a)
    bfam = ', '.join(afam)
    ws.write(i - 4, 3, b)
    ws.write(i - 4, 4, start)
    ws.write(i - 4, 5, date)
    ws.write(i - 4, 8, afam)
    ws.write(i - 4, 9, "Active")
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, "Список задач")


wb.save('Список задач.xls')