准工业级代码分享：Python用于自动生成EXCEL周期报告

最新推荐文章于 2024-07-06 10:43:07 发布

数据不吹牛

最新推荐文章于 2024-07-06 10:43:07 发布

阅读量701

点赞数

文章标签：大数据 python 数据分析编程语言 java

前言

Python自动化在我看来一直是个小打小闹的需求，无法独立成为工业级或者商业级的产品需求。尤其是Python操作PPT，在我看来根本没有一点用武之地。因为好的商业PPT远不是枯燥的复制和粘贴，绝对不是用Python程式能码出来的，除非是学校的课件。

在我接触的项目中，从Excel 提取数据或者生成报表倒是一个刚需。但是这个刚需往往又需要专业人员开发出更复杂的软件，因为数据分析和生成报表只是需求中的一小环节。工业里面Excel报表比较多，Word报表比较少，原因是Excel支持复杂的函数计算，Word就明显无能为力。

举几个例子：

故障诊断有时需要从维修日志中按照一定的规则提取有用的信息，比如维修间隔，故障分类等，这些会用于机器学习的特征工程。
需要将Excel的数据reshape成特定的格式，比如从物料清单KKS中提取设备的层级关系，构建资产树（Asset Tree）。
还有一大类需求，是根据需求生成Excel报表。比如运营的KPI报表，或者是结算报表，发票等。但是这些报表生成后，还需要一些其他软件功能，比如审批，修改等。

工业级生成报告的方法

一般工业软件供应商会给客户提供以下解决方案：

专用的报表编辑器

涉及数据集成和分析的工业软件都会自带报表编辑器。实施人员一般会设计好报表，然后自动或按需（事件触发等）生成周期报表，这就是比较“笨重”的解决方案。这类方案的好处是报表设计方便快捷，但是缺点是需要额外的学习成本，导出的报表格式多为pdf或者html。

采用Excel 插件

工业界相比于商业界一个很明显的区别是存在大量的传感器数据。传感器数据在数据库中可以通过tag和时间戳进行快速检索。所以多数的工业数据库都会提供Excel 插件，方便用户或者设计人员直接在Excel 表格中输tag 获取数据，生成实时报表。这类方案一般不会涉及Python 处理，因为工业界软件会自带常用函数便于计算各类指标。

Python结合Excel模板

还有一种方案是客户提供模板，软件供应商通过程序来生成报表。如果数据源是关系数据库或者Web API，这种方案更普遍。这个方案需要设计好Excel模板，然后通过Python 调用Excel模板，完成数据计算和填充，生成周期性报表。

准工业级报表生成方案

本文分享的案例就是基于Python 结合Excel模板的方案。工业级的软件一般会要考虑软件的稳定性和易用性，比如注册为服务，开机自动运行，以及采用deamon守护进程等。分享的案例中没有包含这些辅助项，所以姑且称为准工业级。另外，为了便于理解，分享的案例采用开源的Kaggle 数据集作为示例。

本案例中分享的代码包括：

准备Excel 模板以及数据
根据选定周期进行数据过滤和计算
计算结果自动填充Excel 单元格
根据生成选定周期的Chart
Bonus: 支持BarChart 和LineChart混合。
自动按周期更新报告，比如每10秒

效果图预览如下：

数据和模板准备

数据来自Kaggle ，是牛油果（avocado）的门店销售记录，里面包含不同门店每周牛油果的价格和销量。

针对这个数据集，我们假定有以下需求：

业务部门需要查看每周绩效报告
报告需要包含所有店铺的销售额以及价格均值
报告需要包含价格最高的店铺的价格以及销量
报告需要包含价格最高的店铺的价格以及销量
报告需要包含销量（价格）前10的门店柱状图和曲线图
当然还要包含公司logo

上面的需求很简单但是很经典，因为它几乎包含了所有的技术要点：

数据分析，聚合和过滤
Excel 读取
Excel 单元格的操作
Excel Chart 自动绘制
Excel 混合Chart的绘制以及细节处理
定时/周期自动运行

首先我们需要设计模板，我对模板的设计只有一个建议：能放模板尽量放模板！！！毕竟时间宝贵，用Python 生成模板太费事。

分享的模板里面有几个细节值得注意：

