# 员工发工资的脚本from openpyxl import load_workbook# 发邮件引入import smtplibfrom email.mime.text import MIMEText #邮件正文from email.header import Header # 邮件头import syspath = sys.path[0] #获取当前脚本路径filename = path + '/' + 'payroll.xlsx'# 读取文件from win32com.client import Dispatchfile_path = 'payroll.xlsx'def just_open(): xlApp = Dispatch("Excel.Application") xlApp.Visible = False xlBook = xlApp.Workbooks.Open(filename) xlBook.Save() xlBook.Close()#加载excel文件just_open() #加载保存将公式转为数据wb = load_workbook(file_path,data_only=True)# 获取对应的sheetsheet1 = wb['Sheet1']#登陆邮箱smtp_obj = smtplib.SMTP('smtp.qq.com') # 邮箱发送服务器（ssL 485报错去掉端口）smtp_obj.login('xxxxx@qq.com','tvegmeymtftbbdha') # 邮箱用户名，密码(授权码)count =0table_col_html = '<thead bgcolor="#E3EDEE" align="center">' # 表头for row in sheet1.iter_rows(min_row=1): count+=1 if count ==1: for index in range(len(row)): if row[index].value: value = row[index].value else: value = '' if index == 1: continue table_col_html +=f'<th style="width: 100px;word-break:break-all">{value}</th>' table_col_html += '</thead>' continue else: row_test ='<tr align="center">' #开始一行 for index in range(len(row)): if row[index].value: value = row[index].value else: value = '' if index == 1: continue print(value,end=',') row_test += f'<td style="width: 100px;word-break:break-all">{value}</td>' row_test +="</tr>"# 结束一行 name = row[2] staff_email =row[1].value mail_body_context = f""" <h3>{name.value},你好：</h3> <p>请查收2022年12月的工资条。。。</p> <div style="width:100%;overflow-x:scroll;"> <table border="1" style="table-layout: fixed; width:3200px; word-break: break-all;border-collapse:collapse;font-family: "微软雅黑";font-size: 14px;"> {table_col_html} {row_test} </table> </div> <br> <div>公司：深圳市xxxx科技有限公司</div> <div>电话：15xxxxx</div> <div>地址：天利名城中央广场</div> <div>网址：xxxx.com</div> """ msg_body = MIMEText(mail_body_context,'html','utf-8') msg_body['From'] = Header('财务部','utf-8') #发送者 msg_body['To'] = Header(f'{staff_email}','utf-8') # 接受者 msg_body['Subject'] = Header('深圳市xxxxx数据科技有限公司2022年12月份工资条','utf-8') # 主题 # 发邮件 smtp_obj.sendmail('xxxxxxx@qq.com',[staff_email,],msg_body.as_string()) print(f"成功发送工资条到{staff_email}-{name.value}.....") break
1，空置处理，
2，计算公式处理
3，无法打开excel保存
