如何使用 Python openpyxl 套件操作 Excel 工作表？openpyxl Worksheet 物件介紹

Python openpyxl 套件中的 Excel 工作表物件 Worksheet

Pythonopenpyxl套件中的Worksheet類別，屬於模組openpyxl.worksheet.worksheet，可用於取得或設定 Excel 活頁簿中的工作表的相關資訊和資料，Worksheet物件可通過openpyxl活頁簿物件Workbook取得或建立。如果Workbook是唯讀的，即read_only屬性為True，那麽你將只能得到ReadOnlyWorksheet工作表物件（屬於模組openpyxl.worksheet._read_only），如果Workbook是唯寫的，即write_only屬性為True，那麽你將只能建立並得到WriteOnlyWorksheet工作表物件（屬於模組openpyxl.worksheet._write_only）。

使用 Python openpyxl 套件的 Worksheet 物件取得目前使用中的 Excel 儲存格的位址

Pythonopenpyxl套件的Worksheet物件的active_cell屬性，可用於取得 Excel 工作表目前使用中的儲存格的位址，比如，active_cell屬性傳回'C1'，表示儲存格C1處於使用中。

worksheet.active_cell

# 請將命令列跳躍至 Goods.xlsx 所在的目錄，然後執行此腳本檔案
from openpyxl import load_workbook


worksheet = load_workbook('Goods.xlsx')['Tables']
# 顯示工作表目前使用中的儲存格的位址
print(f'目前使用中的儲存格為 {worksheet.active_cell}')

目前使用中的儲存格為 C1

使用 Python openpyxl 套件的 Worksheet 物件為 Excel 工作表新增資料（值）

Pythonopenpyxl套件的Worksheet物件的append方法，可用於為 Excel 工作表新增資料（值），被新增的資料將位於工作表末尾一列的下一列，即便在末尾列之前存在某個空白列。這裏需要指出，openpyxl套件確定末尾一列的方式可能和你想象的不一樣，我們會在計算已用儲存格的最小範圍一段中給予說明。

worksheet.append(iterable)

iterable 參數

iterable參數是一個提供資料的物件，可以是串列（list），元組（tuple)，範圍（range），字典（dict），或 Python 建置器（被isgenerator函式判斷為True的物件）。除了字典，以上物件包含的值會從列的第一欄開始，被依次寫入 Excel 工作表。如果使用字典，那麽可以通過鍵值組的鍵來指明資料對應的欄，鍵可以是字串或數值，比如{'A':100}和{1:100}都表示第一欄的值為100。

# 請將命令列跳躍至 Goods.xlsx 所在的目錄，然後執行此腳本檔案
from openpyxl import load_workbook


workbook = load_workbook('Goods.xlsx')
worksheet = workbook.create_sheet()
# 以不同形式為工作表新增 4 列資料
worksheet.append((1.6, 2.5))
# 這相當於將儲存格 A1 和 B1 移動至 A2 和 B2 的所在位置
worksheet.append([worksheet['A1'], worksheet['B1']])
worksheet.append({2: 1.0, 4: 2.0})
worksheet.append({'C': 1.1, 'E': 2.4})
# 儲存為 Append.xlsx
workbook.save('Append.xlsx')


# 顯示所有儲存格的值
for r in worksheet.values:
	print(r)

# 第一列中的 1.6 和 2.5 不會在 Append.xlsx 的 Sheet 工作表中出現
(1.6, 2.5, None, None, None)
(1.6, 2.5, None, None, None)
(None, 1.0, None, 2.0, None)
(None, None, 1.1, None, 2.4)

Pythonopenpyxl套件的WriteOnlyWorksheet物件的append方法，同樣可用於為 Excel 工作表新增資料，其效果與Worksheet物件的append方法類似，只不過其能夠接受的資料形式更少。

writeonlyworksheet.append(iterable)

iterable 參數

iterable參數是一個提供資料的物件，可以是串列（list），元組（tuple)，範圍（range），或 Python 建置器（被isgenerator函式判斷為True的物件）。

from openpyxl import Workbook


