Python を使用してレポートを自動化する方法-Python チュートリアル-php.cn

Excelの基本構成

初めてレポートを作成する場合、ExcelからスタートしてExcelを使ってレポートを作成することが多いので、まずはExcelの基本構成を理解します。

下の図は、Excel のさまざまな部分の関係を示しています。私たちは仕事で毎日多くの Excel ファイルを処理します。Excel ファイルは実際にはブックです。新しい Excel ファイルを作成するたびに、ファイル名はデフォルトでワークブック x に設定されます。x は新しいファイルの数を表します。ワークブックには複数のシートを含めることができ、各シートは独立したテーブルになります。各シートは複数のセルで構成されます。各セルには複数の要素または属性があり、通常 Excel ファイルに対して行うほとんどの設定は、実際にはセルの要素に対する設定です。

Python を使用してレポートを自動化する方法

セル要素に設定する主な内容には、フォント、配置、条件付き書式など、メニューバーに表示される内容が含まれます。本書も Excel のメニューバーの各モジュールに沿って書かれています。

Python を使用してレポートを自動化する方法

自動レポートのプロセス

次の図は、自動レポートを作成するためにまとめたプロセスであり、主に 5 つのステップに分かれています。

Python を使用してレポートを自動化する方法

最初のステップは、レポートを段階的に分解することです。この分解ステップは、ツールを使用するかどうか、またはどのツールを使用するかとは直接関係がありません。レポートの作成など。最初のステップは通常、データを収集することです。このデータは、オフライン担当者が紙のノートに記録したり、Excel テーブルに保存したり、データベースに保存したりすることができます。データソースの種類や保存方法によって対応するデータ収集方法は異なりますが、データ収集ステップ自体はデータを収集することが目的であり、データ収集ステップ自体は変わりません。

2 番目のステップは、最初のステップに含まれる各特定のステップに対応するコード実装について考えることです。一般的には、データをインポートするコードがどのようなものであるかなど、各ステップに対応するコードを見つけることです。別の例は、重複値の削除のコードがどのようなものであるかを示します。

3 番目のステップは、2 番目のステップの各ステップに対応するコードを完全なコードに結合することです。

4 番目のステップでは、3 番目のステップで完全なコードから取得したレポート結果を検証し、結果が正しいかどうかを確認します。

5 番目のステップは、呼び出しを待ってレポートをいつ作成する必要があるかを確認し、記述されたコードを 1 回実行することです。

実際、レポートの自動化は本質的には 人間の代わりに機械に作業を行わせるプロセスです .手動で実行する必要があるすべてのステップを機械が理解できる言語に変換するだけで済みます。、つまりコードを作成し、それをマシンに自動的に実行させる、これが実際には自動化の実現です。

レポート自動化の実践

このセクションでは、実際の作業で Pandas と openpyxl を組み合わせてレポートを自動生成する方法を説明します。

次のデータセットがあるとします。

Python を使用してレポートを自動化する方法

このデータセットに基づいて、主に 3 つの側面を含む日次レポートを作成する必要があります。 :

当日のさまざまな指標の同月比較;
当日に各州で作成された注文数;
最近の注文量の傾向

次に、これら 3 つの部分を個別に実装します。

当日の各種指標の同月比較

まずはPandasを使用してデータの計算・処理を行い、各指標の同月比較を取得します。具体的な実装コードは以下の通りです。次のとおりです:

#导入文件
import pandas as pd
df = pd.read_excel(r&#39;D:\Data-Science\share\excel-python报表自动化\sale_data.xlsx&#39;)

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

df_view[&#39;环比&#39;] = df_view[&#39;当日&#39;] / df_view[&#39;昨日&#39;] - 1
df_view[&#39;同比&#39;] = df_view[&#39;当日&#39;] / df_view[&#39;上周同期&#39;] - 1
df_view

ログイン後にコピー

上記のコードを実行すると、次の結果が得られます:

Python を使用してレポートを自動化する方法

上記は、同じ月の各インジケーターの絶対値のみを取得しますただし、一般的な日報は、フォントの調整など、送信する前にフォーマットを調整する必要があります。形式の調整には、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&#39;D:\Data-Science\share\excel-python报表自动化\核心指标_原始.xlsx&#39;)

ログイン後にコピー

上記のコードは次の結果を取得しますが、元のデータファイルが非常にわかりにくいことがわかります:

Python を使用してレポートを自動化する方法

次に、上記の元のデータファイルの形式を調整します。調整コードは次のとおりです:

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[&#39;A1&#39;] = &#39;指标&#39;

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

#将标题行的单元格进行合并
ws.merge_cells(&#39;A1:F1&#39;) #合并单元格

#对第1行至第6行的单元格进行格式设置
for row in ws[1:6]:
    for c in row:
        #字体设置
        c.font = Font(name = &#39;微软雅黑&#39;,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 = &#39;微软雅黑&#39;,size = 12,bold = True,color = "FFFFFFFF")
        c.fill = PatternFill(fill_type = &#39;solid&#39;,start_color=&#39;FFFF6100&#39;)

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

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

#保存调整后的文件        
wb.save(r&#39;D:\Data-Science\share\excel-python报表自动化\核心指标.xlsx&#39;)

ログイン後にコピー

上記のコードを実行すると、次の結果が得られます:

Python を使用してレポートを自動化する方法

すべての項目が正常に設定されたことがわかります。。

その日に各州で作成された注文の数

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

ログイン後にコピー

また、最初に Pandas ライブラリを使用して、その日に各州で作成された注文の数を処理します。具体的な実装コードは次のとおりです。次のように：＃＃＃

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

Python を使用してレポートを自動化する方法

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

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 = &#39;微软雅黑&#39;,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 = &#39;min&#39;,end_type = &#39;max&#39;,
                    color="FF638EC6", showValue=True, minLength=None, maxLength=None)
ws.conditional_formatting.add(&#39;B1:B11&#39;,rule)

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

#保存调整后的文件     
wb.save(r&#39;D:\Data-Science\share\excel-python报表自动化\各省份销量情况.xlsx&#39;)

ログイン後にコピー

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

Python を使用してレポートを自動化する方法

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

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

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

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

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

#将图表保存到本地
plt.savefig(r&#39;D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png&#39;)

ログイン後にコピー

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

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

wb = Workbook()
ws = wb.active

img = Image(r&#39;D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png&#39;)

ws.add_image(img, &#39;A1&#39;)

wb.save(r&#39;D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.xlsx&#39;)

ログイン後にコピー

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

Python を使用してレポートを自動化する方法

将不同的结果进行合并

我们将每个部分单独实现后，将其存储在不同的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。

由于Excel中的列从1开始计数，而range()函数默认从0开始，因此需要给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&#39;D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png&#39;)
ws.add_image(img, &#39;G1&#39;)

ログイン後にコピー

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

#格式预设

#表头字体设置
title_Font_style = Font(name = &#39;微软雅黑&#39;,size = 12,bold = True,color = "FFFFFFFF")
#普通内容字体设置
plain_Font_style = Font(name = &#39;微软雅黑&#39;,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 = &#39;solid&#39;,start_color=&#39;FFFF6100&#39;)

ログイン後にコピー

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

Python を使用してレポートを自動化する方法

将不同的结果合并到同一工作簿的不同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&#39;D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png&#39;)

ws2.add_image(img, &#39;A1&#39;)

wb.save(r&#39;D:\Data-Science\share\excel-python报表自动化\多结果合并_多Sheet.xlsx&#39;)

ログイン後にコピー

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

Python を使用してレポートを自動化する方法