使用python完成公司考勤数据的邮件发送

来源：互联网发布：windows无法完成格式化编辑：程序博客网时间：2024/09/21 09:23

传统公司的低廉指纹考勤机一般用excel输出，用USB或别的方式拿到数据后，该脚本用python处理excel数据并将结果用邮件发送到各个员工的邮件里。

使用的模块主要是：

excel读取写入模块（实际上不用写入）：xlrd,xlrt.

smtp模块(用于发邮件)：smtplib

获得系统时间的模块：datetime

这里我的思路是先写一个全部员工的字典，然后到对应的excel单元格读取数据，检查出是否忘记签到，是否迟到等信息后将信息用字符串打印出来，然后用smtp发送邮件。这里把smtp设置成自己的东西。当然，如果你要是想对特定的谁发送特定的私人订制，也是可以的，然而我怂了，你行你上啊。

代码如下（是面向过程的，如果写成面向对象应该会更简洁）：

import xlrd
import xlwt
import datetime
import smtplib
from email.mime.text import MIMEText

def getYesterday(): #get the yesterday datetime
today=datetime.date.today()
oneday=datetime.timedelta(days=1)
yesterday=today-oneday
return yesterday

def getYYYesterday():#get the datetime of three days ago
today=datetime.date.today()
threedays=datetime.timedelta(days=3)
yyyesterday=today-threedays
return yyyesterday

def getYWeekday(): #get the yesterday weekday
today=datetime.datetime.now()
oneday=datetime.timedelta(days=1)
yweekday=today-oneday
return yweekday

def dayinmonth(): #get the day's turn in month
dim=datetime.datetime.now().strftime('%d')
return dim

def timedif(t1,t2): #get the difference of two time former earlier latter later
if int(t1[3:])<=int(t2[3:]):
hour=str(int(t2[:2])-int(t1[:2]))
if int(hour)<10:
hour='0'+hour
minute=str(int(t2[3:])-int(t1[3:]))
if int(minute)<10:
minute='0'+minute
dif=hour+':'+minute

else:
hour=str(int(t2[:2])-int(t1[:2])-1)
if int(hour)<10:
hour='0'+hour
minute=str(60+int(t2[3:])-int(t1[3:]))
if int(minute)<10:
minute='0'+minute
dif=hour+':'+minute


return dif


def read_excel():

workbook = xlrd.open_workbook(r'C:\Users\admin\Desktop\may.xls') # change according to your situation
sheet3 = workbook.sheet_by_index(2)#get the sheet needed

transform={'Monday':'周一','Tuesday':'周二','Wednesday':'周三','Thursday':'周四','Friday':'周五','Saturday':'周六','Sunday':'周日'}

people={
'Test':{'name':'测试','mail':'test@test.com','addr':'5','text':''},
... ...

'name':'测试','mail':'test@test.com','addr':'5','text':''},

}



row=int(dayinmonth())-1
yday=str(getYesterday())
ywday=str(getYWeekday().strftime('%A'))

if ywday=='Sunday':
yday=str(getYYYesterday())
ywday='Friday'
row-=2


for (k,v) in people.items():
col=int(v['addr'])
a=sheet3.cell(col,row-1).value

str1='Dear '+v['name']+':\n\n您于上个工作日 '+yday+' '+transform[ywday]+' 的考勤记录为\n'

i=0
str2=''
if len(a)<5:
str2='无记录'

if len(a)<=5:
wktime='00:00'+' 您是否忘记打卡了(⊙o⊙) ？'
else:
if int(a[len(a)-5:].replace(':',''))<=1215:
wktime=timedif(a[:5],a[(len(a)-5):])
elif 1215<int(a[len(a)-5:].replace(':',''))<=1315:
wktime=timedif(a[:5],'12:15')
else:
wktime=timedif('01:00',timedif(a[:5],a[(len(a)-5):]))

str4='\n您的工时为 '+wktime
while i < len(a)/5:

str2+=(a[5*i:(5*i+4)+1]+' ')
i+=1
str3=' \n \n***********' #here is corporation's slogan

if(len(a)>=5 and int(a[len(a)-5:][:2])>=20):
str6='\n加班辛苦啦（*∩_∩*）'
else:
str6=''

if(len(a)>=5 and int(a[:5][:2])>=9):
str5='\n不小心迟到啦>_<|||'
elif(len(a)>=5 and int(a[:5][:2])<9):
str5='\n没有迟到哦(^_^)'
else:
str5=''

str7="\n\n如果您不想再收到这封邮件，请您回复我(→_→) "
strall=str1+str2+str4+str5+str6+str7+str3
v['text']=strall
print(strall)

user = "test@test.com" #change according to your situation
pwd = "test" # change according to your situation
to = ""

for (k,v) in people.items():
to=v['mail']
msg = MIMEText(v['text'])
msg["Subject"] = "您的签到信息"
msg["From"] = user
msg["To"] = to


s = smtplib.SMTP("smtp.test.com", timeout=30) #change according to your situation,if it's netease163,"smtp.163.com"
s.login(user, pwd)
s.sendmail(user, to, msg.as_string())
s.close()

if __name__ == '__main__':

read_excel()

0 0