從Excel中解救你!如何用Python實現報表自動化

全文共2391字，預計學習時長14分鐘

圖源：morioh

工作中，無窮無盡的表格有時會令人抓狂。Excel無處不在，即便有著像Python這樣的強大工具任你使用，你也難以從中逃脫。

也許你的老闆和同事仍然需要這種簡單的方法來訪問重要數據。但沒關係！那並不意味著你不能通過使用Python來簡化Excel工作。

全程無需使用Excel，逃離報表魔爪！

用Python將Excel報表自動化

你還在天天做Excel報表嗎？而且還是為不同的客戶做著四五次同樣的報表嗎？不如讓Python代勞吧！

通過使用筆者在數據透視表教程中的數據（https://towardsdatascience.com/a-step-by-step-guide-to-pandas-pivot-tables-e0641d0c6c70），概覽一下我們將要自動化到底是什麼。

數據格式將使你期望從公司資料庫中獲得的內容與客戶銷售數據相匹配，與你期望從公司資料庫中獲得的數據相匹配。它按區域細分這些數據，並且創建兩個帶有格式和圖表的匯總表。無需使用Excel！

最終的數據流—一個工作簿轉化為四個格式化報告 | 圖源：Nik Piepenbreier

加載庫

使用Pandas和Openpyxl。筆者的「用Python自動化這三項（無聊！！！）Excel任務」（ https://towardsdatascience.com/automate-these-3-boring-excel-tasks-with-python-666b4ded101b）一文中涵蓋了許多關於Openpyxl的內容，對其如何運行提供了詳細介紹。

#Section 1 - Loading our Libraries

import pandas as pd

fromopenpyxl import load_workbook

from openpyxl.styles importFont

from openpyxl.chart importBarChart, Reference

你使用如下兩個Librariy:

1. Pandas負責轉化數據，並創建初始Excel文件

2. Openpyxl將工作薄格式化，並插入圖表

加載數據

加載數據，並概覽正在處理的內容。正如筆者所提到的，這些數據與讀者從公司資料庫系統獲得的數據相似。

#Section 2 - Loading our Data

df = pd.read_excel('https://github.com/datagy/pivot_table_pandas/raw/master/sample_pivot.xlsx', parse_dates=['Date'])

print(df.head())

# Date Region Type Units Sales

#0 2020-07-11 East Children's Clothing 18.0 306

#1 2020-09-23 North Children's Clothing 14.0 448

#2 2020-04-02 South Women's Clothing 17.0 425

#3 2020-02-28 East Children's Clothing 26.0 832

#4 2020-03-19 West Women's Clothing 3.0 33

在這裡，使用Pandas讀取Excel文件，並讀取Date/日期列的日期。

創建數據透視表

接著我們要創建最終報告中所需要的匯總表。這裡將創建一個數據透視表以作為例子，其中僅使用東部地區進行代碼的實驗。

#Section 3 - Testing Pivot Tables

filtered= df[df['Region'] =='East']

quarterly_sales= pd.pivot_table(filtered, index =filtered['Date'].dt.quarter, columns ='Type', values ='Sales', aggfunc='sum')

print("Quarterly Sales Pivot Table:")

print(quarterly_sales.head())

#Quarterly Sales Pivot Table:

#Type Children's Clothing Men'sClothing Women's Clothing

#Date

#1 12274 13293 16729

#2 5496 17817 22384

#3 14463 9622 15065

#4 13616 10953 16051

創建第一個Excel文件

有了數據透視表後，將其導入到一個Excel文件中，我們將用pandas來導入：

#Section 04 - Creating and Excel Workbook

file_path=#Path to where you want your file saved

quarterly_sales.to_excel(file_path, sheet_name ='Quarterly Sales', startrow=3)

這一步是在做什麼：

· 創建一個文件路徑變量，以確定要將文件存儲在何處，

· 使用ExcelWriter保存文件

· 將兩個透視表保存到單獨的工作表中，從第3行開始（稍後從中保留以用於頁眉）

使報表更漂亮

Pandas有助於將數據導入到Excel中。既然數據已經導入Excel，不妨將其美化一下，來添加一些可視化效果。