workbook = Workbook(True)
worksheet = workbook.create_sheet()
# 為唯寫工作表新增資料，並儲存為 AppendWriteOnly.xlsx
worksheet.append((1.6, 2.5))
worksheet.append([1.8, 2.2])
workbook.save('AppendWriteOnly.xlsx')

使用 Python openpyxl 套件的 Worksheet 物件取得 Excel 儲存格

Pythonopenpyxl套件的Worksheet物件的cell方法或[]運算子，可用於取得 Excel 工作表中的某個或某些儲存格。其中，cell方法的傳回值是表示 Excel 儲存格的Cell物件，[]運算子的傳回值是Cell物件，或包含多個Cell物件的 Python 元組，或以巢狀形式包含多列多欄 Excel 儲存格的 Python 元組。

worksheet.cell(row, column, value=None)
worksheet[address]

row 參數

row參數是一個整數，表示需要取得的 Excel 儲存格位於第幾列。

column 參數

column參數是一個整數，表示需要取得的 Excel 儲存格位於第幾欄。

value 參數

value參數是需要為 Excel 儲存格設定的值，可以是數值，字串或時間。如果不希望在取得 Excel 儲存格的同時設定儲存格的值，可以保持value參數的預設值None。

address 值

address為需要取得的某個 Excel 儲存格的位址，比如'B2'，或者包含多個 Excel 儲存格的範圍運算式，比如'A1:C3'（從儲存格A1到C3），2:4（在第二列和第四列之間，從第一欄開始至工作表最大欄結束）。

與Worksheet物件的cell方法和[]運算子類似，Pythonopenpyxl套件的ReadOnlyWorksheet物件的cell方法和[]運算子，同樣可用於取得 Excel 工作表中的某個或某些儲存格。不同的是，ReadOnlyWorksheet物件的cell方法和[]運算子，傳回的是ReadOnlyCell或EmptyCell物件而非Cell，呼叫cell方法時，不能為value參數指定非None值，否則將導致例外狀況，[]運算子會排除 Excel 工作表末尾所有的空白列。

# 請將命令列跳躍至 Goods.xlsx 所在的目錄，然後執行此腳本檔案
import openpyxl


workbook = openpyxl.load_workbook('Goods.xlsx', True)
phones = workbook['Phones']
# 取得儲存格 A1，C1
print(phones.cell(1, 1))
print(phones['C1'])
# 取得第二和第四列之間，從第一欄開始至最大欄結束的範圍內的儲存格
print(phones[2:4])


cups = workbook['Cups']
# 工作表末尾的一些列將被忽略
print(cups['A1:B10'])

<EmptyCell>
<ReadOnlyCell 'Phones'.C1>
((<EmptyCell>, <ReadOnlyCell 'Phones'.B2>, <ReadOnlyCell 'Phones'.C2>, <EmptyCell>), (<EmptyCell>, <ReadOnlyCell 'Phones'.B3>, <EmptyCell>, <EmptyCell>))
((<ReadOnlyCell 'Cups'.A1>, <ReadOnlyCell 'Cups'.B1>), (<ReadOnlyCell 'Cups'.A2>, <ReadOnlyCell 'Cups'.B2>), (<EmptyCell>, <EmptyCell>), (<ReadOnlyCell 'Cups'.A4>, <ReadOnlyCell 'Cups'.B4>))

使用 Python openpyxl 套件的 Worksheet 物件周遊 Excel 儲存格的值

Pythonopenpyxl套件的Worksheet和ReadOnlyWorksheet物件的values屬性，傳回一個可用於按列周遊 Excel 儲存格的值的建置器物件，周遊的範圍從儲存格A1開始，至 Excel 工作表的最大列和最大欄結束，空白儲存格的值將被表示為None。

需要指出，工作表的最大列和最大欄可能會在openpyxl套件的Worksheet物件中發生變化，關於這一點，我們會在計算已用儲存格的最小範圍一段給予說明。

worksheet|readonlyworksheet.values

# 請將命令列跳躍至 Goods.xlsx 所在的目錄，然後執行此腳本檔案
import openpyxl


worksheet = openpyxl.load_workbook('Goods.xlsx')['Phones']
# 存取儲存格 A4 將導致工作表的最大列發生變化
worksheet['A4']
# 顯示 Phones 工作表中的儲存格的值
for row in worksheet.values:
	print(row)

