Python辦公神器：教你使用python批次提取Excel數據

2024-05-20碼農

在本文中，我將教你如何使用python的 openpyxl 和 glob 模組，來實作一個實際的案例：從同一個資料夾下的1000份電商女裝數據的Excel表格中，提取所有購買金額超過100的記錄，並匯總至一個新的Excel表中。

前言

Excel是一款非常強大和實用的辦公軟體，它可以用來儲存、分析和處理各種數據，是許多人工作中不可或缺的工具。但是，當我們需要處理大量的Excel檔時，手動操作就會變得非常繁瑣和耗時，而且容易出錯。有沒有一種方法，可以讓我們用簡單的程式碼，就能批次地提取、篩選和匯總Excel中的數據呢？

答案是肯定的，那就是使用python。python是一種簡潔、優雅、易學的程式語言，它擁有豐富的第三方庫，可以幫助我們完成各種任務，包括處理Excel數據。

準備工作

在開始編寫程式碼之前，我們需要做一些準備工作，包括：

安裝python環境，如果你還沒有安裝python，可以參考這篇教程。

安裝 openpyxl 模組，這是一個用來操作Excel檔的python庫，可以讓我們讀取、寫入和修改Excel中的數據。要安裝 openpyxl 模組，可以在命令列中輸入以下命令：

pip install openpyxl

安裝 glob 模組，這是一個用來匹配檔路徑的python庫，可以讓我們方便地獲取指定資料夾下的所有檔名。要安裝 glob 模組，可以在命令列中輸入以下命令：

pip install glob

準備數據檔，我們需要將同一個資料夾下的1000份電商女裝數據的Excel表格（命名為電商女裝數據1.xlsx，電商女裝數據2.xlsx至電商女裝數據1000.xlsx）放在一個資料夾中，例如D:\data。每個Excel表格中有以下幾列數據：

商品名稱	商品價格	購買數量	購買金額	購買時間
A	50	2	100	2023-01-01
B	80	3	240	2023-01-02
C	120	1	120	2023-01-03
...	...	...	...	...

我們的目標是提取所有購買金額超過100的記錄，並匯總至一個新的Excel表中，例如D:\result.xlsx。新的Excel表中的數據格式如下：

商品名稱	商品價格	購買數量	購買金額	購買時間	數據來源
B	80	3	240	2023-01-02	電商女裝數據1.xlsx
C	120	1	120	2023-01-03	電商女裝數據1.xlsx
D	150	2	300	2023-01-04	電商女裝數據2.xlsx
...	...	...	...	...	...

編寫程式碼

準備工作完成後，我們就可以開始編寫python程式碼了。我們的程式碼主要分為以下幾個步驟：

匯入 openpyxl 和 glob 模組，這樣我們就可以使用它們提供的功能了。

import openpyxl import glob

使用 glob.glob 函式，獲取指定資料夾下的所有Excel檔名，這些檔名將被儲存在一個列表中，方便我們後續遍歷。

# 定義資料夾路徑，註意要使用反斜杠 folder_path = "D:\\data\\" # 定義檔名匹配規則，註意要使用通配符* file_pattern = folder_path + "電商女裝數據*.xlsx" # 獲取所有匹配的檔名 file_list = glob.glob(file_pattern)

建立一個新的Excel檔，並建立一個工作表，用來儲存匯總的數據。我們使用 openpyxl.Workbook 類來建立一個新的Excel檔，使用 create_sheet 方法來建立一個工作表，使用 save 方法來保存檔。

# 建立一個新的Excel檔 result_wb = openpyxl.Workbook() # 建立一個工作表，命名為"匯總數據" result_ws = result_wb.create_sheet("匯總數據") # 保存檔，指定檔名和路徑 result_wb.save("D:\\result.xlsx")

遍歷所有的Excel檔，使用 openpyxl.load_workbook 函式來開啟每個檔，並獲取第一個工作表，使用 iter_rows 方法來遍歷每一行數據，判斷購買金額是否超過100，如果是，就將該行數據復制到新的工作表中，並添加數據來源的資訊。我們使用 append 方法來向新的工作表中添加數據，使用 save 方法來保存檔。

# 遍歷所有的Excel檔 for file_name in file_list: # 開啟每個檔 data_wb = openpyxl.load_workbook(file_name) # 獲取第一個工作表 data_ws = data_wb.active # 遍歷每一行數據，跳過第一行（表頭） for row in data_ws.iter_rows(min_row=2): # 判斷購買金額是否超過100 if row[3].value > 100: # 將該行數據復制到新的工作表中 result_ws.append([cell.value for cell in row]) # 添加數據來源的資訊，即檔名 result_ws.cell(row=result_ws.max_row, column=6).value = file_name # 保存檔 result_wb.save("D:\\result.xlsx")