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())