z2

import xlsxwriter
from openpyxl import load_workbook
import random
from datetime import datetime
from datetime import timedelta


workbook = xlsxwriter.Workbook('z2.xlsx')
worksheet = workbook.add_worksheet('z2')
# заголовки
dates = (
    ['Номер по порядку', 'Дата', 'Время начала разговора', 'Время конца разговора', 'Время разговора', 'День недели',
     'Час суток', 'Стоимость разговора', 'Тариф'],)
for i, (item, d, ts, te, tr, day, hd, ps, tarif) in enumerate(dates, start=1):
    worksheet.write(f'A{i}', item)
    worksheet.write(f'B{i}', d)
    worksheet.write(f'C{i}', ts)
    worksheet.write(f'D{i}', te)
    worksheet.write(f'E{i}', tr)
    worksheet.write(f'F{i}', day)
    worksheet.write(f'G{i}', hd)
    worksheet.write(f'H{i}', ps)
    worksheet.write(f'I{i}', tarif)
workbook.close()
# данные
wb = load_workbook('z2.xlsx')
worksheet = wb['z2']
ws = wb.active
for i in range(2, 22):
    item = i - 1
    ws[f'A{i}'] = item
    x = datetime(2022, 1, i-1).strftime("%d.%m.%Y")
    ws[f'B{i}'] = x
    starttime = timedelta(hours=i, minutes=30, seconds=0)
    ws[f'C{i}'] = starttime
    randmin = random.randint(0, 15)
    randsec = random.randint(0, 15)
    stopttime = timedelta(hours=i+0.5, minutes=i+randmin, seconds=randsec)
    ws[f'D{i}'] = stopttime
    wb.save('z2.xlsx')
for i in range(2, 22):
    starttime = ws[f'C{i}'].value
    stopttime = ws[f'D{i}'].value
    allsays = stopttime - starttime
    ws[f'E{i}'] = allsays
    minutisays = allsays/60
    minutisays = str(minutisays).replace('0:00:', '')
    dayweeks = ws[f'B{i}'].value
    dayweeks = datetime.strptime(dayweeks, "%d.%m.%Y").date()
    dayweeks = dayweeks.strftime("%A")
    ws[f'F{i}'] = dayweeks
    hours = ws[f'C{i}'].value
    hours = datetime.strptime(str(hours), "%H:%M:%S")
    hours = hours.strftime("%H")
    ws[f'G{i}'] = hours
    if dayweeks == 'Saturday' or dayweeks == 'Sunday':
        vihtarif = float(minutisays)*0.1
        ws[f'H{i}'] = vihtarif
        ws[f'I{i}'] = "выходной тариф"
    else:
        if int(hours) >= 8 and int(hours) <= 21:
            vihtarif = float(minutisays) * 0.5
            ws[f'H{i}'] = vihtarif
            ws[f'I{i}'] = "Будни"
        else:
            vihtarif = float(minutisays)* 0.1
            ws[f'H{i}'] = vihtarif
            ws[f'I{i}'] = "Ночной тариф"
    wb.save('z2.xlsx')
    wb.close()