您好,登錄后才能下訂單哦!
本篇內(nèi)容主要講解“怎么用Python實現(xiàn)報表自動化”,感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷,實用性強。下面就讓小編來帶大家學(xué)習(xí)“怎么用Python實現(xiàn)報表自動化”吧!
我們一般在最開始做報表的時候,基本都是從Excel開始的,都是利用Excel在做報表,所以我們先了解下Excel的基本組成。
下圖是Excel的中各個部分的組成關(guān)系,我們工作中每天會處理很多Excel文件,一個Excel文件其實就是一個工作簿。你在每次新建一個Excel文件時,文件名都會默認是工作簿x,其中x就是你新建的文件個數(shù)。而一個工作簿里面又可以有多個Sheet,不同Sheet之間是一個獨立的表。每一個Sheet里面又由若干個單元格組成。每一個單元格又有若干的元素或?qū)傩?,我們一般針對Excel文件進行設(shè)置最多的其實就是針對單元格的元素進行設(shè)置。
而針對單元格元素進行設(shè)置的主要內(nèi)容其實就是如下圖菜單欄中顯示,比如字體、對齊方式、條件格式等內(nèi)容。本書也是按照Excel菜單欄中的各個模塊進行編寫。
下圖是我整理的做一份自動化報表需要經(jīng)歷的流程,主要分為5個步驟:
第一步是對要做的報表進行步驟拆解,這個步驟拆解和用不用工具或者是用什么工具沒有直接關(guān)系,比如做報表的第一步一般都是收集數(shù)據(jù),這個數(shù)據(jù)可能是線下人員記錄在紙質(zhì)筆記本上的,也可能是存儲在Excel表里面的,還有可能是存儲在數(shù)據(jù)庫里面的。會因為數(shù)據(jù)源的類型或者是存儲方式不同,對應(yīng)的收集數(shù)據(jù)方式會不一樣,但是收集數(shù)據(jù)這個步驟本身是不會變的,這個步驟的目的就是把數(shù)據(jù)收集過來。
第二步是去想第一步里面涉及到的每一個具體步驟對應(yīng)的代碼實現(xiàn)方式,一般都是去找對應(yīng)每一步的代碼,比如導(dǎo)入數(shù)據(jù)的代碼是什么樣的,再比如重復(fù)值刪除的代碼是什么樣的。
第三步是將第二步中各個步驟對應(yīng)的代碼進行組合,組合成一個完整的代碼。
第四步是對第三步完整代碼得出來的報表結(jié)果進行驗證,看結(jié)果是否正確。
第五步就是等待調(diào)用,看什么時候需要制作報表了,然后就將寫好的代碼執(zhí)行一遍就行。
其實報表自動化本質(zhì)上就是讓機器代替人工做事情的過程,我們只需要把我們?nèi)斯ば枰龅拿恳粋€步驟轉(zhuǎn)化成機器可以理解的語言,也就是代碼,然后讓機器自動去執(zhí)行,這其實就是實現(xiàn)了自動化。
這一節(jié)給大家演示下在實際工作中如何結(jié)合Pandas和openpyxl來自動化生成報表。
假設(shè)我們現(xiàn)在有如下一份數(shù)據(jù)集:
現(xiàn)在我們需要根據(jù)這份數(shù)據(jù)集來制作每天的日報情況,會主要包含三方面:
當(dāng)日各項指標的同環(huán)比情況;
當(dāng)日各省份創(chuàng)建訂單量情況;
最近一段時間創(chuàng)建訂單量趨勢
接下來分別來實現(xiàn)這三部分。
我們先用Pandas對數(shù)據(jù)進行計算處理,得到各指標的同環(huán)比情況,具體實現(xiàn)代碼如下:
#導(dǎo)入文件 import pandas as pd df = pd.read_excel(r'D:\Data-Science\share\excel-python報表自動化\sale_data.xlsx') #構(gòu)造同時獲取不同指標的函數(shù) def get_data(date): create_cnt = df[df['創(chuàng)建日期'] == 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 #假設(shè)當(dāng)日是2021-04-11 #獲取不同時間段的各指標值 df_view = pd.DataFrame([get_data('2021-04-11') ,get_data('2021-04-10') ,get_data('2021-04-04')] ,columns = ['創(chuàng)建訂單量','付款訂單量','收貨訂單量','退款訂單量'] ,index = ['當(dāng)日','昨日','上周同期']).T df_view['環(huán)比'] = df_view['當(dāng)日'] / df_view['昨日'] - 1 df_view['同比'] = df_view['當(dāng)日'] / df_view['上周同期'] - 1 df_view
運行上面代碼會得到如下結(jié)果:
上面只是得到了各指標的同環(huán)比絕對數(shù)值,但是我們一般的日報在發(fā)出去之前都要做一些格式調(diào)整的,比如調(diào)整字體之類的。而格式調(diào)整就需要用到openpyxl庫,我們需要將Pandas庫中DataFrame格式的數(shù)據(jù)轉(zhuǎn)化為適用openpyxl庫的數(shù)據(jù)格式,具體實現(xiàn)代碼如下:
from openpyxl import Workbook from openpyxl.utils.dataframe import dataframe_to_rows #創(chuàng)建空工作簿 wb = Workbook() ws = wb.active #將DataFrame格式數(shù)據(jù)轉(zhuǎn)化為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')
運行上面代碼會得到如下結(jié)果,可以看到原始的數(shù)據(jù)文件看起來是很混亂的:
接下來我們針對上面原始數(shù)據(jù)文件進行格式調(diào)整,具體調(diào)整代碼如下:
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'] = '電商業(yè)務(wù)方向 2021/4/11 日報' #將標題行的單元格進行合并 ws.merge_cells('A1:F1') #合并單元格 #對第1行至第6行的單元格進行格式設(shè)置 for row in ws[1:6]: for c in row: #字體設(shè)置 c.font = Font(name = '微軟雅黑',size = 12) #對齊方式設(shè)置 c.alignment = Alignment(horizontal = "center") #邊框線設(shè)置 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")) #對標題行和表頭行進行特殊設(shè)置 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') #將環(huán)比和同比設(shè)置成百分比格式 for col in ws["E":"F"]: for r in col: r.number_format = '0.00%' #調(diào)整列寬 ws.column_dimensions['A'].width = 13 ws.column_dimensions['E'].width = 10 #保存調(diào)整后的文件 wb.save(r'D:\Data-Science\share\excel-python報表自動化\核心指標.xlsx')
運行上面代碼會得到如下結(jié)果:
可以看到各項均已設(shè)置成功。
df_province = pd.DataFrame(df[df['創(chuàng)建日期'] == '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':'創(chuàng)建訂單量'}) df_province
我們同樣先利用Pandas庫處理得到當(dāng)日各省份創(chuàng)建訂單量情況,具體實現(xiàn)代碼如下:
運行上面代碼會得到如下結(jié)果:
在得到各省份當(dāng)日創(chuàng)建訂單量的絕對數(shù)值之后,同樣對其進行格式設(shè)置,具體設(shè)置代碼如下:
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行的單元格進行設(shè)置 for row in ws[1:11]: for c in row: #字體設(shè)置 c.font = Font(name = '微軟雅黑',size = 12) #對齊方式設(shè)置 c.alignment = Alignment(horizontal = "center") #邊框線設(shè)置 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")) #設(shè)置進度條條件格式 rule = DataBarRule(start_type = 'min',end_type = 'max', color="FF638EC6", showValue=True, minLength=None, maxLength=None) ws.conditional_formatting.add('B1:B11',rule) #對第1行標題行進行設(shè)置 for c in ws[1]: c.font = Font(name = '微軟雅黑',size = 12,bold = True,color = "FFFFFFFF") c.fill = PatternFill(fill_type = 'solid',start_color='FFFF6100') #調(diào)整列寬 ws.column_dimensions['A'].width = 17 ws.column_dimensions['B'].width = 13 #保存調(diào)整后的文件 wb.save(r'D:\Data-Science\share\excel-python報表自動化\各省份銷量情況.xlsx')
運行上面代碼會得到如下結(jié)果:
一般用折線圖的形式反映某個指標的趨勢情況,我們前面也講過,在實際工作中我們一般用matplotlib或者其他可視化的庫進行圖表繪制,并將其進行保存,然后再利用openpyxl庫將圖表插入到Excel中。
先利用matplotlib庫進行繪圖,具體實現(xiàn)代碼如下:
%matplotlib inline import matplotlib.pyplot as plt plt.rcParams["font.sans-serif"]='SimHei'#解決中文亂碼 #設(shè)置圖表大小 plt.figure(figsize = (10,6)) df.groupby('創(chuàng)建日期')['order_id'].count().plot() plt.title('4.2 - 4.11 創(chuàng)建訂單量分日趨勢') plt.xlabel('日期') plt.ylabel('訂單量') #將圖表保存到本地 plt.savefig(r'D:\Data-Science\share\excel-python報表自動化\4.2 - 4.11 創(chuàng)建訂單量分日趨勢.png')
將保存到本地的圖表插入到Excel中,具體實現(xiàn)代碼如下:
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 創(chuàng)建訂單量分日趨勢.png') ws.add_image(img, 'A1') wb.save(r'D:\Data-Science\share\excel-python報表自動化\4.2 - 4.11 創(chuàng)建訂單量分日趨勢.xlsx')
運行上面代碼會得到如下結(jié)果,可以看到圖表已經(jīng)被成功插入到Excel中:
上面我們是把每一部分都單獨拆開來實現(xiàn),最后存儲在了不同的Excel文件中。當(dāng)然了,有的時候放在不同文件中會比較麻煩,我們就需要把這些結(jié)果合并在同一個Excel的相同Sheet或者不同Sheet中。
將不同的結(jié)果合并到同一個Sheet中:
將不同的結(jié)果合并到同一個Sheet中的難點在于不同表結(jié)果的結(jié)構(gòu)不一樣,而且需要在不同結(jié)果之間進行留白。
首先插入核心指標表df_review,插入方式與單獨的插入是一樣的,具體代碼如下:
for r in dataframe_to_rows(df_view,index = True,header = True): ws.append(r)
接下來就該插入各省份情況表df_province,因為append默認是從第一行開始插入的,而我們前面幾行已經(jīng)有df_view表的數(shù)據(jù)了,所以就不能用appen的方式進行插入,而只能通過遍歷每一個單元格的方式進行插入。
那我們怎么知道要遍歷哪些單元格呢?核心需要知道遍歷開始的行列和遍歷結(jié)束的行列。
遍歷開始的行 = df_view表占據(jù)的行 + 留白的行(一般表與表之間留2行) + 1
遍歷結(jié)束的行 = 遍歷開始的行 + df_province表占據(jù)的行
遍歷開始的列 = 1
遍歷結(jié)束的列 = df_province表占據(jù)的列
而又因為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表有多少行。
前面說過,遍歷開始的行是表占據(jù)的行加上留白的行再加1,一般留白的行是2,可是這里面為啥是df_view.shape[0] + 5呢?這是因為df_view.shape[0]是不包列名行的,同時在插入Excel中的時候會默認增加1行空行,所以就需要在留白行的基礎(chǔ)上再增加2行,即2 + 2 + 1 = 5。
因為range()函數(shù)是默認是從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]
將所有的數(shù)據(jù)插入以后就該對這些數(shù)據(jù)進行格式設(shè)置了,因為不同表的結(jié)構(gòu)不一樣,所以我們沒法直接批量針對所有的單元格進行格式設(shè)置,只能分范圍分別進行設(shè)置,而不同范圍的格式可能是一樣的,所以我們先預(yù)設(shè)一些格式變量,這樣后面用到的時候直接調(diào)取這些變量即可,減少代碼冗余,具體代碼如下:
#插入圖片 img = Image(r'D:\Data-Science\share\excel-python報表自動化\4.2 - 4.11 創(chuàng)建訂單量分日趨勢.png') ws.add_image(img, 'G1')
格式預(yù)設(shè)完之后就可以對各個范圍分別進行格式設(shè)置了,具體代碼如下:
#格式預(yù)設(shè) #表頭字體設(shè)置 title_Font_style = Font(name = '微軟雅黑',size = 12,bold = True,color = "FFFFFFFF") #普通內(nèi)容字體設(shè)置 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')
最后將上面所有代碼片段合并在一起,就是將不同的結(jié)果文件合并到同一個Sheet中的完整代碼,具體結(jié)果如下,可以看到不同結(jié)果文件合并在了一起,并且各自的格式設(shè)置完好。
將不同的結(jié)果合并到同一工作簿的不同Sheet中:
將不同的結(jié)果合并到同一工作簿的不同Sheet中比較好實現(xiàn),只需要新建幾個Sheet,然后針對不同的Sheet插入數(shù)據(jù)即可,具體實現(xiàn)代碼如下:
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 創(chuàng)建訂單量分日趨勢.png') ws2.add_image(img, 'A1') wb.save(r'D:\Data-Science\share\excel-python報表自動化\多結(jié)果合并_多Sheet.xlsx')
運行上面代碼,會得到如下結(jié)果,可以看到創(chuàng)建了3個Sheet,且不同的內(nèi)容保存到了不同Sheet中:
到這里我們的一份自動化報表的代碼就完成了,以后每次需要用到這份報表的時候,把上面代碼執(zhí)行一遍,結(jié)果馬上就可以出來,當(dāng)然了也可以設(shè)置定時執(zhí)行,到時間結(jié)果就自動發(fā)送到你郵箱里面啦。
到此,相信大家對“怎么用Python實現(xiàn)報表自動化”有了更深的了解,不妨來實際操作一番吧!這里是億速云網(wǎng)站,更多相關(guān)內(nèi)容可以進入相關(guān)頻道進行查詢,關(guān)注我們,繼續(xù)學(xué)習(xí)!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。