Skip to content

Instantly share code, notes, and snippets.

@jinyu121

jinyu121/README.md

Last active May 17, 2019 06:16
Show Gist options
  • Save jinyu121/413679bcef12d8091b7481413f4bd81b to your computer and use it in GitHub Desktop.
Save jinyu121/413679bcef12d8091b7481413f4bd81b to your computer and use it in GitHub Desktop.
Download ZIP
将excel中的条目发给每个人
Raw
README.md

将excel中的数据发给每个人

文件组织

SendMail
├── templates
│   ├── __init__.py
│   └── templates
│       └── main.html
└── main.py
Raw
main.py
# -*- coding: utf-8 -*-
import smtplib
import time
import pandas as pd
from email.mime.text import MIMEText
from email.header import Header
from tqdm import tqdm
from jinja2 import Environment, PackageLoader, select_autoescape
# 第三方 SMTP 服务
mail_host = "" # 设置服务器
mail_user = "" # 用户名
mail_pass = "" # 口令
SLEEP_TIME = 10
EXCEL_FILE = ""
sender = mail_user
def get_mail():
try:
smtpObj = smtplib.SMTP()
smtpObj.connect(mail_host, 25)
smtpObj.login(mail_user, mail_pass)
return smtpObj
except smtplib.SMTPException as e:
print("Error: 登录失败")
print(e)
# 加载邮件模板
env = Environment(
loader=PackageLoader('templates', 'templates'),
autoescape=select_autoescape(['html', 'xml']))
template = env.get_template('main.html')
def send(smtpObj, receiver, message):
"""发送邮件"""
try:
smtpObj.sendmail(sender, receiver, message.as_string())
print("成功：{}".format(receiver))
except Exception as e:
print("无法发送邮件：{}".format(receiver))
print(e)
def get_record_by_value(id, coll, column):
return coll[coll[column] == id]
def main():
with pd.ExcelFile(EXCEL_FILE) as xls_file:
st_summary = xls_file.parse(0)
st_deyu = xls_file.parse(3)
st_lunwen = xls_file.parse(4)
st_jingsai = xls_file.parse(5)
ith = 0
smtpObj = get_mail()
for people in tqdm(st_summary.values):
ith += 1
# 每次登录只能发送5封邮件，所以需要登出再登录
if ith % 5 == 0:
smtpObj.close()
smtpObj = get_mail()
sid = people[0]
name = people[1]
stu_type = people[10]
jidian = people[2]
para_jidian_full = 4
para_chengji_full = people[11]
score_xuexi = people[3]
score_keyan = people[4]
score_deyu = people[5]
score_gongzuo = people[6]
detail_deyu = get_record_by_value(sid, st_deyu, '学号')
detail_lunwen = get_record_by_value(name, st_lunwen, '姓名')
detail_jingsai = get_record_by_value(name, st_jingsai, '姓名')
html = template.render(
id=sid,
name=name,
stu_type=stu_type,
jidian=jidian,
para_jidian_full=para_jidian_full,
para_chengji_full=para_chengji_full,
score_xuexi=score_xuexi,
score_keyan=score_keyan,
score_deyu=score_deyu,
score_gongzuo=score_gongzuo,
detail_deyu=zip(detail_deyu.columns, detail_deyu.values[0]),
detail_lunwen=detail_lunwen,
detail_jingsai=detail_jingsai)
message = MIMEText(html, 'html', 'utf-8')
message['From'] = Header("班委成员", 'utf-8')
message['To'] = Header("You<{}@fudan.edu.cn>".format(sid), 'utf-8')
message['Subject'] = Header('奖学金评定核对', 'utf-8')
send(smtpObj, ["{}".format(sid)], message)
# 歇一会儿，防止被 ban
time.sleep(SLEEP_TIME)
if "__main__" == __name__:
main()
Raw
main_2.py
# -*- coding: utf-8 -*-
import smtplib
from email.mime.text import MIMEText
import csv
from tqdm import tqdm
from jinja2 import Environment, PackageLoader, select_autoescape
from argparse import ArgumentParser
from pathlib import Path
from email.header import Header
import functools
import time
# 第三方 SMTP 服务
MAIL_HOET = "" # 设置服务器
MAIL_USER = "" # 用户名
MAIL_PASS = "" # 口令
def retry(num_attempts=3, exception_class=Exception, log=None, sleeptime=1):
def decorator(func):
@functools.wraps(func)
def wrapper(*args, **kwargs):
for i in range(num_attempts):
try:
return func(*args, **kwargs)
except exception_class as e:
if i == num_attempts - 1:
raise
else:
if log:
log.error('Failed with error %r, trying again', e)
time.sleep(sleeptime)
return wrapper
return decorator
def parse_args():
parser = ArgumentParser()
parser.add_argument("--data", type=str, default="info.csv")
parser.add_argument("--skip", "-k", type=int, default=0, help="Skip X records")
parser.add_argument("--sleep", "-s", type=int, default=2, help="Sleep X seconds after sending a mail")
parser.add_argument("--max", "-m", type=int, default=5, help="Re-login after sending X mails")
return parser.parse_args()
class MailDeliver:
def __init__(self, data_source, sleep=10, skip=0, max_mail=5):
self.data = self.get_data(Path(data_source))
self.sleep = sleep
self.skip = skip
self.max_mail = max_mail
self.template = self.get_template()
self._sender = None
self._counter = 0
@retry()
def get_sender(self):
smtp_obj = smtplib.SMTP()
smtp_obj.connect(MAIL_HOET, 25)
smtp_obj.login(MAIL_USER, MAIL_PASS)
return smtp_obj
def get_template(self):
env = Environment(loader=PackageLoader('templates', 'templates'),
autoescape=select_autoescape(['html', 'xml']))
template = env.get_template('main.html')
return template
def get_data(self, data_path):
assert data_path.exists(), "Can not open {}".format(data_path)
reader = csv.DictReader(Path(args.data).open())
for data in reader:
yield data
@retry()
def send(self, sender, receiver, message):
if self._counter % self.max_mail == 0 or self._sender is None:
self._sender = self.get_sender()
self._counter += 1
self._sender.sendmail(sender, receiver, message.as_string())
def work(self):
for ith, info in enumerate(tqdm(self.data)):
if ith < self.skip:
continue
sid = info['sid']
sname = info['name']
try:
html = self.template.render(info=info)
message = MIMEText(html, 'html', 'utf-8')
message['From'] = Header("班委成员".format(MAIL_USER), 'utf-8')
message['To'] = Header("You<{}@fudan.edu.cn>".format(sid), 'utf-8')
message['Subject'] = Header('[毕业信息核对]住宿信息核对', 'utf-8')
self.send(MAIL_USER, ["{}@fudan.edu.cn".format(sid)], message)
tqdm.write("[{} {}] SUCCESS".format(sid, sname))
time.sleep(self.sleep)
except Exception as e:
tqdm.write("[{} {}] FAIL".format(sid, sname))
if "__main__" == __name__:
args = parse_args()
mailer = MailDeliver(args.data, args.sleep, args.skip)
mailer.work()
Raw
template_main.html
<!doctype html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Simple Transactional Email</title>
<style>
/* -------------------------------------
GLOBAL RESETS
------------------------------------- */
img {
border: none;
-ms-interpolation-mode: bicubic;
max-width: 100%;
}
body {
background-color: #f6f6f6;
font-family: sans-serif;
-webkit-font-smoothing: antialiased;
font-size: 14px;
line-height: 1.4;
margin: 0;
padding: 0;
-ms-text-size-adjust: 100%;
-webkit-text-size-adjust: 100%;
}
table {
border-collapse: collapse;
mso-table-lspace: 0pt;
mso-table-rspace: 0pt;
width: 100%;
}
.table-striped tr {
border-bottom: 1px solid #ccc;
}
.table-striped tr:nth-child(odd) {
background: #f9f9f9;
}
.table-striped {
margin-bottom: 20px;
}
table td {
font-family: sans-serif;
font-size: 14px;
vertical-align: top;
}
.table-hover>tbody>tr:hover {
background-color: #f5f5f5;
}
/* -------------------------------------
BODY & CONTAINER
------------------------------------- */
.body {
background-color: #f6f6f6;
width: 100%;
}
/* Set a max-width, and make it display as block so it will automatically stretch to that width, but will also shrink down on a phone or something */
.container {
display: block;
Margin: 0 auto !important;
/* makes it centered */
max-width: 580px;
padding: 10px;
width: 580px;
}
/* This should also be a block element, so that it will fill 100% of the .container */
.content {
box-sizing: border-box;
display: block;
Margin: 0 auto;
max-width: 580px;
padding: 10px;
}
/* -------------------------------------
HEADER, FOOTER, MAIN
------------------------------------- */
.main {
background: #ffffff;
border-radius: 3px;
width: 100%;
}
.wrapper {
box-sizing: border-box;
padding: 20px;
}
.content-block {
padding-bottom: 10px;
padding-top: 10px;
}
.footer {
clear: both;
Margin-top: 10px;
text-align: center;
width: 100%;
}
.footer td,
.footer p,
.footer span,
.footer a {
color: #999999;
font-size: 12px;
text-align: center;
}
/* -------------------------------------
TYPOGRAPHY
------------------------------------- */
h1,
h2,
h3,
h4 {
color: #000000;
font-family: sans-serif;
font-weight: 400;
line-height: 1.4;
margin: 15px 0;
}
h1 {
font-size: 35px;
font-weight: 300;
text-align: center;
text-transform: capitalize;
}
p,
ul,
ol {
font-family: sans-serif;
font-size: 14px;
font-weight: normal;
margin: 0;
Margin-bottom: 15px;
}
p li,
ul li,
ol li {
list-style-position: inside;
margin-left: 5px;
}
a {
color: #3498db;
text-decoration: underline;
}
/* -------------------------------------
BUTTONS
------------------------------------- */
.btn {
box-sizing: border-box;
width: 100%;
}
.btn>tbody>tr>td {
padding-bottom: 15px;
}
.btn table {
width: auto;
}
.btn table td {
background-color: #ffffff;
border-radius: 5px;
text-align: center;
}
.btn a {
background-color: #ffffff;
border: solid 1px #3498db;
border-radius: 5px;
box-sizing: border-box;
color: #3498db;
cursor: pointer;
display: inline-block;
font-size: 14px;
font-weight: bold;
margin: 0;
padding: 12px 25px;
text-decoration: none;
text-transform: capitalize;
}
.btn-primary table td {
background-color: #3498db;
}
.btn-primary a {
background-color: #3498db;
border-color: #3498db;
color: #ffffff;
}
/* -------------------------------------
OTHER STYLES THAT MIGHT BE USEFUL
------------------------------------- */
.last {
margin-bottom: 0;
}
.first {
margin-top: 0;
}
.align-center {
text-align: center;
}
.align-right {
text-align: right;
}
.align-left {
text-align: left;
}
.clear {
clear: both;
}
.mt0 {
margin-top: 0;
}
.mb0 {
margin-bottom: 0;
}
.preheader {
color: transparent;
display: none;
height: 0;
max-height: 0;
max-width: 0;
opacity: 0;
overflow: hidden;
mso-hide: all;
visibility: hidden;
width: 0;
}
.powered-by a {
text-decoration: none;
}
hr {
border: 0;
border-bottom: 1px solid #f6f6f6;
Margin: 20px 0;
}
/* -------------------------------------
RESPONSIVE AND MOBILE FRIENDLY STYLES
------------------------------------- */
@media only screen and (max-width: 620px) {
table[class=body] h1 {
font-size: 28px !important;
margin-bottom: 10px !important;
}
table[class=body] p,
table[class=body] ul,
table[class=body] ol,
table[class=body] td,
table[class=body] span,
table[class=body] a {
font-size: 16px !important;
}
table[class=body] .wrapper,
table[class=body] .article {
padding: 10px !important;
}
table[class=body] .content {
padding: 0 !important;
}
table[class=body] .container {
padding: 0 !important;
width: 100% !important;
}
table[class=body] .main {
border-left-width: 0 !important;
border-radius: 0 !important;
border-right-width: 0 !important;
}
table[class=body] .btn table {
width: 100% !important;
}
table[class=body] .btn a {
width: 100% !important;
}
table[class=body] .img-responsive {
height: auto !important;
max-width: 100% !important;
width: auto !important;
}
}
/* -------------------------------------
PRESERVE THESE STYLES IN THE HEAD
------------------------------------- */
@media all {
.ExternalClass {
width: 100%;
}
.ExternalClass,
.ExternalClass p,
.ExternalClass span,
.ExternalClass font,
.ExternalClass td,
.ExternalClass div {
line-height: 100%;
}
.apple-link a {
color: inherit !important;
font-family: inherit !important;
font-size: inherit !important;
font-weight: inherit !important;
line-height: inherit !important;
text-decoration: none !important;
}
.btn-primary table td:hover {
background-color: #34495e !important;
}
.btn-primary a:hover {
background-color: #34495e !important;
border-color: #34495e !important;
}
}
</style>
</head>
<body class="">
<table border="0" cellpadding="0" cellspacing="0" class="body">
<tr>
<td>&nbsp;</td>
<td class="container">
<div class="content">
<!-- START CENTERED WHITE CONTAINER -->
<span class="preheader">{{ name }}</b> 同学：本学期奖学金评定工作开始了。下面是一些数据，请核对一下。如有问题请及时反馈。</span>
<table class="main">
<!-- START MAIN CONTENT AREA -->
<tr>
<td class="wrapper">
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<div class="container">
<p><b>{{ name }}</b> 同学：</p>
<p>你好！本学期奖学金评定工作开始了。下面是一些数据，请核对一下：</p>
<h2>概览</h2>
<table class="table table-striped table-hover table-condensed">
<thead>
<tr>
<th>Key</th>
<th>Value</th>
<th>Note</th>
</tr>
</thead>
<tbody>
<tr>
<td>姓名</td>
<td>{{ name }}</td>
<td></td>
</tr>
<tr>
<td>学号</td>
<td>{{ id }}</td>
<td></td>
</tr>
<!--
<tr>
<td>类型</td>
<td>{{ stu_type }}</td>
<td></td>
</tr>
-->
<tr>
<td>绩点</td>
<td>{{ jidian }}</td>
<td></td>
</tr>
<tr>
<td>学习成绩</td>
<td>{{ score_xuexi }}</td>
<td>计算方法：<code>绩点/满绩点*成绩满分</code>，<br/>其中满绩点={{ para_jidian_full }}<br/>成绩满分={{ para_chengji_full }}</td>
</tr>
<tr>
<td>科研成绩</td>
<td>{{ score_keyan }}</td>
<td>包括论文加分和竞赛加分<br />详见下表</td>
</tr>
<tr>
<td>德育成绩</td>
<td>{{ score_deyu }}</td>
<td>详见下表</td>
</tr>
<!--
<tr>
<td>工作成绩</td>
<td>{{ score_gongzuo }}</td>
<td>分数由导师/课题组打出</td>
</tr>-->
</tbody>
</table>
<h2>论文加分</h2>
<table class="table table-striped table-hover table-condensed">
<thead>
<tr>
{% for item in detail_lunwen.columns -%} {% if loop.index>1 -%}
<th>{{ item }}</th>
{%- endif %} {%- endfor %}
</tr>
</thead>
<tbody>
{% for item in detail_lunwen.values -%}
<tr>
{% for it in item -%} {% if loop.index>1 %}
<td>{{ it }}</td>
{% endif %} {%- endfor %}
</tr>
{%- endfor %}
</tbody>
</table>
<h2>竞赛加分</h2>
<table class="table table-striped table-hover table-condensed">
<thead>
<tr>
{% for item in detail_jingsai.columns -%} {% if loop.index>1 %}
<th>{{ item }}</th>
{% endif %} {%- endfor %}
</tr>
</thead>
<tbody>
{% for item in detail_jingsai.values -%}
<tr>
{% for it in item -%} {% if loop.index>1 %}
<td>{{ it }}</td>
{% endif %} {%- endfor %}
</tr>
{%- endfor %}
</tbody>
</table>
<h2>德育成绩</h2>
<table class="table table-striped table-hover table-condensed">
<thead>
<tr>
<th>Key</th>
<th>Value</th>
</tr>
</thead>
<tbody>
{% for item in detail_deyu -%} {% if loop.index>2 %}
<tr>
<td>{{ item[0] }}</td>
<td>{{ item[1] | int }}</td>
</tr>
{% endif %} {%- endfor %}
</tbody>
</table>
<p>注：“班委”为2分，其余各项为1分。</p>
<h2>问题反馈</h2>
<p>以上数据如有问题，<b>请于10月7日晚8点之前回复此邮件并抄送给金导<a href="mailto:lfjin@fudan.edu.cn">lfjin@fudan.edu.cn</a></b></p>
<p class="align-right">2016届硕士班 班委</p>
<p class="align-right">2017.10.6</p>
</div>
</td>
</tr>
</table>
</td>
</tr>
<!-- END MAIN CONTENT AREA -->
</table>
<!-- END CENTERED WHITE CONTAINER -->
</div>
</td>
<td>&nbsp;</td>
</tr>
</table>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment