考勤数据处理

考勤数据处理脚本，用了pandas+openpyxl，Pyinstall打包成exe，貌似上传不了

import openpyxl as pl
import os
import re

global work_dir
global day_list
part_list = [] #index:部门编号 value:部门名字
day_list = [] #日期表
minc1 = 8 * 3600 + 10 * 60  # 8点10分
maxc1 = 11 * 3600 + 55 * 60
minc2 = 15 * 3600 + 10 * 60
maxc2 = 16 * 3600 + 50 * 60

align_content = pl.styles.Alignment(wrap_text=True, horizontal='center', vertical='center') #对齐样式
yahei_font = pl.styles.Font(name=u'微软雅黑', underline='single', bold=True) #字体样式
red_font = pl.styles.Font(color='FF0000', bold=True) #字体样式

#######################################外部接口#################################################
#读花名册，返回part1=['张三'， '王五']，下标表示第几行,1表示部门1
def read_roster():
    print("=================>【提示】检查目录下是否有'花名册.xlsx'")
    file_path = os.path.join(work_dir, '花名册.xlsx')
    if not os.path.isfile(file_path):
        print('错误！没有找到花名册表格！退出程序！')
        exit(1)
    workbook = pl.load_workbook(file_path)
    print("=================>step1：读取花名册")
    sht = workbook['Sheet1']
    max_row = sht.max_row
    part = ''
    part_ct = 0
    every_ct = 0
    for i in range(3, max_row+1):
        foo = sht.cell(i, 1).value
        if foo:
            part = foo
            part_list.append(part)
            part_name = 'part' + str(part_ct)
            if every_ct:
                part_len_name = 'partlen' + str(part_ct-1)
                globals()[part_len_name] = every_ct
            globals()[part_name] = list()
            part_ct = part_ct + 1
            every_ct = 0
        bar = sht.cell(i, 2).value
        if bar:
            people = bar
            globals()[part_name].append(people)
        else:
            globals()[part_name].append(' ')
        every_ct = every_ct + 1
    if every_ct:
        part_len_name = 'partlen' + str(part_ct-1)
        globals()[part_len_name] = every_ct
    return

#读进出表，获取每条记录target,变量名dkt_1_3：表示部门1的第3个人（按花名册的排序）
def read_excels():
    print('=================>【提示】把该程序放到需要处理的excel表格同一目录下')
    for file in os.listdir(work_dir):
        try:
            if 'xlsx' not in file:
                if 'xls' in file:
                    print('不支持xls格式的表格，读取文件（', file, '）失败')
                continue
            if '花名册' in file:
                continue
            file_path = os.path.join(work_dir, file)
            if not os.path.isfile(file_path):
                continue
            workbook = pl.load_workbook(file_path)
        except:
            print('读取表格：', file, '出错')
            exit(1)
        print("=================>step1：读取原表格")
        print("----------------->开始处理表格：", file)
        try:
            sht = workbook['Sheet1']
        except:
            print('错误！检查数据是否放在Sheet1！')
            exit(1)
        print("----------------->开始处理：Sheet1")
        maxrow = sht.max_row

        print('----------------->开始读取数据')
        try:
            for i in range(2, maxrow+1):
                time = str(sht.cell(i,4).value)
                all_day = time.split(' ')[0]
                month = all_day.split('-')[1]
                day = all_day.split('-')[2]
                all_hour = time.split(' ')[1]
                hour = all_hour.split(':')[0]
                month_day = month + day

                door = str(sht.cell(i, 7).value)

                cell = sht.cell(i, 10).value
                name = re.search(r'姓名：(.*?)，相似度', cell).group(1)
                part = re.search(r'人脸库名称：(.*?)，提示', cell).group(1)

                info_dict = {
                    'daytime': all_day,
                    'hourtime': all_hour,
                    'name': name,
                    'part': part,
                    'door': door
                }

                if not process_info_dict(info_dict):
                    continue
        except:
            print('错误！读取数据出错，请检查数据格式是否正确！')
            exit(1)
    return False

#处理时间排序和迟到判断
def process_data():
    target_all = []
    for name in globals().keys():
        if 'dkt' not in name:
            continue
        target = globals()[name]
        for day, value in target.items():
            max1 = 0
            min1 = 25 * 3600
            max2 = 0
            min2 = 25 * 3600
            for door, time_list in value.items():
                time_list = sorted(time_list)
                value[door] = time_list
                for time in time_list:
                    [hour, min, sec] = time.split(':')
                    timestamp = int(hour)*3600 + int(min)*60 + int(sec)
                    if 7<=int(hour)<13: #上午时间
                        if door == '入门': #找到入门的最小时间
                            if timestamp < min1:
                                min1 = timestamp
                        if door == '出门': #找到出门的最大时间
                            if timestamp > max1:
                                max1 = timestamp

                    if 13<=int(hour)<18: #下午时间
                        if door == '入门': #找到入门的最小时间
                            if timestamp < min2:
                                min2 = timestamp
                        if door == '出门': #找到出门的最大时间
                            if timestamp > max2:
                                max2 = timestamp

            [t1, t2] = judge_time(min1, max1, min2, max2) #判断迟到早退
            if t1:
                value['上午'] = t1
            if t2:
                value['下午'] = t2
    return

