精华内容
下载资源
问答
  • 从数据到excel自动化报表pdf
    2021-10-14 00:38:25

    我是小z

    今天给大家分享一篇俊红新书《对比Excel,轻松学习Python报表自动化》中关于报表自动化实战的内容,文末也会免费赠送几本新书。

    这本书是俊红新作,强烈安利!

    这篇文章将带你了解报表自动化的流程,并教你用Python实现工作中的一个报表自动化实战,篇幅较长,建议先收藏,文章具体的目录为:

    1.Excel的基本组成
    2.一份报表自动化的流程
    3.报表自动化实战
        - 当日各项指标同环比情况
        - 当日各省份创建订单量情况
        - 最近一段时间创建订单量趋势
    4.将不同的结果进行合并
        - 将不同结果合并到同一个Sheet中
        - 将不同结果合并到同一个工作簿的不同Sheet中

    Excel的基本组成

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

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

    cd7703a3c43e82da254e6827274f0d62.png

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

    8e0e5caea357fd2960498e3045d0ff18.png

    一份自动化报表的流程

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

    5f17fd5d57fbb05c3cfcff8db78fea94.png

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

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

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

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

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

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

    报表自动化实战

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

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

    645cd85f2543c4182e448368e91e8188.png

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

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

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

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

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

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

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

    #导入文件
    import pandas as pd
    df = pd.read_excel(r'D:\Data-Science\share\excel-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

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

    846b65eb2c61be5270c341d1decbdc36.png

    上面只是得到了各指标的同环比绝对数值,但是我们一般的日报在发出去之前都要做一些格式调整的,比如调整字体之类的。而格式调整就需要用到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-Science\share\excel-python报表自动化\核心指标_原始.xlsx')

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

    4045d2ae51c46badf92fc813e3ae7ec5.png

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

    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-Science\share\excel-python报表自动化\核心指标.xlsx')

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

    fb051c99f1b7121a3205865cca505d38.png

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

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

    我们同样先利用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

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

    c4073a00a88080fe5224a115940979df.png

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

    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-Science\share\excel-python报表自动化\各省份销量情况.xlsx')

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

    b82d6f107126b812d0b60946413597e3.png

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

    一般用折线图的形式反映某个指标的趋势情况,我们前面也讲过,在实际工作中我们一般用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-Science\share\excel-python报表自动化\4.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-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png')
    
    ws.add_image(img, 'A1')
    
    wb.save(r'D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.xlsx')

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

    e74115a6c75c086e796cc4783d72be39.png

    将不同的结果进行合并

    上面我们是把每一部分都单独拆开来实现,最后存储在了不同的Excel文件中。当然了,有的时候放在不同文件中会比较麻烦,我们就需要把这些结果合并在同一个Excel的相同Sheet或者不同Sheet中。

    将不同的结果合并到同一个Sheet中:

    将不同的结果合并到同一个Sheet中的难点在于不同表结果的结构不一样,而且需要在不同结果之间进行留白。

    首先插入核心指标表df_review,插入方式与单独的插入是一样的,具体代码如下:

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

    接下来就该插入各省份情况表df_province,因为append默认是从第一行开始插入的,而我们前面几行已经有df_view表的数据了,所以就不能用appen的方式进行插入,而只能通过遍历每一个单元格的方式进行插入。

    那我们怎么知道要遍历哪些单元格呢?核心需要知道遍历开始的行列和遍历结束的行列。

    遍历开始的行 = df_view表占据的行 + 留白的行(一般表与表之间留2行) + 1
    遍历结束的行 = 遍历开始的行 + df_province表占据的行
    
    遍历开始的列 = 1
    遍历结束的列 = df_province表占据的列

    而又因为DataFrame中获取列名的方式和获取具体值的方式不太一样,所以我们需要分别插入,先插入列名,具体代码如下:

    for j in range(df_province.shape[1]):
        ws.cell(row = df_view.shape[0] + 5,column = 1 + j).value = df_province.columns[r]

    df_province.shape[1]是获取df_province表有多少列,df_view.shape[0]是获取df_view表有多少行。

    前面说过,遍历开始的行是表占据的行加上留白的行再加1,一般留白的行是2,可是这里面为啥是df_view.shape[0] + 5呢?这是因为df_view.shape[0]是不包列名行的,同时在插入Excel中的时候会默认增加1行空行,所以就需要在留白行的基础上再增加2行,即2 + 2 + 1 = 5。

    因为range()函数是默认是从0开始的,而Excel中的列是从1开始的,所以column需要加1。

    上面的代码只是把df_province表的列名插入进来了,接下来插入具体的值,方式与插入列名的方式一致,只不过需要在列名的下一行开始插入,具体代码如下:

    #再把具体的值插入
    for i in range(df_province.shape[0]):
        for j in range(df_province.shape[1]):
            ws.cell(row = df_view.shape[0] + 6 + i,column = 1 + j).value = df_province.iloc[i,j]

    接下来就该插入图片了,插入图片的方式与前面单独的插入是一致的,具体代码如下:

    #插入图片
    img = Image(r'D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png')
    ws.add_image(img, 'G1')

    将所有的数据插入以后就该对这些数据进行格式设置了,因为不同表的结构不一样,所以我们没法直接批量针对所有的单元格进行格式设置,只能分范围分别进行设置,而不同范围的格式可能是一样的,所以我们先预设一些格式变量,这样后面用到的时候直接调取这些变量即可,减少代码冗余,具体代码如下:

    #格式预设
    
    #表头字体设置
    title_Font_style = Font(name = '微软雅黑',size = 12,bold = True,color = "FFFFFFFF")
    #普通内容字体设置
    plain_Font_style = Font(name = '微软雅黑',size = 12)
    Alignment_style = Alignment(horizontal = "center")
    Border_style = 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"))
    PatternFill_style = PatternFill(fill_type = 'solid',start_color='FFFF6100')

    格式预设完之后就可以对各个范围分别进行格式设置了,具体代码如下:

    #对A1至F6范围内的单元格进行设置
    for row in ws['A1':'F6']:
        for c in row:
            c.font = plain_Font_style
            c.alignment = Alignment_style
            c.border = Border_style
    
    #对第1行和第2行的单元格进行设置
    for row in ws[1:2]:
        for c in row:
            c.font = title_Font_style
            c.fill = PatternFill_style
    
    #对E列和F列的单元格进行设置
    for col in ws["E":"F"]:
        for r in col:
            r.number_format = '0.00%'
    
    #对A9至B19范围内的单元格进行设置
    for row in ws['A9':'B19']:
        for c in row:
            c.font = plain_Font_style
            c.alignment = Alignment_style
            c.border = Border_style
    
    #对A9至B9范围内的单元格进行设置
    for row in ws['A9':'B9']:
        for c in row:
            c.font = title_Font_style
            c.fill = PatternFill_style
            
    #设置进度条
    rule = DataBarRule(start_type = 'min',end_type = 'max',
                        color="FF638EC6", showValue=True, minLength=None, maxLength=None)
    ws.conditional_formatting.add('B10:B19',rule)
    
    #调整列宽
    ws.column_dimensions['A'].width = 17
    ws.column_dimensions['B'].width = 13
    ws.column_dimensions['E'].width = 10

    最后将上面所有代码片段合并在一起,就是将不同的结果文件合并到同一个Sheet中的完整代码,具体结果如下,可以看到不同结果文件合并在了一起,并且各自的格式设置完好。

    ce0ed2e4e5242b1432406188a4e08bed.png

    将不同的结果合并到同一工作簿的不同Sheet中:

    将不同的结果合并到同一工作簿的不同Sheet中比较好实现,只需要新建几个Sheet,然后针对不同的Sheet插入数据即可,具体实现代码如下:

    from openpyxl import Workbook
    from openpyxl.utils.dataframe import dataframe_to_rows
    
    wb = Workbook()
    ws = wb.active
    
    ws1 = wb.create_sheet()
    ws2 = wb.create_sheet()
    
    #更改sheet的名称
    ws.title = "核心指标" 
    ws1.title = "各省份销情况" 
    ws2.title = "分日趋势" 
    
    for r1 in dataframe_to_rows(df_view,index = True,header = True):
        ws.append(r1)
    
    for r2 in dataframe_to_rows(df_province,index = False,header = True):
        ws1.append(r2)
    
    img = Image(r'D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png')
    
    ws2.add_image(img, 'A1')
    
    wb.save(r'D:\Data-Science\share\excel-python报表自动化\多结果合并_多Sheet.xlsx')

    运行上面代码,会得到如下结果,可以看到创建了3个Sheet,且不同的内容保存到了不同Sheet中:

    ffe3f6d927130b1b83dd2ad66918dc06.png

    到这里我们的一份自动化报表的代码就完成了,以后每次需要用到这份报表的时候,把上面代码执行一遍,结果马上就可以出来,当然了也可以设置定时执行,到时间结果就自动发送到你邮箱里面啦。


    本篇内容经授权摘取自《对比Excel,轻松学习Python报表自动化》书籍,该书为《对比Excel,轻松学习Python数据分析》的拓展版,通过对比Excel的方式来讲解报表自动化过程中涉及到的每个功能对应的Python代码应该如何实现,让你轻松掌握报表自动化,提高工作效率。感兴趣的同学可以长按识别下方二维码进行了解:

    4bbbf0dd22ccc5c1668c97f176aaed46.png


    最后还是老规矩,免费送大家5本,在下方进行留言,会挑选点赞前5名的粉丝赠送,2021年10月15日21:00开奖~

    更多相关内容
  • Power query基础教程配套教程,全部都是一个个例子,配合视频看效果更佳。
  • 我是小z今天给大家分享一篇俊红新书《对比Excel,轻松学习Python报表自动化》中关于报表自动化实战的内容,文末也会免费赠送几本新书。这本书是俊红新作,强烈安利!这篇文章将带你了解报表自动化的流程,并教你用...

    来自小z
    欢迎关注 ,专注Python、数据分析、数据挖掘、好玩工具!

    这篇文章将带你了解报表自动化的流程,并教你用 Python 实现工作中的一个报表自动化实战,篇幅较长,建议先收藏,喜欢点赞支持,文末提供技术交流群,文章具体的目录为:

    1.Excel的基本组成
    2.一份报表自动化的流程
    3.报表自动化实战
    - 当日各项指标同环比情况

    • 当日各省份创建订单量情况
    • 最近一段时间创建订单量趋势
      4.将不同的结果进行合并
    • 将不同结果合并到同一个Sheet中
    • 将不同结果合并到同一个工作簿的不同Sheet中

    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-Science\share\excel-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-Science\share\excel-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-Science\share\excel-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-Science\share\excel-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-Science\share\excel-python报表自动化\4.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-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png')
    
    ws.add_image(img, 'A1')
    
    wb.save(r'D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.xlsx')
    

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

    图片

    将不同的结果进行合并

    上面我们是把每一部分都单独拆开来实现,最后存储在了不同的Excel文件中。当然了,有的时候放在不同文件中会比较麻烦,我们就需要把这些结果合并在同一个Excel的相同Sheet或者不同Sheet中。

    将不同的结果合并到同一个Sheet中:

    将不同的结果合并到同一个Sheet中的难点在于不同表结果的结构不一样,而且需要在不同结果之间进行留白。

    首先插入核心指标表df_review,插入方式与单独的插入是一样的,具体代码如下:

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

    接下来就该插入各省份情况表df_province,因为append默认是从第一行开始插入的,而我们前面几行已经有df_view表的数据了,所以就不能用appen的方式进行插入,而只能通过遍历每一个单元格的方式进行插入。

    那我们怎么知道要遍历哪些单元格呢?核心需要知道遍历开始的行列和遍历结束的行列。

    遍历开始的行 = df_view表占据的行 + 留白的行(一般表与表之间留2) + 1
    遍历结束的行 = 遍历开始的行 + df_province表占据的行
    
    遍历开始的列 = 1
    遍历结束的列 = df_province表占据的列
    

    而又因为DataFrame中获取列名的方式和获取具体值的方式不太一样,所以我们需要分别插入,先插入列名,具体代码如下:

    for j in range(df_province.shape[1]):
        ws.cell(row = df_view.shape[0] + 5,column = 1 + j).value = df_province.columns[r]
    

    df_province.shape[1]是获取df_province表有多少列,df_view.shape[0]是获取df_view表有多少行。

    前面说过,遍历开始的行是表占据的行加上留白的行再加1,一般留白的行是2,可是这里面为啥是df_view.shape[0] + 5呢?这是因为df_view.shape[0]是不包列名行的,同时在插入Excel中的时候会默认增加1行空行,所以就需要在留白行的基础上再增加2行,即2 + 2 + 1 = 5。

    因为range()函数是默认是从0开始的,而Excel中的列是从1开始的,所以column需要加1。

    上面的代码只是把df_province表的列名插入进来了,接下来插入具体的值,方式与插入列名的方式一致,只不过需要在列名的下一行开始插入,具体代码如下:

    #再把具体的值插入
    for i in range(df_province.shape[0]):
        for j in range(df_province.shape[1]):
            ws.cell(row = df_view.shape[0] + 6 + i,column = 1 + j).value = df_province.iloc[i,j]
    
    

    接下来就该插入图片了,插入图片的方式与前面单独的插入是一致的,具体代码如下:

    #插入图片
    img = Image(r'D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png')
    ws.add_image(img, 'G1')
    
    

    将所有的数据插入以后就该对这些数据进行格式设置了,因为不同表的结构不一样,所以我们没法直接批量针对所有的单元格进行格式设置,只能分范围分别进行设置,而不同范围的格式可能是一样的,所以我们先预设一些格式变量,这样后面用到的时候直接调取这些变量即可,减少代码冗余,具体代码如下:

    #格式预设
    
    #表头字体设置
    title_Font_style = Font(name = '微软雅黑',size = 12,bold = True,color = "FFFFFFFF")
    #普通内容字体设置
    plain_Font_style = Font(name = '微软雅黑',size = 12)
    Alignment_style = Alignment(horizontal = "center")
    Border_style = 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"))
    PatternFill_style = PatternFill(fill_type = 'solid',start_color='FFFF6100')
    
    

    格式预设完之后就可以对各个范围分别进行格式设置了,具体代码如下:

    #对A1至F6范围内的单元格进行设置
    for row in ws['A1':'F6']:
        for c in row:
            c.font = plain_Font_style
            c.alignment = Alignment_style
            c.border = Border_style
    
    #对第1行和第2行的单元格进行设置
    for row in ws[1:2]:
        for c in row:
            c.font = title_Font_style
            c.fill = PatternFill_style
    
    #对E列和F列的单元格进行设置
    for col in ws["E":"F"]:
        for r in col:
            r.number_format = '0.00%'
    
    #对A9至B19范围内的单元格进行设置
    for row in ws['A9':'B19']:
        for c in row:
            c.font = plain_Font_style
            c.alignment = Alignment_style
            c.border = Border_style
    
    #对A9至B9范围内的单元格进行设置
    for row in ws['A9':'B9']:
        for c in row:
            c.font = title_Font_style
            c.fill = PatternFill_style
            
    #设置进度条
    rule = DataBarRule(start_type = 'min',end_type = 'max',
                        color="FF638EC6", showValue=True, minLength=None, maxLength=None)
    ws.conditional_formatting.add('B10:B19',rule)
    
    #调整列宽
    ws.column_dimensions['A'].width = 17
    ws.column_dimensions['B'].width = 13
    ws.column_dimensions['E'].width = 10
    

    最后将上面所有代码片段合并在一起,就是将不同的结果文件合并到同一个Sheet中的完整代码,具体结果如下,可以看到不同结果文件合并在了一起,并且各自的格式设置完好。

    图片

    将不同的结果合并到同一工作簿的不同Sheet中:

    将不同的结果合并到同一工作簿的不同Sheet中比较好实现,只需要新建几个Sheet,然后针对不同的Sheet插入数据即可,具体实现代码如下:

    from openpyxl import Workbook
    from openpyxl.utils.dataframe import dataframe_to_rows
    
    wb = Workbook()
    ws = wb.active
    
    ws1 = wb.create_sheet()
    ws2 = wb.create_sheet()
    
    #更改sheet的名称
    ws.title = "核心指标" 
    ws1.title = "各省份销情况" 
    ws2.title = "分日趋势" 
    
    for r1 in dataframe_to_rows(df_view,index = True,header = True):
        ws.append(r1)
    
    for r2 in dataframe_to_rows(df_province,index = False,header = True):
        ws1.append(r2)
    
    img = Image(r'D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png')
    
    ws2.add_image(img, 'A1')
    
    wb.save(r'D:\Data-Science\share\excel-python报表自动化\多结果合并_多Sheet.xlsx')
    

    运行上面代码,会得到如下结果,可以看到创建了3个Sheet,且不同的内容保存到了不同Sheet中:

    图片

    到这里我们的一份自动化报表的代码就完成了,以后每次需要用到这份报表的时候,把上面代码执行一遍,结果马上就可以出来,当然了也可以设置定时执行,到时间结果就自动发送到你邮箱里面啦。

    本文版权归原作者所有,如有内容版权等问题请联系我,本文仅供交流学习使用


    技术交流

    欢迎转载、收藏、有所收获点赞支持一下!

    在这里插入图片描述

    目前开通了技术交流群,群友已超过2000人,添加时最好的备注方式为:来源+兴趣方向,方便找到志同道合的朋友

    • 方式①、发送如下图片至微信,长按识别,后台回复:加群;
    • 方式②、添加微信号:dkl88191,备注:来自CSDN
    • 方式③、微信搜索公众号:Python学习与数据挖掘,后台回复:加群

    长按关注

    展开全文
  • python实实现现报报表表自自动动化化详详解解 这篇文章主要介绍了python实现报表自动化详解涉及python读写excelxlwt常用功能xlutils 常用功能xlwt 写Excel时公式的应用等相关内容具有一定 考价值需要的朋友可以了解...
  • 今天给大家分享一篇俊红新书《对比Excel,轻松学习Python报表自动化》中关于报表自动化实战的内容,文末包邮赠送4本新书。本篇文章将带你了解报表自动化的流程,并教你用Python实现工...

    今天给大家分享一篇俊红新书《对比Excel,轻松学习Python报表自动化》中关于报表自动化实战的内容,文末包邮赠送4本新书

    本篇文章将带你了解报表自动化的流程,并教你用Python实现工作中的一个报表自动化实战,篇幅较长,建议先收藏,文章具体的目录为:

    1.Excel的基本组成
    2.一份报表自动化的流程
    3.报表自动化实战
        - 当日各项指标同环比情况
        - 当日各省份创建订单量情况
        - 最近一段时间创建订单量趋势
    4.将不同的结果进行合并
        - 将不同结果合并到同一个Sheet中
        - 将不同结果合并到同一个工作簿的不同Sheet中

    Excel的基本组成

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

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

    c569b0e91199f863fe33d5b4d43d6248.png

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

    3cc35258da1b754a9cb2646ae87a70b9.png

    一份自动化报表的流程

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

    e6f15f197062260fbbcfe11154c9617b.png

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

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

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

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

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

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

    报表自动化实战

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

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

    34abebfeeab310d8d340de15ab65c426.png

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

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

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

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

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

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

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

    #导入文件
    import pandas as pd
    df = pd.read_excel(r'D:\Data-Science\share\excel-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

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

    fc7094550659a33b3faf2261d8550b44.png

    上面只是得到了各指标的同环比绝对数值,但是我们一般的日报在发出去之前都要做一些格式调整的,比如调整字体之类的。而格式调整就需要用到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-Science\share\excel-python报表自动化\核心指标_原始.xlsx')

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

    82165798f7489d59f7c73dc60705aaa3.png

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

    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-Science\share\excel-python报表自动化\核心指标.xlsx')

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

    1e0e978cf2794748420b5e976f400ab2.png

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

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

    我们同样先利用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

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

    437cec108b027658dfb3699cbba44663.png

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

    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-Science\share\excel-python报表自动化\各省份销量情况.xlsx')

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

    b181c9e89f317512e251518b7a229cac.png

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

    一般用折线图的形式反映某个指标的趋势情况,我们前面也讲过,在实际工作中我们一般用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-Science\share\excel-python报表自动化\4.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-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png')
    
    ws.add_image(img, 'A1')
    
    wb.save(r'D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.xlsx')

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

    a97fbb8ef94370db5352cf85d227793d.png

    将不同的结果进行合并

    上面我们是把每一部分都单独拆开来实现,最后存储在了不同的Excel文件中。当然了,有的时候放在不同文件中会比较麻烦,我们就需要把这些结果合并在同一个Excel的相同Sheet或者不同Sheet中。

    将不同的结果合并到同一个Sheet中:

    将不同的结果合并到同一个Sheet中的难点在于不同表结果的结构不一样,而且需要在不同结果之间进行留白。

    首先插入核心指标表df_review,插入方式与单独的插入是一样的,具体代码如下:

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

    接下来就该插入各省份情况表df_province,因为append默认是从第一行开始插入的,而我们前面几行已经有df_view表的数据了,所以就不能用appen的方式进行插入,而只能通过遍历每一个单元格的方式进行插入。

    那我们怎么知道要遍历哪些单元格呢?核心需要知道遍历开始的行列和遍历结束的行列。

    遍历开始的行 = df_view表占据的行 + 留白的行(一般表与表之间留2行) + 1
    遍历结束的行 = 遍历开始的行 + df_province表占据的行
    
    遍历开始的列 = 1
    遍历结束的列 = df_province表占据的列

    而又因为DataFrame中获取列名的方式和获取具体值的方式不太一样,所以我们需要分别插入,先插入列名,具体代码如下:

    for j in range(df_province.shape[1]):
        ws.cell(row = df_view.shape[0] + 5,column = 1 + j).value = df_province.columns[r]

    df_province.shape[1]是获取df_province表有多少列,df_view.shape[0]是获取df_view表有多少行。

    前面说过,遍历开始的行是表占据的行加上留白的行再加1,一般留白的行是2,可是这里面为啥是df_view.shape[0] + 5呢?这是因为df_view.shape[0]是不包列名行的,同时在插入Excel中的时候会默认增加1行空行,所以就需要在留白行的基础上再增加2行,即2 + 2 + 1 = 5。

    因为range()函数是默认是从0开始的,而Excel中的列是从1开始的,所以column需要加1。

    上面的代码只是把df_province表的列名插入进来了,接下来插入具体的值,方式与插入列名的方式一致,只不过需要在列名的下一行开始插入,具体代码如下:

    #再把具体的值插入
    for i in range(df_province.shape[0]):
        for j in range(df_province.shape[1]):
            ws.cell(row = df_view.shape[0] + 6 + i,column = 1 + j).value = df_province.iloc[i,j]

    接下来就该插入图片了,插入图片的方式与前面单独的插入是一致的,具体代码如下:

    #插入图片
    img = Image(r'D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png')
    ws.add_image(img, 'G1')

    将所有的数据插入以后就该对这些数据进行格式设置了,因为不同表的结构不一样,所以我们没法直接批量针对所有的单元格进行格式设置,只能分范围分别进行设置,而不同范围的格式可能是一样的,所以我们先预设一些格式变量,这样后面用到的时候直接调取这些变量即可,减少代码冗余,具体代码如下:

    #格式预设
    
    #表头字体设置
    title_Font_style = Font(name = '微软雅黑',size = 12,bold = True,color = "FFFFFFFF")
    #普通内容字体设置
    plain_Font_style = Font(name = '微软雅黑',size = 12)
    Alignment_style = Alignment(horizontal = "center")
    Border_style = 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"))
    PatternFill_style = PatternFill(fill_type = 'solid',start_color='FFFF6100')

    格式预设完之后就可以对各个范围分别进行格式设置了,具体代码如下:

    #对A1至F6范围内的单元格进行设置
    for row in ws['A1':'F6']:
        for c in row:
            c.font = plain_Font_style
            c.alignment = Alignment_style
            c.border = Border_style
    
    #对第1行和第2行的单元格进行设置
    for row in ws[1:2]:
        for c in row:
            c.font = title_Font_style
            c.fill = PatternFill_style
    
    #对E列和F列的单元格进行设置
    for col in ws["E":"F"]:
        for r in col:
            r.number_format = '0.00%'
    
    #对A9至B19范围内的单元格进行设置
    for row in ws['A9':'B19']:
        for c in row:
            c.font = plain_Font_style
            c.alignment = Alignment_style
            c.border = Border_style
    
    #对A9至B9范围内的单元格进行设置
    for row in ws['A9':'B9']:
        for c in row:
            c.font = title_Font_style
            c.fill = PatternFill_style
            
    #设置进度条
    rule = DataBarRule(start_type = 'min',end_type = 'max',
                        color="FF638EC6", showValue=True, minLength=None, maxLength=None)
    ws.conditional_formatting.add('B10:B19',rule)
    
    #调整列宽
    ws.column_dimensions['A'].width = 17
    ws.column_dimensions['B'].width = 13
    ws.column_dimensions['E'].width = 10

    最后将上面所有代码片段合并在一起,就是将不同的结果文件合并到同一个Sheet中的完整代码,具体结果如下,可以看到不同结果文件合并在了一起,并且各自的格式设置完好。

    6321811cdd23033b67dedf2c9ae20fe6.png

    将不同的结果合并到同一工作簿的不同Sheet中:

    将不同的结果合并到同一工作簿的不同Sheet中比较好实现,只需要新建几个Sheet,然后针对不同的Sheet插入数据即可,具体实现代码如下:

    from openpyxl import Workbook
    from openpyxl.utils.dataframe import dataframe_to_rows
    
    wb = Workbook()
    ws = wb.active
    
    ws1 = wb.create_sheet()
    ws2 = wb.create_sheet()
    
    #更改sheet的名称
    ws.title = "核心指标" 
    ws1.title = "各省份销情况" 
    ws2.title = "分日趋势" 
    
    for r1 in dataframe_to_rows(df_view,index = True,header = True):
        ws.append(r1)
    
    for r2 in dataframe_to_rows(df_province,index = False,header = True):
        ws1.append(r2)
    
    img = Image(r'D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png')
    
    ws2.add_image(img, 'A1')
    
    wb.save(r'D:\Data-Science\share\excel-python报表自动化\多结果合并_多Sheet.xlsx')

    运行上面代码,会得到如下结果,可以看到创建了3个Sheet,且不同的内容保存到了不同Sheet中:

    717811d0c337e3dfac37439dc2b7765c.png

    到这里我们的一份自动化报表的代码就完成了,以后每次需要用到这份报表的时候,把上面代码执行一遍,结果马上就可以出来,当然了也可以设置定时执行,到时间结果就自动发送到你邮箱里面啦。


    本篇内容经授权摘取自《对比Excel,轻松学习Python报表自动化》书籍,该书为《对比Excel,轻松学习Python数据分析》的拓展版,通过对比Excel的方式来讲解报表自动化过程中涉及到的每个功能对应的Python代码应该如何实现,让你轻松掌握报表自动化,提高工作效率。感兴趣的同学可以长按识别下方二维码进行了解:

    1f9280c702ff764393b958af58392db6.png


    最后包邮送大家4本书,送书方法如下,截止日期明晚22点。

    第一,长按下方二维码,关注我的视频号:程序员zhenguo;

    第二,在置顶的第一条视频(1分钟手把手教你从零搭建Python环境)下留言,说说你对Python报表自动化的了解、实践等,我会从中挑选。

    ce992156e6581ae51372fbdfda0dccb1.png

    点击阅读原文,了解此书详情

    展开全文
  • 相关文章: Python自动化办公--Pandas玩转Excel【一】 ...python处理Excel实现自动化办公教学(数据筛选、公式操作、单元格拆分合并、冻结窗口、图表绘制等)【三】 python入门之后须掌握的知识点(模块化编程、时间.

     相关文章和数据源:

    Python自动化办公--Pandas玩转Excel【一】

    Python自动化办公--Pandas玩转Excel数据分析【二】

    Python自动化办公--Pandas玩转Excel数据分析【三】


    python处理Excel实现自动化办公教学(含实战)【一】

    python处理Excel实现自动化办公教学(含实战)【二】

    python处理Excel实现自动化办公教学(数据筛选、公式操作、单元格拆分合并、冻结窗口、图表绘制等)【三】


    python入门之后须掌握的知识点(模块化编程、时间模块)【一】

    python入门之后须掌握的知识点(excel文件处理+邮件发送+实战:批量化发工资条)【二】


    spandas玩转excel码源.zip-数据挖掘文档类资源-CSDN下载

    Python自动化办公(2021最新版!有源代码,).zip-

    Python自动化办公(可能是B站内容最全的!有源代码,).zip-

    上面是对应码源,图方便的话可以直接下载都分类分好了,当然也可以看文章。


    1.知识巩固excel

    这个系列文章主要以实例为主,学会每个demo,活学活用,办公效率提升飞快!

    1.1 xlrd+xlwt读写excel

    #安装命令:
    pip install xlrd
    
    pip install xlwt

    我们在读取文件的时候,excel的列是字母我们不容易直观看出来是第几列,下面对excel进行设置。

     操作如下

    import xlrd
    # 打开excel
    xlsx = xlrd.open_workbook('7月新.xls')
    
    sheet = xlsx.sheet_by_index(0)
    data = sheet.cell_value(5, 1)
    print(data)
    
    # for i in xlsx.sheet_names():
    #     print(i)
        # table = xlsx.sheet_by_name(i)
        # print(table.cell_value(3, 3))
    # 通过sheet名查找:xlsx.sheet_by_name("7月下旬入库表")
    # 通过索引查找:xlsx.sheet_by_index(3)
    # print(table.cell_value(0, 0))
    
    #单元格读取方式
    print(sheet.cell_value(1, 2))
    print(sheet.cell(0, 0).value)
    print(sheet.row(0)[0].value)
    
    
    # for i in range(0, xlsx.nsheets):
    #     sheet = xlsx.sheet_by_index(i)
    #     print(sheet.name)
        # print(sheet.cell_value(0, 0))
    #
    # # 获取所有sheet名字:xlsx.sheet_names()
    # # 获取sheet数量:xlsx.nsheets
    #
    # for i in xlsx.sheet_names():
    #     print(i)
        # table = xlsx.sheet_by_name(i)
        # print(table.cell_value(3, 3))
    import xlwt
    
    # 新建工作簿
    new_workbook = xlwt.Workbook()
    # 新建sheet
    worksheet = new_workbook.add_sheet('new_test')
    # 新建单元格,并写入内容-行列
    worksheet.write(1, 2, 'test')
    # 保存
    new_workbook.save('test.xls')

     

     1.2  xlutils设置格式

    xlutils整体没有pandas设置来的方便

    from xlutils.copy import copy
    import xlrd
    import xlwt
    
    # 安装:pip install xlutils
    
    tem_excel = xlrd.open_workbook('日统计.xls', formatting_info=True) #格式信息打开
    tem_sheet = tem_excel.sheet_by_index(0)
    
    new_excel = copy(tem_excel)
    new_sheet = new_excel.get_sheet(0)
    
    style = xlwt.XFStyle()
    
    # 字体
    font = xlwt.Font()
    font.name = '微软雅黑'
    font.bold = True
    # 18*20
    font.height = 360
    style.font = font
    
    # 边框:细线==THIN
    borders = xlwt.Borders()
    borders.top = xlwt.Borders.THIN
    borders.bottom = xlwt.Borders.THIN
    borders.left = xlwt.Borders.THIN
    borders.right = xlwt.Borders.THIN
    style.borders = borders
    
    # 对齐
    alignment = xlwt.Alignment()
    alignment.horz = xlwt.Alignment.HORZ_CENTER
    alignment.vert = xlwt.Alignment.VERT_CENTER
    style.alignment = alignment
    
    
    new_sheet.write(2, 1, 12)
    new_sheet.write(3, 1, 18)
    new_sheet.write(4, 1, 19)
    new_sheet.write(5, 1, 15)
    
    
    # new_sheet.write(2, 1, 12, style)
    # new_sheet.write(3, 1, 18, style)
    # new_sheet.write(4, 1, 19, style)
    # new_sheet.write(5, 1, 15, style)
    
    new_excel.save('填写.xls')
    

     

     1.3 自动生成统计报表【案例一:分数统计】

    
    import xlrd
    
    import xlwt
    
    # 读取excel文件
    xlsx = xlrd.open_workbook('三年二班(各科成绩单).xls')
    # 选择指定sheet
    sheet = xlsx.sheet_by_index(0)
    
    all_data = []
    # 统计共有多少学生,并去重
    num_set = set()
    for row_i in range(1, sheet.nrows):
        num = sheet.cell_value(row_i, 0)
        name = sheet.cell_value(row_i, 1)
        grade = sheet.cell_value(row_i, 3)
    
        student = {
            'num': num,
            'name': name,
            'grade': grade,
        }
        all_data.append(student)
        num_set.add(num)
    # print(all_data)
    # print(len(all_data))
    # print(len(num_set))
    
    # 计算总分
    sum_list = []
    for num in num_set:
        name = ''
        sum = 0
        for student in all_data:
            # print(student['num'])
            # print(num)
            if num == student['num']:
                sum += student['grade']
                name = student['name']
        sum_stu = {
            'num': num,
            'name': name,
            'sum': sum
        }
        sum_list.append(sum_stu)
    print(sum_list)
    
    # 写入新的excel
    
    # 新建工作簿
    new_workbook = xlwt.Workbook()
    # 新建sheet
    worksheet = new_workbook.add_sheet('2班')
    # 新建单元格,并写入内容
    # 写入第一列的内容
    worksheet.write(0, 0, '学号')
    worksheet.write(0, 1, '姓名')
    worksheet.write(0, 2, '总分')
    # 自动写入后面的内容
    for row in range(0,len(sum_list)):
        worksheet.write(row+1,0,sum_list[row]['num'])
        worksheet.write(row+1,1,sum_list[row]['name'])
        worksheet.write(row+1,2,sum_list[row]['sum'])
    # 保存
    new_workbook.save('2班学生总分.xls')
    

     1.4 xlsxwriter 和openpyxl

    xlwt会遇到不支持超过列256的表格

    # import xlwt
    #
    # workbook = xlwt.Workbook()
    # sheet0 = workbook.add_sheet('sheet0')
    # for i in range(0,300):
    #     sheet0.write(0,i,i)
    # workbook.save('num.xls')
    
    # 不带格式
    import xlsxwriter as xw
    workbook = xw.Workbook('number.xlsx')
    sheet0 = workbook.add_worksheet('sheet0')
    for i in range(0,300):
        sheet0.write(0,i,i)
    workbook.close()
    
    
    # 性能不稳定
    import openpyxl
    workbook = openpyxl.load_workbook('number.xlsx')
    sheet0 = workbook['sheet0']
    sheet0['B3']= '2'
    sheet0['C2']= '4'
    sheet0['D7']= '3'
    workbook.save('num_open.xlsx')

    1.5 xlwings和pandas

    pandas在第一期已经详细讲解过,参考相关文章

    Python用来处理Excel的全部可用库,以及它们的优缺点

    xlwings具有以下优点:

    - xlwings能够非常方便的读写Excel文件中的数据,并且能够进行单元格格式的修改
    - 可以和matplotlib以及pandas无缝连接
    - 可以调用Excel文件中VBA写好的程序,也可以让VBA调用用Python写的程序。
    - 开源免费,一直在更新

    #### 1、打开/新建Excel文档
    
    ```python
    import xlwings as xw
    wb = xw.Book()  # 新建一个文档
    wb = xw.Book('test.xlsx')  # 打开一个已有的文档
    ```
    
    #### 2、读取/写入数据
    
    ```python
    sht = wb.sheets['Sheet1'] # 找到指定sheet
    print(sht.range('A1').value) # 读取指定单元格的数据,这里读的是A1
    sht.range('B1').value = 10 # 给指定的空白单元格赋值,这里赋值的是B1
    ```
    
    #### 3、保存文件、退出程序
    
    ```python
    wb.save(r'test.xlsx') #保存Excel文档,命名为test.xlsx,并保存在D盘
    wb.close() # 退出程序,该步骤不可省略
    ```
    
    #### 4、连接pandas处理复杂数据
    
    ```python
    import pandas as pd
    df = pd.DataFrame([[1,2], [3,4]], columns=['a', 'b'])
    sht.range('A1').value = df
    sht.range('A1').options(pd.DataFrame, expand='table').value
    ```
    
    #### 5、连接**Matplotlib** 画图
    
    ```python
    import matplotlib.pyplot as plt
    fig = plt.figure()
    plt.plot([1, 2, 3, 4, 5])
    sht.pictures.add(fig, name='MyPlot', update=True)

    1.6 案例实战---把文件名整理到excel中

    import os
    import xlwt
    
    # 目标文件夹
    file_path = 'd:/'
    # 取出目标文件夹下的文件名
    os.listdir(file_path)
    
    new_workbook = xlwt.Workbook()
    sheet = new_workbook.add_sheet('new_dir')
    
    n = 0
    for i in os.listdir(file_path):
        sheet.write(n,0,i)
        n+=1
    new_workbook.save('dir.xls')

    1.7  案例实战---excel填充画

    把一幅画导入到excel中用每个单元格背景色生成原画

    # coding: utf-8
    
    from PIL import Image
    from xlsxwriter.workbook import Workbook
    
    
    class ExcelPicture(object):
        FORMAT_CONSTRAINT = 65536
    
        def __init__(self, pic_file, ratio=0.5):
            self.__pic_file = pic_file
    
            self.__ratio = ratio
            self.__zoomed_out = False
    
            self.__formats = dict()
    
        # 缩小图片
        def zoom_out(self, _img):
            _size = _img.size
            _img.thumbnail((int(_img.size[0] * self.__ratio), int(_img.size[1] * self.__ratio)))
    
            self.__zoomed_out = True
    
        # 对颜色进行圆整
        def round_rgb(self, rgb, model):
            return tuple([int(round(x / model) * model) for x in rgb])
    
        # 查找颜色样式,去重
        def get_format(self, color):
            _format = self.__formats.get(color, None)
    
            if _format is None:
                _format = self.__wb.add_format({'bg_color': color})
                self.__formats[color] = _format
    
            return _format
    
        # 操作流程
        def process(self, output_file='_pic.xlsx', color_rounding=False, color_rounding_model=5.0):
            # 创建xlsx文件,并调整行列属性
            self.__wb = Workbook(output_file)
            self.__sht = self.__wb.add_worksheet()
            self.__sht.set_default_row(height=9)
            self.__sht.set_column(0, 5000, width=1)
    
            # 打开需要进行转换的图片
            _img = Image.open(self.__pic_file)
            print('Picture filename:', self.__pic_file)
    
            # 判断是否需要缩小图片尺寸
            if self.__ratio < 1:
                self.zoom_out(_img)
    
            # 遍历每一个像素点,并填充对应的颜色到对应的Excel单元格
            _size = _img.size
            print('Picture size:', _size)
            for (x, y) in [(x, y) for x in range(_size[0]) for y in range(_size[1])]:
                _clr = _img.getpixel((x, y))
    
                # 如果颜色种类过多,则需要将颜色圆整到近似的颜色上,以减少颜色种类
                if color_rounding: _clr = self.round_rgb(_clr, color_rounding_model)
    
                _color = '#%02X%02X%02X' % _clr
                self.__sht.write(y, x, '', self.get_format(_color))
    
            self.__wb.close()
    
            # 检查颜色样式种类是否超出限制,Excel2007对样式数量有最大限制
            format_size = len(self.__formats.keys())
            if format_size >= ExcelPicture.FORMAT_CONSTRAINT:
                print('Failed! Color size overflow: %s.' % format_size)
            else:
                print('Success!')
                print('Color: %s' % format_size)
                print('Color_rounding:', color_rounding)
                if color_rounding:
                    print('Color_rounding_model:', color_rounding_model)
    
    
    if __name__ == '__main__':
        r = ExcelPicture('test.jpg', ratio=0.5)
        r.process('0407.xlsx', color_rounding=True, color_rounding_model=5.0)
    # 同级目录
    

    效果如下:

    原图

    下面可以看到有一点糊是因为填充单元格可以当作像素点来考虑

     

     2.自动化处理word

    安装库

    >pip install python-docx

    - python-docx
        - 说明文档:https://python-docx.readthedocs.io/en/latest/#api-documentation

    2.1 自动生成word【批量化写模板文档】

    from docx import Document
    from docx.enum.text import WD_ALIGN_PARAGRAPH
    from docx.shared import Pt
    from docx.oxml.ns import qn
    
    import time
    
    price = input('请输入工资调整金额:')
    # 全体员工姓名
    company_list = ['员工1', '员工1', '员工2', '员工3', '员工4',
                    '员工5', '员工6', '员工7', '员工8', '员工9', '员工10']
    # word和excel可以结合
    
    # excel学习:for
    # 当天的日期!
    today = time.strftime("%Y{y}%m{m}%d{d}", time.localtime()
                          ).format(y='年', m='月', d='日')
    
    for i in company_list:
        document = Document()
        # 设置文档的基础字体
        document.styles['Normal'].font.name = u'宋体'
        # 识别中文
        document.styles['Normal'].element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
    
        # 建立一个自然段
        p1 = document.add_paragraph()
        # 对齐方式为居中,没有这句的话默认左对齐
        p1.alignment = WD_ALIGN_PARAGRAPH.CENTER
        run1 = p1.add_run('关于%s工资调整的通知' % (today))
        run1.font.name = '微软雅黑'
        run1.element.rPr.rFonts.set(qn('w:eastAsia'), u'微软雅黑')
        run1.font.size = Pt(21)
        run1.font.bold = True
        p1.space_after = Pt(5)
        p1.space_before = Pt(5)
    
        p2 = document.add_paragraph()
        run2 = p2.add_run(i + ':')
        run2.font.name = '宋体'
        run2.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
        run2.font.size = Pt(16)
        run2.font.bold = True
    
        p3 = document.add_paragraph()
        run3 = p3.add_run('因为疫情影响,我们很抱歉的通知您,您的工资调整为每月%s元,特此通知' % price)
        run3.font.name = '宋体'
        run3.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
        run3.font.size = Pt(14)
    
        p4 = document.add_paragraph()
        p4.alignment = WD_ALIGN_PARAGRAPH.RIGHT
        run4 = p4.add_run('人事:王小姐 电话:686868')
        run4.font.name = '宋体'
        run4.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
        run4.font.size = Pt(14)
        run4.font.bold = True
    
        document.save('%s-工资调整通知.docx' % i)
    

    其中在读取员工信息这块可以和excel结合。

     

    2.2 批量化写模板文档【并添加图片和表格】

    新的要求,要求小杨在通知函上方加上图片红头,价格数据以表格形式展示。并在第二页加
    上广告【插入分页符】。

    效果如下:

    from docx import Document
    from docx.enum.text import WD_ALIGN_PARAGRAPH
    from docx.shared import Pt
    from docx.oxml.ns import qn
    from docx.shared import Inches
    
    import time
    
    price = input('请输入工资调整金额:')
    company_list = ['员工1', '员工1', '员工2', '员工3', '员工4',
                    '员工5', '员工6', '员工7', '员工8', '员工9', '员工10', ]
    today = time.strftime("%Y{y}%m{m}%d{d}", time.localtime()
                          ).format(y='年', m='月', d='日')
    for i in company_list:
        document = Document()
        # 设置文档的基础字体
        document.styles['Normal'].font.name = u'宋体'
        document.styles['Normal'].element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
    
        # 红头文件加入文件
        document.add_picture('title002.jpg', width=Inches(6))
    
        # 建立一个自然段
        p1 = document.add_paragraph()
        # 对齐方式为居中,没有这句的话默认左对齐
        p1.alignment = WD_ALIGN_PARAGRAPH.CENTER
        run1 = p1.add_run('关于%s工资调整的通知' % (today))
        run1.font.name = '微软雅黑'
        run1.element.rPr.rFonts.set(qn('w:eastAsia'), u'微软雅黑')
        run1.font.size = Pt(21)
        run1.font.bold = True
        p1.space_after = Pt(5)
        p1.space_before = Pt(5)
    
        p2 = document.add_paragraph()
        run2 = p2.add_run(i + ':')
        run2.font.name = '宋体'
        run2.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
        run2.font.size = Pt(16)
        run2.font.bold = True
    
        p3 = document.add_paragraph()
        run3 = p3.add_run('因为疫情影响,我们很抱歉的通知您,您的工资调整为每月%s元,特此通知。' % price)
        run3.font.name = '宋体'
        run3.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
        run3.font.size = Pt(14)
        
        #添加表格
        table = document.add_table(rows=2, cols=2, style='Table Grid')#默认格式
        # 合并单元格
        table.cell(0, 0).merge(table.cell(0, 1)) #坐上合并到右下
        table_run1 = table.cell(0, 0).paragraphs[0].add_run('签名栏')
        table_run1.font.name = '黑体'
        table_run1.element.rPr.rFonts.set(qn('w:eastAsia'), u'黑体')
        table.cell(0, 0).paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
        table.cell(1, 0).text = i
        table.cell(1, 0).paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
        
       
        p4 = document.add_paragraph()
        p4.alignment = WD_ALIGN_PARAGRAPH.RIGHT
    
        run4 = p4.add_run('人事:王小姐 电话:686868')
        run4.font.name = '宋体'
        run4.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
        run4.font.size = Pt(14)
        run4.font.bold = True
        
            #插入分页符
        document.add_page_break()
        p5= document.add_paragraph()
        run4=p5.add_run("此处是广告")
    
        document.save('%s-工资调整通知.docx' % i)
    

    2.3 读取word文档【 文字+表格混合文档:

    from docx import Document
    
    document = Document('pure.docx')
    all_paragraphs = document.paragraphs
    for p in all_paragraphs:
        print(p.text)
    
    
    # excel写入

     如果word里是表格呈现读取如下:

     文字+表格形式:

    word基本格式问题:把word重名后缀为zip的文件,打开看到里面有xml的格式文件

     导入zipfile库解压文件,设置格式只读取我们需要的文字:进行组合。

    import zipfile
    
    word_book = zipfile.ZipFile('word_table.docx')
    xml = word_book.read("word/document.xml").decode('utf-8')
    # print(xml)
    xml_list = xml.split('<w:t>')
    # print(xml_list)
    text_list = []
    for i in xml_list:
        if i.find('</w:t>') + 1:
            text_list.append(i[:i.find('</w:t>')])
        else:
            pass
    text = "".join(text_list)
    print(text)
    

    2.4 word转pdf(批量化)

    安装库:

    pip install pywin32
    from win32com.client import Dispatch,constants,gencache
    
    doc_path = 'test.docx'
    pdf_path = 'test.pdf'
    
    gencache.EnsureModule('{00020905-0000-0000-C000-000000000046}',0,8,4)
    wd = Dispatch("Word.Application")
    doc = wd.Documents.Open(doc_path,ReadOnly=1)
    doc.ExportAsFixedFormat(pdf_path,constants.wdExportFormatPDF,
                            Item=constants.wdExportDocumentWithMarkup,
                            CreateBookmarks=constants.wdExportCreateHeadingBookmarks)
    wd.Quit(constants.wdDoNotSaveChanges)

    在执行时报错了,下面来说解决方法。
    参考网站:

    win32api pywin32 安装后出现 ImportError: DLL load failed_mengfanteng的博客-CSDN博客

    • 找到我们安装python的文件夹,在Lib文件中找到site-packages\pywin32_system32
    • D:\Program Files (x86)\Python\Python36\Lib\site-packages\pywin32_system32
    • 把里面的所有的文件复制到:C:\Windows\System32
    import os
    from win32com.client import Dispatch,constants,gencache
    
    
    
    from docx import Document
    from docx.enum.text import WD_ALIGN_PARAGRAPH
    from docx.shared import Pt
    from docx.oxml.ns import qn
    
    import time
    
    price = input('请输入工资调整金额:')
    # 全体员工姓名
    company_list = ['员工1', '员工1', '员工2', '员工3', '员工4', '员工5', '员工6', '员工7', '员工8', '员工9', '员工10' ]
    # 当天的日期
    today = time.strftime("%Y{y}%m{m}%d{d}", time.localtime()).format(y='年', m='月', d='日')
    
    for i in company_list:
        document = Document()
        # 设置文档的基础字体
        document.styles['Normal'].font.name = u'宋体'
        # 识别中文
        document.styles['Normal'].element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
    
        # 建立一个自然段
        p1 = document.add_paragraph()
        # 对齐方式为居中,没有这句的话默认左对齐
        p1.alignment = WD_ALIGN_PARAGRAPH.CENTER
        run1 = p1.add_run('关于%s工资调整的通知' % (today))
        run1.font.name = '微软雅黑'
        run1.element.rPr.rFonts.set(qn('w:eastAsia'), u'微软雅黑')
        run1.font.size = Pt(21)
        run1.font.bold = True
        p1.space_after = Pt(5)
        p1.space_before = Pt(5)
    
        p2 = document.add_paragraph()
        run2 = p2.add_run(i + ':')
        run2.font.name = '宋体'
        run2.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
        run2.font.size = Pt(16)
        run2.font.bold = True
    
        p3 = document.add_paragraph()
        run3 = p3.add_run('因为疫情影响,我们很抱歉的通知您,您的工资调整为每月%s元,特此通知' % price)
        run3.font.name = '宋体'
        run3.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
        run3.font.size = Pt(14)
    
        p4 = document.add_paragraph()
        p4.alignment = WD_ALIGN_PARAGRAPH.RIGHT
        run4 = p4.add_run('人事:王小姐 电话:686868')
        run4.font.name = '宋体'
        run4.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
        run4.font.size = Pt(14)
        run4.font.bold = True
    
    
        document.save('%s-工资调整通知.docx' % i)
    
        doc_path = '%s-工资调整通知.docx' % i
        pdf_path = '%s-工资调整通知.pdf' % i
    
        gencache.EnsureModule('{00020905-0000-0000-C000-000000000046}',0,8,4)
        wd = Dispatch("Word.Application")
        doc = wd.Documents.Open(doc_path,ReadOnly=1)
        doc.ExportAsFixedFormat(pdf_path,constants.wdExportFormatPDF,Item=constants.wdExportDocumentWithMarkup,CreateBookmarks=constants.wdExportCreateHeadingBookmarks)
        wd.Quit(constants.wdDoNotSaveChanges)
        time.sleep(10)

    3.PDF识别以及读取PDF中文字【pdf合并】

    3.1 使用 pdfplumber和PyPDF2

    安装库:

    pip install pdfplumber
    
    pip install PyPDF2

    参考文章:

    PDFPlumber使用入门_顺其自然~的博客-CSDN博客_pdfplumber

    import PyPDF2
    import pdfplumber
    
    def extract_content(pdf_path):
        # 内容提取,使用 pdfplumber 打开 PDF,用于提取文本
        with pdfplumber.open(pdf_path) as pdf_file:
            # 使用 PyPDF2 打开 PDF 用于提取图片
            pdf_image_reader = PyPDF2.PdfFileReader(open(pdf_path, "rb"))
            print(pdf_image_reader.getNumPages())
    
            content = ''
            # len(pdf.pages)为PDF文档页数,一页页解析
            for i in range(len(pdf_file.pages)):
                print("当前第 %s 页" % i)
                # pdf.pages[i] 是读取PDF文档第i+1页
                page_text = pdf_file.pages[i]
                # page.extract_text()函数即读取文本内容
                page_content = page_text.extract_text()
                if page_content:
                    content = content + page_content + "\n"
                    print(page_content)
    
    extract_content('静夜思.pdf')

    合并pdf

    from PyPDF2 import PdfFileReader, PdfFileWriter
    
    def merge_pdfs(paths, output):
        pdf_writer = PdfFileWriter()
    
        for path in paths:
            pdf_reader = PdfFileReader(path)
            for page in range(pdf_reader.getNumPages()):
                # 把每张PDF页面加入到这个可读取对象中
                pdf_writer.addPage(pdf_reader.getPage(page))
    
        # 把这个已合并了的PDF文档存储起来
        with open(output, 'wb') as out:
            pdf_writer.write(out)
    
    if __name__ == '__main__':
        paths = ['静夜思.pdf', '静夜思.pdf']
        merge_pdfs(paths, output='pandas官方文档中文版.pdf')
    from PyPDF2 import PdfFileReader, PdfFileWriter
    
    def merge_pdfs(paths, output):
        pdf_writer = PdfFileWriter()
    
        for path in paths:
            pdf_reader = PdfFileReader(path)
            for page in range(pdf_reader.getNumPages()):
                # 把每张PDF页面加入到这个可读取对象中
                pdf_writer.addPage(pdf_reader.getPage(page))
    
        # 把这个已合并了的PDF文档存储起来
        with open(output, 'wb') as out:
            pdf_writer.write(out)
    
    if __name__ == '__main__':
        paths = ['study.pdf', 'labuladong的算法小抄官方完整版.pdf']
        merge_pdfs(paths, output='pandas官方文档中文版.pdf')

    3.2 pdfminer(推荐)

    读取:

     4.ppt自动化操作

     python-pptx说明文档
        - https://pypi.org/project/python-pptx/

    # pip install python-pptx

    4..1在ppt中写入文字

    
    
    from pptx import Presentation
    from pptx.util import Inches,Pt
    
    ppt = Presentation()
    slide = ppt.slides.add_slide(ppt.slide_layouts[1])# 在PPT中插入一个幻灯片
    
    body_shape = slide.shapes.placeholders
    # body_shape[0].text = '这是占位符0'
    # body_shape[1].text = '这是占位符1'
    #
    title_shape = slide.shapes.title
    title_shape.text = '这是标题'
    # subtitle = slide.shapes.placeholders[1] #取出本页第二个文本框
    # subtitle.text = '这是文本框'
    #
    # new_paragraph = body_shape[1].text_frame.add_paragraph()
    # new_paragraph.text = '新段落'
    # new_paragraph.font.bold = True
    # new_paragraph.font.italic = True
    # new_paragraph.font.size = Pt(15)
    # new_paragraph.font.underline = True
    #
    left = Inches(2)
    top = Inches(2)
    width = Inches(3)
    height = Inches(3)
    #
    #
    #
    textbox = slide.shapes.add_textbox(left,top,width,height)
    textbox.text = 'new textbox'
    # 如何在文本框里添加第二段文字?
    # new_para = textbox.text_frame.add_paragraph()
    # new_para.text = '第二段文字'
    #
    
    
    ppt.save('test.pptx')

    4.2 在ppt插入图片表格

    
    # pip install python-pptx
    from pptx import Presentation
    from pptx.util import Inches,Pt
    
    ppt = Presentation()
    slide = ppt.slides.add_slide(ppt.slide_layouts[1])# 在PPT中插入一个幻灯片
    
    left = Inches(1)
    top = Inches(1)
    width = Inches(2)
    height = Inches(2)
    
    img = slide.shapes.add_picture('img.jpg',left,top,width,height)
    
    rows = 2
    cols = 2
    left = Inches(1)
    top = Inches(1)
    width = Inches(4)
    height = Inches(4)
    
    table = slide.shapes.add_table(rows,cols,left,top,width,height).table
    table.columns[0].width = Inches(1)
    table.columns[1].width = Inches(3)
    table.cell(0,0).text = '00'
    table.cell(0,1).text = '01'
    table.cell(1,0).text = '10'
    table.cell(1,1).text = '11' #二进制的11,代表十进制的多少?
    
    ppt.save('text.pptx')
    

    展开全文
  • 时间被重复工作挤满?永远处理不完的文档与报表?工作效率低 总被领导批评教育?
  • PDF表格转化生成Excel
  • 点击上方“Python爬虫与数据挖掘”,进行关注回复“书籍”即可获赠Python入门进阶共10本电子书今日鸡汤乘兴南游不戒严,九重谁省谏书函。自动化办公OFFICE AUTOMATIO...
  • 勤智数码OneCenterIT运维自动化管理平台精.pdf勤智数码OneCenterIT运维自动化管理平台精.pdf勤智数码OneCenterIT运维自动化管理平台精.pdf勤智数码OneCenterIT运维自动化管理平台精.pdf勤智数码OneCenterIT运维自动...
  • 需要选择整张报表进行透视表计算时可以怎样操作 A Ctrl+a 快选整张表格 B 鼠标在最左行变为黑色箭头时可以全选行 C 鼠标移动至报表内部可自动选择整张报表 正确答案 C 3. 在数据透视表中需要对某一字段进行对比分析...
  • 在UI自动化或者接口自动化数据维护是一个核心,所以此模块非常实用。 xlrd模块可以用于读取Excel数据,速度非常快,推荐使用! 官方文档:https://xlrd.readthedocs.io/en/latest/ 1.2 安装xlrd模块 python...
  • 我是一个毕业两年的90后,毕业后在一个主营粮食交易的企业工作,岗位的主要工作就是制作各类数据统计报表、台账、数据图表等等,各类的日报,每天都要做一遍,此外还有什么周报月报…… 之前在这个岗位的同事,每天...
  • 这是免费系列教程《7天学会商业智能(BI)-Power BI》的最后一篇内容:制作完的报表,如何发布?主题内容第1天:什么是报表?什么是报表?如何设计报表?第2天:认识Power BI什么...
  • 第9章 数据可视技术 华中科技大学软件学院 数据可视 2 通过图表可视分析数据结果,不仅能让数据更加生动、形象,便于用户发现 数据中隐含的规律与知识,而且这也是软件工程师与数据工程师合作的最终工 作成果...
  • 本书全面系统地介绍了Excel 2013数据透视表的技术特点和应用方法,深入揭示数据透视表的原理,并配合大量典型实用的应用实例,帮助读者全面掌握Excel 2013数据透视表技术。 本书共23章,分别介绍创建数据透视表,...
  • 主要介绍了基于SpringBoot框架,管理ExcelPDF文件类型,本文给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下
  • 平时苦于做报表的小伙伴们,今天TJ君给你们带来一个开源低代码可视化报表项目,JimuReport,来解决你们的报表难题!JimuReport,作为一个报表项目,它拥有类似excel的操作风...
  • 精通qtp_自动化测试技术领航.pdf

    热门讨论 2015-03-30 17:12:38
    1、精通qtp_自动化测试技术领航.pdf 资料很全,适合初学自动化测试的朋友们; 2、《精通qtp_自动化测试技术领航》的扫描件,书籍目录如下: 第1章 测试脚本开发零开始 1.1 自动化测试零开始 1.1.1 什么是自动...
  • Excel自动化,是用Python程序创建、编辑、修改Excel文件,处理其中的数据,从而无人化、大批量处理excel文件数据。处理excel常用的库有:openpyxl、xlwings、xlrd、xlwt等。数据处理的库常用 pandas。这里主要讲...
  • 是学习Excel的经典书籍,含有大量的案例,数据透视表的必备书籍
  • 大部分的数据分析师都或多或少掉入这样的陷阱:每天大部分的工作都花在查数上,干着干着变成了“查数菇”。看上去帮老板或其他同事查数据数据分析师天经地义的任务,怎么会成为陷阱呢?我来给你分析分析: 业务...
  • 支持数据分析的数据集,你值得拥有!很好用的分析数据!在压缩包里有具体的使用说明!主要是针对初学数据分析的人,而打造的数据集,可以在数据集上进行相关的操作。
  • 为节省成本,保证高效及高质量迭代,我们需采用更高效的测试方式,App自动化测试是较高效的手段。 之前自动测试实践过程中遇到的诸多问题(代码复用率低,Case开发及数据构造繁琐,问题定位困难,学习成本高等),为...
  • 用ActiveX扩展WinCC的数据查询报表功能pdf,用ActiveX扩展WinCC的数据查询报表功能
  • 今天就带大家来实战一波,使用Python自动化生成数据报表一条条的数据中,创建出一张数据报表,得出你想要的东西,提高效率。主要使用pandas、xlwings以及matplotlib...
  • 积木报表,一款免费的可视Web报表工具,像搭建积木一样在线拖拽设计!功能涵盖,数据报表、打印设计、图表报表、大屏设计等! 秉承“简单、易用、专业”的产品理念,极大的降低报表开发难度、缩短开发周期、节省...
  • 华为数字转型中的数据治理实践.pdf华为数字转型中的数据治理实践.pdf华为数字转型中的数据治理实践.pdf华为数字转型中的数据治理实践.pdf华为数字转型中的数据治理实践.pdf华为数字转型中的数据治理实践...
  • 29、熟悉 Excel 的“文本语音” 30、Excel 中“摄影”功能的妙用 31、在多张表格间实现公用数据的链接和引用 32、“驯服”Excel 2002 的剪贴板窗口 33、利用公式审核工具查看数据出处 34、巧用Excel 2002 的...
  • 其实excel不只是一个数据统计工具,它的数据分析能力十分强大,除了基本的数据计算之外,还可以进行数据清洗、数据可视等等,财务人、业务人等都很喜欢用excel报表。 虽然Excel不能实现大数据量的数据分析,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 5,400
精华内容 2,160
关键字:

从数据到excel自动化报表pdf