對比Excel，輕松學習Python報表自動化實戰，這篇教程有點肝!

大家好，我是黃同學

今天給大家分享一篇俊紅新書《對比Excel，輕松學習Python報表自動化》中關于報表自動化實戰的内容，文末也會免費贈送幾本新書。

本篇文章将帶你了解報表自動化的流程，并教你用Python實現工作中的一個報表自動化實戰，篇幅較長，建議先收藏，文章具體的目錄為：

1.Excel的基本組成
2.一份報表自動化的流程
3.報表自動化實戰
-當日各項指标同環比情況
- 當日各省份創建訂單量情況
- 最近一段時間創建訂單量趨勢
4.将不同的結果進行合并
-将不同結果合并到同一個Sheet中
- 将不同結果合并到同一個工作簿的不同Sheet中

Excel的基本組成

我們一般在最開始做報表的時候，基本都是從Excel開始的，都是利用Excel在做報表，所以我們先了解下Excel的基本組成。

下圖是Excel的中各個部分的組成關系，我們工作中每天會處理很多Excel文件，一個Excel文件其實就是一個工作簿。你在每次新建一個Excel文件時，文件名都會默認是工作簿x，其中x就是你新建的文件個數。而一個工作簿裡面又可以有多個Sheet，不同Sheet之間是一個獨立的表。每一個Sheet裡面又由若幹個單元格組成。每一個單元格又有若幹的元素或屬性，我們一般針對Excel文件進行設置最多的其實就是針對單元格的元素進行設置。

而針對單元格元素進行設置的主要内容其實就是如下圖菜單欄中顯示，比如字體、對齊方式、條件格式等内容。本書也是按照Excel菜單欄中的各個模塊進行編寫。

一份自動化報表的流程

下圖是我整理的做一份自動化報表需要經曆的流程，主要分為5個步驟：

第一步是對要做的報表進行步驟拆解，這個步驟拆解和用不用工具或者是用什麼工具沒有直接關系，比如做報表的第一步一般都是收集數據，這個數據可能是線下人員記錄在紙質筆記本上的，也可能是存儲在Excel表裡面的，還有可能是存儲在數據庫裡面的。會因為數據源的類型或者是存儲方式不同，對應的收集數據方式會不一樣，但是收集數據這個步驟本身是不會變的，這個步驟的目的就是把數據收集過來。

第二步是去想第一步裡面涉及到的每一個具體步驟對應的代碼實現方式，一般都是去找對應每一步的代碼，比如導入數據的代碼是什麼樣的，再比如重複值删除的代碼是什麼樣的。

第三步是将第二步中各個步驟對應的代碼進行組合，組合成一個完整的代碼。

第四步是對第三步完整代碼得出來的報表結果進行驗證，看結果是否正确。

第五步就是等待調用，看什麼時候需要制作報表了，然後就将寫好的代碼執行一遍就行。

其實報表自動化本質上就是讓機器代替人工做事情的過程，我們隻需要把我們人工需要做的每一個步驟轉化成機器可以理解的語言，也就是代碼，然後讓機器自動去執行，這其實就是實現了自動化。

報表自動化實戰

這一節給大家演示下在實際工作中如何結合Pandas和openpyxl來自動化生成報表。

假設我們現在有如下一份數據集：

現在我們需要根據這份數據集來制作每天的日報情況，會主要包含三方面：

• 當日各項指标的同環比情況；
• 當日各省份創建訂單量情況；
• 最近一段時間創建訂單量趨勢

接下來分别來實現這三部分。

當日各項指标的同環比情況：

我們先用Pandas對數據進行計算處理，得到各指标的同環比情況，具體實現代碼如下：

#導入文件
importpandasaspd
df=pd.read_excel(r'D:\Data-Science\share\excel-python報表自動化\sale_data.xlsx')

#構造同時獲取不同指标的函數
defget_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()
returncreate_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庫的數據格式，具體實現代碼如下：

fromopenpyxlimportWorkbook
fromopenpyxl.utils.dataframeimportdataframe_to_rows

#創建空工作簿
wb=Workbook()
ws=wb.active

