python项目流程设计_Python_办公自动化_结合Excel进行项目流程控制

1，目的：尝试利用Python结合Excel进行项目流程管理

2，效果：1)利用Excel模板进行项目流程控制；2)利用python 自动提取风险，问题清单等重要内容并发送给团队相关人员、

3，Excel项目管理模板的下载地址：https://gitee.com/civilli/pyplan/blob/master/%E5%B7%A5%E4%BD%9C%E8%A1%A8.xlsx

4，代码：

# 通过import 命令导入已下载的python模块from email.header import Headerfrom email.mime.multipart import MIMEMultipartfrom email.mime.text import MIMETextfrom smtplib import SMTP_SSLfrom datetime import datetimefrom xlrd import xldate_as_tupleimport smtplibimport xlwtimport xlrdimport time# 定义已存放excel文件的路径excel_path = 'C:工作表.xlsx'# 通过已定义的文件路径打开excel表格workbook = xlrd.open_workbook(excel_path)# 获取所有sheet(工作薄)数目#print('该excel文件中工作簿总数量为', workbook.nsheets)# 获取所有工作薄对象#print(workbook.sheet_names())# 单独对表格进行操作table0 = workbook.sheets()[0]table1 = workbook.sheets()[1]table2 = workbook.sheets()[2]table3 = workbook.sheets()[3]table4 = workbook.sheets()[4]# 获取表格名称#print(table0.name)# 自行定义邮件的smtp服务器host_server = 'smtp.?'# 自行定义发送邮件的地址sender_email = '?@?'# 自行定义登录邮件地址的密码pwd = '??????'# 根据项目组成员的角色，定义主送和抄送人Mreceiver = []CCMreceiver = []for i in range(7,table0.nrows):    if table0.row_values(i)[5] != '':        Mreceiver.append(table0.row_values(i)[5])receiver = ','.join(Mreceiver)for i in range(7,table0.nrows):    if table0.row_values(i)[14] != '':        CCMreceiver.append(table0.row_values(i)[14])cc = ','.join(CCMreceiver)toaddrs = [receiver] + [cc]#print(Mreceiver)#print(receiver.split(','))# 定义邮件主题mail_title = table2.row_values(9)[3] + '_' + table2.row_values(10)[2] + '_' + table2.row_values(9)[4] +             str(xldate_as_tuple(table2.row_values(6)[12], 0)[0]) + '年' + str(xldate_as_tuple(table2.row_values(6)[12], 0)[1]) + '月'              + str(xldate_as_tuple(table2.row_values(6)[12], 0)[2]) + '日'#print(mail_title)#根据项目表中的内容，定义邮件内容delayed = ['    2，存在的主要问题:' ]for i in range(18,table2.nrows):    if type(table2.row_values(i)[10]) == float and table2.row_values(i)[10] <= -1.0 and table2.row_values(i)[12] == '高':        item = '    序号:' + str(i-17) + '；' + str(table2.row_values(i)[2]) + '；' + ' 解决方法:' + str(table2.row_values(i)[13] + '  延误时间:' + str(table2.row_values(i)[10]) + '天')        delayed.append(item)        #print('   ','序号:',i,'；','延误时间:',table2.row_values(i)[10],'天','；','存在问题:',table2.row_values(i)[2],'；','解决方法:',table2.row_values(i)[13],'；')solved = ['    3，已解决的主要问题:'  ]for i in range(18,table2.nrows):    if table2.row_values(i)[11] == 'done' and table2.row_values(i)[12] == '高':        item = '    序号:'+ str(i-17) + '；' + table2.row_values(i)[2] + '；'        #print('   ','序号:',i,'；','事件:',table2.row_values(i)[2],'；')        solved.append(item)risking = ['    1，存在的风险:']for i in range(9,table4.nrows):    if table4.row_values(i)[4] == '高' and table4.row_values(i)[3] == '高':        item = '    序号:'+ str(i-8) + '；' + table4.row_values(i)[1] + '；'        #print('   ','序号:',i,'；','事件:',table2.row_values(i)[2],'；')        risking.append(item)mail_content = risking + delayed + solved#组合完成邮件所有内容a = ''for i in mail_content:    a = a + str(i) + ''mail_content = amail_content = '各位同事好：' + '    感谢大家相互配合工作，将近期进展总结如下：' + a + '' + # 引入已编写的邮件内容msg = MIMEMultipart() # 定义邮件主题msg['Subject'] = Header(mail_title, 'utf-8')msg['from'] = sender_emailprint(mail_content)msg.attach(MIMEText(mail_content,'plain','utf-8'))smtp = SMTP_SSL(host_server)smtp.login(sender_email,pwd)print(toaddrs)print(mail_content)#访问服务器发送所有邮件smtp.sendmail(sender_email,toaddrs,msg.as_string())4，Excel项目管理模板的下载地址：https://gitee.com/civilli/pyplan/blob/master/%E5%B7%A5%E4%BD%9C%E8%A1%A8.xlsx