python小代码

为朋友写的简单统计考勤的小代码,分为python3.5.2版本和python2.7.11版本:

2.7.11版本代码如下(需要引用的库自己去引用):

#encoding=utf-8
import xlrd
import xlwt
from xlrd import xldate_as_tuple
import datetime
import sys

def getFirstNum(time):
	timeArray = time.split(':');
	return int(timeArray[0])*60 + int(timeArray[1]);

def getRowLine(file,index):
	row_content = []
	for j in range(file.ncols):
		ctype = file.cell(index, j).ctype  # 表格的数据类型
		cell = file.cell_value(index, j)
		if ctype == 2 and cell % 1 == 0:  # 如果是整形
			cell = int(cell)
		elif ctype == 3: # 转成datetime对象
			y,mm,d,h,m,s = xldate_as_tuple(cell, 0)
			cell = str(h)+':'+str(m)+':'+str(s);
		elif ctype == 4:
			cell = True if cell == 1 else False
		row_content.append(cell)
	return row_content;
#补全考勤缺勤部分
def queQinFill(copyItem,startDate,endDate,index):
	qDiffDays = (endDate - startDate).days;
	for i in range(0,qDiffDays):
		dayWeek = startDate.weekday();
		if(dayWeek != 5 and dayWeek != 6 and (startDate.strftime('%Y-%m-%d') not in noQueQin)):
			for j in range(0,4):
				writeSheet.write(index,j,str(copyItem[j]));
			writeSheet.write(index,j,startDate.strftime('%Y-%m-%d'));
			j+=1;
			writeSheet.write(index,j,u'缺勤');
			index+=1;
		startDate = startDate + datetime.timedelta(1);
	return index;


#打开读文件
readWorkBook = xlrd.open_workbook('kaoqin.xls');
readSheet = readWorkBook.sheet_by_index(0) #sheet索引从0开始
#打开写文件
writeWorkBook = xlwt.Workbook(encoding='utf-8');
writeSheet = writeWorkBook.add_sheet('kaoqin',cell_overwrite_ok=True) 
#获取要读取文件的行数
rows = readSheet.nrows;
#考勤日期列表
#当前操作人员
cUserName = getRowLine(readSheet,1)[1];
#读取考勤起始和结束月份,以及差值天数
beginDay = datetime.datetime.strptime('2017-09-26','%Y-%m-%d');
endDay = datetime.datetime.strptime('2017-10-26','%Y-%m-%d');
diffDay = (endDay - beginDay).days;
#出勤天数
cqDay = 0;
#餐补天数
cbDay = 0;
#当前函数
cRows = 0;
#上一个日期
lastDay = '';
lastTable = '';
#抛去不算加班的日期列表
noJiaBan = ['2017-10-14'];
#抛去异常考勤的日期列表
noQueQin = ['2017-10-02','2017-10-03','2017-10-04','2017-10-05','2017-10-06'];

#重置系统的默认编码
reload(sys)                      # reload 才能调用 setdefaultencoding 方法  
sys.setdefaultencoding('utf-8')

