公司考勤分析

公司考勤分析

from datetime import datetime
py import copy
import numpy as np
import xlrd as rd
import xlwt as xlwt
from copy import deepcopy as dcp
import tkinter as tk
from tkinter import ttk
py import copyex_path='C:/test/工作时间.xls'df=rd.open_workbook(ex_path)
tb=df.sheets()[0]global dtname
dtname = []#write_merge(x, x + m, y, y + n, string, sytle)x表示行，y表示列，m表示跨行个数，n表示跨列个数，string表示要写入的单元格内容，style表示单元格样式。其中，x，y，w，h，都是以0开始计算的。ex_path='C:/test/工作时间.xls'
df=rd.open_workbook(ex_path,formatting_info=True)
tb=df.sheets()[0]
path = 'C:/test/工作时间.xls'
book = rd.open_workbook(path,formatting_info=True)
#  book = xlrd.open_workbook(path, formatting_info=True)
#  设置 formatting_info=True ，当打开表格是保存表格原有的样式，进行保存时，
#  原来的样式不会丢失
sheet = book.sheets()[0]
wb = copy(book)
ws = wb.get_sheet(0)
def setstyle(ws,x,y):pattern = xlwt.Pattern()pattern.pattern = xlwt.Pattern.SOLID_PATTERNpattern.pattern_fore_colour = 5style = xlwt.XFStyle()style.pattern = patternborders = xlwt.Borders()borders.left = 1borders.right = p = 1borders.bottom = 1style.borders =bordersfnt=xlwt.Font()fnt.bold = Falsefnt.height = 20*8style.font=fntpattern = xlwt.Pattern()  # 创建一个模式pattern.pattern = xlwt.Pattern.SOLID_PATTERN  # 设置其模式为实型pattern.pattern_fore_colour = 3# 设置单元格背景颜色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, the list alignment = xlwt.Alignment()alignment.horz = 0x02# 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)alignment.vert = 0x01# 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)alignment.wrap = 1style.alignment=alignmentstyle.pattern = pattern  # 将赋值好的模式参数导入Stylet = tb.cell_value(x,y)ws.write_merge(x,x,y,y,t,style)wb.save('C:/test/工作时间.xls')def math_dtname():for r in range(ws,2):val=[]de=[]dname=[]for c in ls):val.ll_value(r,c))de.append(val)dname.append(eval(de[0][2]))dname.append(de[0][10])dtname.append(dname)
math_dtname()
dtkong=[]
dtqing = []
dtzao=[]
dtpjia=[]
dtque=[]
dtzjia=[]
global gettime,getName
getName=''
gettime=''window = tk.Tk()
window.title('中班设置')
ry('700x500')
tk.Label(text='中班人员n日期选择').place(x=140,y=40)def Combobox_show(dtname,source,x,y,z):cv= tk.StringVar()tk.Label(text=source).place(x=x,y=y)com=ttk.Combobox(textvariable=cv,state='readonly')com.place(x=x,y=(y+20),width=z)#com['state'] = 'readonly'com["value"]=dtnamecom.current(0)def func(event):if len(com['value'])>32:global getNamegetName&#()else:global gettimegettime = cv.get()com.bind("<<ComboboxSelected>>",func) #等同于textvariable=cv这个变量Combobox_show(dtname,'员工工号姓名',200,30,80)
Combobox_show([i for i in range(ls+1)][:],'日期',300,30,40)def fun_inser():tbrow=dtname.index(eval('['+getName[0:2].replace(' ', '') + ',' + "'" + getName[-3:].replace(' ', '') + "'"+']'))tbcol=int(gettime[0:2])setstyle(ws, (tbrow+1)*2+3,tbcol-1)
tk.Button(text='确定',width=15,heigh=1,command=fun_inser).place(x=350,y=45)def checkColor(x,y):#check单元格背景是不是绿色#print(row+1, col+1)# thecell = ll(row, col)# could get 'dump', 'value', 'xf_index'#print(thecell.value)xfx = tb.cell_xf_index(x,y)xf = df.xf_list[xfx]bgx = xf.background.pattern_colour_indexif bgx==3:return Truereturn Falsefor r in range(ws,2):val = []for c in ls):dtime&#ll_value(2,2).replace('-','')[0:6]dtime+=str(c+1).zfill(2)dkong = []dqing = []dzao = []dpjia = []dque = []if datetime.strptime(dtime,"%Y%m%d").weekday()-5<0:#如果是工作日if checkColor(r,c):ll_value(r, c)=='' and r<(len(dtname)+2)*2:dkong.append(dtname[(r - 5) // 2])dkong.append(c+1)dtkong.append(dkong)dtime=ll_value(r,c)).replace(':', '')if r<(len(dtname)+2)*2 and c&ls-1:if ll_value(r,c+1))!=0:if ll_value(r,c+1).replace(':', '')[0:4]) < 400:dtime+=str(ll_value(r,c+1).replace(':', '')[0:4])+2400)#如果第二天第一次刷卡时间在四点之前，那就算在前一天之前if len(dtime)<5 and r<(len(dtname)+2)*2:quetime=''dque.append(dtname[(r - 5) // 2])dque.append(c+1)if len(dtime)==0:quetime=0else:quetime=dtime[-4:]dque.append(quetime)dtque.append(dque)if len(dtime)>4and  r<(len(dtname)+2)*2:  #两次刷卡有可能是上班刷了两次卡，也有可能上班一次，下班一次t=int(dtime[0:4])if t<400:for i in range(len(dtime)//4):#有可能前一天加班到第二天凌晨4点，然后回去休息，然后当天十点钟再来打卡,所以要取四点以后的，到九点钟之前的有无打卡记录if int(dtime[i*4:(i+1)*4])>1400:if int(dtime[(i-1)*4:i*4])<400:dqing.append(dtname[(r - 5)//2])dqing.append(c+1)dqing.append(dtime[i*4:(i+1)*4])dtqing.append(dqing)elif  1800>t>1400 :#如果第一次刷卡在九点以后，有可能就是迟到,(有可能前一天加班到第二天凌晨4点，然后回去休息，这个情况已经在前面算过了，是算到前面一天的。）然后当天十点钟再来打卡dqing.append(dtname[(r - 5)//2])#这里需要判断是否为中班dqing.append(c+1)dqing.append(t)dtqing.append(dqing)Get_off = int(dtime[-4:])if int(dtime[-4:])<2200 :#早退的情况，正常九点前上班，但是5:30以前下班#虽然五点半打卡了，但是第二天上午三点还是打卡了，这个情况之前有Get_off =int(dtime[-4:])dzao.append(dtname[(r - 5) // 2])dzao.append(c+1)dzao.append(Get_off)dtzao.append(dzao)if Get_off>2200:#平常日加班dpjia.append(dtname[(r - 5) // 2])dpjia.append(c+1)dpjia.append(Get_off)dtpjia.append(dpjia)else:ll_value(r, c)=='' and r<(len(dtname)+2)*2:dkong.append(dtname[(r - 5) // 2])dkong.append(c+1)dtkong.append(dkong)dtime=ll_value(r,c)).replace(':', '')if r<(len(dtname)+2)*2 and c&ls-1:if ll_value(r,c+1))!=0:if ll_value(r,c+1).replace(':', '')[0:4]) < 400:dtime+=str(ll_value(r,c+1).replace(':', '')[0:4])+2400)#如果第二天第一次刷卡时间在四点之前，那就算在前一天之前if len(dtime)<5 and r<(len(dtname)+2)*2:quetime=''dque.append(dtname[(r - 5) // 2])dque.append(c+1)if len(dtime)==0:quetime=0else:quetime=dtime[-4:]dque.append(quetime)dtque.append(dque)if len(dtime)>4and  r<(len(dtname)+2)*2:  #两次刷卡有可能是上班刷了两次卡，也有可能上班一次，下班一次t=int(dtime[0:4])if t<400:for i in range(len(dtime)//4):#有可能前一天加班到第二天凌晨4点，然后回去休息，然后当天十点钟再来打卡,所以要取四点以后的，到九点钟之前的有无打卡记录if int(dtime[i*4:(i+1)*4])>900:if int(dtime[(i-1)*4:i*4])<400:dqing.append(dtname[(r - 5)//2])dqing.append(c+1)dqing.append(dtime[i*4:(i+1)*4])dtqing.append(dqing)elif  1300>t>900 :#如果第一次刷卡在九点以后，有可能就是迟到,(有可能前一天加班到第二天凌晨4点，然后回去休息，这个情况已经在前面算过了，是算到前面一天的。）然后当天十点钟再来打卡dqing.append(dtname[(r - 5)//2])#这里需要判断是否为中班dqing.append(c+1)dqing.append(t)dtqing.append(dqing)Get_off = int(dtime[-4:])if int(dtime[-4:])<1730 :#早退的情况，正常九点前上班，但是5:30以前下班#虽然五点半打卡了，但是第二天上午三点还是打卡了，这个情况之前有Get_off =int(dtime[-4:])dzao.append(dtname[(r - 5) // 2])dzao.append(c+1)dzao.append(Get_off)dtzao.append(dzao)if Get_off>1730:#平常日加班dpjia.append(dtname[(r - 5) // 2])dpjia.append(c+1)dpjia.append(Get_off)dtpjia.append(dpjia)else:#非工作日dqing = []dzao = []dpjia = []dzjia=[]dtime = ll_value(r, c)).replace(':', '')if r < (len(dtname)+2)*2 and c < tb.ncols - 1:if ll_value(r, c + 1)) != 0:#如果第二天有打卡记录if ll_value(r, c + 1).replace(':', '')[0:4]) < 400:#且打卡记录在四点之前dtime += str(ll_value(r, c + 1).replace(':', '')[0:4]) + 2400)  #if r<(len(dtname)+2)*2and c&ls-1 and len(dtime)!=0:if int(dtime[0:4])<400:for i in range(len(dtime) // 4-1):  #截取四点以后上班迟到的，这个要算下班时间到上班时间，剩下的要算下班时间减去上班时间if int(dtime[i * 4:(i + 1) * 4]) > 900 and i>=1:if int(dtime[(i - 1) * 4:i * 4]) < 400:dtime = dtime[i * 4:]if len(dtime) < 5 and r < (len(dtname)+2)*2 and len(dtime)>1:dque = []quetime=''dque.append(dtname[(r - 5) // 2])dque.append(c+1)if len(dtime) == 0:quetime = 0else:quetime = dtime[-4:]dque.append(quetime)dtque.append(dque)elif len(dtime)>4 and r<(len(dtname)+2)*2:if int(dtime[-4:])-int(dtime[0:4])<400:dque=[]quetime = ''dque.append(dtname[(r - 5) // 2])dque.append(c+1)if len(dtime) == 0:quetime = 0else:quetime = dtime[-4:]dque.append(quetime)dtque.append(dque)else:if int(dtime[-2:])>=30:t = int(dtime[-4:-2]) * 60+30else:t = int(dtime[-4:-2]) * 60if int(dtime[0:4])>900:if int(dtime[2:4])>30:dtime=dtime[0:2]+'00'+dtime[4:]if dtime[1:2]!=9:dtime=dtime[0:1]+str(int(dtime[1:2])+1)+dtime[2:]else:dtime=str(int(dtime[0:1])+1)+'0'+dtime[2:]elif int(dtime[2:4])<=30 :dtime=dtime[0:2]+'30'+dtime[4:]else:dtime='0900'+dtime[4:]if 1170>t>780:t-=60elif t>1170:t-=120dzjiatime=(t-(int(dtime[0:2])*60+int(dtime[2:4])))/60dzjia.append(dtname[(r - 5) // 2])dzjia.append(c+1)dzjia.append(dzjiatime)dtzjia.append(dzjia)
def dtzongjie(a):dnameqing=dcp(dtname)for i in range(len(a)):for c in range(len(dtname)):if dtname[c]==a[i][0]:#t=str(a[i][1])+','+str(a[i][2])t=[]t.append(a[i][1])t.append(a[i][2])unt(dtname[c]) == 1 and len(dnameqing[dnameqing.index(dtname[c])]) == 2:dnameqing[dnameqing.index(dtname[c])].append([t])#dnameqing[dtname.index(dtname[c])][2]+=[[t]]unt(dtname[c])==0:dnameqing[dtname.index(dtname[c])][2]+=[t]return dnameqingdtque=dtzongjie(dtque)
dtpjia=dtzongjie(dtpjia)
dtqing=dtzongjie(dtqing)
dtzao=dtzongjie(dtzao)
dtzjia=dtzongjie(dtzjia)
#write_excel(dtque)
dztname=dcp(dtname)
def fun_checkall(dtque):for i in range(len(dtque)):t=[]if len(dtque[i])>2:for m in range(len(dtque[i][2])):t.append(dtque[i][2][m][0])dztname[i].append(t)else:dztname[i].append([0])def fun_checkallque(dtque):for i in range(len(dtque)):t=[]if len(dtque[i])>2:for m in range(len(dtque[i][2])):if len(str(dtque[i][2][m][1]))>1:t.append(str(dtque[i][2][m][0])+'/'+ str(int(dtque[i][2][m][1][0:2]))+':'+str(dtque[i][2][m][1][2:4]))else: t.append(str(dtque[i][2][m][0])+'/'+str(dtque[i][2][m][1]))dztname[i].append(t)else:dztname[i].append([0])
#fun_checkall(dtque)
fun_checkallque(dtque)fun_checkall(dtqing)fun_checkall(dtzao)
fun_checkall(dtpjia)
fun_checkall(dtzjia)
#工号，姓名，忘记打卡（平时加周末），平时早上迟到，平时早退的，平时加班，周末加班
def write_excel(d):f = xlwt.Workbook()sheet1 = f.add_sheet('考勤总结',cell_overwrite_ok=True)row0 = ["工号",'姓名','忘记打卡日期','迟到日期','早退日期','正常加班日期','假日加班日期']dtime = tb.cell_value(2, 2).replace('-', '')# 写第一行sheet1.write(0, 0, dtime)#写第二行"工号",'姓名','忘记打卡日期','迟到日期','早退日期','正常加班日期','假日加班日期'for i in range(0,len(row0)):sheet1.write(1,i,row0[i])#从第三行开始写从数据库里面捞出来的数据for i in range(len(dztname)):for m in range(0, len(dztname[i])):c = ''if type(dztname[i][m])==list:c = ','.join([str(i) for i in dztname[i][m]])else:c = dztname[i][m]sheet1.write(i + 2, m, c)f.save('c:/test/'+dtime+'考勤.xls',)
#write_excel(dztname)
tk.Button(text='加班情况统计',width=15,heigh=1,command=write_excel).place(x=350,y=85)
window.mainloop()