选课报班>>

您现在所在的位置:主页 > 新闻资讯 > IT资讯 >

绍兴Python自动化办公 | 如何实现报表数据处理自

来源:绍兴上元小陆
发布时间:2022-05-19

最近一个之前在我们这边学过office软件的市委党校的人找到我,是负责新农村建设宣传这块的,说想了解一种计算机语言,能便捷的处理平时工作流程中上千条繁琐的数据。那对于这种办公类员工数据的分析处理,自然是最推荐python了。现在上元小陆就带大家了解下报表自动化的流程,并教你用Python实现工作中的一个报表自动化实战,篇幅有点长,建议先收藏,文章具体的目录为:

1.Excel的基本组成
2.一份报表自动化的流程
3.报表自动化实战
    - 当日各项指标同环比情况
    - 当日各省份创建订单量情况
    - 最近一段时间创建订单量趋势
 

Excel的基本组成

我们一般在最开始做报表的时候,基本都是从Excel开始的,都是利用Excel在做报表,所以我们先了解下Excel的基本组成。

下图是Excel的中各个部分的组成关系,我们工作中每天会处理很多Excel文件,一个Excel文件其实就是一个工作簿。你在每次新建一个Excel文件时,文件名都会默认是工作簿x,其中x就是你新建的文件个数。而一个工作簿里面又可以有多个Sheet,不同Sheet之间是一个独立的表。每一个Sheet里面又由若干个单元格组成。每一个单元格又有若干的元素或属性,我们一般针对Excel文件进行设置最多的其实就是针对单元格的元素进行设置。

而针对单元格元素进行设置的主要内容其实就是如下图菜单栏中显示,比如字体、对齐方式、条件格式等内容。本书也是按照Excel菜单栏中的各个模块进行编写。

一份自动化报表的流程

下图是我整理的做一份自动化报表需要经历的流程,主要分为5个步骤:

第一步是对要做的报表进行步骤拆解,这个步骤拆解和用不用工具或者是用什么工具没有直接关系,比如做报表的第一步一般都是收集数据,这个数据可能是线下人员记录在纸质笔记本上的,也可能是存储在Excel表里面的,还有可能是存储在数据库里面的。会因为数据源的类型或者是存储方式不同,对应的收集数据方式会不一样,但是收集数据这个步骤本身是不会变的,这个步骤的目的就是把数据收集过来。

第二步是去想第一步里面涉及到的每一个具体步骤对应的代码实现方式,一般都是去找对应每一步的代码,比如导入数据的代码是什么样的,再比如重复值删除的代码是什么样的。

第三步是将第二步中各个步骤对应的代码进行组合,组合成一个完整的代码。

第四步是对第三步完整代码得出来的报表结果进行验证,看结果是否正确。

第五步就是等待调用,看什么时候需要制作报表了,然后就将写好的代码执行一遍就行。

其实报表自动化本质上就是让机器代替人工做事情的过程,我们只需要把我们人工需要做的每一个步骤转化成机器可以理解的语言,也就是代码,然后让机器自动去执行,这其实就是实现了自动化。

报表自动化实战

这一节给大家演示下在实际工作中如何结合Pandas和openpyxl来自动化生成报表。

假设我们现在有如下一份数据集:

现在我们需要根据这份数据集来制作每天的日报情况,会主要包含三方面:

  • 当日各项指标的同环比情况;
  • 当日各省份创建订单量情况;
  • 最近一段时间创建订单量趋势

接下来分别来实现这三部分。

当日各项指标的同环比情况:

我们先用Pandas对数据进行计算处理,得到各指标的同环比情况,具体实现代码如下:

#导入文件
import pandas as pd
df = pd.read_excel(r'D:Data-Scienceshareexcel-python报表自动化sale_data.xlsx')

#构造同时获取不同指标的函数
def get_data(date):   
    create_cnt = df[df['创建日期'] == date]['order_id'].count()
    pay_cnt = df[df['付款日期'] == date]['order_id'].count()
    receive_cnt = df[df['收货日期'] == date]['order_id'].count()
    return_cnt = df[df['退款日期'] == date]['order_id'].count()
    return create_cnt,pay_cnt,receive_cnt,return_cnt
    
#假设当日是2021-04-11
#获取不同时间段的各指标值
df_view = pd.DataFrame([get_data('2021-04-11')
                     ,get_data('2021-04-10')
                     ,get_data('2021-04-04')]
                     ,columns = ['创建订单量','付款订单量','收货订单量','退款订单量']
                     ,index = ['当日','昨日','上周同期']).T

df_view['环比'] = df_view['当日'] / df_view['昨日'] - 1
df_view['同比'] = df_view['当日'] / df_view['上周同期'] - 1
df_view

运行上面代码会得到如下结果:

上面只是得到了各指标的同环比绝对数值,但是我们一般的日报在发出去之前都要做一些格式调整的,比如调整字体之类的。而格式调整就需要用到openpyxl库,我们需要将Pandas库中DataFrame格式的数据转化为适用openpyxl库的数据格式,具体实现代码如下:

from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows

#创建空工作簿
wb = Workbook()
ws = wb.active

#将DataFrame格式数据转化为openpyxl格式
for r in dataframe_to_rows(df_view,index = True,header = True):
    ws.append(r)

wb.save(r'D:Data-Scienceshareexcel-python报表自动化核心指标_原始.xlsx')

运行上面代码会得到如下结果,可以看到原始的数据文件看起来是很混乱的:

 

接下来我们针对上面原始数据文件进行格式调整,具体调整代码如下:

from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import colors
from openpyxl.styles import Font
from openpyxl.styles import PatternFill
from openpyxl.styles import Border, Side
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active