#Section 05 - Loading the Workbook

wb =load_workbook(file_path)

sheet1= wb['Quarterly Sales']

# Section 06 - Formatting the First Sheet

sheet1['A1'] ='Quarterly Sales'

sheet1['A2'] ='datagy.io'

sheet1['A4'] ='Quarter'

sheet1['A1'].style ='Title'

sheet1['A2'].style ='Headline 2'

for i inrange(5, 9):

sheet1[f'B{i}'].style='Currency'

sheet1[f'C{i}'].style='Currency'

sheet1[f'D{i}'].style='Currency'

# Section 07 - Adding a Bar Chart

bar_chart=BarChart()

data=Reference(sheet1, min_col=2, max_col=4, min_row=4, max_row=8)

categories=Reference(sheet1, min_col=1, max_col=1, min_row=5, max_row=8)

bar_chart.add_data(data, titles_from_data=True)

bar_chart.set_categories(categories)

sheet1.add_chart(bar_chart, "F4")

bar_chart.title ='Sales by Type'

bar_chart.style=3

wb.save(filename = file_path)

在Section 5中，將工作簿和工作表加載到Openpyxl可以處理的單獨對象中。

而Section 6中操作更多：

· 在表一的A1和A2單元格中添加標題和副標題。

· 更改「quarters」列的標題，使其更能反映數據。

· 對標題和副標題應用樣式。

· 將金融領域的單元格轉換為貨幣。這需要對每單個單元格進行單獨處理。因此使用了for循環。

在Section 7中，添加了條形圖：

· 創建一個條形圖對象，並識別存儲數據和類別的欄位。

· 隨後將數據和類別應用於對象。

· 最後，添加描述性標題和樣式。使用許多不同的樣式都試試！

這就是工作簿現在的樣子：

所得工作簿之一 | 圖源: Nik Piepenbreier

對多個工作簿執行工作流自動化

雖然已經很方便了，但是僅在一個區域執行這樣的操作只能節約一點點的時間。我們可使用for循環，對所有的區域執行此操作。

#Section 08 - Getting Region Names

regions =list(df['Region'].unique())

# Section 09 - Looping Over All Regions

folder_path=#Insert the path to the folder you want tosave the reports in

for region in regions:

filtered =df[df['Region'] ==f'{region}']

quarterly_sales = pd.pivot_table(filtered, index =filtered['Date'].dt.quarter, columns ='Type', values ='Sales', aggfunc='sum')

file_path =f"{path to your folder}{region}.xlsx"

quarterly_sales.to_excel(file_path,sheet_name ='QuarterlySales', startrow=3)

wb =load_workbook(file_path)

sheet1 = wb['Quarterly Sales']

sheet1['A1'] ='Quarterly Sales'

sheet1['A2'] ='datagy.io'

sheet1['A4'] ='Quarter'

sheet1['A1'].style ='Title'

sheet1['A2'].style='Headline 2'

for i inrange(5, 10):

sheet1[f'B{i}'].style='Currency'

sheet1[f'C{i}'].style='Currency'

sheet1[f'D{i}'].style='Currency'

bar_chart =BarChart()

data =Reference(sheet1, min_col=2, max_col=4, min_row=4, max_row=8)

categories =Reference(sheet1, min_col=1, max_col=1, min_row=5, max_row=8)

bar_chart.add_data(data,titles_from_data=True)

bar_chart.set_categories(categories)

sheet1.add_chart(bar_chart,"F4")

bar_chart.title='Sales by Type'

bar_chart.style =3

在Section 8中，創建了一個列表，其中包含了想要覆蓋的不同區域的所有唯一值。

在Section 9中，在for循環中重複先前的代碼：

· 創建一個新變量，該變量用於保存文件所在文件夾的路徑

· 接下來，使用f-strings將區域名插入到腳本中，使得腳本對每個區域都是動態的。

圖源：unsplash

Python的好處在於，它可使重複的任務具有可伸縮性。

想像一下，如果你每天都會收到這份文件，並且每天都要創建這些工作簿。這種方法能幫你節省多少時間！

留言點讚關注

我們一起分享AI學習與發展的乾貨

如轉載，請後臺留言，遵守轉載規範