import openpyxl
dst_xlsx = '/data/python_home/cqg/data_model.xlsx'#模板，设置格式。
file_path = '/data/python_home/cqg/youjian.xlsx'#最终写好的模板路径
df=openpyxl.load_workbook(dst_xlsx) # 打开模板
tar = pd.read_excel('/data/python_home/cqg/duibi1.xlsx')#目标数据
tar
dt=pd.read_excel('data_model.xlsx')#打开看一下模板长啥样
dt
fund_list = ['多平台']
for k in range(1):
sheet0 = df.worksheets[k]# sheet页
target_data = pd.read_excel('/data/python_home/cqg/duibi1.xlsx').reset_index().T.reset_index().T.iloc[1:,2:10]
for i in range(len(target_data)):
#print(target_data.iloc[i,:])
for j in range(8):
sheet0.cell(i+3,j+1).value = target_data.iloc[(i,j)] # 写入数据
#sheet0.cell(i+1,j+1).alignment = Alignment(horizontal='center', vertical='center') # 居中对齐
df.save(file_path) # 保存文件
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import smtplib
from email.mime.image import MIMEImage
import datetime
mail_host = "smtphz.qiye.163.com" #SMTP服务器地址
mail_sender = "jckxpy@ihanchen.com" #账号
mail_passwd = "Cloud@12@@" #密码
msg = MIMEMultipart('related')
msg["Subject"] = '多平台对比'
msg["From"] = mail_sender #发送人
receiver=['chenqianguang@ihanchen.com']
邮件正文
content = '''
各位好:
'''
import datetime
yesterday = datetime.date.today()-datetime.timedelta(days=1)
msg.attach(MIMEText(content,'plain','utf-8'))
file_path = '/data/python_home/cqg2/数据情况1.xlsx'
构造附件2，xlsx文件
att2 = MIMEText(open(file_path, 'rb').read(), 'base64', 'utf-8')
att2["Content-Type"] = 'application/octet-stream'
att2.add_header("Content-Disposition",'attachment', filename=file_path)
msg.attach(att2)
s = smtplib.SMTP() #实例化对象
s.connect(mail_host) #连接163邮箱服务器，端口号为465,注意，这里不需要写端口号
s.login(mail_sender, mail_passwd) #登录邮箱
s.sendmail(mail_sender, receiver, msg.as_string())
s.quit()