(None, 'iPhone 100', 10000, 10)
(None, 'Samsung', 9999, None)
(None, 'iPhone 13', None, None)
(None, None, None, None)

使用 Python openpyxl 套件的 Worksheet 物件移動 Excel 儲存格

Pythonopenpyxl套件的Worksheet物件的move_range方法，可用於移動 Excel 工作表中的某個儲存格或指定範圍內的所有儲存格，移動的目標範圍中的儲存格將被覆蓋。

worksheet.move_range(cell_range, rows=0, cols=0, translate=False)

cell_range 參數

cell_range參數為某個 Excel 儲存格的位址，或表示儲存格範圍的運算式和CellRange物件。

rows 參數

rows參數為 Excel 儲存格移動的列數，大於0時向下移動，小於0時向上移動。

cols 參數

cols參數為 Excel 儲存格移動的欄數，大於0時向右移動，小於0時向左移動。

translate 參數

translate參數表示是否對被移動儲存格的公式進行轉換，預設為False，不轉換。

# 請將命令列跳躍至 Goods.xlsx 所在的目錄，然後執行此腳本檔案
from openpyxl import open


workbook = open('Goods.xlsx')
worksheet = workbook['Pens']
# 移動包含資料和公式儲存格，並轉換公式
worksheet.move_range('A1:C3', 1, 1, True)
workbook.save('Move.xlsx')

使用 Python openpyxl 套件的 Worksheet 物件合併或取消合併 Excel 儲存格

Pythonopenpyxl套件的Worksheet物件的merge_cells方法，可用於合併 Excel 工作表中某個範圍內的所有儲存格，合併之後，原本範圍左上角的儲存格將占據整個範圍，並可以通過該儲存格原本的位址來存取他。如果只給出一個儲存格，那麽合併操作不會產生任何效果。

Pythonopenpyxl套件的Worksheet物件的unmerge_cells方法，可用於取消合併 Excel 工作表中被合併的儲存格，你需要為unmerge_cells方法指明取消合併的範圍，該範圍應該與之前進行合併操作的範圍一致。

worksheet.merge_cells(range_string=None, start_row=None, start_column=None, end_row=None, end_column=None)
worksheet.unmerge_cells(range_string=None, start_row=None, start_column=None, end_row=None, end_column=None)

range_string 參數

range_string參數為表示被合併或取消合併的儲存格範圍的運算式，如果忽略或設定為None，則需要給出merge_cells方法的參數start_row，start_column，end_row和end_column。

start_row 參數

start_row參數是一個整數，表示被合併或取消合併的範圍開始於第幾列。

start_column 參數

start_column參數是一個整數，表示被合併或取消合併的範圍開始於第幾欄。

end_row 參數

end_row參數是一個整數，表示被合併或取消合併的範圍結束於第幾列。

end_column 參數

end_column參數是一個整數，表示被合併或取消合併的範圍結束於第幾欄。

# 請將命令列跳躍至 Goods.xlsx 所在的目錄，然後執行此腳本檔案
from openpyxl import open


workbook = open('Goods.xlsx')
worksheet = workbook['Flowers']
print(f'合併之前的 B2 為 {worksheet["B2"].value}')
# 合併範圍 A1:B2 之後，立即取消合併
worksheet.merge_cells('A1:B2')
print(f'合併之後的 B2 為 {worksheet["B2"].value}')
worksheet.unmerge_cells(start_row=1, start_column=1, end_row=2, end_column=2)

合併之前的 B2 為 =B1
合併之後的 B2 為 None

使用 Python openpyxl 套件的 Worksheet 物件取得 Excel 工作表中所有被合併的範圍

Pythonopenpyxl套件的Worksheet物件的merged_cells變數是一個MultiCellRange物件，可用於表示 Excel 工作表中所有被合併的範圍，通過該物件和in關鍵字，你可判斷某個 Excel 儲存格是否已經被合併。

worksheet.merged_cells

# …
# 合併範圍 B2:C3，D4:H6
worksheet.merge_cells('B2:C3')
worksheet.merge_cells('D4:H6')
# 判斷儲存格 E3，D4 是否被合併了
print(f'儲存格 E3 被合併了嗎？{"E3" in worksheet.merged_cells}')
print(f'儲存格 D4 被合併了嗎？{"D4" in worksheet.merged_cells}')

