使用PythonOpenPyXL模組控制Excel-1

Photo by Mika Baumeister on Unsplash

緣起

上班時進行的各種任務，或許你已經對於目前的處理方式習以為常。你可曾想過，如果用別的方式來處理，會不會比較好？會不會比較有效率？會不會比較不無聊？

為什麼要學習寫程式？或許有人會答案，可以找到較高薪的工作？或許有人是為了炫技？對個人我來說，學習程式語言的目的，是為了解決身邊的工作，尤其是那些看似重複的枯燥工作。今天，我要在這裏介紹一下，如何透過Python處理大量Excel資料的方式。

Photo by Franck V. on Unsplash

Excel試算表

Excel試算表，是由一個或者數個工作表（Sheets）組成的一個活頁簿（Workbooks）。一個檔案代表一個活頁簿，一個活頁簿裡面可以有複數的工作表。每個工作表中都有欄（column）和列（row）。在指定的欄與列所形成的方格稱為儲存格（cell）。每個儲存格都可以放入文字或數字。如下圖，這個空白的活頁簿裡面有一個工作表。Excel檔案的檔案格式是（.xls或.xlsx檔案）。雖然Excel試算表的操作可以透過撰寫檔案裡面的VBA巨集來操作，我們在這裡不使用裡面的巨集。

使用OpenPyXL模組

要透過Python程式來操作Excel，需要先安裝OpenPyXL模組。我們先從工作環境的設定開始吧。

環境設定

開始寫程式之前，我們必須先把開發的環境設定好。

1. 安裝Python

先確定使用的電腦上面已經安裝Python。

下載位置：https://www.python.org/

參考其他網誌

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.xlsx
wb = load_workbook("added_sheets.xlsx")

ㄧ. 透過index或是名稱取得工作表

如果活頁簿裡面有兩個以上的工作表，我們要如何指定工作表來供我們進行後續的操作？

這時候可以透過index或是name取得活頁簿中特定的工作表。以下示範取得的方式：

例如，我們有一個活頁簿，裡面有兩個工作表Sheet1與Sheet2我們可以：

1.透過index取得

使用worksheet[i]來取得，其中i為工作表的位置，0 就是第一個工作表、1 就是第二個工作表，依此類推。

//worksheet
ws1 = wb.worksheet[0]
ws2 = wb.worksheet[1]

或者是：

由於wb.sheetnames的物件型態為串列（list），我們可以使用串列的方式來取得第一個與第二個工作表。

//sheet names
sheets = 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會產生一個活頁簿的副本，如果沒有特別替副本取名字的話，副本會以ＸＸ 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的基本方式。學會這些方式雖然還不足以解決現實生活中實際碰到的問題，上面的範例『一個星期的活頁簿』也似乎沒有什麼厲害之處，但這些操作方法卻是最基本的，可以說是「蹲馬步」吧。下一遍將延續這篇，繼續介紹其他的控制方法。

繼續閱讀：使用PythonOpenPyXL模組控制Excel-2