工作中往往会存在反复的制表工作，而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)
好了，至此已完成报表的制作更新及自动推送，聪明的你学会了吗？记得点赞和收藏本文并及时动手实践哦！
参考文献：
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
