python sqlserver 列名_报表自动化,三流用Excel,二流用Python,一流用它

从事数据工作的人都知道,企业每天都要做很多报表,这个过程当中会涉及到很多手工操作和常规性报表。为了减少人工介入,节省工作时间,我们会想办法将一部分操作用工具或代码来替代,这个过程就称为报表自动化。

报表自动化如何实现?

报表自动化的起点是能对接数据源,期间能自动化的生成事先设计好格式的报表,最终通过邮件自动推送。具体流程可以分为3个步骤:

1、对接数据源:从数据库或数据仓库中取数,生成原始数据sheet或其他工具对接所需要的数据文件格式。

2、设计报表框架:要展示哪些数据指标,什么样的表头格式,是否需要用到图表、函数、空间等,最好手动设计好报表的版式。

3、自动化过程实现:自动化分为两块,自动化的数据处理和自动化发送邮件。

用什么工具实现?

实现的路径有很多。精通Excel的可以通过写VB脚本来实现,如果你会Python那更好不过,Python可以实现很多自动化设计,可以从读取到输出数据一步到位。更直接的,可以用报表工具来实现。所谓三流用Excel,二流用Python,一流直接用现成的报表工具!

我之前做数据报表的流程,一般是先打开数据库,然后运行一段写好的 SQL 语句,把数据查询出来,然后再把数据复制到 Excel 中并制作报表。后面用了 Python 之后,很多工作都可以自动化。再到后面公司上了BI报表系统,做报表就更加自动化和专业化了。

这里简述下后两者实现报表自动化的方法。

Python报表自动化

一次自动化的报表制作,通常需要经历这样几个步骤:

  • 连接并操作数据库
  • 利用Pandas进行数据清洗和处理
  • 操作Excel并开发报表
  • 设置定时邮件发送给相关人员

1、连接并操作数据库

Python可以连接并操作各种数据库,包括 Oracle、PostgreSQL、MySQL、SQL Server 等等。不同的数据库,需要安装不同的第三方模块,比如说,要操作 Oracle,那么通常需要先安装 cx_Oracle:

pip install cx_Oracle

在开始操作数据库之前,需要先创建一个数据库引擎,然后再连接数据库:

from sqlalchemy import create_engine# 创建数据库引擎engine = create_engine('oracle://user:password@ip_address:1521/orcl')# 连接数据库con = engine.connect()

如果你有数据库账号拥有创建表的权限,那么就可以对数据库进行增删改查的操作。

# 增con.execute("insert into usr(id, name) values(1, 'Jim')")con.execute("insert into usr(id, name) values(2, 'Joe')")# 删con.execute('delete from usr where id = 1')# 改con.execute("update usr set name = 'Jack' where id = 2")# 查sql = 'select id, name from usr where id = :id'import pandas as pddf = pd.read_sql(sa.text(sql), engine, params={'id': 2})df

另外,利用Python去执行各种SQL 语句,自动完成更加复杂的数据库操作。

2、数据处理+自动化报表

在Python执行SQL后,取出以下原始数据:

fc6dec2e87e1ae78b8a98e4cb4df81b0.png

想要实现的报表如下,这张日报表是用来监控每一天的销售、发货和用户反馈情况。

cb82caddfd505add5956b451a57d62c4.png

代码如下:

# PART2 自动化报表data = pd.read_excel(r'C:甥敳獲cindy407Desktopdelivery_data.xlsx',sheet_name='原始数据')# pandas行和列全部展示pd.options.display.max_rows=Nonepd.options.display.max_columns=None# 1、订单、销售金额、发货订单数df1 = data.groupby(['销售时间'])['订单号'].count() # 销售订单数df2 = data.groupby(['销售时间'])[['数量','销售金额']].sum() # 销量和金额df3 = data.groupby(['销售时间'])['交货时间'].count() # 交货订单数# 2、发货天数分布# 日期相加减,需先转变成日期格式,使用applydata['销售时间1'] = data['销售时间'].apply(lambda x:datetime.strptime(x.replace('/','-'),'%Y-%m-%d'))data['交货时间'] = data['交货时间'].apply(lambda x:datetime.strptime(x.replace('/','-'),'%Y-%m-%d'))data['送货时间'] = (data['交货时间'] - data['销售时间1']).apply(lambda x: x.days)# 连续型字段分成多区间,用pd.cutbin = [0,14,30,60,90,120]data['送货天数'] = pd.cut(data['送货时间'] ,bins=bin)# 按销售时间维度进行统计df4 = data.groupby(['销售时间','送货天数'])['销售时间'].count().unstack()# 3、用户反馈情况df5 = data.groupby(['销售时间','货品用户反馈'])['销售时间'].count().unstack()# 4、数据按列拼接df = pd.concat([df1,df2,df3,df4,df5],axis=1)# 5、修改列名df.columns=['销售订单数','销售件数','销售金额','已交货订单数','30-60天交货占比','90-120天交货占比','60-90天交货占比','拒收比例','质量合格比例','返修比例']# 6、将数值转化成百分比df['60-90天交货占比'] = df['60-90天交货占比']/df['已交货订单数']df['90-120天交货占比'] = df['90-120天交货占比']/df['已交货订单数']df['30-60天交货占比'] = df['30-60天交货占比']/df['已交货订单数']df['拒收比例'] = df['拒收比例']/df['已交货订单数']df['质量合格比例'] = df['质量合格比例']/df['已交货订单数']df['返修比例'] = df['返修比例']/df['已交货订单数']# 7、将汇总和明细存入同一个EXCELwriter =pd.ExcelWriter(r'C:甥敳獲cindy407Desktopdaily_report.xlsx')df.to_excel(writer,sheet_name='汇总数据')data.to_excel(writer,sheet_name='明细数据',index=False)writer.save()

3、设置定时邮件发送给相关人员

以上就是一个典型的日报表,源数据不变,报表格式不变,就可以通过这段脚本自动生成,生成的报表也可以实现推送,利用Python实现自动化发送邮件,使用发送邮件的协议SMTP,可以在邮箱的设置页面中开启SMTP。

用QQ邮箱来举例,自动化发邮件的完整代码如下:

import smtplibfrom email import encoders from email.header import Header from email.mime.text import MIMEText from email.utils import parseaddr,formataddr from email.mime.application import MIMEApplication from email.mime.multipart import MIMElMultipart from email.mime.base import MIMEBase asender='XXX@qq.com'#发件人邮箱areceiver='XXX@qq.com'#收件人邮箱acc='XXX@qq.com'#抄送人邮箱asubject='2020年XX月XX日报表'#邮件主题from_addr='XXX@qq.com'#邮件主题password='XXXX'授权码#邮件设置msg=MIMEMultipart()msg['Subject']=asubject msg['to']=areceiver msg['Cc']=acc msg['from']='数据分析不是个事儿'#邮件正文body='您好,这是今天的数据,请查收'#添加邮件正文msg.attach(MIMEText(body,'plain','utf-8'))#添加附件x1sxpart=MIMEApplication(open(r"C:甥敳獲AdministratorDesktop今日数据情况.x1sx",'rb').read())
xlsxpart.add header('Content-Disposition',                                         ’attachment',                                         filename='今日数据情况.x1sx’)msg.attach(xlsxpart)#设置邮箱服务器地址以及端口smtp_server='smtp.qq.com'server=smtplib.SIMTP(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()

做表的过程是通过python操作excel,这是很基础的用法,更高级的做法是用pandas替代excel进行处理再存入Excel。

以上,利用Python可以解决企业日常60%的报表自动化。那剩余的40%呢?

一方面,并不是所有报表都是需要或者适合做自动化的。像日报,周报,这种频率性很强的东西,就需要自动化。而那些不同活动的效果数据,每次都不一样,这样的做自动化就没有太大意义。

另一方面,一些复杂的报表,尤其是非数据类的报表用Python开发也不适合。比如图1这种报表格式很复杂的,用代码凭空构思难度很大;图2一些用于打印货单的凭证,需要涉及到数据填报和读取;还有图3这种能够用于分析联动钻取的动态报表,开发量很大。

5613e606c1bff3128589bad7872e5858.png

图1

82848562c78749fc34aea80b2305cfed.png

图2

c66e64bb807b500b5e00d013a6036fe6.gif

图3

再者,如果说企业数据量涉及千万上亿条数据,python加载数据就会卡顿。数据量大的企业报表需求量也大,一年可能要做上千张报表,用Python开发基本就是招程序员堆人力,这样的研发做着做着离职率也是极高的,整体的效率就会成问题。