Logo。Logo作为图片当然可以通过Python 写入，但是它是个静态文件，所以可以直接放在模板里。
单元格格式。比如字体大小，合并单元格，货币单位等。在模板里就是鼠标点一两下的事情，放在Python里可是好多行代码的问题。
销售总额计算。这个可以放在Python中进行计算。但是案例中里我直接采用Excel的函数，通过平均价和总销量来计算。目的也是为了展示能用Excel就用Excel。

下图是Excel模板，注意上面的日期只是为了检查字体等单元格格式，最后会被Python动态覆盖。

关于工具

本方案采用的核心Python库包括：

pandas：用于处理和分析数据
openpyxl：用于操作excel文件
schedule：用于生成和管理job

pandas 不用多说，极其常用的数据处理库。openpyxl 是用于处理Excel表格的，虽然pandas 可以读写Excel表格，但是无法高效地操作Excel单元格。还有其他的Python库可以操作Excel，比如xlsxwriter也不错。schedule，正如其名，就是用于job 调度的库。

官方链接：

https://pypi.org/project/schedule/

https://openpyxl.readthedocs.io/en/stable/index.html#

实现思路和核心代码

要生成报告，无非就是整理数据，以及将数据更新到模板中，最后设置一个定时任务，这个思路很直观。

为了生成周期性的报告，需要我们指定一个日期相关的变量。然后，我们才能根据这个日期变量来检索对应的历史数据以及分析和统计，可以参考以下代码。

   def price_statistic(df, calc_date):
        calc_df = df[df['Date'] == calc_date]
        mean_price = calc_df['AveragePrice'].mean()
        num_store = len(calc_df['region'].unique())
        sum_sale = calc_df['Total Volume'].sum()
        high_price = calc_df['AveragePrice'].max()
        high_store = calc_df.loc[calc_df['AveragePrice'].idxmax()]['region']
        high_store_sale = calc_df.loc[calc_df['AveragePrice'].idxmax(
        )]['Total Volume']
        low_price = calc_df['AveragePrice'].min()
        low_store = calc_df.loc[calc_df['AveragePrice'].idxmin()]['region']
        low_store_sale = calc_df.loc[calc_df['AveragePrice'].idxmin(
        )]['Total Volume']
        return [
            mean_price,
            num_store,
            sum_sale,
            high_price,
            high_store,
            high_store_sale,
            low_price,
            low_store,
            low_store_sale]

数据准备好之后，操作Excel表格也很简单，可以参考官方文档分分钟入门。首先读取Excel Workbook，然后定位到Sheet级别，之后就是对单元格进行操作了，核心代码其实就是一句：sheet['单元格'] = 值

wb = load_workbook(report_template)  # workbook
report_sheet = wb['report']
raw_data_sheet = wb['raw']

pandas 分析和统计的结果可以通过循环，将数据List 逐个写入到单元格List中。

def report_sheet_update(report_sheet, external_data, raw_data_sheet):
    to_fill_cells_list = ['B2',
                          'C12', 'E12', 'H12',
                          'C14', 'E14', 'H14',
                          'C16', 'E16', 'H16'
                          ]  # define totill cell list
    for i, c in enumerate(to_fill_cells_list):
        report_sheet[c] = external_data[i]

生成Cha

生成Chart是本实例中较为耗时的部分，因为需要处理的细节比较多，在实际项目中也是较为耗时的环节。

本案例需要建立一个混合Chart，里面包含了BarChart 和LineChart。所以，在代码中我们需要分别创建BarChart 和LineChart，并且指定每个Chart对应的x轴和y轴数据源。

通过设置其中一个Chart的y_axis.crosses为 "max"，来创建次坐标轴。之后，通过一个简单的BarChart += LineChart将两个Chart重叠。

Chart的绘制需要准备数据源，并且数据源需要指向参考Excel自身的Sheet。这里，我们需要把用于绘图的数据保存在Excel中另一个Sheet中，然后采用Reference 类进行调用。需要注意的一个细节就是Excel的行和列都是从1开始，这个有别于Python。

另外本文中还涉及一些项目中常用的独门技巧：比如取消网格线，更改背景色等细节设置，这些在官方的教程里面很难找到。

类似于单元格数据的填写，定义好的Chart需要通过add_chart函数添加到指定单元格。