def on_write_excel():
    print("=================>step2：生成新表格")
    print("----------------->生成输出目录，准备写入excel文件")
    global work_dir
    folder_path = os.path.join(work_dir, '输出')
    folder_exist = os.path.exists(folder_path)
    if not folder_exist:
        os.makedirs(folder_path)

    write_name = '考勤结果.xlsx'
    write_path = os.path.join(folder_path, write_name)

    if os.path.exists(write_path):
        os.remove(write_path)

    if write_excel(write_path):
        print("----------------->成功生成表头，准备写入数据")

    if write_data(write_path):
        print("----------------->成功生成数据，完成考勤结果表")

    return

#######################################内部方法#################################################
#迟到判断
def judge_time(min1, max1, min2, max2):
    t1 = []
    t2 = []
    if (min1 > minc1) and (min1 != 25 * 3600):
        time_str = format_time(min1)
        #print('[提示]检测到上午迟到, 时间为：', time_str)
        hint_str = '迟到：' + time_str
        t1.append(hint_str)
    if (max1 < maxc1) and (max1 != 0):
        time_str = format_time(max1)
        #print('[提示]检测到上午早退, 时间为：', time_str)
        hint_str = '早退：' + time_str
        t1.append(hint_str)
    if (min2 > minc2) and (min2 != 25 * 3600):
        time_str = format_time(min2)
        #print('[提示]检测到下午迟到, 时间为：', time_str)
        hint_str = '迟到：' + time_str
        t2.append(hint_str)
    if (max2 < maxc2) and (max2 != 0):
        time_str = format_time(max2)
        #print('[提示]检测到下午早退, 时间为：', time_str)
        hint_str = '早退：' + time_str
        t2.append(hint_str)

    if (min1 == 25*3600):
        #print('[提示]上午上班时间无入门数据')
        hint_str = '上午无入门数据'
        t1.append(hint_str)
    if (max1 == 0):
        #print('[提示]上午上班时间无出门数据')
        hint_str = '上午无出门数据'
        t1.append(hint_str)
    if (min2 == 25*3600):
        #print('[提示]下午上班时间无入门数据')
        hint_str = '下午无入门数据'
        t2.append(hint_str)
    if (max2 == 0):
        #print('[提示]下午上班时间无出门数据')
        hint_str = '下午无出门数据'
        t2.append(hint_str)

    return t1, t2

def format_time(time):
    hour1 = time // 3600
    minite1 = (time % 3600) // 60
    sec1 = (time % 3600) % 60
    time_str = str(hour1) + ':' + str(minite1) + ':' + str(sec1)
    return time_str

def process_info_dict(info):
    if not info:
         return False
    [part_list, part_index] = find_part_list(info['part'])
    if not part_list:
        return None
    index = find_people_index(info['name'], part_list, part_index)
    name_dict_name = 'name_dict' + str(part_index)
    if name_dict_name not in globals():
        globals()[name_dict_name] = {}
    name_dict = globals()[name_dict_name]
    if str(index) not in name_dict:
        name_dict[str(index)] = info['name']
    target = 'dkt_' + str(part_index) + '_' + str(index)
    if target not in globals():
        globals()[target] = dict()
    build_target_dict(globals()[target], info)

#target：用来存放单条记录的字典，外层键值是日期，里层键值是出门入门时间
def build_target_dict(target, info):
    hourtime = info['hourtime']
    daytime = info['daytime']
    door = info['door']
    if daytime not in target:
        target.setdefault(daytime, {})
    if door not in target[daytime]:
        target[daytime].setdefault(door, [])
    target[daytime][door].append(hourtime)
    if daytime not in day_list:
        day_list.append(daytime)

def find_part_list(part):
    if not part:
        return [None, None]
    try:
        index = part_list.index(part)
    except:
        print("花名册中没有", part, "这个部门")
        return [None, None]
    part_list_name = 'part' + str(index)
    if part_list_name not in globals():
        return [None, None]
    return globals()[part_list_name], index