for i in range(1, rows): #根据行数建立循环
	#取出每一行的数据
	rowsTable = getRowLine(readSheet,i);
	#判断取出的人员是否和前一个一致
	userName = rowsTable[1]; #获取用户名称
	if cUserName != userName:
		#判断结束位置缺勤日期
		if(lastDay != '' and  (endDay - lastDay).days > 1):
			cRows = queQinFill(lastTable,(lastDay + datetime.timedelta(1)),endDay,cRows);
		writeSheet.write(cRows,0,str(cUserName));
		writeSheet.write(cRows,1,'出勤'+str(cqDay)+'天');
		writeSheet.write(cRows,2,'餐补'+str(cbDay)+'天');
		cqDay = 1;
		cbDay = 1;
		cUserName = userName;
		cRows+=1;
		lastDay = '';
		lastTable = '';
	else:
		#判断此日期是否在考勤指定日期内
		currentDay = datetime.datetime.strptime(rowsTable[3],'%Y-%m-%d');
		if(beginDay <= currentDay and  currentDay <= endDay):
			#判断起始位置的考勤缺
			if(lastDay == '' and currentDay > beginDay):
				cRows = queQinFill(rowsTable,beginDay,currentDay,cRows);
			#判断是否当前和上一个差值是一天
			if(lastDay != '' and  (currentDay - lastDay).days > 1):
				cRows = queQinFill(rowsTable,(lastDay + datetime.timedelta(1)),currentDay,cRows);
			#更新last标识
			lastDay = currentDay;
			lastTable = rowsTable;
			#加班统计
			if((currentDay.weekday() == 5 or currentDay.weekday() == 6) and (rowsTable[3] not in noJiaBan)):
				for i in range(0,len(rowsTable)):
					writeSheet.write(cRows,i,str(rowsTable[i]));
				writeSheet.write(cRows,i,'加班');
				cRows+=1;
				continue;
			#判断餐补天数和异常考勤
			cqDay+=1;
			kqDate = rowsTable[4:];
			kqDate = [x for x in kqDate if x != ''];
			if(len(kqDate) < 2):   #若是打开时间段小于2则说明是异常考勤存在漏打卡
				for i in range(0,len(rowsTable)):
					writeSheet.write(cRows,i,str(rowsTable[i]));
				writeSheet.write(cRows,i,'漏打卡');
				cRows+=1;
			else:  #判断时间段的第一个值和最后一个值,若是在7-19:30之间则属于正常考勤
				if(getFirstNum(kqDate[0]) > 7*60 and getFirstNum(kqDate[0]) < 10*60 and getFirstNum(kqDate[-1]) > 19.5*60):
					if(getFirstNum(kqDate[-1]) > 20*60): #大于晚上八点有一个餐补
						cbDay+=1;
				else:
					for i in range(0,len(rowsTable)):
						writeSheet.write(cRows,i,str(rowsTable[i]));
					writeSheet.write(cRows,i,'打卡异常');
					cRows+=1;
#最后一个人的数据存储
if(lastDay != '' and  (endDay - lastDay).days > 1):
	cRows = queQinFill(lastTable,(lastDay + datetime.timedelta(1)),endDay,cRows);
writeSheet.write(cRows,0,str(cUserName));
writeSheet.write(cRows,1,'出勤'+str(cqDay)+'天');
writeSheet.write(cRows,2,'餐补'+str(cbDay)+'天');
writeWorkBook.save('result.xls')

python3.5.2版本(需要引用的库自己引用,编码处理这块比2.7还是要方便很多啊)

import xlrd
import xlwt
from xlrd import xldate_as_tuple
import datetime

def getFirstNum(time):
	timeArray = time.split(':');
	return int(timeArray[0])*60 + int(timeArray[1]);

def getRowLine(file,index):
	row_content = []
	for j in range(file.ncols):
		ctype = file.cell(index, j).ctype  # 表格的数据类型
		cell = file.cell_value(index, j)
		if ctype == 2 and cell % 1 == 0:  # 如果是整形
			cell = int(cell)
		elif ctype == 3: # 转成datetime对象
			*year,h,m,s = xldate_as_tuple(cell, 0)
			cell = str(h)+':'+str(m)+':'+str(s);
		elif ctype == 4:
			cell = True if cell == 1 else False
		row_content.append(cell)
	return row_content;
#补全考勤缺勤部分
def queQinFill(copyItem,startDate,endDate,index):
	qDiffDays = (endDate - startDate).days;
	for i in range(0,qDiffDays):
		dayWeek = startDate.weekday();
		if(dayWeek != 5 and dayWeek != 6 and (startDate.strftime('%Y-%m-%d') not in noQueQin)):
			for j in range(0,4):
				writeSheet.write(index,j,str(copyItem[j]));
			writeSheet.write(index,j,startDate.strftime('%Y-%m-%d'));
			j+=1;
			writeSheet.write(index,j,'缺勤');
			index+=1;
		startDate = startDate + datetime.timedelta(1);
	return index;


