使用PythonOpenPyXL模組控制Excel-1
緣起
上班時進行的各種任務,或許你已經對於目前的處理方式習以為常。你可曾想過,如果用別的方式來處理,會不會比較好?會不會比較有效率?會不會比較不無聊?
為什麼要學習寫程式?或許有人會答案,可以找到較高薪的工作?或許有人是為了炫技?對個人我來說,學習程式語言的目的,是為了解決身邊的工作,尤其是那些看似重複的枯燥工作。今天,我要在這裏介紹一下,如何透過Python處理大量Excel資料的方式。
Excel試算表
Excel試算表,是由一個或者數個工作表(Sheets)組成的一個活頁簿(Workbooks)。一個檔案代表一個活頁簿,一個活頁簿裡面可以有複數的工作表。每個工作表中都有欄(column)和列(row)。在指定的欄與列所形成的方格稱為儲存格(cell)。每個儲存格都可以放入文字或數字。如下圖,這個空白的活頁簿裡面有一個工作表。Excel檔案的檔案格式是(.xls或.xlsx檔案)。雖然Excel試算表的操作可以透過撰寫檔案裡面的VBA巨集來操作,我們在這裡不使用裡面的巨集。
使用OpenPyXL模組
要透過Python程式來操作Excel,需要先安裝OpenPyXL模組。我們先從工作環境的設定開始吧。
環境設定
開始寫程式之前,我們必須先把開發的環境設定好。
1. 安裝Python
先確定使用的電腦上面已經安裝Python。
參考其他網誌
2. 安裝OpenPyXL
由於Python的標準函式庫裡面沒有內建可以讀寫excel檔案的模組,若要讀寫Excel我們需要先安裝外部模組OpenPyXL。你可以透過Anaconda安裝或者是pip install安裝。下面是兩種安裝方式:
- Anaconda安裝
安裝完畢會出現在Anaconda Navigator的右邊。
- pip install安裝
也可以使用pip install的方式安裝openpyxl模組
$ pip install openpyxl
3.閱讀OpenPyXL文件
OpenPyXL有很多版本,透過閱讀OpenPyXL文件,可以更加暸解如何使用該版本的模組。對於特定版本的使用有問題,可以透過關鍵字搜尋問題以尋求文件上面記載的方法。網址:https://openpyxl.readthedocs.io/en/latest/index.html
4.使用OpenPyXL的注意事項
由於OpenPyXL沒有復原(undo)可以還原對Excel的操作,一旦執行錯誤的指令,就會產生不可逆的錯誤結果。所以請別忘了在操作前要先複製一份副本,再來進行程式的執行。
環境設定完畢之後,就可以來試試看OpenPyXL。我們可以透過這個模組:
- 建立一個空白的活頁簿或者是;
- 開啟現有已經存在的活頁簿讀取裡面的內容;
- 在活頁簿裡面建立新工作表或刪除工作表;
- 在活頁簿裡面重新命名工作表;
- 複製現有的工作表;
- 讀取工作表中某個或者是某個範圍的儲存格資料;
- 移動儲存格位置;
- 將結果儲存到Excel檔案;
以下,我們就先來看看該怎麼操作OpenPyXL才能達到這些效果。
建立空白活頁簿
我們來試試,透過OpenPyXL套件來建立空白活頁簿。
1. OpenPyXL載入套件
首先要在Python檔案(test001.py)裡面載入OpenPyXL套件。
import openpyxl//或者是from openpyxl import Workbook
2. 實體化活頁簿 — Workbook()
接下來,透過Python建立一個活頁簿(Workbooks)。一般我們會給定一個變數wb(workbook的縮寫):
wb = openpyxl.Workbook()
3. 儲存活頁簿 — save
儲存活頁簿,並且設定檔名為「My First Workbook.xls」。當然,你也可以設定其他名稱為檔案名稱。
wb.save("My First Workbook.xlsx")
4. 執行程式
執行程式後,你會發現在同一個資料夾裡面出現excel檔案。
開啟已存在活頁簿(workbook)
另一種狀況是,資料夾中已經有Excel檔案了,這時候可以使用load_workbook來讀取活頁簿的內容。
load_workbook
下面程式碼,我們使用load_workbook函式來讀取活頁簿,並且利用 for迴圈來巡迴取得活頁簿裡面所有工作表的名稱。
註:依照範例,My First Workbook.xlsx檔案,要放在目前的工作目錄中才可以被讀取到(也就是說要把excel檔案與python檔案放在同一層)。如果想要放在不同的目錄,就需要再透過os模組來處理路徑問題了。
wb = openpyxl.load_workbook("My First Workbook.xlsx")for sheet in wb: print(sheet.title)
對工作表(worksheet)的一些操作
我們可以進一步透過OpenPyXL來操作工作表。首先,載入OpenPyXL套件裡的Workbook。
from openpyxl import Workbook// 指定wb為變數,建立活頁簿。wb = Workbook()
1. 建立工作表 — create_sheet
接下來,我們要透過create_sheet來建立工作表。姑且就叫它A sheet。後面的0代表放在活頁簿中的第一個位置。
ws = wb.create_sheet("A sheet",0)
接著,可以再增加一個工作表,如果後面沒有放參數的話,Python會自動加在目前工作表的後面。
ws2 = wb.create_sheet("Sheet 2")
我們希望可以得到什麼結果,例如把工作表的名稱印出來。利用上面的for迴圈,可以把活頁簿裡面所有工作表的名稱全部印出來。
for sheet in wb: print(sheet.title)
2. 刪除工作表 — remove、del
如果要刪除工作表的話,我們可以使用remove來處理。後面的wb[“Sheet”]表示我們想要刪除的工作表名稱是Sheet,括弧裡面的名稱就是工作表的名稱。
wb.remove(wb["Sheet"])
另外,我們也可以使用del來達到同樣的效果。
del wb["Sheet"]
3. 重新命名工作表 — title
對於目前存在的工作表名稱,我們可以用title重新予以命名。下面的式子可以重新把ws2 工作表命名為「New title」。:
ws2.title = "New title"
4. 儲存為excel檔案 — save
跟前面一樣的方式,透過save把剛剛的結果儲存到Excel檔案中。
wb.save("added_sheets.xlsx")
複製現存的工作表
我們也可以將現存的工作表複製另一份工作表出來。首先,載入load_workbook來讀取活頁簿。
from openpyxl import load_workbook// 讀取現有的excel檔案 added_sheets.xlsxwb = load_workbook("added_sheets.xlsx")
ㄧ. 透過index或是名稱取得工作表
如果活頁簿裡面有兩個以上的工作表,我們要如何指定工作表來供我們進行後續的操作?
這時候可以透過index或是name取得活頁簿中特定的工作表。以下示範取得的方式:
例如,我們有一個活頁簿,裡面有兩個工作表Sheet1
與Sheet2
我們可以:
1.透過index取得
使用worksheet[i]來取得,其中i為工作表的位置,0 就是第一個工作表、1 就是第二個工作表,依此類推。
//worksheetws1 = wb.worksheet[0]
ws2 = wb.worksheet[1]
或者是:
由於wb.sheetnames的物件型態為串列(list),我們可以使用串列的方式來取得第一個與第二個工作表。
//sheet namessheets = wb.sheetnames
ws1 = wb[sheets[0]]
ws2 = wb[sheets[1]]
2.透過Name取得
我們也可以透過指定工作表的名稱來取得第一個與第二個工作表,(假定第工作表的名稱分別是Sheet1與Sheet2:
wb1 = wb["Sheet1"]wb2 = wb["Sheet2"]
二. 複製工作表 — copy_worksheet
要完整複製一個已經存在的工作表,可以使用copy_worksheet來達成。下面程式碼設定source變數來指定我們想要複製的工作表名稱。然後使用copy_worksheet來複製,並且賦予新工作表一個名稱。
// 來源工作表
source = wb["sheetA"]// 複製來源工作表
new_sheet = copy_worksheet(source)// 目的地工作表
new_sheet.title = "sheetA_copy"
執行copy_worksheet會產生一個活頁簿的副本,如果沒有特別替副本取名字的話,副本會以XX Copy的型態出現,以上面的例子來說,副本的名稱會是「sheetA Copy」。
下面的程式碼可以從舊活頁簿abc.xlsx複製一個工作表後,另存成為abc_copies.xlsx。在複製的過程中,會將原來活頁簿裡面工作表的內容一併的複製過去。
from openpyxl import load_workbook
def main():
#建立活頁簿
wb = load_workbook("abc.xlsx") #複製工作表
source = wb["Sheet"]
new_sheet = wb.copy_worksheet(source)
new_sheet.title = "Sheet 2" # 檢視活頁簿裡面有哪些工作表
print("活頁簿裡面的工作表:")
# 列出所有工作表
for sheet in wb:
print(sheet.title) # 另存新活頁簿
wb.save("abc_copies.xlsx")if __name__ == "__main__":
main()
實作:一個星期的活頁簿
import openpyxl#設定一個變數,內含有星期一到星期日dates =['星期日','星期一','星期二','星期三','星期四','星期五','星期六']#建立檔案wb = openpyxl.Workbook()print("建立活頁簿...")#刪除預設工作表if wb["Sheet"]:wb.remove(wb["Sheet"])print("刪除預設工作表...Sheet")#巡迴變數,把變數取出#將變數放到每一個工作表title裡面。for index, day in enumerate(dates):#print(index,day)wb.create_sheet(day ,index)print("建立工作表..."+ day)#標籤塗上顏色。ws1 = wb['星期六']ws2 = wb['星期日']print(ws1, ws2)ws1.sheet_properties.tabColor = '1072BA'ws2.sheet_properties.tabColor = 'd64123'#存擋wb.save("sheet_with_dates.xlsx")print("儲存活頁簿...")
以上是使用PythonOpenPyXL模組控制Excel的基本方式。學會這些方式雖然還不足以解決現實生活中實際碰到的問題,上面的範例『一個星期的活頁簿』也似乎沒有什麼厲害之處,但這些操作方法卻是最基本的,可以說是「蹲馬步」吧。下一遍將延續這篇,繼續介紹其他的控制方法。