def find_people_index(name, part_list, part_index):
    if not name or not part_list:
        return None
    if name in part_list:
        index = part_list.index(name)
    else:
        part_len_name = 'partlen' + str(part_index)
        add_list_name = 'add_list' + str(part_index)
        if add_list_name not in globals():
            globals()[add_list_name] = []
        add_list = globals()[add_list_name]
        if name not in add_list:
            add_list.append(name)
        add_index = add_list.index(name)
        index = globals()[part_len_name] + add_index
    return index

#生成表和写表头
def write_excel(path):
    global day_list
    work_book = pl.Workbook()
    work_book.save(path)
    book = pl.load_workbook(path)
    day_list = sorted(day_list)
    for part in part_list:
        sheet = book.create_sheet(part)
        sheet['A1'].font = yahei_font
        sheet['A1'].value = '部门'
        sheet.merge_cells('A1:A2')
        sheet['B1'].font = yahei_font
        sheet['B1'].value = '姓名'
        sheet.merge_cells('B1:B2')
        sheet['A3'].font = yahei_font
        sheet['A3'] = part
        for index, day in enumerate(day_list):
            start = 4*index + 3
            sheet.cell(1, start).value = '日期: ' + day
            sheet.cell(1, start).font = yahei_font
            sheet.cell(1, start).alignment = align_content
            sheet.merge_cells(start_row=1, start_column=start, end_row=1, end_column=start+3)
            sheet.cell(2, start).value = '出门时间'
            sheet.cell(2, start).font = yahei_font
            sheet.cell(2, start).alignment = align_content
            sheet.column_dimensions[chr(64+start)].width = 8.82
            sheet.cell(2, start+1).value = '进门时间'
            sheet.cell(2, start+1).font = yahei_font
            sheet.cell(2, start+1).alignment = align_content
            sheet.column_dimensions[chr(64+start+1)].width = 8.82
            sheet.cell(2, start+2).value = '上午迟到/早退'
            sheet.cell(2, start+2).font = yahei_font
            sheet.cell(2, start+2).alignment = align_content
            sheet.column_dimensions[chr(64+start+2)].width = 14
            sheet.cell(2, start+3).value = '下午迟到/早退'
            sheet.cell(2, start+3).font = yahei_font
            sheet.cell(2, start+3).alignment = align_content
            sheet.column_dimensions[chr(64+start+3)].width = 14
        book.save(path)
    return True

#写表内数据
def write_data(write_path):

    book = pl.load_workbook(write_path)

    for target_name in globals():
        if 'dkt_' not in target_name:
            continue
        target = globals()[target_name]
        [foo, part_index, name_index] = target_name.split('_')
        p_i = int(part_index)
        n_i = int(name_index)
        part = part_list[p_i]
        name_dict_name = 'name_dict' + str(p_i)
        name = globals()[name_dict_name][str(n_i)]

        sheet = book[part]
        r_s = 3 + n_i #第几行
        sheet.cell(r_s, 2).value = name
        for i, day in enumerate(day_list):
            if day not in target.keys():
                continue
            c_s = 3 + 4*i #第几列
            if '出门' in target[day].keys():
                sheet.cell(r_s, c_s).alignment = align_content
                sheet.cell(r_s, c_s).value = ''.join(target[day]['出门'])
            if '入门' in target[day].keys():
                sheet.cell(r_s, c_s+1).alignment = align_content
                sheet.cell(r_s, c_s+1).value = ''.join(target[day]['入门'])
            if '上午' in target[day].keys():
                sheet.cell(r_s, c_s+2).alignment = align_content
                sheet.cell(r_s, c_s+2).font = red_font
                sheet.cell(r_s, c_s+2).value = ';'.join(target[day]['上午'])
            if '下午' in target[day].keys():
                sheet.cell(r_s, c_s+3).alignment = align_content
                sheet.cell(r_s, c_s+3).font = red_font
                sheet.cell(r_s, c_s+3).value = ';'.join(target[day]['下午'])

        book.save(write_path)

    write_parti_people(write_path)

    return True

#补足花名册中有而考勤表中没有的人名
def write_parti_people(path):
    book = pl.load_workbook(path)
    for i, part in enumerate(part_list):
        sheet = book[part]
        part_len_name = 'partlen' + str(i)
        part_len = globals()[part_len_name]
        part_name = 'part' + str(i)
        part_people = globals()[part_name]
        for j in range(part_len):
            cells = sheet.cell(j+3, 2).value
            if not cells:
                name = part_people[j]
                sheet.cell(j + 3, 2).value = name
    book.save(path)
    return

if __name__ == "__main__":
    work_dir = os.getcwd()
    read_roster()
    read_excels()
    process_data()
    on_write_excel()