工作中往往会存在反复的制表工作，而Python自动化报表就能很好解决大家的问题，学好python自动化报表，轻轻松松搞定各类报表。

现将相关知识整理后分享给大家，希望对你有所帮助^.^

一、Python邮件测试

邮件测试可谓是整个过程中核心环节，因为将做完后的报表定时通过邮件推送解放了我们的劳动力，遇到周末，让Python帮助我们自动推送报表即可，麻麻再也不用担心我们周末加班啦！

操作步骤如下：

1.登录邮箱，开启SMTP服务并生成授权码作为密码

以QQ邮箱为例，进入【设置】-【账户】，开启SMTP服务，具体如下，建议将POP3和IMAP都开启，会得到一个授权码，用于python内输入授权码作为登录密码。

2.在Python中测试发送邮件

import smtplib

from email.mime.text import MIMEText

from email.header import Header

#%%

host = '' # 服务器地址，

port = # 端口

#%%

user = '' # 发件人账号

#%%

password = '' # 发件账号密码（授权码）

#%%

sender = '' # 发件人账号

#%%

receivers = [''] # 收件人账号,此处设置为本人

subject = 'Python邮件测试' # 邮件标题

# 三个参数：第一个为文本内容，第二个 plain 设置文本格式，第三个 utf-8 设置编码

#message = MIMEText('Python 邮件发送测试', 'plain', 'utf-8')

try:

message = MIMEText('Python 邮件发送测试', 'plain', 'utf-8')

message['Subject'] = Header(subject, 'utf-8')

#%%

message['From'] = ''

#%%

message['To'] = ';'.join(receivers)

smtp_obj = smtplib.SMTP_SSL() # 开启发信服务，加密传输

smtp_obj.connect(host, port)

smtp_obj.login(user, password) # 登录邮箱

smtp_obj.sendmail(sender, receivers, message.as_string()) #发送邮件

print ("邮件发送成功")

except smtplib.SMTPException:

print ("邮件发送失败")

3.成果展示

将第2步中缺失数据进行填写后，发送成功结果如下所示：

二、Python链接数据库

前面已完成邮箱测试，现在需要将python连接上数据库，以保证后续报表的推送。代码如下：

import pymysql

import pandas as pd

risk1 =pymysql.connect(host="",user="",

password="",database="",

charset="") #根据实际情况填写

query1="""select * from ... where ..."""

#在数据库中查询想要的数据，上述仅为样例

data1=pd.read_sql(query1,risk1)

三、制作报表

1.新建一张报表

import pandas as pd

import pymysql

import xlrd

from xlutils.copy import copy

# 1、连接数据库

risk = pymysql.connect(host="",user="",

password="",database="",

charset="utf8")

# 2、查询数据

query="""select * from ... where ..."""

data=pd.read_sql(query,risk)

# 3、新建xls及sheet,把data写在这个sheet里

writer = pd.ExcelWriter('') #具体位置

data.to_excel(writer,'...',index=False) #补充具体文件名

# 4、保存报表

writer.save()

2. 更新已有报表中的数据

# 1、复制原有的报表文件，formatting_info=True表示保留原文件格式

oldWb = xlrd.open_workbook('', formatting_info=True);#根据实际情况填写

newWb = copy(oldWb)

newWs = newWb.get_sheet('')#根据想要的sheet填写

# 2、测出data_1长度、宽度，以range列出赋值给list_1、list2

list_1=range(len(data))

list_2=range(len(data.columns))

# 3、按照一定的格式和位置循环写入EXCEL表格--此处大家根据自己的需求进行编写即可，下面仅为举例

data['总进件']=data['总进件'].astype('float64')

for i in list_1:

for j in range(1):

newWs.write(i+1,j+1,data['总进件'][i])#写入EXCEL表格

newWs.write(i+1,j+2,data['审批量'][i])

newWs.write(i+1,j+3,data['通过量'][i])

