如何使用 openpyxl 处理 Excel 工作表？Worksheet 对象介绍

openpyxl Excel 工作表对象 Worksheet

openpyxl包的Worksheet类，属于模块openpyxl.worksheet.worksheet，可用于获取或设置 Excel 工作表的相关信息和数据，Worksheet对象可通过工作簿对象Workbook获取或创建。如果Workbook是只读的，即read_only属性为True，那么你将只能得到ReadOnlyWorksheet工作表对象（属于模块openpyxl.worksheet._read_only），如果Workbook是只写的，即write_only属性为True，那么你将只能创建并得到WriteOnlyWorksheet工作表对象（属于模块openpyxl.worksheet._write_only）。

获取当前活动的 Excel 单元格的地址

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

为 Excel 工作表添加数据（值）

Worksheet对象的append方法，可用于为 Excel 工作表添加数据（值），被添加的数据将位于工作表末尾一行的下一行，即便在末尾行之前存在某个空行。这里需要指出，openpyxl包确定末尾一行的方式可能和你想象的不一样，我们会在计算包含所有已用单元格的最小区域一段中给予说明。

worksheet.append(iterable)

iterable 参数

iterable参数是一个提供数据的对象，可以是列表（list），元组（tuple)，区域（range），字典（dict），或 Python 生成器（被isgenerator函数判断为True的对象）。除了字典，以上对象包含的值会从行的第一列开始，被依次写入工作表。如果使用字典，那么可以通过键值对的键来指明数据对应的列，键可以是字符串或数字，比如{'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)

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')

获取 Excel 单元格

Worksheet对象的cell方法或[]运算符，可用于获取 Excel 工作表中的某个或某些单元格。其中，cell方法的返回值是表示单元格的Cell对象，[]运算符的返回值是Cell对象，或包含多个Cell对象的元组，或以嵌套形式包含多行多列单元格的元组。

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

row 参数

row参数是一个整数，表示需要获取的单元格位于第几行。

column 参数

column参数是一个整数，表示需要获取的单元格位于第几列。

value 参数

value参数是需要为单元格设置的值，可以是数字，字符串或时间。如果不希望在获取单元格的同时设置单元格的值，可以保持value参数的默认值None。

address 值

address为需要获取的某个单元格的地址，比如'B2'，或者包含多个单元格的区域表达式，比如'A1:C3'（从单元格A1到C3），2:4（在第二行和第四行之间，从第一列开始至工作表最大列结束）。

与Worksheet对象的cell方法和[]运算符类似，ReadOnlyWorksheet对象的cell方法和[]运算符，同样可用于获取 Excel 工作表中的某个或某些单元格。不同的是，ReadOnlyWorksheet对象的cell方法和[]运算符，返回的是ReadOnlyCell或EmptyCell对象而非Cell，调用cell方法时，不能为value参数指定非None值，否则将导致异常，[]运算符会排除工作表末尾所有的空行。

# 请将命令行跳转至 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>))

遍历 Excel 单元格的值

Worksheet和ReadOnlyWorksheet对象的values属性，返回一个可用于按行遍历 Excel 单元格的值的生成器对象，遍历的范围从单元格A1开始，至工作表的最大行和最大列结束，空单元格的值将被表示为None。

需要指出，工作表的最大行和最大列可能会在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)

移动 Excel 单元格

Worksheet对象的move_range方法，可用于移动 Excel 工作表中的某个单元格或指定区域内的所有单元格，移动的目标区域中的单元格将被覆盖。

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

cell_range 参数

cell_range参数为某个单元格的地址，或表示单元格区域的表达式和CellRange对象。

rows 参数

rows参数为单元格移动的行数，大于0时向下移动，小于0时向上移动。

cols 参数

cols参数为单元格移动的列数，大于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')

合并或取消合并 Excel 单元格

Worksheet对象的merge_cells方法，可用于合并 Excel 工作表中某个区域内的所有单元格，合并之后，原本区域左上角的单元格将占据整个区域，并可以通过该单元格原本的地址来访问他。如果只给出一个单元格，那么合并操作不会产生任何效果。

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

获取 Excel 工作表中所有被合并的区域

Worksheet对象的merged_cells变量是一个MultiCellRange对象，可用于表示 Excel 工作表中所有被合并的区域，通过该对象和in关键字，你可判断某个单元格是否已经被合并。

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

计算包含 Excel 工作表所有已用单元格的最小区域

Worksheet对象的calculate_dimension方法和dimensions属性，可用于计算包含 Excel 工作表已用单元格的最小区域，并返回该最小区域的表达式。已用单元格可能包括值为空的单元格，比如，一个没有数据但被设置了背景颜色的单元格。

worksheet.calculate_dimension()
worksheet.dimensions

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

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)

获取拥有数组公式的 Excel 单元格及其影响区域

Worksheet对象的array_formulae属性，返回一个 Python 字典，用于表示 Excel 工作表中所有拥有数组公式的单元格及其影响的区域，字典键值对的键为拥有数组公式的单元格的地址，字典键值对的值为数组公式所影响的区域对应的表达式，该区域中的单元格将存储公式的计算结果。比如，{'E1':'E1:E3'}表示单元格E1具有数组公式，该公式的计算结果存储在区域E1:E3中。

worksheet.array_formulae

获取和设置 Excel 工作表的名称（标题）

Worksheet和WriteOnlyWorksheet对象的title属性，可用于获取或设置 Excel 工作表的名称（标题）。ReadOnlyWorksheet对象的title变量，用于表示 Excel 工作表的名称，对该变量进行修改并没有太大意义，因为无法将其保存至 Excel 文件。

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

name 值

name为工作表的名称。

# 请将命令行跳转至 Goods.xlsx 所在的目录，然后运行此脚本文件
from openpyxl import load_workbook