for r in dataframe_to_rows(df_view,index = True,header = True):
    ws.append(r)
    
#第二行是空的,删除第二行
ws.delete_rows(2)

#给A1单元格进行赋值
ws['A1'] = '指标'

#插入一行作为标题行
ws.insert_rows(1)
ws['A1'] = '电商业务方向 2021/4/11 日报'

#将标题行的单元格进行合并
ws.merge_cells('A1:F1'#合并单元格

#对第1行至第6行的单元格进行格式设置
for row in ws[1:6]:
    for c in row:
        #字体设置
        c.font = Font(name = '微软雅黑',size = 12)
        #对齐方式设置
        c.alignment = Alignment(horizontal = "center")
        #边框线设置
        c.border = Border(left = Side(border_style = "thin",color = "FF000000"),
                   right = Side(border_style = "thin",color = "FF000000"),
                   top = Side(border_style = "thin",color = "FF000000"),
                   bottom = Side(border_style = "thin",color = "FF000000"))

#对标题行和表头行进行特殊设置
for row in ws[1:2]:
    for c in row:
        c.font = Font(name = '微软雅黑',size = 12,bold = True,color = "FFFFFFFF")
        c.fill = PatternFill(fill_type = 'solid',start_color='FFFF6100')

#将环比和同比设置成百分比格式        
for col in ws["E":"F"]:
    for r in col:
        r.number_format = '0.00%'

#调整列宽
ws.column_dimensions['A'].width = 13
ws.column_dimensions['E'].width = 10

#保存调整后的文件        
wb.save(r'D:Data-Scienceshareexcel-python报表自动化核心指标.xlsx')

运行上面代码会得到如下结果:

 

可以看到各项均已设置成功。

当日各省份创建订单量情况:

我们同样先利用Pandas库处理得到当日各省份创建订单量情况,具体实现代码如下:

df_province = pd.DataFrame(df[df['创建日期'] == '2021-04-11'].groupby('省份')['order_id'].count())
df_province = df_province.reset_index()
df_province = df_province.sort_values(by = 'order_id',ascending = False)
df_province = df_province.rename(columns = {'order_id':'创建订单量'})
df_province

运行上面代码会得到如下结果:


在得到各省份当日创建订单量的绝对数值之后,同样对其进行格式设置,具体设置代码如下:

from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import colors
from openpyxl.styles import Font
from openpyxl.styles import PatternFill
from openpyxl.styles import Border, Side
from openpyxl.styles import Alignment
from openpyxl.formatting.rule import DataBarRule

wb = Workbook()
ws = wb.active

for r in dataframe_to_rows(df_province,index = False,header = True):
    ws.append(r)

#对第1行至第11行的单元格进行设置
for row in ws[1:11]:
    for c in row:
        #字体设置
        c.font = Font(name = '微软雅黑',size = 12)
        #对齐方式设置
        c.alignment = Alignment(horizontal = "center")
        #边框线设置
        c.border = Border(left = Side(border_style = "thin",color = "FF000000"),
                   right = Side(border_style = "thin",color = "FF000000"),
                   top = Side(border_style = "thin",color = "FF000000"),
                   bottom = Side(border_style = "thin",color = "FF000000"))

#设置进度条条件格式
rule = DataBarRule(start_type = 'min',end_type = 'max',
                    color="FF638EC6", showValue=True, minLength=None, maxLength=None)
ws.conditional_formatting.add('B1:B11',rule)

#对第1行标题行进行设置
for c in ws[1]:
    c.font = Font(name = '微软雅黑',size = 12,bold = True,color = "FFFFFFFF")
    c.fill = PatternFill(fill_type = 'solid',start_color='FFFF6100')
        
#调整列宽
ws.column_dimensions['A'].width = 17
ws.column_dimensions['B'].width = 13

#保存调整后的文件     
wb.save(r'D:Data-Scienceshareexcel-python报表自动化各省份销量情况.xlsx')

运行上面代码会得到如下结果:

最近一段时间创建订单量趋势:

一般用折线图的形式反映某个指标的趋势情况,我们前面也讲过,在实际工作中我们一般用matplotlib或者其他可视化的库进行图表绘制,并将其进行保存,然后再利用openpyxl库将图表插入到Excel中。

先利用matplotlib库进行绘图,具体实现代码如下:

%matplotlib inline
import matplotlib.pyplot as plt
plt.rcParams["font.sans-serif"]='SimHei'#解决中文乱码

#设置图表大小
plt.figure(figsize = (10,6))
df.groupby('创建日期')['order_id'].count().plot()
plt.title('4.2 - 4.11 创建订单量分日趋势')
plt.xlabel('日期')
plt.ylabel('订单量')

#将图表保存到本地
plt.savefig(r'D:Data-Scienceshareexcel-python报表自动化.2 - 4.11 创建订单量分日趋势.png')

将保存到本地的图表插入到Excel中,具体实现代码如下:

from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
ws = wb.active

img = Image(r'D:Data-Scienceshareexcel-python报表自动化.2 - 4.11 创建订单量分日趋势.png')

ws.add_image(img, 'A1')

wb.save(r'D:Data-Scienceshareexcel-python报表自动化.2 - 4.11 创建订单量分日趋势.xlsx')

运行上面代码会得到如下结果,可以看到图表已经被成功插入到Excel中:



到这里我们的一份自动化报表的代码就完成了,以后每次需要用到这份报表的时候,把上面代码执行一遍,结果马上就可以出来。这就是为什么推荐用python来提高平时大量数据的处理效率了,又快又丰富!想了解更多,欢迎联系小陆老师哦。