紹興Python自動化辦公 | 如何實現報表數據處理自
最近一個之前在我們這邊學過office軟件的市委黨校的人找到我，是負責新農村建設宣傳這塊的，說想了解一種計算機語言，能便捷的處理平時工作流程中上千條繁瑣的數據。那對于這種辦公類員工數據的分析處理，自然是最推薦python了?，F在上元小陸就帶大家了解下報表自動化的流程，并教你用Python實現工作中的一個報表自動化實戰，篇幅有點長，建議先收藏，文章具體的目錄為：
1.Excel的基本組成
2.一份報表自動化的流程
3.報表自動化實戰
- 當日各項指標同環比情況
- 當日各省份創建訂單量情況
- 最近一段時間創建訂單量趨勢
Excel的基本組成
我們一般在最開始做報表的時候，基本都是從Excel開始的，都是利用Excel在做報表，所以我們先了解下Excel的基本組成。
下圖是Excel的中各個部分的組成關系，我們工作中每天會處理很多Excel文件，一個Excel文件其實就是一個工作簿。你在每次新建一個Excel文件時，文件名都會默認是工作簿x，其中x就是你新建的文件個數。而一個工作簿里面又可以有多個Sheet，不同Sheet之間是一個獨立的表。每一個Sheet里面又由若干個單元格組成。每一個單元格又有若干的元素或屬性，我們一般針對Excel文件進行設置最多的其實就是針對單元格的元素進行設置。
而針對單元格元素進行設置的主要內容其實就是如下圖菜單欄中顯示，比如字體、對齊方式、條件格式等內容。本書也是按照Excel菜單欄中的各個模塊進行編寫。
一份自動化報表的流程
下圖是我整理的做一份自動化報表需要經歷的流程，主要分為5個步驟：
第一步是對要做的報表進行步驟拆解，這個步驟拆解和用不用工具或者是用什么工具沒有直接關系，比如做報表的第一步一般都是收集數據，這個數據可能是線下人員記錄在紙質筆記本上的，也可能是存儲在Excel表里面的，還有可能是存儲在數據庫里面的。會因為數據源的類型或者是存儲方式不同，對應的收集數據方式會不一樣，但是收集數據這個步驟本身是不會變的，這個步驟的目的就是把數據收集過來。
第二步是去想第一步里面涉及到的每一個具體步驟對應的代碼實現方式，一般都是去找對應每一步的代碼，比如導入數據的代碼是什么樣的，再比如重復值刪除的代碼是什么樣的。
第三步是將第二步中各個步驟對應的代碼進行組合，組合成一個完整的代碼。
第四步是對第三步完整代碼得出來的報表結果進行驗證，看結果是否正確。
第五步就是等待調用，看什么時候需要制作報表了，然后就將寫好的代碼執行一遍就行。
其實報表自動化本質上就是讓機器代替人工做事情的過程，我們只需要把我們人工需要做的每一個步驟轉化成機器可以理解的語言，也就是代碼，然后讓機器自動去執行，這其實就是實現了自動化。
報表自動化實戰
這一節給大家演示下在實際工作中如何結合Pandas和openpyxl來自動化生成報表。
假設我們現在有如下一份數據集：
現在我們需要根據這份數據集來制作每天的日報情況，會主要包含三方面：
-
當日各項指標的同環比情況； -
當日各省份創建訂單量情況； -
最近一段時間創建訂單量趨勢
接下來分別來實現這三部分。
當日各項指標的同環比情況：
我們先用Pandas對數據進行計算處理，得到各指標的同環比情況，具體實現代碼如下：
#導入文件
import pandas as pd
df = pd.read_excel(r'D:Data-Scienceshareexcel-python報表自動化sale_data.xlsx')
#構造同時獲取不同指標的函數
def get_data(date):
create_cnt = df[df['創建日期'] == date]['order_id'].count()
pay_cnt = df[df['付款日期'] == date]['order_id'].count()
receive_cnt = df[df['收貨日期'] == date]['order_id'].count()
return_cnt = df[df['退款日期'] == date]['order_id'].count()
return create_cnt,pay_cnt,receive_cnt,return_cnt
#假設當日是2021-04-11
#獲取不同時間段的各指標值
df_view = pd.DataFrame([get_data('2021-04-11')
,get_data('2021-04-10')
,get_data('2021-04-04')]
,columns = ['創建訂單量','付款訂單量','收貨訂單量','退款訂單量']
,index = ['當日','昨日','上周同期']).T
df_view['環比'] = df_view['當日'] / df_view['昨日'] - 1
df_view['同比'] = df_view['當日'] / df_view['上周同期'] - 1
df_view
運行上面代碼會得到如下結果：
上面只是得到了各指標的同環比絕對數值，但是我們一般的日報在發出去之前都要做一些格式調整的，比如調整字體之類的。而格式調整就需要用到openpyxl庫，我們需要將Pandas庫中DataFrame格式的數據轉化為適用openpyxl庫的數據格式，具體實現代碼如下：
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
#創建空工作簿
wb = Workbook()
ws = wb.active
#將DataFrame格式數據轉化為openpyxl格式
for r in dataframe_to_rows(df_view,index = True,header = True):
ws.append(r)
wb.save(r'D:Data-Scienceshareexcel-python報表自動化核心指標_原始.xlsx')
運行上面代碼會得到如下結果，可以看到原始的數據文件看起來是很混亂的：
接下來我們針對上面原始數據文件進行格式調整，具體調整代碼如下：
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import colors
from openpyxl.styles import Font
from openpyxl.styles import PatternFill
from openpyxl.styles import Border, Side
from openpyxl.styles import Alignment
wb = Workbook()
ws = wb.active
for r in dataframe_to_rows(df_view,index = True,header = True):
ws.append(r)
#第二行是空的，刪除第二行
ws.delete_rows(2)
#給A1單元格進行賦值
ws['A1'] = '指標'
#插入一行作為標題行
ws.insert_rows(1)
ws['A1'] = '電商業務方向 2021/4/11 日報'
#將標題行的單元格進行合并
ws.merge_cells('A1:F1') #合并單元格
#對第1行至第6行的單元格進行格式設置
for row in ws[1:6]:
for c in row:
#字體設置
c.font = Font(name = '微軟雅黑',size = 12)
#對齊方式設置
c.alignment = Alignment(horizontal = "center")
#邊框線設置
c.border = Border(left = Side(border_style = "thin",color = "FF000000"),
right = Side(border_style = "thin",color = "FF000000"),
top = Side(border_style = "thin",color = "FF000000"),
bottom = Side(border_style = "thin",color = "FF000000"))
#對標題行和表頭行進行特殊設置
for row in ws[1:2]:
for c in row:
c.font = Font(name = '微軟雅黑',size = 12,bold = True,color = "FFFFFFFF")
c.fill = PatternFill(fill_type = 'solid',start_color='FFFF6100')
#將環比和同比設置成百分比格式
for col in ws["E":"F"]:
for r in col:
r.number_format = '0.00%'
#調整列寬
ws.column_dimensions['A'].width = 13
ws.column_dimensions['E'].width = 10
#保存調整后的文件
wb.save(r'D:Data-Scienceshareexcel-python報表自動化核心指標.xlsx')
運行上面代碼會得到如下結果：
可以看到各項均已設置成功。
當日各省份創建訂單量情況：
我們同樣先利用Pandas庫處理得到當日各省份創建訂單量情況，具體實現代碼如下：
df_province = pd.DataFrame(df[df['創建日期'] == '2021-04-11'].groupby('省份')['order_id'].count())
df_province = df_province.reset_index()
df_province = df_province.sort_values(by = 'order_id',ascending = False)
df_province = df_province.rename(columns = {'order_id':'創建訂單量'})
df_province
運行上面代碼會得到如下結果：
在得到各省份當日創建訂單量的絕對數值之后，同樣對其進行格式設置，具體設置代碼如下：
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import colors
from openpyxl.styles import Font
from openpyxl.styles import PatternFill
from openpyxl.styles import Border, Side
from openpyxl.styles import Alignment
from openpyxl.formatting.rule import DataBarRule
wb = Workbook()
ws = wb.active
for r in dataframe_to_rows(df_province,index = False,header = True):
ws.append(r)
#對第1行至第11行的單元格進行設置
for row in ws[1:11]:
for c in row:
#字體設置
c.font = Font(name = '微軟雅黑',size = 12)
#對齊方式設置
c.alignment = Alignment(horizontal = "center")
#邊框線設置
c.border = Border(left = Side(border_style = "thin",color = "FF000000"),
right = Side(border_style = "thin",color = "FF000000"),
top = Side(border_style = "thin",color = "FF000000"),
bottom = Side(border_style = "thin",color = "FF000000"))
#設置進度條條件格式
rule = DataBarRule(start_type = 'min',end_type = 'max',
color="FF638EC6", showValue=True, minLength=None, maxLength=None)
ws.conditional_formatting.add('B1:B11',rule)
#對第1行標題行進行設置
for c in ws[1]:
c.font = Font(name = '微軟雅黑',size = 12,bold = True,color = "FFFFFFFF")
c.fill = PatternFill(fill_type = 'solid',start_color='FFFF6100')
#調整列寬
ws.column_dimensions['A'].width = 17
ws.column_dimensions['B'].width = 13
#保存調整后的文件
wb.save(r'D:Data-Scienceshareexcel-python報表自動化各省份銷量情況.xlsx')
運行上面代碼會得到如下結果：
最近一段時間創建訂單量趨勢：
一般用折線圖的形式反映某個指標的趨勢情況，我們前面也講過，在實際工作中我們一般用matplotlib或者其他可視化的庫進行圖表繪制，并將其進行保存，然后再利用openpyxl庫將圖表插入到Excel中。
先利用matplotlib庫進行繪圖，具體實現代碼如下：
%matplotlib inline
import matplotlib.pyplot as plt
plt.rcParams["font.sans-serif"]='SimHei'#解決中文亂碼
#設置圖表大小
plt.figure(figsize = (10,6))
df.groupby('創建日期')['order_id'].count().plot()
plt.title('4.2 - 4.11 創建訂單量分日趨勢')
plt.xlabel('日期')
plt.ylabel('訂單量')
#將圖表保存到本地
plt.savefig(r'D:Data-Scienceshareexcel-python報表自動化.2 - 4.11 創建訂單量分日趨勢.png')
將保存到本地的圖表插入到Excel中，具體實現代碼如下：
from openpyxl import Workbook
from openpyxl.drawing.image import Image
wb = Workbook()
ws = wb.active
img = Image(r'D:Data-Scienceshareexcel-python報表自動化.2 - 4.11 創建訂單量分日趨勢.png')
ws.add_image(img, 'A1')
wb.save(r'D:Data-Scienceshareexcel-python報表自動化.2 - 4.11 創建訂單量分日趨勢.xlsx')
運行上面代碼會得到如下結果，可以看到圖表已經被成功插入到Excel中：
到這里我們的一份自動化報表的代碼就完成了，以后每次需要用到這份報表的時候，把上面代碼執行一遍，結果馬上就可以出來。這就是為什么推薦用python來提高平時大量數據的處理效率了，又快又豐富！想了解更多，歡迎聯系小陸老師哦。