workbook = load_workbook('Goods.xlsx')
# 修改工作表 Tables 的名称，并保存为 Name.xlsx
workbook['Tables'].title = 'New Tables'
workbook.save('Name.xlsx')

获取和设置 Excel 工作表的显示状态

Worksheet，WriteOnlyWorksheet和ReadOnlyWorksheet对象的sheet_state变量，可用于获取或设置 Excel 工作表的显示状态（是否被隐藏），当然，修改ReadOnlyWorksheet对象的sheet_state变量意义不大，因为无法进行保存。sheet_state变量的有效取值有以下几种。

'visible'

'visible'对应了Worksheet的类变量SHEETSTATE_VISIBLE，表示工作表处于显示状态。

'hidden'

'hidden'对应了Worksheet的类变量SHEETSTATE_HIDDEN，表示工作表处于隐藏状态，一般可通过 Office 软件重新显示。

'veryHidden'

'veryHidden'对应了Worksheet的类变量SHEETSTATE_VERYHIDDEN，表示工作表处于隐藏状态，且无法通过 Office 软件重新显示（由具体的 Office 软件决定）。

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

state 值

state为工作表的显示状态。

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

获取 Excel 工作表所隶属的工作簿

Worksheet，WriteOnlyWorksheet和ReadOnlyWorksheet对象的parent属性，表示了 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')

openpyxl Excel 工作表保护对象 SheetProtection

openpyxl包的SheetProtection类，属于模块openpyxl.worksheet.protection，用于表示 Excel 工作表的保护信息，以下为其拥有的部分变量。

sheet 变量

sheet变量是一个布尔值，表示是否启用对工作表的保护。

objects 变量

objects变量是一个布尔值，如果为False则表示当工作表被保护时，允许用户编辑工作表中的对象。

scenarios 变量

scenarios变量是一个布尔值，如果为False则表示当工作表被保护时，允许用户编辑方案。

formatCells 变量

formatCells变量是一个布尔值，如果为False则表示当工作表被保护时，允许用户设置单元格格式。

formatRows 变量

formatRows变量是一个布尔值，如果为False则表示当工作表被保护时，允许用户设置行的格式。

formatColumns 变量

formatColumns变量是一个布尔值，如果为False则表示当工作表被保护时，允许用户设置列的格式。

insertColumns 变量

insertColumns变量是一个布尔值，如果为False则表示当工作表被保护时，允许用户插入新的列。

insertRows 变量

insertRows变量是一个布尔值，如果为False则表示当工作表被保护时，允许用户插入新的行。

insertHyperlinks 变量

insertHyperlinks变量是一个布尔值，如果为False则表示当工作表被保护时，允许用户插入新的链接。

deleteColumns 变量

deleteColumns变量是一个布尔值，如果为False则表示当工作表被保护时，允许用户删除列。

deleteRows 变量

deleteRows变量是一个布尔值，如果为False则表示当工作表被保护时，允许用户删除行。

selectLockedCells 变量

selectLockedCells变量是一个布尔值，如果为False则表示当工作表被保护时，允许用户选择锁定的单元格。

selectUnlockedCells 变量

selectUnlockedCells变量是一个布尔值，如果为False则表示当工作表被保护时，允许用户选择未锁定的单元格。

sort 变量

sort变量是一个布尔值，如果为False则表示当工作表被保护时，允许用户进行排序。

autoFilter 变量

autoFilter变量是一个布尔值，如果为False则表示当工作表被保护时，允许用户进行筛选。

pivotTables 变量

pivotTables变量是一个布尔值，如果为False则表示当工作表被保护时，允许用户使用数据透视表（包括新建，修改等操作）。

password 变量

password变量是一个表示密码或密码哈希值的字符串，当用户取消对工作表的保护时，需要输入正确的密码。

algorithmName，saltValue，spinCount，hashValue 变量

algorithmName，saltValue，spinCount，hashValue变量与加密有关。

SheetProtection对象还拥有一些方法，其中方法enable和disable可用于启用或禁用对工作表的保护，这等同于直接设置变量sheet，方法set_password可用于设置工作表的保护密码，当用户希望取消保护工作表时，需要输入相关密码。

enable()
disable()

set_password(value='', already_hashed=False)

value 参数

value参数是表示密码或密码哈希值的字符串。

already_hashed 参数

already_hashed参数是一个布尔值，如果为True则表示value参数是密码的哈希值，否则value参数是密码本身。

获取和设置 Excel 工作表的保护信息

Worksheet和WriteOnlyWorksheet对象的protection变量是一个SheetProtection对象，该对象包含了 Excel 工作表的保护信息。这些保护信息仅被 Office 软件用于限制用户的行为，并不会对openpyxl包的行为造成影响。

worksheet|writeonlyworksheet.protection

# 请将命令行跳转至 Goods.xlsx 所在的目录，然后运行此脚本文件
from openpyxl import load_workbook


workbook = load_workbook('Goods.xlsx')
protection = workbook['Flowers'].protection
# 启用对工作表 Flowers 的保护
protection.enable()
# 允许用户在 Office 软件中删除行或列
protection.deleteColumns = False
protection.deleteRows = False
# 设置密码
protection.set_password('123')


workbook.save('Protection.xlsx')

Worksheet 对象的其他特性

以下是与Worksheet，ReadOnlyWorksheet或WriteOnlyWorksheet对象相关，但不太重要的一些特性，这些特性可能并非从 Microsoft Excel 文件获取，只是由openpyxl包给出了固定值。

encoding 属性

Workbook，ReadOnlyWorksheet和WriteOnlyWorksheet对象的encoding属性，用于表示工作表所属的工作簿的编码，他被指定为'utf-8'。

源码

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