實戰｜如何製作資料報表並實現自動化？

本章給大家演示一下在實際工作中如何結合 Pandas 庫和 openpyxl 庫來自動化生成報表。假設我們現在有如圖 1 所示的資料集。

（圖1）

現在需要根據這份資料集來製作每天的日報情況，主要包含以下 3 個方面。

• 當日各項指標的同/環比情況。

• 當日各省份創建訂單量情況。

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

接下來分別實現。

01、當日各項指標的同/環比情況

我們先用 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

運行上面程式碼會得到如圖 2 所示結果。

（圖2）

上面只是得到了各指標的同/環比絕對數值，但是日報在發出去之前一般都要做一些格式調整，比如調整字型。而格式調整需要用到 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')

運行上面程式碼會得到如圖 3 所示結果，可以看到原始的資料檔案看起來是很混亂的。

（圖3）

接下來，對上面的原始資料檔案進行格式調整，具體調整程式碼如下。

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) #第 2 行是空的，刪除第 2 行 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')

運行上面程式碼會得到如圖 4 所示結果。

（圖4）

可以看到各項均已設定成功。

02、當日各省份創建訂單量情況

我們同樣先利用 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

運行上面程式碼會得到如圖 5 所示結果。

（圖5）

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

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')

運行上面程式碼會得到如圖6所示結果。

（圖6）

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

一般用折線圖反映某個指標的趨勢情況，我們前面也講過，在實際工作中一般用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 報表自動化4.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 報表自動化4.2 - 4.11 創建訂單量 分日趨勢.png') ws.add_image(img, 'A1') wb.save(r'D:Data-Scienceshareexcel-python 報表自動化4.2 - 4.11 創建訂單量分日 趨勢.xlsx')

運行上面程式碼會得到如圖 7 所示結果，可以看到圖表已經被成功插入 Excel 中。

（圖7）

04、將不同的結果進行合併

上面我們是把每一部分都單獨拆開來實現的，最後儲存在了不同的 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()方法預設是從第 1 行開始插入的，而我們前面幾行已經有 df_view 表的資料了，所以就不能用 append()方法插入，而只能通過遍歷每一個單元格的方式。

那我們怎麼知道要遍歷哪些單元格呢？核心是需要知道遍歷開始的行/列和遍歷結束的行/列。

遍歷開始的行 = 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。

因為 range()函數預設是從 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-Scienceshareexcel-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 範圍內的單元格進行設定 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 行的單元格進行設定 for row in ws[1:2]: for c in row: c.font = title_Font_style c.fill = PatternFill_style #對 E 列和 F 列的單元格進行設定 for col in ws["E":"F"]: for r in col: r.number_format = '0.00%' #對 A9 至 B19 範圍內的單元格進行設定 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 範圍內的單元格進行設定 for row in ws['A9':'B9']: for c in row: 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 中的完整程式碼，具體如下。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 #先將核心指標 df_view 表插入進去 for r in dataframe_to_rows(df_view,index = True,header = True): ws.append(r) #再將各省份情況 df_province 表插入進去 #先將表頭插入 for j in range(df_province.shape[1]): ws.cell(row = df_view.shape[0] + 5,column = 1 + j).value = df_province.columns[r] #再把具體的值插入 #先遍歷行 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-Scienceshareexcel-python 報表自動化4.2 - 4.11 創建訂單量 分日趨勢.png') ws.add_image(img, 'G1') ##---格式調整--- ws.delete_rows(2) ws['A1'] = '指標' ws.insert_rows(1) ws['A1'] = '電商業務方向 2021/4/11 日報' ws.merge_cells('A1:F1') #合併單元格 #格式預設 #表頭字型設定 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 範圍內的單元格進行設定 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 行的單元格進行設定 for row in ws[1:2]: for c in row: c.font = title_Font_style c.fill = PatternFill_style #對 E 列和 F 列的單元格進行設定 for col in ws["E":"F"]: for r in col: r.number_format = '0.00%' #對 A9 至 B19 範圍內的單元格進行設定 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 範圍內的單元格進行設定 for row in ws['A9':'B9']: for c in row: 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 #將結果檔案進行儲存 wb.save(r'D:Data-Scienceshareexcel-python 報表自動化多結果合併.xlsx')

運行上面程式碼，會得到如圖 8 所示結果，可以看到不同結果檔案合併在了一起，並且各自的格式設定完好。

（圖8）

將不同的結果合併到同一工作簿的不同 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'D:Data-Scienceshareexcel-python 報表自動化4.2 - 4.11 創建訂單量 分日趨勢.png') ws2.add_image(img, 'A1') wb.save(r'D:Data-Scienceshareexcel-python 報表自動化多結果合併_多 Sheet.xlsx')

運行上面程式碼，會得到如圖 9 所示結果，可以看到創建了 3 個 Sheet，且不同的內容被儲存到了不同 Sheet 中。

（圖9）

本文節選自《對比Excel，輕鬆學習Python報表自動化》一書，更多關於使用Python進行報表自動化的內容，歡迎閱讀本書！