#打开读文件
readWorkBook = xlrd.open_workbook('kaoqin.xls');
readSheet = readWorkBook.sheet_by_index(0) #sheet索引从0开始
#打开写文件
writeWorkBook = xlwt.Workbook();
writeSheet = writeWorkBook.add_sheet('kaoqin',cell_overwrite_ok=True) 
#获取要读取文件的行数
rows = readSheet.nrows;
#考勤日期列表
#当前操作人员
cUserName = getRowLine(readSheet,1)[1];
#读取考勤起始和结束月份,以及差值天数
beginDay = datetime.datetime.strptime('2017-09-26','%Y-%m-%d');
endDay = datetime.datetime.strptime('2017-10-26','%Y-%m-%d');
diffDay = (endDay - beginDay).days;
#出勤天数
cqDay = 0;
#餐补天数
cbDay = 0;
#当前函数
cRows = 0;
#上一个日期
lastDay = '';
lastTable = '';
#抛去不算加班的日期列表
noJiaBan = ['2017-10-14'];
#抛去异常考勤的日期列表
noQueQin = ['2017-10-02','2017-10-03','2017-10-04','2017-10-05','2017-10-06'];
for i in range(1, rows): #根据行数建立循环
	#取出每一行的数据
	rowsTable = getRowLine(readSheet,i);
	#判断取出的人员是否和前一个一致
	userName = rowsTable[1]; #获取用户名称
	if cUserName != userName:
		#判断结束位置缺勤日期
		if(lastDay != '' and  (endDay - lastDay).days > 1):
			cRows = queQinFill(lastTable,(lastDay + datetime.timedelta(1)),endDay,cRows);
		writeSheet.write(cRows,0,str(cUserName));
		writeSheet.write(cRows,1,'出勤'+str(cqDay)+'天');
		writeSheet.write(cRows,2,'餐补'+str(cbDay)+'天');
		cqDay = 1;
		cbDay = 1;
		cUserName = userName;
		cRows+=1;
		lastDay = '';
		lastTable = '';
	else:
		#判断此日期是否在考勤指定日期内
		currentDay = datetime.datetime.strptime(rowsTable[3],'%Y-%m-%d');
		if(beginDay <= currentDay and  currentDay <= endDay):
			#判断起始位置的考勤缺
			if(lastDay == '' and currentDay > beginDay):
				cRows = queQinFill(rowsTable,beginDay,currentDay,cRows);
			#判断是否当前和上一个差值是一天
			if(lastDay != '' and  (currentDay - lastDay).days > 1):
				cRows = queQinFill(rowsTable,(lastDay + datetime.timedelta(1)),currentDay,cRows);
			#更新last标识
			lastDay = currentDay;
			lastTable = rowsTable;
			#加班统计
			if((currentDay.weekday() == 5 or currentDay.weekday() == 6) and (rowsTable[3] not in noJiaBan)):
				for i in range(0,len(rowsTable)):
					writeSheet.write(cRows,i,str(rowsTable[i]));
				writeSheet.write(cRows,i,'加班');
				cRows+=1;
				continue;
			#判断餐补天数和异常考勤
			cqDay+=1;
			kqDate = rowsTable[4:];
			kqDate = [x for x in kqDate if x != ''];
			if(len(kqDate) < 2):   #若是打开时间段小于2则说明是异常考勤存在漏打卡
				for i in range(0,len(rowsTable)):
					writeSheet.write(cRows,i,str(rowsTable[i]));
				writeSheet.write(cRows,i,'漏打卡');
				cRows+=1;
			else:  #判断时间段的第一个值和最后一个值,若是在7-19:30之间则属于正常考勤
				if(getFirstNum(kqDate[0]) > 7*60 and getFirstNum(kqDate[0]) < 10*60 and getFirstNum(kqDate[-1]) > 19.5*60):
					if(getFirstNum(kqDate[-1]) > 20*60): #大于晚上八点有一个餐补
						cbDay+=1;
				else:
					for i in range(0,len(rowsTable)):
						writeSheet.write(cRows,i,str(rowsTable[i]));
					writeSheet.write(cRows,i,'打卡异常');
					cRows+=1;
#最后一个人的数据存储
if(lastDay != '' and  (endDay - lastDay).days > 1):
	cRows = queQinFill(lastTable,(lastDay + datetime.timedelta(1)),endDay,cRows);
writeSheet.write(cRows,0,str(cUserName));
writeSheet.write(cRows,1,'出勤'+str(cqDay)+'天');
writeSheet.write(cRows,2,'餐补'+str(cbDay)+'天');
writeWorkBook.save('result.xls')

感谢看完指鹤文章,希望指鹤的文章对您有所帮助。

闲暇时,指鹤喜欢写一些文章,部分发表在了豆瓣,若是您对此感兴趣,您可以点击下面连接支持下指鹤,指鹤在此表示感谢了

绝命笔记 一封匿名信引发的追寻 迷案追凶 量子危机

Leave a Reply