newWs.write(i+1,j+4,data['通过率'][i])

newWs.write(i+1,j+5,data['XXX'][i])

newWs.write(i+1,j+6,data['XXX'][i])

newWs.write(i+1,j+7,data['XXX'][i])

newWs.write(i+1,j+8,data['XXX'][i])

newWs.write(i+1,j+9,data['XXX'][i])

...

i += 1

print ("write new values ok")

# 4、保存报表

newWb.save('')

四、定时更新报表——以信贷数据为例

大家可以根据自己的需要进行相关数据的查询。

import pandas as pd

import pymysql

import xlsxwriter

import time

while True:

#配置时间，自己设置

ehour= #定时小时

emin= #定时分钟

esec= #定时秒

current_time = time.localtime(time.time()) #当前时间date

cur_time = time.strftime('%H:%M', time.localtime(time.time())) #当前时间str

if ((current_time.tm_hour == ehour) and (current_time.tm_min == emin) and (current_time.tm_sec == esec)):

print ("开始")

#执行

try:

risk_test = pymysql.connect(host="",user="",

password="",database="",

charset="")

query="""

select c.放款月,round(sum(放款金额/10000),2) 放款金额,

case when sum(放款金额)>0 then round(sum(mob1)/sum(放款金额),4) else null end as mob_1,

case when sum(放款金额)>0 then round(sum(mob2)/sum(放款金额),4) else null end as mob_2,

case when sum(放款金额)>0 then round(sum(mob3)/sum(放款金额),4) else null end as mob_3,

case when sum(放款金额)>0 then round(sum(mob4)/sum(放款金额),4) else null end as mob_4,

case when sum(放款金额)>0 then round(sum(mob5)/sum(放款金额),4) else null end as mob_5,

case when sum(放款金额)>0 then round(sum(mob6)/sum(放款金额),4) else null end as mob_6

from(

select 分期数,放款月,

sum(case when mob=1 and 当前最大逾期天数>0 then 剩余本金 else 0 end) as mob1,

sum(case when mob=2 and 当前最大逾期天数>0 then 剩余本金 else 0 end) as mob2,

sum(case when mob=3 and 当前最大逾期天数>0 then 剩余本金 else 0 end) as mob3,

sum(case when mob=4 and 当前最大逾期天数>0 then 剩余本金 else 0 end) as mob4,

sum(case when mob=5 and 当前最大逾期天数>0 then 剩余本金 else 0 end) as mob5,

sum(case when mob=6 and 当前最大逾期天数>0 then 剩余本金 else 0 end) as mob6

from (

select m.分期数,m.剩余本金,m.放款月,m.观测月,m.当前最大逾期天数,

case when substr(m.观测月,1,4)=substr(m.放款月,1,4) then substr(m.观测月,6,2)-substr(m.放款月,6,2)

when substr(m.观测月,1,4)=substr(m.放款月,1,4)+1 then 12+substr(m.观测月,6,2)-substr(m.放款月,6,2)

else 0 end as mob

from risk_test.repayment_sum_month m) a

group by 分期数,放款月) b

join (select 合同期限,substr(放款日期,1,7) 放款月,

sum(合同金额) 放款金额,count(1) 放款量

from risk_test.customer_detail

where 放款日期>=''

and 放款日期<=''

and 合同期限=

group by 合同期限,substr(放款日期,1,7)) c

on b.分期数=c.合同期限 and b.放款月=c.放款月

group by c.放款月"""

data=pd.read_sql(query,risk_test)

workbook = xlsxwriter.Workbook('') #新建一个excel文本

worksheet = workbook.add_worksheet("")

chart = workbook.add_chart({'type': 'line'}) #创建一个图表对象

list_1=range(len(data))

title = [u'放款月',u'放款金额',u'mob_1',u'mob_2',u'mob_3',u'mob_4',u'mob_5',u'mob_6']