随着机器变得越来越智能,许多工作都变得越来越自动化,也会推动着我们寻求更加敏捷的工具。其实市面上有很成熟的报表工具、报表控件可以用来开发报表,一旦企业数据业务成熟到一定阶段,都会上这一套系统,典型如FineReport

相比较Python,不要写代码,报表设计有类似Excel的可视界面。相比较Excel,可直接对接数据库读取写入,能操作大数据量,性能可随数据量适配。

报表工具实现报表自动化

企业级的系统追求效率,要让绝大多数没有技术基础的人能用,所以他们在开发工具的时候就会将很多功能零代码封装好。那FineReport是怎么实现报表自动化的呢?

简述几个报表自动化的功能点吧

  • SQL可视界面连接并操作数据源
  • 可视化报表模板设计器
  • 自动化的数据分发与提醒

1、可视化SQL语句编辑界面连接操作数据源

操作数据源无论是连接数据库还是Excel,Python每次都要写一串代码。FineReport操作数据库有一个可视化SQL语句编辑面板,在与数据库链接成功后能看到数据库表和字段,然后拖拽数据表和字段到编辑页面就可以生成SQL语句。其也能对接各种数据库和数据仓库。也能导入Excel数据集、Jason数据集等。

13d0058662da18beab4447f5ebf8899b.png

SQL做数据的人都懂,有些人习惯在SQL里处理一些数据,那就可以在取数环节处理一部分表和数据,就像一个内置的navicat。

2、可视化报表模板设计器,三种制作模式覆盖所有报表类型

读完库取完数之后就是作报表。Excel做的是一个个单张报表,FineReport做的是一个个报表哦模板,也就是说一些固定化格式的报表就可以设计统一的报表模板样式,一劳永逸,后续只要维护维护模板就好。

如图,FineReport的主面板就是表格模板设计器,表格式、功能操作、函数等都类似Excel,不同点在于Excel是对单个数据操作,这里是对数据字段操作,将需要的数据字段拖到表格中,再配以各种可视化的属性设置。

2502363cec17a1de414753f3cb1ba948.png

设计器针对有三种报表设计模式:普通报表、聚合报表、决策报表。

普通报表模式最贴近Excel,用来做大部分报表包括基础报表、中国式复杂报表,运算上能多SHEET和跨SHEET计算,兼容EXCEL公式。聚合报表模式用来做一些不规则的报表,像运单表,一些保险单之类的,这种报表要频繁的合并、拆分单元格,工作极其繁琐。决策报表模式用来做可视化仪表板、驾驶舱、管理看板等,就像一个空白画布,把各种可视化图表拖拽到界面搭建仪表板。

51469e49002c95e80f0e5af61509279b.gif

拖拽制作复杂报表

2fdb133e7ea3f75dbb633c8231a91a59.gif

固定报表的查询,可导出

d5ba65379a271dd701eb082a72abe599.png

可视化报表

3、自动化的数据分发与提醒

FineReport是有一个平台的,上面可以挂在报表,也有一些平台功能,比如自动化里常用到的报表推送,就可以用它的定时调度与消息提醒功能,就定时生成报表结果,将每日/每月的生产报表推送给对应的人员。发送方式有邮件、短信、平台、微信、钉钉等。根本不需要写代码。

d33090a57887c01dfde7a8057f6215f6.png

报表自动化只是FineReport的一部分特性,SQL编辑、报表页面设计、参数查询设计、填报设置、多层钻取等基本都是不要写代码。相比excel和Python开发,更快速和高效,配置好数据,1到2个小时就可开发出一张报表。

最后

最后,咱们数据从业者要想避免沦为“取数工具”、“表哥”、“表姐”,势必是要将报表工作自动化的,这样才能有更多的时间去思考和解决业务相关的问题,而不是陷入重复使用工具的手动操作。

对于制作数据报表这项工作而言,其最终价值也是赋能业务,所以需要从实际业务应用场景出发,去高效制作一套数据管理模版,并推动业务方大大利用。这需要一个数据平台来承接,需要报表工具来承担,也是企业利用数据道路上不可阻挡的趋势。

