【需求】
日常工作中,经常需要制作excel报表,并通过邮件发送出去。
【设计思路】
1、利用Python生成excel及发送邮件;
2、建立一张邮件发送配置表,用来配置所有需要发送的邮件基本信息;再建立一张邮件发送内容表,通过读取配置表数据来生成发送内容表。
邮件发送配置表(REP_SEND_CONF):
关键字段说明:
REP_NAME:报表名称
EMAIL_TITLE:邮件主题
EMAIL_RECEIVER:收件人,多个以逗号分隔
EMAIL_CC:抄送人,多个以逗号分隔
FREQ:报表发送频率类型,D:天;W:周;M:月;R:按规则。按规则的意思是说报表发送时间不固定,例如需要每周一、三、五发邮件
NUM:报表发送频率间隔,例如每天发邮件,这里就填1
PROC_BEGIN_TIME:报表生成时间段开始时间,格式:hh24:mi,例如需要每天7:00后才发送邮件,这里就填07:00
PROC_END_TIME :报表生成时间段结束时间,格式:hh24:mi
LATEST_EXEC_TIME:最近一次生成报表时间
NEXT_EXEC_TIME:下次生成报表时间。每次生成报表后,根据报表发送频率类型计算下次生成报表的时间,然后每天只处理报表配置表中下次生成报表时间为当天的报表。
PROC_NAME:调用程序名称。调用数据库存储过程生成邮件正文的HTML,这个字段填存储过程名。
ERR_MSG:程序运行异常日志
SEND_CONDITION:报表生成前置条件。这个字段配置一段SQL,返回值为Y或N,标识前置条件是否满足。
IS_HOLIDAY_SEND:节假日及周末是否发送,Y:是;N:否
EMAIL_CONTENT:邮件附件文件地址,多个以逗号分隔
STATUS:状态,Y:有效;N:无效。标识该配置项是否有效。
OS_TYPE:操作系统类型:Windows,Linux。如果有生成excel,则只能在Windows机器,否则可以在Linux。
PICTURE_ADD:正文图片地址,多个以逗号分隔。邮件正文里面的excel表格数据,全部从excel中截图贴到正文。
COPY_EMAIL_RECEIVER:复制邮件收件人。同一封邮件,如果需要再重复发一次给不同的人,在这里配置。
COPY_EMAIL_CC:复制邮件抄送人
NEXT_EXEC_TIME_RULE:下一次运行时间(针对发送频率为按规则发送的报表)
邮件发送内容表(REP_SEND_CONTENT):
关键字段说明:
REP_SEND_CONF_ID:配置表ID。通过此字段与邮件发送配置表关联。
EMAIL_TITLE:邮件主题。来自于邮件发送配置表。
EMAIL_RECEIVER :收件人。来自于邮件发送配置表。
EMAIL_CC:抄送人。来自于邮件发送配置表。
EMAIL_TEXT:邮件正文。HTML格式。
EMAIL_ATTACHMENT:邮件附件地址。多个以逗号分隔。
SEND_FLAG:发送标记,Y:已发送;N:未发送。初始为N,邮件发送后置为Y。
OS_TYPE:操作系统类型:Windows,Linux
PICTURE_ADD:邮件正文引用图片地址,多个以逗号分隔。
【代码】
1、生成邮件正文HTML(Oracle存储过程)
create or replace procedure rcas.p_get_html(str_l_html out varchar2)
/**********************************************************************************
* 名称:p_get_html
* 功能:生成邮件正文
**********************************************************************************/
as
str_l_html_content clob;
begin
str_l_html_content := '<p>Dear All,</p><p>以下为×××项目日报,请查收,谢谢!</p>
<p>简报如下:</p><p></p>
<p>一:日报项一</p>
<span><img src="cid:p1" /></span>
<p>明细1</p>
<span><img src="cid:p2" /></span>
<p>明细2</p>
<span><img src="cid:p3" /></span>
<p>二:日报项二</p>
<span><img src="cid:p4" /></span>
<p>明细1</p>
<span><img src="cid:p5" /></span>
<p>明细2</p>
<span><img src="cid:p6" /></span>
<p>三:日报项三</p>
<span><img src="cid:p7" /></span>
<p>四:日报项四</p>
<span><img src="cid:p8" /></span>
<p>五:日报项五</p>
<span><img src="cid:p9" /></span>
<p>本邮件为自动发送,如有疑问,请与×××联系。</p>';
str_l_html := str_l_html_content;
-- 异常捕获
exception
when others then
rollback;
dbms_output.put_line(sqlerrm);
end p_get_html;
2、读取邮件发送配置表,生成邮件发送内容表数据。(Oracle存储过程)
procedure p_rep_send_content
/**********************************************************************************
* 名称:p_rep_send_content
* 功能:生成报表发送内容
**********************************************************************************/
as
str_l_msg_body clob; -- 邮件正文HTML
str_l_sql varchar2(100); -- 执行SQL
dt_l_next_exec_time date; -- 下一次运行时间
str_l_flag varchar(32); -- 前置条件是否满足的标记
begin
for c in (select *
from rep.rep_send_conf t
where t.status = 'Y'
and sysdate between to_date(to_char(t.next_exec_time, 'yyyy-mm-dd')|| ' '||t.proc_begin_time,'yyyy-mm-dd hh24:mi:ss')
and to_date(to_char(t.next_exec_time, 'yyyy-mm-dd')|| ' '||t.proc_end_time,'yyyy-mm-dd hh24:mi:ss') and t.next_exec_time is not null) loop
-- 判断前置条件是否满足
str_l_flag :='Y';
if c.send_condition is not null then
execute immediate c.send_condition into str_l_flag;
end if;
-- 满足前置条件才发邮件
if str_l_flag ='Y' then
str_l_sql := 'begin '||c.proc_name||'; end;';
-- 生成邮件正文HTML
execute immediate str_l_sql using out str_l_msg_body;
-- 邮件正文生成成功的情况下,向邮件发送表写数据
if str_l_msg_body not like 'Err,%' then
if str_l_msg_body <> 'Not need send' then
insert into rep.rep_send_content
(REP_SEND_CONF_ID,
EMAIL_TITLE,
EMAIL_RECEIVER,
EMAIL_CC,
EMAIL_BCC,
EMAIL_TEXT,
EMAIL_ATTACHMENT,
SEND_FLAG,
OS_TYPE,
picture_add)
values (c.id,
c.email_title,
c.email_receiver,
c.email_cc,
c.email_bcc,
str_l_msg_body,
replace(c.email_content,'$sysdate$',to_char(sysdate,'yyyy-mm-dd')),
'N',
c.os_type,
c.picture_add);
-- 复制邮件发送
if c.copy_email_receiver is not null then
insert into rep.rep_send_content
(REP_SEND_CONF_ID,
EMAIL_TITLE,
EMAIL_RECEIVER,
EMAIL_CC,
EMAIL_BCC,
EMAIL_TEXT,
EMAIL_ATTACHMENT,
SEND_FLAG,
OS_TYPE,
picture_add)
values (c.id,
c.email_title,
c.copy_email_receiver,
c.copy_email_cc,
null,
str_l_msg_body,
replace(c.email_content,'$sysdate$',to_char(sysdate,'yyyy-mm-dd')),
'N',
c.os_type,
c.picture_add);
end if;
end if;
-- 计算下一次执行日期
-- 按天发送的情况
if c.freq = 'D' then
-- 判断是否节假日发送
if c.is_holiday_send = 'Y' then
-- 计算节假日发送情况下的下一次执行日期
dt_l_next_exec_time := c.next_exec_time + c.num;
else
-- 计算节假日不发送情况下的下一次执行日期
select nvl(min(t1.days),dt_l_next_exec_time)
into dt_l_next_exec_time
from rep.bse_calendar t1
where t1.days >= c.next_exec_time + c.num
and t1.is_holiday = 'N';
end if;
-- 按规则指定日期发送的情况
elsif c.freq = 'R' then
execute immediate c.next_exec_time_rule
into dt_l_next_exec_time;
end if;
-- 更新下一次执行日期
update rep.rep_send_conf t
set t.next_exec_time = dt_l_next_exec_time,
t.latest_exec_time = sysdate,
t.err_msg = null
where t.id = c.id;
commit;
else
-- 邮件正文生成失败的情况下,记录异常信息
update rep.rep_send_conf t
set t.err_msg = str_l_msg_body,
t.latest_exec_time = sysdate
where t.id = c.id;
commit;
end if;
end if;
end loop;
-- 异常捕获
exception
when others then
rollback;
dbms_output.put_line(sqlerrm);
end p_rep_send_content;
3、读取邮件发送内容表数据发送Email(Python脚本)
import cx_Oracle
import os
import smtplib #邮件模块
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.image import MIMEImage
#连接Oracle数据库
v_username = '数据库用户名' #数据库用户名
v_password = '数据库密码' #数据库密码
v_tns = cx_Oracle.makedsn('数据库IP', 端口号 , 'SID') #配置Oracle监听
conn = cx_Oracle.connect(v_username, v_password, v_tns) #连接到Oracle
#获取数据
cursor = conn.cursor()
sql = "select ID,EMAIL_TITLE,EMAIL_RECEIVER,EMAIL_CC,EMAIL_BCC,EMAIL_TEXT,EMAIL_ATTACHMENT,picture_add,'156' from 邮件发送内容表 where os_type = 'Windows' and send_flag = \'N\'"
cursor.execute(sql)
datas = cursor.fetchall()
for d in datas:
#发送邮件
s = smtplib.SMTP_SSL("smtp邮件发送服务器地址",端口号) # 连接smtp邮件服务器,端口默认是25
_user = "发件人邮箱" # 发件人邮箱
_pwd = "发件人邮箱密码" # 发件人邮箱密码
s.login(_user, _pwd) # 登陆服务器
v_subject = d[1] #邮件标题
v_receiver = d[2] #收件人邮箱,多人用逗号分隔
v_acc = d[3] #抄送人邮箱,多人用逗号分隔
v_bcc = d[4] #密送人邮箱,多人用逗号分隔
v_content = d[5] #邮件正文
v_attachment = d[6] #邮件附件
v_picture = d[7] #邮件正文图片
# 如名字所示multipart就是分多个部分
msg = MIMEMultipart('related')
msg["Subject"] = str(v_subject) #邮件主题
msg["From"] = str(_user) #邮件发件人
msg["To"] = str(v_receiver)+'' #邮件收件人
msg["Cc"] = str(v_acc)+'' #邮件抄送人
msgAlternative = MIMEMultipart('alternative')
msg.attach(msgAlternative)
content = str(v_content) # 邮件内容,\n是换行符
msgText = (MIMEText(content,'html','utf-8'))
msgAlternative.attach(msgText)
#添加图片
picutre_list = v_picture.split(',')
for i in range (0,len(picutre_list)):
fp = open(picutre_list[i], 'rb')
msgImage = MIMEImage(fp.read())
fp.close()
msgImage.add_header('Content-ID', '<p'+str(i+1)+'>')
msg.attach(msgImage)
#添加附件
if v_attachment:
part = MIMEText(open(v_attachment, 'rb').read(), 'base64', 'utf-8')
part["Content-Type"] = 'application/octet-stream'
basename = os.path.basename(v_attachment)
part.add_header('Content-Disposition', 'attachment', filename=('gbk', '', basename) ) #解决中文附件名不能发送的问题,不过目录还是不能为中文
msg.attach(part)
if not v_acc :
if not v_bcc:
s.sendmail(_user, v_receiver.split(','), msg.as_string())
else:
s.sendmail(_user, v_receiver.split(',')+v_bcc.split(','), msg.as_string())
else:
if not v_bcc:
s.sendmail(_user, v_receiver.split(',')+ v_acc.split(','), msg.as_string())
else:
s.sendmail(_user, v_receiver.split(',')+ v_acc.split(',')+v_bcc.split(','), msg.as_string())
print('邮件发送成功')
sql = 'update 邮件发送内容表 set send_flag = \'Y\' where id = '+str(d[0])
cursor.execute(sql)
mit()
print('修改成功')
s.close()
if conn:
#无论如何,连接记得关闭
conn.close()
完毕。