之前小F分享了不少關於Python自動化操作Excel的文章，大家都挺喜歡的。
所以今天就帶大家來實戰一波，使用Python自動化生成資料報表！
從一條條的資料中，創建出一張資料報表，得出你想要的東西，提高效率。
主要使用到pandas、xlwings以及matplotlib這幾個庫。
先來看一下動態的GIF，都是程式自動生成。
下面我們就來看看這個案例吧，水果蔬菜銷售報表。
原始資料如下，主要有水果蔬菜名稱、銷售日期、銷售數量、平均價格、平均成本、總收入、總成本、總利潤等。
先匯入相關庫，使用pandas讀取原始資料。
import pandas as pdimport xlwings as xwimport matplotlib。pyplot as plt# 對齊資料pd。set_option（‘display。unicode。ambiguous_as_wide’， True）pd。set_option（‘display。unicode。east_asian_width’， True）# 讀取資料df = pd。read_csv（r“fruit_and_veg_sales。csv”）print（df）
結果如下。
一共是有1000行的銷售資料。
使用xlwings庫建立一個Excel工作簿，在工作簿中建立一個表，表名為fruit_and_veg_sales，然後將原始資料複製進去。
# 建立原始資料表並複製資料wb = xw。Book（）sht = wb。sheets［“Sheet1”］sht。name = “fruit_and_veg_sales”sht。range（“A1”）。options（index=False）。value = d
關於xlwings庫的使用，小F推薦兩個文件地址
中文版：
https：//www。kancloud。cn/gnefnuy/xlwings-docs/1127455
英文版：
https：//docs。xlwings。org/en/stable/index。html
推薦使用中文版，可以降低學習難度。。。
當然關於Excel的VBA操作，也可以看看微軟的文件。
地址：
https：//docs。microsoft。com/zh-cn/office/vba/api/overview/excel
將原始資料取過來後，再在工作簿中建立一個視覺化表，即Dashboard表。
# 建立表wb。sheets。add（‘Dashboard’）sht_dashboard = wb。sheets（‘Dashboard’）
現在，我們有了一個包含兩個工作表的Excel工作簿。fruit_and_veg_sales表有我們的資料，Dashboard表則是空白的。
下面使用pandas來處理資料，生成Dashboard表的資料資訊。
DashBoard表的頭兩個表格，一個是產品的利潤表格，一個是產品的銷售數量表格。
使用到了pandas的資料透視表函式。
# 總利潤透視表pv_total_profit = pd。pivot_table（df， index=‘類別’， values=‘總利潤（美元）’， aggfunc=‘sum’）print（pv_total_profit）# 銷售數量透視表pv_quantity_sold = pd。pivot_table（df， index=‘類別’， values=‘銷售數量’， aggfunc=‘sum’）print（pv_quantity_sold）
得到資料如下。
稍後會將資料放置到Excel的表中去。
下面對月份進行分組彙總，得出每個月的銷售情況。
# 檢視每列的資料型別print（df。dtypes）df［“銷售日期”］ = pd。to_datetime（df［“銷售日期”］）# 每日的資料情況gb_date_sold = df。groupby（df［“銷售日期”］。dt。to_period（‘m’））。sum（）［［“銷售數量”， ‘總收入（美元）’， ‘總成本（美元）’， “總利潤（美元）”］］gb_date_sold。index = gb_date_sold。index。to_series（）。astype（str）print（gb_date_sold）
得到結果如下。
這裡先對資料進行了查詢，發現日期列為object，是不能進行分組彙總的。
所以使用了pd。to_datetime（）對其進行了格式轉換，而後根據時間進行分組彙總，得到每個月的資料情況。
最後一個groupby將為Dashboard表提供第四個資料資訊。
# 總收入前8的日期資料gb_top_revenue = （df。groupby（df［“銷售日期”］） 。sum（） 。sort_values（‘總收入（美元）’， ascending=False） 。head（8） ）［［“銷售數量”， ‘總收入（美元）’， ‘總成本（美元）’， “總利潤（美元）”］］print（gb_top_revenue）
總收入前8的日期，得到結果如下。
現在我們有了4份資料，可以將其附加到Excel中。
# 設定背景顏色， 從A1單元格到Z1000單元格的矩形區域sht_dashboard。range（‘A1：Z1000’）。color = （198， 224， 180）# A、B列的列寬sht_dashboard。range（‘A：B’）。column_width = 2。22print（sht_dashboard。range（‘B2’）。api。font_object。properties。get（））# B2單元格， 文字內容、字型、字號、粗體、顏色、行高（主標題）sht_dashboard。range（‘B2’）。value = ‘銷售資料報表’sht_dashboard。range（‘B2’）。api。font_object。name。set（‘黑體’）sht_dashboard。range（‘B2’）。api。font_object。font_size。set（48）sht_dashboard。range（‘B2’）。api。font_object。bold。set（True）sht_dashboard。range（‘B2’）。api。font_object。color。set（［0， 0， 0］）sht_dashboard。range（‘B2’）。row_height = 61。2# B2單元格到W2單元格的矩形區域， 下邊框的粗細及顏色sht_dashboard。range（‘B2：W2’）。api。get_border（which_border=9）。weight。set（4）sht_dashboard。range（‘B2：W2’）。api。get_border（which_border=9）。color。set（［0， 176， 80］）# 不同產品總的收益情況圖表名稱、字型、字號、粗體、顏色（副標題）sht_dashboard。range（‘M2’）。value = ‘每種產品的收益情況’sht_dashboard。range（‘M2’）。api。font_object。name。set（‘黑體’）sht_dashboard。range（‘M2’）。api。font_object。font_size。set（20）sht_dashboard。range（‘M2’）。api。font_object。bold。set（True）sht_dashboard。range（‘M2’）。api。font_object。color。set（［0， 0， 0］）# 主標題和副標題的分割線， 粗細、顏色、線型sht_dashboard。range（‘L2’）。api。get_border（which_border=7）。weight。set（3）sht_dashboard。range（‘L2’）。api。get_border（which_border=7）。color。set（［0， 176， 80］）sht_dashboard。range（‘L2’）。api。get_border（which_border=7）。line_style。set（-4115）
先配置一些基本內容，比如文字，顏色背景，邊框線等，如下圖。
使用函式，批次生成四個表格的格式。
# 表格生成函式。def create_formatted_summary（header_cell， title， df_summary， color）： “”“ Parameters —————— header_cell ： Str 左上角單元格位置， 放置資料 title ： Str 當前表格的標題 df_summary ： DataFrame 表格的資料 color ： Str 表格填充色 ”“” # 可選擇的表格填充色 colors = {“purple”： ［（112， 48， 160）， （161， 98， 208）］， “blue”： ［（0， 112， 192）， （155， 194， 230）］， “green”： ［（0， 176， 80）， （169， 208， 142）］， “yellow”： ［（255， 192， 0）， （255， 217， 102）］} # 設定表格標題的列寬 sht_dashboard。range（header_cell）。column_width = 1。5 # 獲取單元格的行列數 row， col = sht_dashboard。range（header_cell）。row， sht_dashboard。range（header_cell）。column # 設定表格的標題及相關資訊， 如：字號、行高、向左居中對齊、顏色、粗體、表格的背景顏色等 summary_title_range = sht_dashboard。range（（row， col）） summary_title_range。value = title summary_title_range。api。font_object。font_size。set（14） summary_title_range。row_height = 32。5 # 垂直對齊方式 summary_title_range。api。verticalalignment = xw。constants。HAlign。xlHAlignCenter summary_title_range。api。font_object。color。set（［255， 255， 255］） summary_title_range。api。font_object。bold。set（True） sht_dashboard。range（（row， col）， （row， col + len（df_summary。columns） + 1））。color = colors［color］［0］ # Darker color # 設定表格內容、起始單元格、資料填充、字型大小、粗體、顏色填充 summary_header_range = sht_dashboard。range（（row + 1， col + 1）） summary_header_range。value = df_summary summary_header_range = summary_header_range。expand（‘right’） summary_header_range。api。font_object。font_size。set（11） summary_header_range。api。font_object。bold。set（True） sht_dashboard。range（（row + 1， col）， （row + 1， col + len（df_summary。columns） + 1））。color = colors［color］［1］ # Darker color sht_dashboard。range（（row + 1， col + 1）， （row + len（df_summary）， col + len（df_summary。columns） + 1））。autofit（） for num in range（1， len（df_summary） + 2， 2）： sht_dashboard。range（（row + num， col）， （row + num， col + len（df_summary。columns） + 1））。color = colors［color］［1］ # 找到表格的最後一行 last_row = sht_dashboard。range（（row + 1， col + 1））。expand（‘down’）。last_cell。row side_border_range = sht_dashboard。range（（row + 1， col）， （last_row， col）） # 給表格左邊新增帶顏色的邊框 side_border_range。api。get_border（which_border=7）。weight。set（3） side_border_range。api。get_border（which_border=7）。color。set（colors［color］［1］） side_border_range。api。get_border（which_border=7）。line_style。set（-4115）# 生成4個表格create_formatted_summary（‘B5’， ‘每種產品的收益情況’， pv_total_profit， ‘green’）create_formatted_summary（‘B17’， ‘每種產品的售出情況’， pv_quantity_sold， ‘purple’）create_formatted_summary（‘F17’， ‘每月的銷售情況’， gb_date_sold， ‘blue’）create_formatted_summary（‘F5’， ‘每日總收入排名Top8 ’， gb_top_revenue， ‘yellow’）
得到結果如下。
可以看到，一行行的資料經過Python的處理，變為一目瞭然的表格。
最後再繪製一個matplotlib圖表，新增一張logo圖片，並儲存Excel檔案。
# 中文顯示plt。rcParams［‘font。sans-serif’］=［‘Songti SC’］# 使用Matplotlib繪製視覺化圖表， 餅圖fig， ax = plt。subplots（figsize=（6， 3））pv_total_profit。plot（color=‘g’， kind=‘bar’， ax=ax）# 新增圖表到Excelsht_dashboard。pictures。add（fig， name=‘ItemsChart’， left=sht_dashboard。range（“M5”）。left， top=sht_dashboard。range（“M5”）。top， update=True）# 新增logo到Excellogo = sht_dashboard。pictures。add（image=“pie_logo。png”， name=‘PC_3’， left=sht_dashboard。range（“J2”）。left， top=sht_dashboard。range（“J2”）。top+5， update=True）# 設定logo的大小logo。width = 54logo。height = 54# 儲存Excel檔案wb。save（rf“水果蔬菜銷售報表。xlsx”）
此處需設定一下中文顯示，否則會顯示不了中文，只有一個個方框。
得到最終的水果蔬菜銷售報表。
本文的示例程式碼，可以在Mac+Excel2016中執行的，與Windows還是會有一些區別，API函式的呼叫（pywin32 or appscript）。
比如表格文字的字型設定。
# Windowssht_dashboard。range（‘B2’）。api。font。name = ‘黑體’# Macsht_dashboard。range（‘B2’）。api。font_object。name。set（‘黑體’）
對於Windows版本的，小F也提供了相關的程式檔案，程式碼及相關資料都已上傳
。
感興趣的小夥伴，可以動手嘗試一下。無需太多的程式碼，就能輕鬆的建立一個Excel報表出來～