def create_bar_chart(chart_sheet, data_sheet):
        bar_chart = BarChart()
        bar_chart.title = 'TOP10 门店信息'
        row_start = 1
        row_end = 12 + row_start
        y = Reference(
            data_sheet,
            min_col=3,
            min_row=row_start,
            max_col=3,
            max_row=row_end)
        x = Reference(
            data_sheet,
            min_col=2,
            min_row=row_start,
            max_col=2,
            max_row=row_end)
        bar_chart.add_data(y, titles_from_data=True)
        bar_chart.set_categories(x)
        bar_chart.x_axis.title = '门店'
        bar_chart.y_axis.title = '价格'


        # Create a second chart
        line_chart = LineChart()
        y2 = Reference(
            data_sheet,
            min_col=4,
            min_row=row_start,
            max_col=4,
            max_row=row_end)


        line_chart.add_data(y2, titles_from_data=True)
        line_chart.y_axis.title = "销量"
        line_chart.y_axis.axId = 1
        gridline_sgp = GraphicalProperties(ln=LineProperties(noFill=True))
        line_chart.y_axis.majorGridlines.spPr = gridline_sgp
        bar_chart.y_axis.crosses = "max"


        # combine and adjust style
        bar_chart += line_chart
        bar_chart.legend.position = "t"
        bar_chart.height = 10  # default is 7.5
        bar_chart.width = 12  # default is 15
        props = GraphicalProperties(solidFill="f3f3f3")
        bar_chart.graphical_properties = props
        bar_chart.plot_area.graphicalProperties = props
        bar_chart.style = 2  # fast change color
        bar_chart.y_axis.majorGridlines.spPr = gridline_sgp
        chart_sheet.add_chart(bar_chart, 'B24')

按周期自动生成报告

自动生成报告的代码，主要包含两部分。

第一部分是定义job。对于本实例，job就是创建报告。我们需要指定一个calc_date，该参数用于控制如何刷选数据，以及生成的报告名称。在实际的项目中可以通过获取当前日期和时间来直接得到calc_date，比如datetime.now()。这里由于采用的是历史数据集，我们需要通过date_index 来指定下一个报表的生成日期。

def generate_final_report_job(df, report_template, calc_date_list):
    global date_index
    calc_date = calc_date_list[date_index]
    wb = load_workbook(report_template)  # workbook
    report_sheet = wb['report']
    raw_data_sheet = wb['raw']
    raw_sheet_update(raw_data_sheet, df, calc_date)
    report_data = report_data_gen(df, calc_date)
    report_sheet_update(report_sheet, report_data, raw_data_sheet)
    calc_date = pd.to_datetime(str(calc_date))
    timestr = calc_date.strftime('%Y_%m_%d_%H_%M_%S')
    print(timestr)
    file_path = f'report_{timestr}.xlsx'
    wb.save(file_path)
    date_index += 1

第二步是设定job运行的周期。需要执行schedule.every(5).seconds 语句来设定运行的周期，本案例演示为每5秒更新一次报告。

schedule.every(5).seconds.do(
    generate_final_report_job,
    df=df,
    report_template=report_template,
    calc_date_list=calc_date_list)
while True:
    schedule.run_pending()
    time.sleep(1)

最终生成的Excel 包含Report Sheet和数据Raw Sheet。

总结

关于openpyxl 以及schedule的用法，本文没有做特别详细的解释。对于这部分，建议是直接参考官方文档，需要处理什么细节查看对应代码和教程即可。

本文包含了Python自动生成周期报表的完整代码以及真实项目经验分享，代码应该属于中等难度。代码功能完整，几乎涵盖了报表生成的所有技术要点，对于处理日常工作中的报表应该绰绰有余。

本文源码已打包好：

链接：https://pan.baidu.com/s/1x5GZMnkns28e49WMTN-SIw

提取码：mv2b

●解一道反常的Pandas题

●12000+字超详细 SQL 语法速成！

后台回复“入群”即可加入小z干货交流群

数据不吹牛

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
准工业级代码分享：Python用于自动生成EXCEL周期报告

前言Python自动化在我看来一直是个小打小闹的需求，无法独立成为工业级或者商业级的产品需求。尤其是Python操作PPT，在我看来根本没有一点用武之地。因为好的商业PPT远不是枯燥的复制...
复制链接

扫一扫