python实战|表格拆分，邮件发送，回收表格合并统计_python excel 分组发送邮件

背景：运维每周会给到一份全国‘失联’快递柜的数据，作为分析人员。需要知道每台柜子的失联原因。

需要做的事：将这份快递柜的数据拆分成全国6个大区，对应的6个文件，在每个文件的最后新增一个字段，添加数据有效性，让运维同事选择原因，然后回收6份数据，将数据汇总合并，统计失联原因的分布。

第一步：数据拆分，将文件拆解成6份

初始数据结构，典型中国式报表，前两列数据合并，区域数据错误，需要调整替换，最后还需要增加一列包含数据验证的列，方便一线同事选择，也方便后续数据汇总。

import os
import pandas as pd
import numpy as np
from openpyxl  import Workbook
from openpyxl  import load_workbook
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import Border,PatternFill,Font
import psycopg2
 
 
# 数据拆分
# 拆分生成新文件，设置数据有效性，设置表格样式，配置邮件发送
 
#---------------------------------------------------------------------------
#数据读取和清洗
file_path = r'C:\Users\james\Desktop\运维数据\11111111'   #设置根目录
file_name = r'快递柜运力状态监控数据.xlsx'   #设置excel文件名，一般为固定
 
#①连接数据库并获取维表数据及清洗
conn = psycopg2.connect(database="database",
                        user="root",
                        password="admin", 
                        host="localhost",
                        port="443")
cursor = conn.cursor()
cursor.execute('select * from dim_city')
df_city = pd.DataFrame(cursor.fetchall())
df_city.columns = ['big_city_name','city_code','city_name','region']
 
#②读取excel数据表及清洗
path = file_path + '\\' + file_name
df = pd.read_excel(path)
df['城市'] = df['城市'].ffill()  #对城市数据进行向下填充
df.dropna(subset=['失联状态'],inplace=True) #删除掉非失联状态的数据
 
 
#③两个数据合并，增加维度信息，以便后续数据拆分
df_new= pd.merge(df,df_city,how='left',left_on = '城市',right_on = 'city_name')
df['区域'] = df_new['region']
df['失联原因'] = None
cursor.close()
 
#---------------------------------------------------------------------------
#设置数据有效性规则
dv = DataValidation(type="list", formula1='"测试机-内部、代理商测试,外部因素-断电、纠纷等,业务待开发-用户没上来、要转移,硬件问题，其他等"', allow_blank=True)
# 设置错误信息提示
dv.error ='你输入的内容不符合规范'
dv.errorTitle = '非法的输入'
# 设置提示信息
dv.prompt = '请直接选择故障原因，勿修改表格表头结构'
dv.promptTitle = '下拉选择'
 
#---------------------------------------------------------------------------
#设置边框颜色
border = Border(left=Side(border_style='thin',color='000000'),
right=Side(border_style='thin',color='000000'),
top=Side(border_style='thin',color='000000'),
bottom=Side(border_style='thin',color='000000'))
#设置字体颜色
font = Font(size=10, bold=True, name='微软雅黑',  color="000000")#字体大小，加粗，字体名称，字体名字
#设置背景颜色
fill = PatternFill(patternType="solid", start_color="5B9BD5")#纯色填充
 
#---------------------------------------------------------------------------
for i in set(df_city.region.dropna()):    #通过for循环拆分文件
    wb = Workbook()
    ws = wb.active
    df_i = df[df['区域']==i]
    df_i.reset_index(inplace=True,drop=True)
    for r in dataframe_to_rows(df_i, index=True, header=True):
        if len(r)!=1: 
            ws.append(r)
    print('读取_'+i+'_数据完成！')
    c1 = 'R2'+':'+'R'+str(df_i.shape[0]+1)
    c2 = 'A1'+':'+'R'+str(df_i.shape[0]+1) 
    dv.add(c1)
    ws.add_data_validation(dv)    #给单元格添加数据验证
    for row in ws[c2]:           #给单元格添加边框
        for cell in row:
            if cell.row == 1:          #首行加粗，设置背景颜色
                cell.font = font
                cell.border = border
                cell.fill  = fill 
            cell.border = border
    wb.save(file_path + '\\' + file_name.split('.')[0]+'_'+i+'.xlsx')
    print('成功生成_'+i+'_失联快递柜数据！\n')
    
# 设置数据有效性并根据地区拆分多个excel文件

脚本执行的结果，生成所需的文件数据，方便后续发送给各地区同事。

新数据增加一列失联原因，添加数据有效性验证，方便选择及规范数据。也方便后期核对数据。

第二步：邮件发送模块

