import?xlrd
import?xlsxwriter

import?smtplib
from?email.mime.text?import?MIMEText
from?email.mime.multipart?import?MIMEMultipart
from?email.mime.application?import?MIMEApplication

#1、讀取Excel
data?=?xlrd.open_workbook("info.xlsx")
classinfo?=?[]
for?sheet?in?data.sheets():
????dict={'name':sheet.name,'avgsalary':0}#班級(jí)名稱(chēng)
????sum=0
????for?i?in?range(sheet.nrows):
????????if?i>1:
????????????sum+=?float(sheet.cell(i,5).value)
????????????#print(sum)?打印薪資求和數(shù)量
????dict['avgsalary']?=?sum/(sheet.nrows-2)
????classinfo.append(dict)
print(classinfo)

#2、寫(xiě)入Excel
workbook?=?xlsxwriter.Workbook('nowinfo.xlsx')
sheet?=?workbook.add_worksheet()?#?創(chuàng)建工作表

#寫(xiě)入班級(jí)數(shù)據(jù)
nameinfo=[]
salaryinfo=[]
for?item?in?classinfo:
????nameinfo.append(item['name'])
????salaryinfo.append(item['avgsalary'])

sheet.write_column('A2',nameinfo)
sheet.write_column('B2',salaryinfo)
#寫(xiě)入圖表
chart?=?workbook.add_chart({'type':'column'})
#標(biāo)題
chart.set_title({'name':'平均就業(yè)薪資'})
#數(shù)據(jù)源
chart.add_series({
????'name':'班級(jí)',
????'categories':'=sheet1!$A$2:$A$4',
????'values':'=sheet1!$B$2:$B$4',
})

sheet.insert_chart('C8',chart)

workbook.close()

#發(fā)送郵件
host_server='smtp.qq.com'#主機(jī)地址
#發(fā)件人郵箱
sender=''
#發(fā)件人郵箱密碼（授權(quán)碼）
code=""
#收件人
user1=""
#準(zhǔn)備郵件數(shù)據(jù)
#郵件標(biāo)題
mail_title="1月份平均就業(yè)薪資"
#內(nèi)容
mail_content="1月份平均就業(yè)薪資，請(qǐng)具體查收附件"

attachment?=?MIMEApplication(open('newinfo.xlsx','rb').read)
attachment.add_header('Content-Disposition','attachment',filename='data')

#SMTP
smtp?=?smtplib.SMTP(host_server)
#登錄
smtp.login(sender,code)
msg=MIMEMultipart()#帶附件的實(shí)例
msg['Subject']=mail_title
msg['From']=sender
msg['To']=user1
msg.attach(MIMEText(mail_content))
msg.attach(attachment)
smtp.sendmail(sender,user1,msg.as_string())