7.4. 存取Excel檔
儲存程式結果到 Excel 檔中也是一種可以選擇的方式，並且後續可以使用 Excel 的分析工具來分析資料結果，能夠以圖表方式呈現資料結果及從資料結果中歸納更具有義意的資訊。Excel 試算表文件被稱為活頁簿(workbook)，使用「*.xlsx」的副檔名。在每個活頁簿中可以有多個工作表(worksheet)，而使用者正在使用的工作表又被稱為使用中工作表(active worksheet)。在每個工作表中有欄(column)及列(row)，其中欄的標示從英文字母A開始及列的標示從數字1開始。用欄及列所形成的方格稱為儲存格(cell)，例如：第E欄及第5列的儲存格，在儲存格中可以存放數字或文字資料。
安裝 openpyxl 模組
由於 openpyxl 不是 Python 內建的模組，所以必須先使用 pip 安裝 openpyxl 模組。
pip install openpyxl
使用 openpyxl 模組
使用 import 關鍵字匯入 openpyxl 模組：
import openpyxl
建立及操作 Excel 檔案
使用 openpyxl 模組建立一個 Excel 檔案。首先，使用 Workbook() 建立一個 Excel 檔案，接著，利用 get_sheet_names() 取得所有工作表名稱。隨後，再透過 .active 取得目前正在使用的工作表及 .title 取得該工作表名或設定工作表名稱。最後，使用 save() 儲存此 Excel 檔案。
>>> import openpyxl
>>> import os
>>> wb = openpyxl.Workbook()
>>> wb.get_sheet_names()
['Sheet']
>>> sheet = wb.active # 取得正在使用的工作表
>>> sheet.title
'Sheet'
>>> sheet.title = 'Sheet1' # 更改工作表名稱
>>> sheet.title
'Sheet1'
>>> wb.get_sheet_names()
['Sheet1']
>>> wb.save('excel_crate.xlsx') # 儲存工作表
>>> os.listdir()
['excel_crate.xlsx']
在 Excel 檔案中，使用 create_sheet() 建立新的工作表及利用 .title 設定工作表名稱。
>>> import openpyxl
>>> wb = openpyxl.load_workbook('excel_crate.xlsx')
>>> wb.create_sheet()
<Worksheet "Sheet">
>>> wb.get_sheet_names()
['Sheet1', 'Sheet']
>>> sheet = wb.get_sheet_by_name('Sheet')
>>> sheet.title
'Sheet'
>>> sheet.title = 'Sheet2' # 在活頁簿中提定工作表名稱並且更改其名稱, 或使用 wb['Sheet'].title = 'Sheet2'
>>> wb.get_sheet_names()
['Sheet1', 'Sheet2']
>>> wb.active = 1 # 設定目前的工作表為'Sheet2'
>>> wb.save('excel_read.xlsx') # 儲存相關變動
在 Excel 檔案中，使用 copy_worksheet() 複製某一個工作表。
>>> import openpyxl
>>> wb = openpyxl.load_workbook('excel_create.xlsx')
>>> sheet = wb.active # 在前一個範例子中，Sheet2已經設定為目前的工作表
>>> sheet.title
'Sheet2'
>>> copy_sheet = wb.copy_worksheet(sheet)
>>> copy_sheet.title
'Sheet2 Copy'
>>> copy_sheet.title = 'Sheet3'
>>> copy_sheet.title
'Sheet3'
>>> wb.get_sheet_names()
['Sheet1', 'Sheet2', 'Sheet3']
>>> wb.save('excel_create.xlsx') # 儲存相關變動
在 Excel 檔案中，使用 remove_sheet() 刪除某一個工作表。
>>> import openpyxl
>>> wb = openpyxl.load_workbook('excel_create.xlsx')
>>> wb.get_sheet_names()
['Sheet1', 'Sheet2', 'Sheet3']
>>> sheet = wb.get_sheet_by_name('Sheet3')
>>> sheet.title
'Sheet3'
>>> wb.remove_sheet(sheet)
>>> wb.get_sheet_names()
['Sheet1', 'Sheet2']
>>> wb.save('excel_crate.xlsx') # 儲存相關變動
讀取 Excel 檔
使用 openpyxl 模組讀取一個 Excel 檔案，使用 for 迴圈以列或欄為主的方式，讀取工作表中的內容。
>>> import openpyxl
>>> wb = openpyxl.load_workbook('excel_read.xlsx')
>>> wb.get_sheet_names()
['Sheet1']
>>> sheet = wb.get_sheet_by_name('Sheet1') # 也可以使用sheet = wb['Sheet1']
>>> for row in sheet.rows:
... for cell in row:
... print(str(cell.value), end='') # 使用 for 迴圈讀取Sheet1的內容, 以列為主
... print()
...
A1 B1 C1 D1 E1
A2 B2 C2 D2 E2
A3 B3 C3 D3 E3
A4 B4 C4 D4 E4
A5 B5 C5 D5 E5
>>> for col in sheet.columns:
... for cell in col:
... print(str(cell.value)) # 使用 for 迴圈讀取Sheet1的內容, 以欄為主
... print()
...
A1
A2
A3
A4
A5
B1
B2
B3
B4
B5
C1
C2
C3
C4
C5
D1
D2
D3
D4
D5
E1
E2
E3
E4
E5
>>> sheet['A1'].value
'A1'
>>> sheet['E5'].value
'E5'
>>> sheet.max_column # 總共有5個欄，欄位索引值為1~5
5
>>> sheet.max_row
5
>>> sheet.cell(row=1, column=1).value
'A1'
>>> sheet.cell(row=5, column=5).value
'E5'
>>> cell_letter = openpyxl.utils.get_column_letter(sheet.max_column) # 透過欄位索引值得到欄位名稱
>>> cell_etter
'E'
>>> cell_index = openpyxl.utils.column_index_from_string('E') # 透過欄位名稱得到欄位索引值
>>> cell_index
5
>>> for i in range(1, sheet.max_row+1):
... for j in range(1, sheet.max_column+1):
... print(sheet.cell(row=i, column=j).value, end=' ') # 透過cell()取得儲存格的資料
... print()
...
A1 B1 C1 D1 E1
A2 B2 C2 D2 E2
A3 B3 C3 D3 E3
A4 B4 C4 D4 E4
A5 B5 C5 D5 E5
寫入 Excel 檔
使用 openpyxl 模組寫入資料到一個 Excel 檔案。
>>> import openpyxl
>>> wb = openpyxl.load_workbook('excel_read.xlsx')
>>> wb.get_sheet_names()
['Sheet1']
>>> sheet = wb.get_sheet_by_name('Sheet1')
>>> for row in sheet.rows:
... for cell in row:
... print(str(cell.value), end=' ')
... print()
...
A1 B1 C1 D1 E1
A2 B2 C2 D2 E2
A3 B3 C3 D3 E3
A4 B4 C4 D4 E4
A5 B5 C5 D5 E5
>>> count = 1
>>> for row in sheet.rows:
... for cell in row:
... cell.value = count # 使用 for 迴圈對每個儲存格寫入資料
... count += 1
...
>>>
>>> for row in sheet.rows:
... for cell in row:
... print('{0:02}'.format(cell.value), end=" ")
... print()
...
01 02 03 04 05
06 07 08 09 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25
>>> wb.save('excel_read.xlsx')
參考資料
- Working with Excel Files in Python
- Python自動化的樂趣, 第十二章, Al Sweigart 著、H&C 譯, 碁峰