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}#班級名稱
????sum=0
????for?i?in?range(sheet.nrows):
????????if?i>1:
????????????sum+=?float(sheet.cell(i,5).value)
????????????#print(sum)?打印薪資求和數量
????dict['avgsalary']?=?sum/(sheet.nrows-2)
????classinfo.append(dict)
print(classinfo)

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

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

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

sheet.insert_chart('C8',chart)

workbook.close()

#發送郵件
host_server='smtp.qq.com'#主機地址
#發件人郵箱
sender=''
#發件人郵箱密碼（授權碼）
code=""
#收件人
user1=""
#準備郵件數據
#郵件標題
mail_title="1月份平均就業薪資"
#內容
mail_content="1月份平均就業薪資，請具體查收附件"

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()#帶附件的實例
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())