format=workbook.add_format() #定义format格式对象

format.set_border(1) #定义format对象单元格边框加粗

format_title=workbook.add_format() #定义format_title格式对象

format_title.set_border(1) #定义format_title对象单元格边框加粗

format_title.set_bg_color('#blue') #定义format_title对象单元格背景颜色

format_title.set_align('center') #定义format_title对象单元格居中对齐

format_title.set_bold() #定义format_title对象单元格内容加粗

format_title.set_font_color('white')

worksheet.write_row('A1',title,format_title)

for i in list_1:

for j in range(1):

worksheet.write(i+1,j+0,data['放款月'][i],format)#写入EXCEL表格

worksheet.write(i+1,j+1,data['放款金额'][i],format)

worksheet.write(i+1,j+2,data['mob_1'][i],format)

worksheet.write(i+1,j+3,data['mob_2'][i],format)

worksheet.write(i+1,j+4,data['mob_3'][i],format)

worksheet.write(i+1,j+5,data['mob_4'][i],format)

worksheet.write(i+1,j+6,data['mob_5'][i],format)

worksheet.write(i+1,j+7,data['mob_6'][i],format)

i += 1

#定义图表数据系列函数

def chart_series(cur_row):

chart.add_series({

'categories': '=vintage_report!$C$1:$H$1',

'values': '=vintage_report!$C$'+cur_row+':$H$'+cur_row,

'name': '=vintage_report!$A$'+cur_row,

})

for row in range(2, 8): #数据域以第2~7行进行图表数据系列函数调用

chart_series(str(row))

chart.set_size({'width': 520, 'height': 300}) #设置图表大小

chart.set_title ({'name': u'vintage报表'}) #设置图表(上方)大标题

worksheet.insert_chart('A9', chart) #在A8单元格插入图表

workbook.close() # 关闭报表

print("报表更新成功")

except:

print("报表更新失败")

print(cur_time)

time.sleep(1)

五、报表定时推送

上面我们已经学会了如何用python发送邮件以及如何通过python连接数据库，并制作报表，保存及更新本地报表文件，现在我们将上述知识串起来，进行报表定时推送的学习，具体代码如下：

import smtplib #邮件模块

from email.mime.text import MIMEText

from email.mime.multipart import MIMEMultipart

from email.header import Header

import time

while True:

#配置时间

ehour= #定时小时

emin= #定时分钟

esec= #定时秒

current_time = time.localtime(time.time()) #当前时间date

cur_time = time.strftime('%H:%M', time.localtime(time.time())) #当前时间str

smtpserver = ''

smtpport =

#%% 补充邮箱信息

username = ' '

password = ' '

sender = ' '

receiver = ' '

subject = 'XXX'

#%%

message = MIMEMultipart()

message['From'] = sender #发送

message['To'] = receiver #收件

message['Subject'] = Header(subject, 'utf-8')

message.attach(MIMEText("""Dear All:\n \

附件是截止今天的xx日报，请查阅！\n \

""", 'plain', 'utf-8'))# 邮件正文

# 构造附件

att1 = MIMEText(open('','rb').read(), 'base64', 'utf-8')

att1["Content-Type"] = 'application/octet-stream'

att1["Content-Disposition"] = "attachment;filename="

message.attach(att1)

#操作

if ((current_time.tm_hour == ehour) and (current_time.tm_min == emin) and (current_time.tm_sec == esec)):

print ("开始")

#执行

try:

smtp = smtplib.SMTP_SSL()

smtp.connect(smtpserver, smtpport) #连接服务器

smtp.login(username, password) #登录

smtp.sendmail(sender, receiver, message.as_string()) #发送

smtp.quit()

print("发送成功")

except:

print("发送失败")

print(cur_time)

time.sleep(1)

好了，至此已完成报表的制作更新及自动推送，聪明的你学会了吗？记得点赞和收藏本文并及时动手实践哦！

参考文献：