如何使用 openpyxl 处理 Excel 行和列
关注 1421
本节内容不涉及与 Excel 格式,计算,页眉页脚相关的内容。
遍历 Excel 行或列
Worksheet
对象的属性rows
和columns
,返回一个可按照行或列遍历 Excel 单元格的 Python 生成器对象,遍历的范围从工作表的第一行或第一列开始,至工作表的最大行或最大列结束。
与Worksheet
对象类似,ReadOnlyWorksheet
对象同样拥有属性rows
,但ReadOnlyWorksheet
并不具有属性columns
。
worksheet|readonlyworksheet.rows
worksheet.columns
Excel 文件Food.xlsx
的Fruit
工作表的最大行和最大列均为2
。
# 读取 Excel 文件 Food.xlsx 中的工作表 Fruit
from openpyxl import load_workbook
workbook = load_workbook('Food.xlsx')
worksheet = workbook['Fruit']
# 工作表最大行是 2,最大列是 2
for row in worksheet.rows:
print(row)
for column in worksheet.columns:
print(column)
(<Cell 'Fruit'.A1>, <Cell 'Fruit'.B1>)
(<Cell 'Fruit'.A2>, <Cell 'Fruit'.B2>)
(<Cell 'Fruit'.A1>, <Cell 'Fruit'.A2>)
(<Cell 'Fruit'.B1>, <Cell 'Fruit'.B2>)
Worksheet
对象的方法iter_rows
和iter_cols
,可按照行或列的方式遍历 Excel 工作表的某个区域。
与Worksheet
对象类似,ReadOnlyWorksheet
对象同样拥有方法iter_rows
,但ReadOnlyWorksheet
并不具有方法iter_cols
。
worksheet|readonlyworksheet.iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)
worksheet.iter_cols(min_col=None, max_col=None, min_row=None, max_row=None, values_only=False)
- min_row,min_col,max_row,max_col 参数
参数
min_row
,min_col
,max_row
,max_col
分别为最小行,最小列,最大行和最大列,他们将用于表示被遍历的区域。如果min_row
,min_col
为默认值None
,那么其效果等同于设置为1
,如果max_row
,max_col
为默认值None
,那么其效果等同于使用工作表的最大行和最大列。需要指出的是,对于
ReadOnlyWorksheet
对象,当参数max_row
大于工作表的最大行时,将采用工作表的最大行。- values_only 参数
values_only
参数的默认值为False
,表示遍历的目标是单元格,如果设置为True
,那么将遍历单元格的值。
在下面的示例中,我们分别使用iter_rows
和iter_cols
方法,遍历了 Excel 文件Food.xlsx
的Sweets
工作表的区域B1:C3
和A1:B2
。
# 读取 Excel 文件 Food.xlsx 中的工作表 Sweets
from openpyxl import load_workbook
workbook = load_workbook('Food.xlsx')
worksheet = workbook['Sweets']
# 按照行的方式遍历单元格区域 B1:C3
for row in worksheet.iter_rows(max_row=3, min_col=2, max_col=3):
print(row)
# 按照列的方式遍历区域 A1:B2 内的单元格的值
for column_values in worksheet.iter_cols(max_col=2, max_row=2, values_only=True):
print(column_values)
(<Cell 'Sweets'.B1>, <Cell 'Sweets'.C1>)
(<Cell 'Sweets'.B2>, <Cell 'Sweets'.C2>)
(<Cell 'Sweets'.B3>, <Cell 'Sweets'.C3>)
(None, None)
(None, '白糖')
获取 Excel 的最小行,最小列,最大行,最大列
Worksheet
和ReadOnlyWorksheet
对象的属性min_row
,min_column
,max_row
,max_column
,可用于获取 Excel 工作表的最小行,最小列,最大行,最大列,他们表示了工作表所有已用单元格的最小区域。需要注意的是,Worksheet
对象所计算的工作表的最小行,最小列,最大行和最大列,可能会因为对某个单元格的访问而改变,这包括访问单元格的方法或函数,比如iter_rows
和iter_cols
。
worksheet|readonlyworksheet.min_row
worksheet|readonlyworksheet.min_column
worksheet|readonlyworksheet.max_row
worksheet|readonlyworksheet.max_column
工作表
关于如何计算 Excel 工作表中已用单元格的最小区域,你可以查看计算包含 Excel 工作表所有已用单元格的最小区域一段。
Excel 文件Food.xlsx
的Cakes
工作表的已用单元格的最小区域为B2:C3
,在通过Worksheet
对象访问单元格A1
和D4
之后,最小区域将发生变化。
# 读取 Excel 文件 Food.xlsx 中的工作表 Cakes
from openpyxl import open
workbook = open('Food.xlsx')
worksheet = workbook['Cakes']
# 工作表的已用单元格的最小区域为 B2:C3
print(f'最小行 {worksheet.min_row},最小列 {worksheet.min_column},最大行 {worksheet.max_row},最大列 {worksheet.max_row}')
# 在访问单元格 A1 和 D4 之后,最小区域发生改变
worksheet['A1']
worksheet['D4']
print(f'最小行 {worksheet.min_row},最小列 {worksheet.min_column},最大行 {worksheet.max_row},最大列 {worksheet.max_row}')
最小行 2,最小列 2,最大行 3,最大列 3
最小行 1,最小列 1,最大行 4,最大列 4
插入和删除 Excel 行或列
Worksheet
对象的方法insert_rows
和insert_cols
,可用于在指定位置为 Excel 工作表插入指定数量的行或列。
worksheet.insert_rows(idx, amount=1)
worksheet.insert_cols(idx, amount=1)
- idx 参数
idx
参数表示在工作表的第几行或第几列之前插入新的行或列,1
表示第一行或第一列。需要指出,如果将该参数设置为0
或None
,那么最终效果与设置为1
类似,但可能会导致一些问题。- amount 参数
amount
参数表示需要插入的行或列的个数,其默认值为1
。如果将该参数设置为0
,那么整个工作表中的单元格可能会被清空。
Worksheet
对象的方法delete_rows
和delete_cols
,可用于在指定位置为 Excel 工作表删除指定数量的行或列。
worksheet.delete_rows(idx, amount=1)
worksheet.delete_cols(idx, amount=1)
- idx 参数
idx
参数表示在工作表的第几行或第几列执行删除操作,1
表示第一行或第一列。需要指出,如果将该参数设置为0
或None
,那么最终效果与设置为1
类似,但可能会导致一些问题。- amount 参数
amount
参数表示需要删除的行或列的个数,其默认值为1
。如果将该参数设置为0
,那么整个工作表中的单元格可能会被清空。
工作表
如果希望在 Excel 工作表中添加一行数据,请查看为 Excel 工作表添加数据(值)一段。
下面的代码,我们为 Excel 文件Food.xlsx
的Bread
工作表先后插入和删除一些行和列,并将其保存为NewFood.xlsx
。
# 读取 Excel 文件 Food.xlsx 中的工作表 Bread
import openpyxl
workbook = openpyxl.load_workbook('Food.xlsx')
worksheet = workbook['Bread']
# 在当前第一行的位置插入两行
worksheet.insert_rows(1, 2)
# 在当前第一列的位置插入两列
worksheet.insert_cols(1, 2)
# 删除第四行和第四列
worksheet.delete_rows(4)
worksheet.delete_cols(4)
# 保存为 Excel 文件 NewFood.xlsx
workbook.save('NewFood.xlsx')
Worksheet 对象不会在插入和删除行或列之后转换单元格公式
如果你在一些 Office 软件中插入或删除工作表中的行或列,那么相关单元格的公式可能会自动转换,以消除由于单元格移动而导致的公式计算问题。不过,Worksheet
对象并未提供此类功能,在使用方法insert_rows
,insert_cols
,delete_rows
和delete_cols
之后,相关单元格的公式不会改变,如果他们拥有公式的话。
在 Excel 文件Food.xlsx
的Fish
工作表中,单元格B4
拥有计算公式=SUM(B1:B3)
,在插入两行向下移动之后,单元格B6
(原B4
)的计算公式没有变化。
# 读取 Excel 文件 Food.xlsx 中的工作表 Fish
import openpyxl
workbook = openpyxl.load_workbook('Food.xlsx')
worksheet = workbook['Fish']
# 在工作表的开始插入两行,原单元格 B4 的计算公式不会改变
worksheet.insert_rows(1, 2)
# 保存为 Excel 文件 Formula.xlsx
workbook.save('Formula.xlsx')
WriteOnlyWorksheet 应在写入数据之前设置 Excel 行或列
应该在写入任何数据之前,使用WriteOnlyWorksheet
对象的column_dimensions
变量,设置某一个 Excel 列的相关信息(比如,显示状态,保护信息等),否则设置可能不会生效。
应该在写入任何一行数据之前,使用WriteOnlyWorksheet
对象的row_dimensions
变量,设置该 Excel 行的相关信息(比如,显示状态,保护信息等),否则设置可能不会生效。
获取和设置 Excel 行的高度和列的宽度
Worksheet
和WriteOnlyWorksheet
对象的变量row_dimensions
,其变量ht
和height
是一个浮点数,用于表示 Excel 行的高度。
worksheet|writeonlyworksheet.row_dimensions[index].ht|height
worksheet|writeonlyworksheet.row_dimensions[index].ht|height = height
- index 值
index
为需要获取或设置高度的行的索引,1
表示第一行。- height 值
height
是表示行的高度的浮点数。
Worksheet
和WriteOnlyWorksheet
对象的变量column_dimensions
,其变量width
是一个浮点数,用于表示 Excel 列的宽度。
worksheet|writeonlyworksheet.column_dimensions[index].width
worksheet|writeonlyworksheet.column_dimensions[index].width = width
- index 值
index
为需要获取或设置宽度的列的索引,'A'
表示第一列。- width 值
width
是表示列的宽度的浮点数。
在下面的示例中,我们设置了第一行的高度,以及第一列的宽度,虽然都是30
,但单元格A1
并不是一个正方形,因为高度和宽度可能采用了不同的度量单位。
# 创建只写工作簿,并添加工作表 HW
from openpyxl import Workbook
wb = Workbook(True)
ws = wb.create_sheet('HW')
# 设置第一行的高度
ws.row_dimensions[1].height = 30
# 设置第一列的宽度
ws.column_dimensions['A'].width = 30
wb.save('HW.xlsx')
判断 Excel 行或列是否自定义了高度或宽度
Worksheet
和WriteOnlyWorksheet
对象的变量row_dimensions
,其customHeight
属性可用于判断 Excel 行是否自定义了高度。
worksheet|writeonlyworksheet.row_dimensions[index].customHeight
- index 值
index
为需要判断是否拥有自定义高度的行的索引,1
表示第一行。
Worksheet
和WriteOnlyWorksheet
对象的变量column_dimensions
,其customWidth
属性可用于判断 Excel 列是否自定义了宽度。
worksheet|writeonlyworksheet.column_dimensions[index].customWidth
- index 值
index
为需要判断是否拥有自定义宽度的列的索引,'A'
表示第一列。
我们为之前的示例添加一些代码,判断第一行是否拥有自定义高度,第一列是否拥有自定义宽度。
# …
# 是否拥有自定义高度和宽度?
print(ws.row_dimensions[1].customHeight)
print(ws.column_dimensions['A'].customWidth)
True
True
获取和设置 Excel 行或列的显示状态
Worksheet
和WriteOnlyWorksheet
对象的变量row_dimensions
和column_dimensions
,他们的hidden
变量用于表示 Excel 行或列的显示状态。
worksheet|writeonlyworksheet.row_dimensions|column_dimensions[index].hidden
worksheet|writeonlyworksheet.row_dimensions|column_dimensions[index].hidden = state
- index 值
index
为需要获取或设置显示状态的行或列的索引,1
表示第一行,'A'
表示第一列。- state 值
state
表示行或列的显示状态,为True
表示隐藏行或列,为False
表示显示行或列。
在下面的示例中,我们隐藏了 Excel 文件Food.xlsx
的Fruit
工作表的第一行和第一列,并保存为文件Hidden.xlsx
。
# 读取 Excel 文件 Food.xlsx 中的工作表 Fruit
from openpyxl import load_workbook
workbook = load_workbook('Food.xlsx')
worksheet = workbook['Fruit']
# 隐藏第一行或第一列
worksheet.row_dimensions[1].hidden = True
worksheet.column_dimensions['A'].hidden = True
workbook.save('Hidden.xlsx')
获取和设置 Excel 行或列的保护信息
Worksheet
和WriteOnlyWorksheet
对象的变量row_dimensions
和column_dimensions
,他们的protection
变量用于表示 Excel 行或列的保护信息,其类型为openpyxl
包的Protection
类。
worksheet|writeonlyworksheet.row_dimensions|column_dimensions[index].protection
worksheet|writeonlyworksheet.row_dimensions|column_dimensions[index].protection = protection
- index 值
index
为需要获取或设置保护信息的行或列的索引,1
表示第一行,'A'
表示第一列。- protection 值
protection
为表示行或列保护信息的Protection
对象。
单元格
关于 Pythonopenpyxl
包的Protection
对象,请查看openpyxl Excel 单元格保护对象 Protection一段。
在下面的示例中,我们设置了 Excel 文件Food.xlsx
的Sandwich
工作表的第一行的保护信息。
# 打开 Food.xlsx 中的工作表 Sandwich
import openpyxl
workbook = openpyxl.load_workbook('Food.xlsx')
worksheet = workbook['Sandwich']
from openpyxl.styles.protection import Protection
# 当工作表被保护时,将隐藏第一行单元格的公式,并锁定第一行单元格
worksheet.row_dimensions[1].protection = Protection(True, True)
workbook.save('PRow.xlsx')
组合 Excel 行或列
Worksheet
和WriteOnlyWorksheet
对象的变量row_dimensions
和column_dimensions
,他们的group
方法可以组合(可折叠或展开 Excel 行或列)工作表中的某些行或列,他们的outlineLevel
,outline_level
变量可以获取和设置行或列在组合中的大纲级别。
worksheet|writeonlyworksheet.row_dimensions|column_dimensions.group(start, end=None, outline_level=1, hidden=False)
- start 参数
start
参数表示组合开始的行或列,比如,1
表示第一行,'A'
表示第一列。- end 参数
end
参数表示组合结束的行或列(不能位于开始行的上方,或开始列的左边),比如,3
表示第三行,'C'
表示第三列。默认值为None
,这将仅组合start
参数所表示的行或列。- outline_level 参数
outline_level
参数为组合的大纲级别(折叠级别),默认为1
。- hidden 参数
hidden
参数表示被组合的行或列是否被折叠(隐藏),默认为False
,不折叠。
worksheet|writeonlyworksheet.row_dimensions|column_dimensions[index].outlineLevel|outline_level
worksheet|writeonlyworksheet.row_dimensions|column_dimensions[index].outlineLevel|outline_level = level
- index 值
index
为需要获取或设置大纲级别的行或列的索引,1
表示第一行,'A'
表示第一列。- level 值
level
表示行或列的大纲级别,大于0
等同于将行或列加入组合,小于等于0
等同于取消行或列的组合。
在下面的代码中,我们组合了 Excel 文件Food.xlsx
的Sweets
工作表的第2
行至第4
行,B
列至D
列。
# 读取 Excel 文件 Food.xlsx 中的工作表 Sweets
from openpyxl import load_workbook
workbook = load_workbook('Food.xlsx')
worksheet = workbook['Sweets']
# 组合工作表的区域 B2:D4
worksheet.row_dimensions.group(2, 4, hidden=True)
worksheet.column_dimensions['B'].outlineLevel = 1
worksheet.column_dimensions['C'].outlineLevel = 2
worksheet.column_dimensions['D'].outline_level = 1
workbook.save('Group.xlsx')
获取 Excel 行或列所隶属的工作表
Worksheet
和WriteOnlyWorksheet
对象的变量row_dimensions
和column_dimensions
,他们的parent
变量表示了 Excel 行或列所隶属的工作表对象。
worksheet|writeonlyworksheet.row_dimensions|column_dimensions[index].parent
- index 值
index
为需要获取隶属工作表对象的行或列的索引,1
表示第一行,'A'
表示第一列。
Excel 行和列的其他特性
以下是与 Excel 行或列相关的其他特性。
- index 变量
Workbook
和WriteOnlyWorksheet
对象的变量row_dimensions
和column_dimensions
,他们的index
变量表示了行或列的索引,1
表示第一行,'A'
表示第一列,比如,wb['Sheet'].row_dimensions[1].index
。- r 变量
Workbook
和WriteOnlyWorksheet
对象的变量row_dimensions
,他们的r
变量表示了行的索引,1
表示第一行。
源码
src/zh/openpyxl/rows_and_columns·codebeatme/office-programming·GitHub