python 处理得力考勤表

原表格

处理后

这里只处理一张同文件夹内的表格，没考虑迟到等，请自行根据需求更改

import openpyxl
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Alignment
from openpyxl.styles import PatternFill
from openpyxl.utils import get_column_letter, column_index_from_string
import os


# 遍历文件夹
def ergodic(imagePath, type):
    file = []
    for fi in os.listdir(imagePath):
        if fi.endswith(type):
            file.append(fi)
    return file  # 返回文件列表

# 读取奇数行,列，返回列表，列最大值，列名称
def row_odd(sheet):
    maxrow = sheet.max_row  # 最大行
    maxcol = sheet.max_column  # 最大列
    list_all = []
    list_odd = []
    for i in range(1, maxrow + 1):
        if i % 2 != 0:
            list_odd.append(i)
    list_all.append(list_odd)
    list_all.append(maxcol)
    return list_all


# 处理旧Excel文件
def new_excel(workbook):
    wb = load_workbook(workbook)  # 打开文件
    ws = wb.active  # 活动表格
    maxrow = ws.max_row  # 最大行
    wb_new = Workbook()
    ws_new = wb_new.active
    ws_new.title = 'new_sheet'

    list_fist_line = []  # 创建表格，写入ws用
    for i in ws[1]:  # 读取第一行
        list_fist_line.append(i.value)

    ws_new.append(list_fist_line)  # 写入第一行

    # 表格添加班数
    for j in range(3, maxrow + 1):  # 第3行开始，循环行
        print('正在处理第', j, '行')
        a, b = ws_change(j, ws)
        ws_new.append(a)
        ws_new.append(b)

    # 修改单元格颜色
    change_colour(ws_new)

    # 打卡天数类别为数字,调整行高
    date_num(ws_new)

    #统计班数
    count_days(ws_new)


    #统计加班
    count_up_days(ws_new)

    new_name=workbook.strip('.xlsx')+'_new'+'.xlsx'
    wb_new.save(new_name)


# 添加打卡列表
def ws_change(row, sheet):
    old_row = []
    for i in sheet[row]:
        old_row.append(i.value)  # 旧表格行
    new_list = row_change(old_row)  # 新表格行
    return old_row, new_list  # 返回旧列表和新列表


# 制作打卡列表
def row_change(row):
    new_row_name = []  # 名称行
    for i in row[:3]:
        new_row_name.append(i)
    # print(new_row_name)
    time_row = []  # 打卡日期
    for j in row[3:]:
        time_row.append(j)
        a = day_change(time_row)  # 每行日期改为班数
    merged_list = new_row_name + a  # 合并名字和班数
    return merged_list


# 将日期改为班数
def day_change(data):
    operations = {
        '12': lambda: "0.5",
        '1234': lambda: "1",
        '123456': lambda: "1.5",
        '1234567': lambda: "2",
        '123457': lambda: "2",
        '34': lambda: "0.5",
        '3456': lambda: "1",
        '34567': lambda: "1.5",
        '3457': lambda: "1.5",
        '14': lambda: "1",
        '16': lambda: "1.5",
        '17': lambda: "2",
    }

    list_date_to2 = []  # 0900的列表
    banshu_list = []
    for i in data:
        i = str(i)
        j = i.split('\n')
        k = one_date_togeer(j)  # 将日期改为0900
        list_date_to2.append(k)

    for a in list_date_to2:
        b = num_change(a)
        c = ''.join(map(str, b))
        result = operations.get(c, lambda: " ")()
        banshu_list.append(result)
    return banshu_list


# 时间改为0900
def one_date_togeer(time):
    list_time_2 = []
    for i in time:
        if i == '-':
            list_time_2.append(i)
        else:
            j = i.split(':')
            k = ''.join(j)
            list_time_2.append(k)
    return list_time_2


# 时间改为班数
def num_change(time):#这里没考虑迟到等，请自行根据需求更改
    list_one_day = []
    list_of_num = []
    for i in time:
        if i == '-':
            return '0'
        else:
            if '0500' < i < '0900':
                list_one_day.append('1')
            else:
                if "0901" < i < "1145":
                    list_one_day.append('2')
                else:
                    if "1145" < i < '1400':
                        list_one_day.append('3')
                    else:
                        if '1401' < i < '1710':
                            list_one_day.append('4')
                        else:
                            if '1711' < i < '2000':
                                list_one_day.append('5')
                            else:
                                if "2001" < i < "2150":
                                    list_one_day.append('6')
                                else:
                                    if "2151" < i < "23559":
                                        list_one_day.append("7")
    for j in list_one_day:
        if j not in list_of_num:
            list_of_num.append(j)
    return list_of_num


# 修改单元格颜色
def change_colour(sheet):
    cell_rgb1 = PatternFill("solid", fgColor="FFFF00")  # 1.5班打卡颜色
    cell_rgb2 = PatternFill("solid", fgColor="FFC000")  # 2班打卡颜色
    maxrow = sheet.max_row  # 最大行
    maxcol = sheet.max_column  # 最大列
    for a in range(1, maxrow + 1):
        for b in range(1, maxcol + 1):
            c = sheet.cell(a, b).value
            d = sheet.cell(a, b)
            if c == '1.5':
                d.fill = cell_rgb1
            else:
                if c == '2':
                    d.fill = cell_rgb2
    for key in list(sheet._cells.keys()): sheet._cells[key].alignment = Alignment(wrapText=True)  # 自动调整行高度
    return sheet


# 打卡天数类别为数字,调整行高
def date_num(sheet):
    list_odd,num_col = row_odd(sheet)
    list_odd.pop(0)
    for a in list_odd:
        sheet.row_dimensions[a].height = 40
        for b in range(4, num_col + 1):
            sheet.cell(a, b).data_type = "int"

    return sheet

#统计班数
def count_days(sheet):
    list_odd,maxcol = row_odd(sheet)
    a = get_column_letter(int(maxcol-1))
    b = get_column_letter(int(maxcol))
    n = 'D'
    lis_col=[]
    for i in list_odd:
        lis_col.append(b+str(i))
    sheet[lis_col[0]] = '班数'
    lis_col.pop(0)
    for i in lis_col:  # ai3
        j = n + i.strip(b)  # d3
        k = i.strip(b)  # 3
        l = '=sum(' + j + ":" + a + str(k) + ')'
        sheet[i] = l
    return sheet


#统计加班
def count_up_days(sheet):
    list_odd,maxcol = row_odd(sheet)
    a = get_column_letter(int(maxcol-1))
    b = get_column_letter(int(maxcol+1))
    n = 'D'
    lis_col=[]
    for i in list_odd:
        lis_col.append(b+str(i))
    sheet[lis_col[0]] = '加班次数'
    lis_col.pop(0)
    for i in lis_col:  # for aj
        j = n + i.strip(b)  # d3
        k = i.strip(b)  # 3
        l = '=COUNTIF('+n+str(k)+':'+a+str(k)+','+'\">1\")'
        sheet[i] = l
    return sheet




if __name__ == "__main__":
    filePath = os.getcwd()
    os.chdir(filePath)  # 修改文件路径

    type = '.xlsx'  # 文件类型,如.jpg .pdf .png
    file_list = ergodic(filePath, type)  # 遍历文件夹def
    file_project = ''.join(file_list)  # 单一文件
    new_excel(file_project)