相关资源:python 报表--reportlab
<strong>课程背景:</strong><span style="color:#333333;">    </span><br /><span style="color:#333333;">企业里面很多系统管理后台,用easyui + highcharts + django 进行后台管理的整合,而这块资料在网上资料甚少,很多有经验的朋友在做这块时候也经常出现各种问题,目前我们老师以前在大数据真实项目中用到这块,现在受一些网友建议单独录制easyui + highcharts + django,希望可以帮助那些同学。视频+技术文档+源码让你看过后马上也可以搭建起来。  </span><br /><span style="color:#333333;">比如现在的友盟 和 百度统计 都是在类似这样的统计,我们会通过2~3个小时左右的课程,让你掌握这样的技术。   </span><br /><br /><strong><span>目标人群:</span></strong><span style="color:#333333;">  </span><br /><span style="color:#333333;">1、初级以上普通开发人员  </span><br /><span style="color:#333333;">2、web开发人员,对python感兴趣同学  </span><br /><span style="color:#333333;">3、对编程感兴趣的同学   </span><br /><br /><strong>课程目录:</strong><span style="color:#333333;">  </span><br /><span style="color:#333333;">01easyui_highcharts_django整合之需求介绍  </span><br /><span style="color:#333333;">02easyui_highcharts_django整合通过pycharm创建项目  </span><br /><span style="color:#333333;">03easyui_highcharts_django整合配置路由显示第一个页面  </span><br /><span style="color:#333333;">04easyui_highcharts_django整合下载easyui资源并且导入到项目里面  </span><br /><span style="color:#333333;">05easyui_highcharts_django整合修改配置文件加载static静态文件  </span><br /><span style="color:#333333;">06easyui_highcharts_django整合对页面进行修改  </span><br /><span style="color:#333333;">07easyui_highcharts_django整合对合并母模板layout  </span><br /><span style="color:#333333;">08easyui_highcharts_django整合添加一个新的业务的代码实现流程  </span><br /><span style="color:#333333;">09easyui_highcharts_django整合highcharts简单介绍  </span><br /><span style="color:#333333;">10easyui_highcharts_django整合highcharts的整合到django里面  </span><br /><span style="color:#333333;">11easyui_highcharts_django整合快速做出另一个业务流程  </span><br /><span style="color:#333333;">12easyui_highcharts_django整合柱状图  </span><br /><span style="color:#333333;">13easyui_highcharts_django后台返回json数据并且展示   </span><br /><span style="color:#333333;">14大数据django后台数据保存到mysql里面  </span><br /><span style="color:#333333;">15大数据django从mysql里面读取数据并显示到页面上面  </span><br /><br /><strong><span>课程环境:</span> </strong><span style="color:#333333;">   </span><br /><span style="color:#333333;">环境:win10,  </span><br /><span style="color:#333333;">python 2.7.13,  </span><br /><span style="color:#333333;">django 1.8.3 ,  </span><br /><span style="color:#333333;">pymsql   </span><br /><br /><strong><span>收获预期:</span></strong><span style="color:#333333;">    </span><br /><span style="color:#333333;">1.使用django开发项目  </span><br /><span style="color:#333333;">2.学会使用easyui框架  </span><br /><span style="color:#333333;">3.学会使用highcharts  </span><br /><span style="color:#333333;">4.easyui_highcharts_django开发后面展示项目   </span><br /><br /><strong>案例截图:</strong><span style="color:#333333;">  </span><br /><img src="https://10.url.cn/qqke_course_info/ajNVdqHZLLBg9jcMU16DOribraDkO2LW4hYpgGiccFiauF0gfaSTT10AOGF67T7JMQp6qkFYXXHsVk/" alt="" /><span style="color:#333333;">  </span><br /><img src="https://10.url.cn/qqke_course_info/ajNVdqHZLLDvU3EX0eOK19y7NCial5M76cbgA0W3Sl5ImSBjL2mmQ4po3HHDsD6TUyBIrsmdib1ac/" alt="" /><span style="color:#333333;">  </span><br /><br /><img src="https://10.url.cn/qqke_course_info/ajNVdqHZLLAAUQuuU3RoYW9ia0HsnyjRaA61piaLs07xRRhjUha8HsCIPk3k5ZzVP4RVLwdskEs8w/" alt="" />
表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页