本章給大家演示一下在實際工作中如何結合 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進行報表自動化的內容，歡迎閱讀本書！