儲存格 E3 被合併了嗎？False
儲存格 D4 被合併了嗎？True

使用 Python openpyxl 套件的 Worksheet 物件計算包含 Excel 工作表所有已用儲存格的最小範圍

Pythonopenpyxl套件的Worksheet物件的calculate_dimension方法和dimensions屬性，可用於計算包含 Excel 工作表已用儲存格的最小範圍，並傳回該最小範圍的運算式。Excel 已用儲存格可能包括值為空的儲存格，比如，一個沒有資料但被設定了背景色彩的儲存格。

worksheet.calculate_dimension()
worksheet.dimensions

Pythonopenpyxl套件的ReadOnlyWorksheet物件的calculate_dimension方法，與Worksheet物件的calculate_dimension方法類似，只不過ReadOnlyWorksheet物件的calculate_dimension方法增加了一個參數force。出於對效能的考慮，如果一個 Excel 工作表尚未確定最大列和最大欄，那麽預設情況下，呼叫calculate_dimension方法會導致例外狀況，此時可將參數force設定為True，以強製計算未確定的最大列和最大欄。

readonlyworksheet.calculate_dimension(force=False)

force 參數

force參數表示是否強製計算已用儲存格的最小範圍，預設為False，不強製計算。

# 請將命令列跳躍至 Goods.xlsx 所在的目錄，然後執行此腳本檔案
from openpyxl import load_workbook


empty = load_workbook('Goods.xlsx')['Empty']
# C3 是一個值為空但擁有背景色彩的儲存格
print(empty['C3'])
print(f'最小範圍 {empty.dimensions}')
# 存取 E5 導致最小範圍改變
empty['E5']
print(f'存取 E5 後的最小範圍 {empty.calculate_dimension()}')


r_empty = load_workbook('Goods.xlsx', True)['Empty']
# 對於唯讀工作表，存取 E5 不會導致最小範圍改變
r_empty['E5']
print(f'唯讀工作表存取 E5 後的最小範圍 {r_empty.calculate_dimension()}')

<Cell 'Empty'.C3>
最小範圍 B2:C3
存取 E5 後的最小範圍 B2:E5
唯讀工作表存取 E5 後的最小範圍 B2:C3

Pythonopenpyxl套件的ReadOnlyWorksheet物件的reset_dimensions方法，會將ReadOnlyWorksheet物件計算的最大列和最大欄設定為None，這導致在呼叫calculate_dimension方法時，需要把參數force設定為True，以重新計算最大列和最大欄，否則會出現例外狀況。

readonlyworksheet.reset_dimensions()

# …
# 重設最大列和最大欄
r_empty.reset_dimensions()
# ERROR 需要將 force 參數設定為 True
r_empty.calculate_dimension()

ValueError: Worksheet is unsized, use calculate_dimension(force=True)

使用 Python openpyxl 套件的 Worksheet 物件取得擁有陣列公式的 Excel 儲存格及其影響範圍

Pythonopenpyxl套件的Worksheet物件的array_formulae屬性，傳回一個 Python 字典，用於表示 Excel 工作表中所有擁有陣列公式的儲存格及其影響的範圍，字典鍵值組的鍵為擁有陣列公式的儲存格的位址，字典鍵值組的值為陣列公式所影響的範圍對應的運算式，該範圍中的儲存格將儲存公式的計算結果。比如，{'E1':'E1:E3'}表示儲存格E1具有陣列公式，該公式的計算結果儲存在範圍E1:E3中。

worksheet.array_formulae

使用 Python openpyxl 套件的 Worksheet 物件取得和設定 Excel 工作表的名稱（標題）

Pythonopenpyxl套件的Worksheet和WriteOnlyWorksheet物件的title屬性，可用於取得或設定 Excel 工作表的名稱（標題）。Pythonopenpyxl套件的ReadOnlyWorksheet物件的title變數，用於表示 Excel 工作表的名稱，對該變數進行修改並沒有太大意義，因為無法將其儲存至 Excel 檔案。

