Python自動化辦公小程式：實現報表自動化和自動傳送到目的郵箱

12 min readSep 5, 2022

文章來於：傑哥的IT之旅

專案背景

作為資料分析師，我們需要經常製作統計分析圖表。但是報表太多的時候往往需要花費我們大部分時間去製作報表。這耽誤了我們利用大量的時間去進行資料分析。但是作為資料分析師我們應該儘可能去挖掘表格圖表資料背後隱藏關聯資訊，而不是簡單的統計表格製作圖表再發送報表。既然報表的工作不可免除，那我們應該如何利用我們所學的技術去更好的處理工作呢？這就需要我們製作一個Python小程式讓它自己去實現，這樣我們就有更多的時間去做資料分析。我們把讓程式自己執行的這個過程稱為自動化。

一、報表自動化目的

節省時間，提高效率

自動化總是能夠很好的節省時間，提高我們的工作效率。讓我們的程式程式設計儘可能的降低每個功能實現程式碼的耦合性，更好的維護程式碼。這樣我們會節省很多時間讓我們有空去做更多有價值有意義的工作。

2.減少錯誤

編碼實現效果正確無誤的話是是可以一直沿用的，如果是人為來操作的話反而可能會犯一些錯誤。交給固定的程式來做更加讓人放心，需求變更時僅修改部分程式碼即可解決問題。

二、報表自動化範圍

首先我們需要根據業務需求來制定我們所需要的報表，並不是每個報表都需要進行自動化的，一些複雜二次開發的指標資料要實現自動化程式設計的比較複雜的，而且可能會隱藏著各種BUG。所以我們需要對我們工作所要用到的報表的特性進行歸納，以下是我們需要綜合考慮的幾個方面：

頻率

對於一些業務上經常需要用到的表，這些表我們可能要納入自動化程式的範圍。例如客戶資訊清單、銷售額流量報表、業務流失報表、環比同比報表等。

這些使用頻率較高的報表，都很有必要進行自動化。對於那些偶爾需要使用的報表，或者是二次開發指標，需要複製統計的報表，這些報表就沒必要實現自動化了。

2.開發時間

這就相當於成本和利率一樣，若是有些報表自動化實現困難，還超過了我們普通統計分析所需要的時間，就沒必要去實現自動化。所以開始自動化工作的時候要衡量一下開發指令碼所耗費的時間和人工做表所耗費的時間哪個更短了。當然我會提供一套實現方案，但是僅對一些常用簡單的報表。

3.流程

對於我們報表每個過程和步驟，每個公司都有所不同，我們需要根據業務場景去編碼實現各個步驟功能。所以我們製作的流程應該是符合業務邏輯的，製作的程式也應該是符合邏輯的。

三、實現步驟

首先我們需要知道我們需要什麼指標：

指標

總體概覽指標：反映某一資料指標的整體大小

對比性指標

環比：相鄰時間段內的指標直接作差

同比：相鄰時間段內某一共同時間點上指標的對比

集中趨勢指標

中位數

眾數

平均數/加權平均數

離散程度指標

標準差

方差

四分位數

全距（極差）：最大界減最小界

相關性指標

我們拿一個簡單的報表來進行模擬實現：

第一步：讀取資料來源檔案

首先我們要了解我們的資料是從哪裡來的，也就是資料來源。我們最終的資料處理都是轉化為DataFrame來進行分析的，所以需要對資料來源進行轉化為DataFrame形式：

import pandas as pd
import json
import pymysql
from sqlalchemy import create_engine

# 打开数据库连接
conn = pymysql.connect(host='localhost',
                       port=3306,
                       user='root',
                       passwd='xxxx',
                       charset = 'utf8'
                       )
engine=create_engine('mysql+pymysql://root:xxxx@localhost/mysql?charset=utf8')

def read_excel(file):
    df_excel=pd.read_excel(file)
    return df_excel
def read_json(file):
    with open(file,'r')as json_f:
        df_json=pd.read_json(json_f)
        return df_json
def read_sql(table):
    sql_cmd ='SELECT * FROM %s'%table
    df_sql=pd.read_sql(sql_cmd,engine)
    return df_sql
def read_csv(file):
    df_csv=pd.read_csv(file)
    return df_csv

以上程式碼均透過測試可以正常使用，但是pandas的read函式針對不同的形式的檔案讀取，其read函式引數也有不同的含義，需要直接根據表格的形式來調整。

其他read函式將會在文章寫完之後後續補上，除了read_sql需要連線資料庫之外，其他的都是比較簡單的。

