利用Python自动生成及发送Excel报表

【需求】

日常工作中,经常需要制作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)
    conn.commit()
    print('修改成功')
    
    s.close()

if conn:
    #无论如何,连接记得关闭
    conn.close()

完毕。
 

  • 2
    点赞
  • 29
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值