#将DataFrame格式數據轉化為openpyxl格式
forrindataframe_to_rows(df_view,index=True,header=True):
ws.append(r)

wb.save(r'D:\Data-Science\share\excel-python報表自動化\核心指标_原始.xlsx')

運行上面代碼會得到如下結果，可以看到原始的數據文件看起來是很混亂的：

接下來我們針對上面原始數據文件進行格式調整，具體調整代碼如下：

fromopenpyxlimportWorkbook
fromopenpyxl.utils.dataframeimportdataframe_to_rows
fromopenpyxl.stylesimportcolors
fromopenpyxl.stylesimportFont
fromopenpyxl.stylesimportPatternFill
fromopenpyxl.stylesimportBorder,Side
fromopenpyxl.stylesimportAlignment

wb=Workbook()
ws=wb.active

forrindataframe_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行的單元格進行格式設置
forrowinws[1:6]:
forcinrow:
#字體設置
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'))

#對标題行和表頭行進行特殊設置
forrowinws[1:2]:
forcinrow:
c.font=Font(name='微軟雅黑',size=12,bold=True,color='FFFFFFFF')
c.fill=PatternFill(fill_type='solid',start_color='FFFF6100')

#将環比和同比設置成百分比格式
forcolinws['E':'F']:
forrincol:
r.number_format='0.00%'

#調整列寬
ws.column_dimensions['A'].width=13
ws.column_dimensions['E'].width=10

