利用python整理出勤记录表

早餐和午餐打卡机上会显示打卡时间的记录，在同一个excel表里，会显示工号，姓名，打卡时间的信息。本程序将每个人的打卡时间做统计，算出该员工在本月内吃过几次早餐，吃过几次午餐。

原始数据如下图所示。

 初始化一个员工的类，每读取完成一个员工的数据，就将该类添加到列表里。最后将列表按照固定格式输出到excel表里。

这里打卡记录会出现“07:5812:20”这样的数据，这其实是“07:58”和“12:20”，也就是员工在“07:58”吃了早饭，在“12:20”吃了午饭。这里用正则表达式"r'\d\d:\d\d'"来匹配这个时间，若匹配到，则返回一个列表。

注意：xlrd读取excel时，只读取有效信息。例如，有10名员工（20行数据，一行姓名，一行打卡记录），若第10名员工一天都没在公司吃饭，即第20行是空行，那么xlrd只会读取19行的信息！

import re
import xlrd
import xlsxwriter
import easygui
 
class Staff:
	def __init__(self, index, name = "", gh=0, breakfast=0, lunch=0):
		self._index = index
		self._name = name
		self._breakfast = breakfast
		self._lunch = lunch
		self._sn = 0
		self._department = ""
		self._gonghao = gh
 
	def _repr_(self):
		return 'Staff({})'.format(self._sn)
 
	def addBreakfast(self):
		self._breakfast += 1
 
	def addLunch(self):
		self._lunch += 1
 
	def getInfo(self):
		print("name      : ", self._name)
		print("breakfast : ", self._breakfast)
		print("lunch     : ", self._lunch)
 
	def getName(self):
		return self._name
 
	def addDepartment(self, d):
		self._department = d
 
	def addSn(self, sn):
		self._sn = sn
 
	def getSn(self):
		return self._sn
 
 
# 从excel中读出原始数据
# 要求： 第一个sheet为原始数据， 第二个sheet为花名册
filename = easygui.fileopenbox()
workbook = xlrd.open_workbook(filename)
date = easygui.enterbox(msg = "请输入统计日期，例：201906")
year = int(date[:4])
month = int(date[4:6])
# 读取并统计数据
table1 = workbook.sheets()[0]
gonghao = table1.cell(4,0).value
pattern = re.compile(r'\d\d:\d\d')
staff_dict = {}
# print("ncols:", table1.ncols)
#print("nrows:", table1.nrows) 
for i in range(4, table1.nrows,2):
	name = table1.cell(i,10).value	
	gh = table1.cell(i, 2).value
	staff_temp = Staff((i/2 -1), name, gh)
	row = i+1
	bre = 0
	lun = 0
	for col in range(31):
		val = table1.cell(row,col).value
		doEat = pattern.findall(val)
		if doEat:
	# 		for each in doEat:
	# 			if int(each[0:2]) < 10:
	# 				staff_temp.addBreakfast()
	# 			elif int(each[0:2]) >= 11 and int(each[0:2]) <= 18:
	# 				staff_temp.addLunch()
	# if staff_temp._breakfast + staff_temp._lunch != 0:
			if int(doEat[0][0:2]) < 9:
				staff_temp.addBreakfast()
			if int(doEat[-1][0:2]) > 10 and int(doEat[-1][0:2]) <= 18:
				staff_temp.addLunch()
		staff_dict[staff_temp.getName()] = staff_temp
# 读取花名册
sn = 1
table2 = workbook.sheets()[-1]
for row in range(2,table2.nrows):
	name = table2.cell(row, 0).value
#	print(name)
	department = table2.cell(row, 1).value
#	print(type(staff_dict[name]))
	if name in staff_dict:
		staff_dict[name].addDepartment(department)
		staff_dict[name].addSn(sn)
		sn += 1
staff_sort = sorted(staff_dict.items(), key = lambda v: v[1]._sn)
for each in staff_sort:
	if each[1]._sn > 0:
		print(each[1]._sn, each[1]._name, each[1]._department, each[1]._breakfast, each[1]._lunch) 
 
# result_list[-1].getInfo()
# print(result_list[10])
 
 
# 将最终签名写到excel
filename_r = "最终签名.xlsx"
workbook = xlsxwriter.Workbook(filename_r)
worksheet = workbook.add_worksheet("最终签名")
# 写表头
title_format = workbook.add_format({
	'bold': True,
	'align': 'center',
	'valign': 'vcenter',
	'font_size': 18,
	'border': 1
	})
worksheet.merge_range('A1:J1', '合并单元格')
worksheet.write('A1', str(year)+"年"+str(month)+"月员工食堂就餐明细表", title_format)
 
boldcenter_format = workbook.add_format({
	'bold': True,
	'align': 'center',
	'valign': 'vcenter',
	'font_size': 11,	
	'border': 1,
	'top': 1,
	'bottom': 1,
	'left': 1,
	'right': 1,
	})
worksheet.merge_range('A2:A3', '合并单元格')
worksheet.write('A2:A3', "部门经理", boldcenter_format)
worksheet.merge_range('B2:B3', '合并单元格')
worksheet.write('B2', "姓名", boldcenter_format)
worksheet.merge_range('C2:E2', '合并单元格')
worksheet.write('C2', "早餐", boldcenter_format)
worksheet.write('C3', "次数", boldcenter_format)
worksheet.write('D3', "单价", boldcenter_format)
worksheet.write('E3', "小计", boldcenter_format)
worksheet.merge_range('F2:H2', '合并单元格')
worksheet.write('F2', "午餐", boldcenter_format)
worksheet.write('F3', "次数", boldcenter_format)
worksheet.write('G3', "单价", boldcenter_format)
worksheet.write('H3', "小计", boldcenter_format)
worksheet.merge_range('I2:I3', '合并单元格')
worksheet.write('I2', "消费金额", boldcenter_format)
worksheet.merge_range('J2:J3', '合并单元格')
worksheet.write('J2', " 备注", boldcenter_format)
 
# 写数据
data_format = workbook.add_format({
	'align': 'center',
	'valign': 'vcenter',
	'font_size': 11,	
	'border': 1,
	})
start_row = 3
for each in staff_sort:
	if each[1]._sn > 0 and int(each[1]._breakfast)*2 + int(each[1]._lunch)*3>0 :
		worksheet.set_row(start_row, 15.75)
		worksheet.write(start_row, 0, each[1]._department, data_format)
		worksheet.write(start_row, 1, each[1]._name, data_format)
		worksheet.write(start_row, 2, each[1]._breakfast, data_format)
		worksheet.write(start_row, 3, "2", data_format)
		worksheet.write(start_row, 4, int(each[1]._breakfast)*2, data_format)
		worksheet.write(start_row, 5, each[1]._lunch, data_format)
		worksheet.write(start_row, 6, "3", data_format)
		worksheet.write(start_row, 7, int(each[1]._lunch)*3, data_format)
		worksheet.write(start_row, 8, int(each[1]._breakfast)*2 + int(each[1]._lunch)*3, data_format)
		start_row += 1
 
worksheet.set_column("A:B", 15.63)
worksheet.set_column("B:C", 8.38)
worksheet.set_column("C:H", 7.25)
worksheet.set_column("H:I", 8.38)
worksheet.set_column("I:J", 10.75)
 
workbook.close()
 
easygui.msgbox(msg = "文件保存为：最终签字.xlsx", title = "转换完成", ok_button = "完成")

基本完成了，就剩一个问题，在合并单元格后，单元格的边框没办法扩展（这一步手工操作特别简单），等以后有时间再研究吧。

最后发布出来

pyinstaller -F -i xlsx.ico do.py

完成。