第二步：DataFrame計算

我們以使用者資訊為例：

我們需要統計的指標為：

指标说明
单表图：
前十个产品受众最多的地区

產品的受眾地區：

#将城市空值的一行删除
    df=df[df['city_num'].notna()]
    #删除error
    df=df.drop(df[df['city_num']=='error'].index)
    #统计
    df = df.city_num.value_counts()#将城市空值的一行删除
    df=df[df['city_num'].notna()]
    #删除error
    df=df.drop(df[df['city_num']=='error'].index)
    #统计
    df = df.city_num.value_counts()

我們僅獲取前10名的城市就好了，封裝為餅圖：

def pie_chart(df):
    #将城市空值的一行删除
    df=df[df['city_num'].notna()]
    #删除error
    df=df.drop(df[df['city_num']=='error'].index)
    #统计
    df = df.city_num.value_counts()
    df.head(10).plot.pie(subplots=True,figsize=(5, 6),autopct='%.2f%%',radius = 1.2,startangle = 250,legend=False)
pie_chart(read_csv('user_info.csv'))

將圖表儲存起來：

plt.savefig('fig_cat.png')

要是你覺得matplotlib的圖片不太美觀的話，你也可以換成echarts的圖片，會更加好看一些：

pie = Pie()
pie.add("",words)
pie.set_global_opts(title_opts=opts.TitleOpts(title="前十地区"))
#pie.set_series_opts(label_opts=opts.LabelOpts(user_df))
pie.render_notebook()

封裝後就可以直接使用了：

def echart_pie(user_df):
    user_df=user_df[user_df['city_num'].notna()]
    user_df=user_df.drop(user_df[user_df['city_num']=='error'].index)
    user_df = user_df.city_num.value_counts()
    name=user_df.head(10).index.tolist()
    value=user_df.head(10).values.tolist()
    words=list(zip(list(name),list(value)))
    pie = Pie()
    pie.add("",words)
    pie.set_global_opts(title_opts=opts.TitleOpts(title="前十地区"))
    #pie.set_series_opts(label_opts=opts.LabelOpts(user_df))
    return pie.render_notebook()
user_df=read_csv('user_info.csv')
echart_pie(user_df)

可以進行儲存，可惜不是動圖：

from snapshot_selenium import snapshot
make_snapshot(snapshot,echart_pie(user_df).render(),"test.png")

儲存為網頁的形式就可以自動載入JS進行渲染了：

echart_pie(user_df).render('problem.html')
os.system('problem.html')

第三步：自動傳送郵件

做出來的一系列報表一般都要發給別人看的，對於一些每天需要傳送到指定郵箱或者需要傳送多封報表的可以使用Python來自動傳送郵箱。

在Python傳送郵件主要藉助到smtplib和email這個兩個模組。

smtplib：主要用來建立和斷開與伺服器連線的工作。

email：主要用來設定一些些與郵件本身相關的內容。

不同種類的郵箱伺服器連線地址不一樣，大家根據自己平常使用的郵箱設定相應的伺服器進行連線。

首先需要開啟POP3/SMTP/IMAP服務，之後便可以根據授權碼使用python登入了。

import smtplib
from email import encoders
from email.header import Header
from email.utils import parseaddr,formataddr
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

#发件人邮箱
asender="fanstuck@163.com"
#收件人邮箱
areceiver="1079944650@qq.com"
#抄送人邮箱
acc="fanstuck@163.com"
#邮箱主题
asubject="谢谢关注"
#发件人地址
from_addr="fanstuck@163.com"
#邮箱授权码
password="####"
#邮件设置
msg=MIMEMultipart()
msg['Subject']=asubject
msg['to']=areceiver
msg['Cc']=acc
msg['from']="fanstuck"
#邮件正文
body="你好，欢迎关注fanstuck，您的关注就是我继续创作的动力！"
msg.attach(MIMEText(body,'plain','utf-8'))
#添加附件
htmlFile = 'C:/Users/10799/problem.html'
html = MIMEApplication(open(htmlFile , 'rb').read())
html.add_header('Content-Disposition', 'attachment', filename='html')

msg.attach(html)
#设置邮箱服务器地址和接口
smtp_server="smtp.163.com"
server = smtplib.SMTP(smtp_server,25)
server.set_debuglevel(1)
#登录邮箱
server.login(from_addr,password)
#发生邮箱
server.sendmail(from_addr,areceiver.split(',')+acc.split(','),msg.as_string())
#断开服务器连接
server.quit()