#保存調整後的文件
wb.save(r'D:\Data-Science\share\excel-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

運行上面代碼會得到如下結果：

在得到各省份當日創建訂單量的絕對數值之後，同樣對其進行格式設置，具體設置代碼如下：

fromopenpyxlimportWorkbook
fromopenpyxl.utils.dataframeimportdataframe_to_rows
fromopenpyxl.stylesimportcolors
fromopenpyxl.stylesimportFont
fromopenpyxl.stylesimportPatternFill
fromopenpyxl.stylesimportBorder,Side
fromopenpyxl.stylesimportAlignment
fromopenpyxl.formatting.ruleimportDataBarRule

wb=Workbook()
ws=wb.active

forrindataframe_to_rows(df_province,index=False,header=True):
ws.append(r)

#對第1行至第11行的單元格進行設置
forrowinws[1:11]:
forcinrow:
#字體設置
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行标題行進行設置
forcinws[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-Science\share\excel-python報表自動化\各省份銷量情況.xlsx')

運行上面代碼會得到如下結果：

最近一段時間創建訂單量趨勢：

一般用折線圖的形式反映某個指标的趨勢情況，我們前面也講過，在實際工作中我們一般用matplotlib或者其他可視化的庫進行圖表繪制，并将其進行保存，然後再利用openpyxl庫将圖表插入到Excel中。

先利用matplotlib庫進行繪圖，具體實現代碼如下：

%matplotlibinline
importmatplotlib.pyplotasplt
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-Science\share\excel-python報表自動化\4.2-4.11創建訂單量分日趨勢.png')

将保存到本地的圖表插入到Excel中，具體實現代碼如下：

fromopenpyxlimportWorkbook
fromopenpyxl.drawing.imageimportImage

wb=Workbook()
ws=wb.active

img=Image(r'D:\Data-Science\share\excel-python報表自動化\4.2-4.11創建訂單量分日趨勢.png')

ws.add_image(img,'A1')

wb.save(r'D:\Data-Science\share\excel-python報表自動化\4.2-4.11創建訂單量分日趨勢.xlsx')

運行上面代碼會得到如下結果，可以看到圖表已經被成功插入到Excel中：

将不同的結果進行合并

上面我們是把每一部分都單獨拆開來實現，最後存儲在了不同的Excel文件中。當然了，有的時候放在不同文件中會比較麻煩，我們就需要把這些結果合并在同一個Excel的相同Sheet或者不同Sheet中。

将不同的結果合并到同一個Sheet中：

将不同的結果合并到同一個Sheet中的難點在于不同表結果的結構不一樣，而且需要在不同結果之間進行留白。

首先插入核心指标表df_review，插入方式與單獨的插入是一樣的，具體代碼如下：

forrindataframe_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中獲取列名的方式和獲取具體值的方式不太一樣，所以我們需要分别插入，先插入列名，具體代碼如下：

forjinrange(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。

因為range()函數是默認是從0開始的，而Excel中的列是從1開始的，所以column需要加1。

上面的代碼隻是把df_province表的列名插入進來了，接下來插入具體的值，方式與插入列名的方式一緻，隻不過需要在列名的下一行開始插入，具體代碼如下：

#再把具體的值插入
foriinrange(df_province.shape[0]):
forjinrange(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'D:\Data-Science\share\excel-python報表自動化\4.2-4.11創建訂單量分日趨勢.png')
ws.add_image(img,'G1')

将所有的數據插入以後就該對這些數據進行格式設置了，因為不同表的結構不一樣，所以我們沒法直接批量針對所有的單元格進行格式設置，隻能分範圍分别進行設置，而不同範圍的格式可能是一樣的，所以我們先預設一些格式變量，這樣後面用到的時候直接調取這些變量即可，減少代碼冗餘，具體代碼如下：

#格式預設

#表頭字體設置
title_Font_style=Font(name='微軟雅黑',size=12,bold=True,color='FFFFFFFF')
#普通内容字體設置
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')

格式預設完之後就可以對各個範圍分别進行格式設置了，具體代碼如下：

#對A1至F6範圍内的單元格進行設置
forrowinws['A1':'F6']:
forcinrow:
c.font=plain_Font_style
c.alignment=Alignment_style
c.border=Border_style

#對第1行和第2行的單元格進行設置
forrowinws[1:2]:
forcinrow:
c.font=title_Font_style
c.fill=PatternFill_style

#對E列和F列的單元格進行設置
forcolinws['E':'F']:
forrincol:
r.number_format='0.00%'

#對A9至B19範圍内的單元格進行設置
forrowinws['A9':'B19']:
forcinrow:
c.font=plain_Font_style
c.alignment=Alignment_style
c.border=Border_style

#對A9至B9範圍内的單元格進行設置
forrowinws['A9':'B9']:
forcinrow:
c.font=title_Font_style
c.fill=PatternFill_style

#設置進度條
rule=DataBarRule(start_type='min',end_type='max',
color='FF638EC6',showValue=True,minLength=None,maxLength=None)
ws.conditional_formatting.add('B10:B19',rule)

#調整列寬
ws.column_dimensions['A'].width=17
ws.column_dimensions['B'].width=13
ws.column_dimensions['E'].width=10

最後将上面所有代碼片段合并在一起，就是将不同的結果文件合并到同一個Sheet中的完整代碼，具體結果如下，可以看到不同結果文件合并在了一起，并且各自的格式設置完好。

将不同的結果合并到同一工作簿的不同Sheet中：

将不同的結果合并到同一工作簿的不同Sheet中比較好實現，隻需要新建幾個Sheet，然後針對不同的Sheet插入數據即可，具體實現代碼如下：

fromopenpyxlimportWorkbook
fromopenpyxl.utils.dataframeimportdataframe_to_rows

wb=Workbook()
ws=wb.active

ws1=wb.create_sheet()
ws2=wb.create_sheet()

#更改sheet的名稱
ws.title='核心指标'
ws1.title='各省份銷情況'
ws2.title='分日趨勢'

forr1indataframe_to_rows(df_view,index=True,header=True):
ws.append(r1)

forr2indataframe_to_rows(df_province,index=False,header=True):
ws1.append(r2)

img=Image(r'D:\Data-Science\share\excel-python報表自動化\4.2-4.11創建訂單量分日趨勢.png')

ws2.add_image(img,'A1')

wb.save(r'D:\Data-Science\share\excel-python報表自動化\多結果合并_多Sheet.xlsx')

運行上面代碼，會得到如下結果，可以看到創建了3個Sheet，且不同的内容保存到了不同Sheet中：

到這裡我們的一份自動化報表的代碼就完成了，以後每次需要用到這份報表的時候，把上面代碼執行一遍，結果馬上就可以出來，當然了也可以設置定時執行，到時間結果就自動發送到你郵箱裡面啦。

---