解决python获取excel指定sheet表中字段的值并发送邮件乱码问题
需要用python操作的表
#根据此表结合代码的实现来讲解
#导入需要的模块
xlrd模块是读取excel表的模块,需要手动导入,这里举例pycharm导入模块方法,所有模块导入均可参照此例。
点击file选择setting
选中proje interpreter点击右边有个添加的符号
搜索xlrd模块,点击install package即可
代码如下
import xlrd
import sys
import smtplib
import string
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from datetime import date,datetime
# name1,指定姓名1 name2,指定姓名2,mail_feild指定字段,这里只邮件 wage_feild指定字段,这里指工资
def read_excel(name1,name2,mail_feild,wage_feild):
# 打开文件,设置编码格式
workbook = xlrd.open_workbook(rpathxlsx,encoding_override = "utf-8")
# 获取所有sheet,即一个文件所有的表
sheet1_name = workbook.sheet_names()[0]
sheet1 = workbook.sheet_by_index(1) # sheet索引从0开始
sheet1= workbook.sheet_by_name(sheet1_name) #获取第一张表名
nrows = sheet1.nrows #获取该表有效的行数
ncols = sheet1.ncols #获取该表有效的列数
#遍历指定列的所有行数,这里遍历第二列names字段的值
for i in range(0,nrows):
if sheet1.cell(i,1).value.encode('utf-8') == name1:#找到name1后将此行记录到name_rows中
name_rows1 = i
if sheet1.cell(i,1).value.encode('utf-8') == name2:
name_rows2 = i
#遍历指定行的所有列数,这里指的是第一行的所有字段
for j in range(0,ncols):
if sheet1.cell(0,j).value.encode('utf-8') == mail_feild:#找到“邮件"字段的所处列,utf-8防止获取单元格内容中文乱码
M_email = sheet1.cell(name_rows1,j).value.encode('utf-8') #将指定的行和对应的列中单元格的内容获取出来,即读出指定名字的邮箱号
Z_email = sheet1.cell(name_rows2,j).value.encode('utf-8')
# print M_email,Z_email
if sheet1.cell(0,j).value.encode('utf-8') == wage_feild: #找到“工资”字段所处列
M_wage = sheet1.cell(name_rows1,j).value #将指定的行和对应的列中单元格的内容获取出来,即读出指定名字的工资
Z_wage = sheet1.cell(name_rows2,j).value
m_wage = str(M_wage)
z_wage = str(Z_wage)
# print M_wage,Z_wage
charbuff = name1 + "的工资是" + m_wage #将获取的工资和名字合并成字符串发送给指定邮箱
sendEmail("[email protected]", "trxgcnuupjlwbfch","[email protected]",M_email,charbuff)
charbuff = name2 + "的工资是" + z_wage
sendEmail("[email protected]", "trxgcnuupjlwbfch","[email protected]",Z_email,charbuff)
# username 邮箱名, password 授权码, sender 发送者的邮箱,receiver 接收者的邮箱 news,发送的消息
def sendEmail(username,password,sender,receiver,news):
#定制发送格式和内容
msgText = MIMEText(news,_charset = 'utf-8') #解决发送邮件中文乱码
msgText['Subject'] = '薪酬'
msgText["To"] = receiver
msgText["From"] = sender
#sendEmail
smtp = smtplib.SMTP_SSL('smtp.qq.com', port=465)
smtp.login(username, password)
smtp.sendmail(sender, receiver, msgText.as_string())
smtp.quit()
print 'success'
#调用函数
read_excel("Mike","张三","邮件","工资")
###欢迎转载### https://blog.csdn.net/righting123/article/details/88897565