【需求】
日常工作中，经常需要制作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()
完毕。
