今天給大家分享一篇我新書《對比Excel，輕松學(xué)習(xí)Python報表自動化》中關(guān)于報表自動化實戰(zhàn)的內(nèi)容。關(guān)于這本書的介紹見：時隔500天后，對比Excel系列又一新書發(fā)布

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

1.Excel的基本組成
2.一份報表自動化的流程
3.報表自動化實戰(zhàn)
? ? -?當(dāng)日各項指標(biāo)同環(huán)比情況
? ? - 當(dāng)日各省份創(chuàng)建訂單量情況
? ? - 最近一段時間創(chuàng)建訂單量趨勢
4.將不同的結(jié)果進行合并
? ? -?將不同結(jié)果合并到同一個Sheet中
? ? - 將不同結(jié)果合并到同一個工作簿的不同Sheet中

Excel的基本組成

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

下圖是Excel的中各個部分的組成關(guān)系，我們工作中每天會處理很多Excel文件，一個Excel文件其實就是一個工作簿。你在每次新建一個Excel文件時，文件名都會默認是工作簿x，其中x就是你新建的文件個數(shù)。而一個工作簿里面又可以有多個Sheet，不同Sheet之間是一個獨立的表。每一個Sheet里面又由若干個單元格組成。每一個單元格又有若干的元素或?qū)傩裕覀円话汜槍xcel文件進行設(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)了自動化。

報表自動化實戰(zhàn)

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

假設(shè)我們現(xiàn)在有如下一份數(shù)據(jù)集：

現(xiàn)在我們需要根據(jù)這份數(shù)據(jù)集來制作每天的日報情況，會主要包含三方面：

當(dāng)日各項指標(biāo)的同環(huán)比情況；
當(dāng)日各省份創(chuàng)建訂單量情況；
最近一段時間創(chuàng)建訂單量趨勢

接下來分別來實現(xiàn)這三部分。

當(dāng)日各項指標(biāo)的同環(huán)比情況：

我們先用Pandas對數(shù)據(jù)進行計算處理，得到各指標(biāo)的同環(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)造同時獲取不同指標(biāo)的函數(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
#獲取不同時間段的各指標(biāo)值
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é)果：

上面只是得到了各指標(biāo)的同環(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報表自動化\核心指標(biāo)_原始.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']?=?'指標(biāo)'

#插入一行作為標(biāo)題行
ws.insert_rows(1)
ws['A1']?=?'電商業(yè)務(wù)方向?2021/4/11?日報'

#將標(biāo)題行的單元格進行合并
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"))

#對標(biāo)題行和表頭行進行特殊設(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報表自動化\核心指標(biāo).xlsx')

運行上面代碼會得到如下結(jié)果：

可以看到各項均已設(shè)置成功。

當(dāng)日各省份創(chuàng)建訂單量情況：

我們同樣先利用Pandas庫處理得到當(dāng)日各省份創(chuàng)建訂單量情況，具體實現(xiàn)代碼如下：

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

運行上面代碼會得到如下結(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行標(biāo)題行進行設(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é)果：

最近一段時間創(chuàng)建訂單量趨勢：

一般用折線圖的形式反映某個指標(biāo)的趨勢情況，我們前面也講過，在實際工作中我們一般用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中：

將不同的結(jié)果進行合并

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

將不同的結(jié)果合并到同一個Sheet中：

將不同的結(jié)果合并到同一個Sheet中的難點在于不同表結(jié)果的結(jié)構(gòu)不一樣，而且需要在不同結(jié)果之間進行留白。

首先插入核心指標(biāo)表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]

接下來就該插入圖片了，插入圖片的方式與前面單獨的插入是一致的，具體代碼如下：

#插入圖片
img?=?Image(r'D:\Data-Science\share\excel-python報表自動化\4.2?-?4.11?創(chuàng)建訂單量分日趨勢.png')
ws.add_image(img,?'G1')

將所有的數(shù)據(jù)插入以后就該對這些數(shù)據(jù)進行格式設(shè)置了，因為不同表的結(jié)構(gòu)不一樣，所以我們沒法直接批量針對所有的單元格進行格式設(shè)置，只能分范圍分別進行設(shè)置，而不同范圍的格式可能是一樣的，所以我們先預(yù)設(shè)一些格式變量，這樣后面用到的時候直接調(diào)取這些變量即可，減少代碼冗余，具體代碼如下：

#格式預(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')

格式預(yù)設(shè)完之后就可以對各個范圍分別進行格式設(shè)置了，具體代碼如下：

#對A1至F6范圍內(nèi)的單元格進行設(shè)置
for?row?in?ws['A1':'F6']:
????for?c?in?row:
????????c.font?=?plain_Font_style
????????c.alignment?=?Alignment_style
????????c.border?=?Border_style

#對第1行和第2行的單元格進行設(shè)置
for?row?in?ws[1:2]:
????for?c?in?row:
????????c.font?=?title_Font_style
????????c.fill?=?PatternFill_style

#對E列和F列的單元格進行設(shè)置
for?col?in?ws["E":"F"]:
????for?r?in?col:
????????r.number_format?=?'0.00%'

#對A9至B19范圍內(nèi)的單元格進行設(shè)置
for?row?in?ws['A9':'B19']:
????for?c?in?row:
????????c.font?=?plain_Font_style
????????c.alignment?=?Alignment_style
????????c.border?=?Border_style

#對A9至B9范圍內(nèi)的單元格進行設(shè)置
for?row?in?ws['A9':'B9']:
????for?c?in?row:
????????c.font?=?title_Font_style
????????c.fill?=?PatternFill_style
????????
#設(shè)置進度條
rule?=?DataBarRule(start_type?=?'min',end_type?=?'max',
????????????????????color="FF638EC6",?showValue=True,?minLength=None,?maxLength=None)
ws.conditional_formatting.add('B10:B19',rule)

#調(diào)整列寬
ws.column_dimensions['A'].width?=?17
ws.column_dimensions['B'].width?=?13
ws.column_dimensions['E'].width?=?10

最后將上面所有代碼片段合并在一起，就是將不同的結(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?=?"核心指標(biāo)"?
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')