worksheet|writeonlyworksheet|readonlyworksheet.title
worksheet|writeonlyworksheet|readonlyworksheet.title = name

name 值

name為 Excel 工作表的名稱。

# 請將命令列跳躍至 Goods.xlsx 所在的目錄，然後執行此腳本檔案
from openpyxl import load_workbook


workbook = load_workbook('Goods.xlsx')
# 修改工作表 Tables 的名稱，並儲存為 Name.xlsx
workbook['Tables'].title = 'New Tables'
workbook.save('Name.xlsx')

使用 Python openpyxl 套件的 Worksheet 物件取得和設定 Excel 工作表的顯示狀態

Pythonopenpyxl套件的Worksheet，WriteOnlyWorksheet和ReadOnlyWorksheet物件的sheet_state變數，可用於取得或設定 Excel 工作表的顯示狀態（是否被隱藏），當然，修改ReadOnlyWorksheet物件的sheet_state變數意義不大，因為無法進行儲存。sheet_state變數的有效取值有以下幾種。

'visible'

'visible'對應了Worksheet的類別變數SHEETSTATE_VISIBLE，表示 Excel 工作表處於顯示狀態。

'hidden'

'hidden'對應了Worksheet的類別變數SHEETSTATE_HIDDEN，表示 Excel 工作表處於隱藏狀態，一般可通過 Office 軟體重新顯示。

'veryHidden'

'veryHidden'對應了Worksheet的類別變數SHEETSTATE_VERYHIDDEN，表示 Excel 工作表處於隱藏狀態，且無法通過 Office 軟體重新顯示（由具體的 Office 軟體決定）。

worksheet|writeonlyworksheet|readonlyworksheet.sheet_state
worksheet|writeonlyworksheet|readonlyworksheet.sheet_state = state

state 值

state為 Excel 工作表的顯示狀態。

# …
workbook = load_workbook('Goods.xlsx')
# 隱藏工作表 Pens，Cups
workbook['Pens'].sheet_state = 'hidden'
workbook['Cups'].sheet_state = 'veryHidden'
workbook.save('Hidden.xlsx')

使用 Python openpyxl 套件的 Worksheet 物件取得 Excel 工作表所隸屬的活頁簿

Pythonopenpyxl套件的Worksheet，WriteOnlyWorksheet和ReadOnlyWorksheet物件的parent屬性，表示了 Excel 工作表所隸屬的活頁簿物件。如果 Excel 工作表不屬於任何活頁簿，那麽parent屬性將傳回空值None。

worksheet|writeonlyworksheet|readonlyworksheet.parent

from openpyxl import Workbook


worksheet = Workbook(True).create_sheet()
worksheet.title = 'MySheet'
# 通過 parent 屬性取得工作表對應的活頁簿物件
worksheet.parent.save('Parent.xlsx')

使用 Python openpyxl 套件的 Worksheet 物件取得和設定 Excel 工作表的保護資訊

Pythonopenpyxl套件的Worksheet和WriteOnlyWorksheet物件的protection變數是一個SheetProtection物件，該物件包含了 Excel 工作表的保護資訊。這些保護資訊僅被 Office 軟體用於限製使用者的行為，並不會對 Pythonopenpyxl套件的行為造成影響。

worksheet|writeonlyworksheet.protection

# …
workbook = load_workbook('Goods.xlsx')
protection = workbook['Flowers'].protection
# 啟用對工作表 Flowers 的保護
protection.sheet = True
# 允許使用者在 Office 軟體中刪除列或欄
protection.deleteColumns = False
protection.deleteRows = False
workbook.save('Protection.xlsx')

Python openpyxl 套件的 Worksheet 物件的其他特性

以下是與openpyxl套件的Worksheet，ReadOnlyWorksheet或WriteOnlyWorksheet物件相關，但不太重要的一些特性，這些特性可能並非從 Microsoft Excel 檔案取得，只是由openpyxl套件給出了固定值。

encoding 屬性

Workbook，ReadOnlyWorksheet和WriteOnlyWorksheet物件的encoding屬性，用於表示 Excel 工作表所屬的活頁簿的編碼，他由openpyxl指定為'utf-8'。

程式碼

src/zh-hant/openpyxl/worksheets·codebeatme/office-programming·GitHub