# email负责构造邮件，smtplib负责发送邮件
# coding:utf-8
# smtplib模块负责连接服务器和发送邮件
# MIMEText：定义邮件的文字数据
# MIMEImage：定义邮件的图片数据
# MIMEMultipart：负责将文字图片音频组装在一起添加附件
 
import smtplib  # 加载smtplib模块
from email.mime.text import MIMEText
from email.utils import formataddr
from email.mime.application import MIMEApplication
from email.mime.image import MIMEImage
from email.mime.multipart import MIMEMultipart
from email import encoders
 
sender = 'james.wang@baidu.com'  # 发件人邮箱账号
receive = 'harden.Li@baidu.com'  # 收件人邮箱账号
cc_mail = 'kurry.Lin@baidu.com,thompson.xiong@baidu.com'  # 抄送邮箱账号
passwd = 'james123'
mailserver = 'smtp.baidu.com'
port = '25'
sub = '快递柜失联原因监控数据'
 
try:
    msg = MIMEMultipart('related')
    msg['From'] = formataddr(["sender", sender])  # 发件人邮箱昵称、发件人邮箱账号
    msg['To'] = formataddr(["receiver", receive])  # 收件人邮箱昵称、收件人邮箱账号
    #msg['Cc'] = formataddr(["ccmail", cc_mail])  # 邮件抄送人
    msg['Cc'] = cc_mail
    msg['Subject'] = sub
    print('读取账号信息完成')
    #文本信息
    #txt = MIMEText('this is a test mail', 'plain', 'utf-8')
    #msg.attach(txt)
 
    #正文内容
    body = """
    <b>本周快递柜失联明细:</b><br></br>
    <!doctype html> <html><head> <meta charset="utf-8"></head>
    <body><div>
    <p>各位运维同事大家好，请认真填写选择快递柜失联原因，并于周四将数据提交数据分析同事，谢谢！:</P>
    </div> <style type="text/css">p{text-indent: 2em; /*首行字符缩进设置*/}</style>
    <span style="float:right;">数据分析：james</span>
    </body>
    </html>
    """
    text = MIMEText(body, 'html')  #, 'utf-8'
    msg.attach(text)
    print('正文信息构建完成')
 
    path = file_path+'\\'+file_name
    #明细附件信息 ①
    attach = MIMEApplication(open(path,'rb').read(),encoding="utf8")
    attach.add_header('Content-Disposition', 'attachment', filename='filename.xls')
    msg.attach(attach)
    
    #明细附件信息 ②
    attach = MIMEApplication(open(path,'rb').read(),encoding="utf8")
    attach.add_header('Content-Disposition', 'attachment', filename='filename.xls')
    msg.attach(attach)   
    print('附件添加成功')    
 
    #发送邮件
    server = smtplib.SMTP(mailserver, port)  # 发件人邮箱中的SMTP服务器，端口是25
    server.login(sender, passwd)  # 发件人邮箱账号、邮箱密码
    server.sendmail(sender, receive.split(',')+cc_mail.split(','), msg.as_string())  # 发件人邮箱账号、收件人邮箱账号、发送邮件
    server.quit()
    print('邮件发送成功')
except Exception as e:
    print(e)

将数据下发到各区域，由各地运维人员下载填写。

第三步：回收文件统计

# 运维回收数据合并  
# 合并所有文件，统计所需字段数据
path = file_path+'\\'+'回收数据'
df = pd.DataFrame({'区域':[],'快递柜SN':[],'失联原因':[]})
n=1
 
for filename in os.listdir(path):
    if '汇总' not in filename:
        filename=path+"\\" + filename
        print('读取第%d个文件' %n,filename )
        data = pd.read_excel(filename)
        print('读取第%d个文件成功' %n)
        n = n + 1
        df = pd.concat([df,data],join='inner')
 
df.drop_duplicates(subset='快递柜SN',keep='last',inplace = True) #防止各地区提交数据出现重复
df.to_excel(path+"\\"+'汇总文件.xlsx',encoding='utf-8')
print('\n')
print(df['失联原因'].value_counts(),'\n')
print(df['区域'].value_counts())
 
df['失联原因'].value_counts().plot(kind = 'pie',figsize=(15,8),fontsize=12)

做简单的数据可视化展示~

运营层面来说，沟通总是最耗时间的，和几个大区的同事沟通新数据填写和收集流程，耗时接近3个小时。第一周数据通过手动汇总，耗时共40分钟。但业务流程定下来后，通过脚本触发，第二周直接邮件给到一线同事，催下大家提交数据结果，最后通过脚本合并，第二周耗时低于2分钟。效率提升几十倍还是有的。技术和运营能很好结合的话，带来的效率提升回报还是很丰厚的。