Python을 사용하여 보고서를 자동화하는 방법-파이썬 튜토리얼-php.cn

엑셀의 기본 구성

우리가 처음 보고서를 작성할 때 보통 엑셀부터 시작해 엑셀을 이용해 보고서를 작성하게 되므로 먼저 엑셀의 기본 구성을 이해해 봅시다.

아래 그림은 Excel의 다양한 부분 간의 관계를 보여줍니다. 우리는 직장에서 매일 수많은 Excel 파일을 처리합니다. Excel 파일은 실제로 통합 문서입니다. 새 Excel 파일을 만들 때마다 파일 이름은 기본적으로 통합 문서 x로 지정됩니다. 여기서 x는 새 파일 수를 나타냅니다. 통합 문서에는 여러 시트가 포함될 수 있으며 각 시트는 독립적인 테이블입니다. 각 시트는 여러 개의 셀로 구성됩니다. 각 셀에는 여러 요소나 속성이 있습니다. 일반적으로 Excel 파일에 적용되는 대부분의 설정은 실제로 셀 요소에 대한 설정입니다.

Python을 사용하여 보고서를 자동화하는 방법

셀 요소에 주로 설정되는 콘텐츠에는 글꼴, 정렬, 조건부 서식 등 메뉴 표시줄에 표시되는 콘텐츠가 포함됩니다. 이 책 역시 엑셀 메뉴바의 각 모듈별로 구성되어 있다.

Python을 사용하여 보고서를 자동화하는 방법

자동 보고서 프로세스

아래 그림은 자동 보고서를 만들기 위해 편집한 프로세스로, 크게 5단계로 나뉩니다.

Python을 사용하여 보고서를 자동화하는 방법

첫 번째 단계는 보고서를 준비하는 것입니다. done 단계별 분해를 수행합니다. 이 단계별 분해는 도구 사용 여부 또는 어떤 도구를 사용하는지와 직접적인 관계가 없습니다. 예를 들어 보고서 작성의 첫 번째 단계는 일반적으로 데이터를 수집하는 것입니다. 이 데이터는 오프라인 직원이 종이 노트에 기록할 수도 있고 데이터베이스에 저장할 수도 있습니다. 데이터 소스의 종류나 저장 방식에 따라 해당 데이터 수집 방식은 다르지만, 데이터를 수집하는 단계 자체는 데이터를 수집하는 것이 목적으로 변하지 않습니다.

두 번째 단계는 첫 번째 단계에 포함된 각 특정 단계에 해당하는 코드 구현에 대해 생각하는 것입니다. 일반적으로 데이터를 가져오는 코드가 어떻게 생겼는지 등 각 단계에 해당하는 코드를 찾는 것입니다. , 반복되는 값 삭제된 코드는 어떤 모습인가요?

세 번째 단계는 두 번째 단계의 각 단계에 해당하는 코드를 결합하여 완전한 코드로 만드는 것입니다.

네 번째 단계는 세 번째 단계의 전체 코드에서 얻은 보고서 결과를 확인하여 결과가 올바른지 확인하는 것입니다.

다섯 번째 단계는 보고가 필요한 시점을 확인하기 위해 호출을 기다린 다음 작성된 코드를 한 번 실행하는 것입니다.

사실 보고서 자동화는 본질적으로 기계가 인간 대신 일을 하게 하는 과정입니다. 우리가 수동으로 해야 할 모든 단계를 기계가 이해할 수 있는 언어, 즉 코드로 변환하고 나면 됩니다. 기계가 자동으로 실행하도록 하세요. 이것은 실제로 자동화입니다.

보고서 자동화 실습

이 섹션에서는 Pandas와 openpyxl을 결합하여 실제 작업에서 자동으로 보고서를 생성하는 방법을 보여줍니다.

이제 다음 데이터 세트가 있다고 가정합니다.

Python을 사용하여 보고서를 자동화하는 방법

이제 이 데이터 세트를 기반으로 일일 보고서를 작성해야 하며 여기에는 주로 세 가지 측면이 포함됩니다.

day ;
그날 각 지방에서 발생한 주문 수
최근 기간 동안 발생한 주문 수 추세

다음으로 이 세 부분을 별도로 구현하겠습니다.

당일 각 지표의 동월 비교

먼저 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을 사용하여 보고서를 자동화하는 방법