2.7 统计加班时间

from openpyxl import Workbook, load_workbook
from datetime import date

def create_data():
    wb = Workbook()
    sh = wb.active
    rows = [
        ['Date', '姓名', '打卡时间'],
        [date(2020, 12, 1), '吕小布', '18:50'],
        [date(2020, 12, 2), '貂的蝉', '18:10'],
        [date(2020, 12, 3), '刘备', '18:02'],
        [date(2020, 12, 4), '吕小布', '18:50'],
        [date(2020, 12, 5), '张飞', '19:22'],
        [date(2020, 12, 6), '吕小布', '18:50'],
    ]
    for row in rows:
        sh.append(row)
    wb.save('./办公自动化/files/打卡时间.xlsx')

# 统计学statistics
def statistics():
    # 读取数据
    wb = load_workbook('./办公自动化/files/打卡时间.xlsx')
    sh = wb.active
    n_sh = wb.create_sheet('统计加班')
    n_sh.append(['Date', '姓名', '打卡时间', '加班时长'])
    data = []
    for i in range(2, sh.max_row+1):
        t_data = []
        for j in range(1, sh.max_column+1):
            t_data.append(sh.cell(i, j).value)
        # 统计，可以按分钟统计，按：进行分隔为2部分，18：22
        h, m = t_data[2].split(":")
        full = int(h)*60 + int(m)
        tmp = full - 18*60
        t_data.append(tmp)
        t_data[0] = t_data[0].date()
        data.append(t_data)

    for d in data:
        n_sh.append(d)

    wb.save('./办公自动化/files/打卡时间.xlsx')

if __name__ == "__main__":
    create_data()
    statistics()