Python之openpyxl模块的学习

简介

之前针对Excel的读取都是使用的xlrd模块，但是在一次实践中发现xlrd模块去掉了对xlsx格式的支持，因此改用openpyxl。

以下内容仅包含使用openpyxl读取Excel文档的操作，并且比较浅显，更多内容请参考官方文档。

注：一个excel就是一个工作簿（workbook），一个工作簿含有多个工作表 （sheet）

实践

导入openpyxl

import openpyxl

读取Excel文件

wb = openpyxl.load_workbook(filename='')

根据官方文档所述：当需要处理极端大的xlsx文件，但通用的 openpyxl 程序无法处理这么大的负载时，提供了 只读模式 和 只写模式 这两种优化模式可以使我们在（几乎）恒定的内存消耗下读写无限量的数据。

只读模式只需要指定 read_only 参数为 True

wb = openpyxl.load_workbook(filename='', readonly=True)

获取工作表

使用索引获取：ws = wb.worksheets[sheet_idx]

• 其中 wb.worksheets 返回的是一个openpyxl中工作表对象的list（list of :class:`openpyxl.worksheet.worksheet.Worksheet`）

使用sheet名称获取：ws = wb['Sheet1']

**注意：get_sheet_by_name() 方法已经废弃，不再使用。

获取行数与列数

获取行数：ws.max_row

获取列数：ws.max_column

访问单个单元格数据

可以直接通过工作表的键来访问单元格：

# 单元格对象 <ReadOnlyCell 'Sheet1'.A1>
cell = ws['A1']
# 获取单元格的值
data = ws['A1'].value

也可以通过指定 行、列 数来访问：

# 获取 第二行第三列 单元格的值
ws.cell(row=2, column=3).value

# 此方法还有个参数 value=None，默认为None，可以通过指定value的值来修改单元格的数据，但此处是以只读模式读取单元格，不再赘述

访问多个单元格数据

使用切片来访问多个单元格数据

# 访问A1单元格到C2单元格，返回的是tuple(tuple, tuple)，其中一个tuple是一行，内层tuple的元素是cell对象
# ((<ReadOnlyCell 'Sheet1'.A1>, <ReadOnlyCell 'Sheet1'.B1>, <ReadOnlyCell 'Sheet1'.C1>), (<ReadOnlyCell 'Sheet1'.A2>, <ReadOnlyCell 'Sheet1'.B2>, <ReadOnlyCell 'Sheet1'.C2>))

print(ws['A1':'C2'])

# 在此基础上获取单元格的值
for row in ws['A1':'C2']:
    for cell in row:
        print(cell.value)

使用切片来访问行和列

# 访问第二行的数据
# (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.D2>)
row2 = ws[2]
print(row2)

# 访问第二行到第三行的数据，
# ((<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.D2>), (<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>, <Cell 'Sheet1'.D3>))
row_range = ws[2:3]
print(row_range)

# 访问C列的数据
# (<Cell 'Sheet1'.C1>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.C3>)
col_c = ws['C']
print(col_c)

# 访问C列到D列的数据
# ((<Cell 'Sheet1'.C1>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.C3>), (<Cell 'Sheet1'.D1>, <Cell 'Sheet1'.D2>, <Cell 'Sheet1'.D3>))
col_range = ws['C': 'D']
print(col_range)

**注意：

• 这里的切片不同于python切片，此处的[start : end] 会将stop包含在内（在python中不会将stop包含在内）
• 此处获取列数据的方法调用的是 Worksheet.iter_cols() 方法，而由于性能原因此方法在只读模式下不可用，因此这里获取列数据的方法仅在非只读模式下可用。

获取文件中所有的行和列

使用ws.rows获取文件中所有的行：

# ws.rows 返回一个生成器对象，这个生成器每次生成的值为 此行所有单元格对象组成的tuple对象
# (<ReadOnlyCell 'Sheet1'.A1>, <ReadOnlyCell 'Sheet1'.B1>, <ReadOnlyCell 'Sheet1'.C1>)
# (<ReadOnlyCell 'Sheet1'.A2>, <ReadOnlyCell 'Sheet1'.B2>, <ReadOnlyCell 'Sheet1'.C2>)
# (<ReadOnlyCell 'Sheet1'.A3>, <ReadOnlyCell 'Sheet1'.B3>, <ReadOnlyCell 'Sheet1'.C3>)
for r in ws.rows:
    print(r)

# 如果要取值，可以对每一行数据进行遍历，再使用cell.value取值

使用ws.columns获取文件中所有的列：

# ws.columns与ws.rows相同，也是返回一个生成器对象，其中每次生成的值为 此列所有单元格对象组成的tuple
# **注意：由于性能原因 Worksheet.columns 方法在只读模式下不可用
for col in ws.columns:
    print(col)

使用ws.iter_rows()获取指定行和列：

• 与ws.rows不同的是，ws.iter_rows()可以指定参数来获取特定的行和列的数据；若不指定参数，则和ws.rows相同

# ws.iter_rows()返回一个生成器对象，每次生成的值为每一行的cell对象组成的tuple
for row in ws.iter_rows():
    print(row)

# 指定参数，values_only默认为False，指定为True则只返回值而不是cell对象
# ws.iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)
# 取第一行到第三行 第4列到第6列的数据
for row in ws.iter_rows(min_row=1, max_row=3, min_col=4, max_col=6, values_only=False):
    print(row)

使用ws.iter_cols()获取指定行和列

• 和ws.columns的区别同上

# ws.iter_cols()返回一个生成器对象，每次生成的值为每一列的cell对象组成的tuple
for col in ws.iter_cols():
    print(col)

# 指定参数，values_only默认为False，指定为True则只返回值而不是cell对象
# iter_cols(self, min_col=None, max_col=None, min_row=None, max_row=None, values_only=False)
# 取第三列到第五列 第1-2行的数据
for col in ws.iter_cols(min_col=3, max_col=5, min_row=None, max_row=2, values_only=False):
    print(col)

**注意：

• 由于性能原因ws.iter_cols()在只读模式下不可用，若要使用请修改read_only=False
• 这两种方法的区别在于 ws.iter_rows()是以行的维度返回数据，ws.iter_cols()是以列的维度返回数据

直接获取文件中所有的值

如果只想要工作表中的值，可以使用 Worksheet.values 属性，这样会遍历工作表中所有的行但只返回单元格的值。

相同的，Worksheet.values 也是返回一个生成器对象，其中每次生成的值为 工作表中每一行的值组成的tuple

# row 为每一行的值组成的tuple
for row in ws.values:
    for value in row:
        print(value)

也可以通过 Worksheet.iter_rows 和 Worksheet.iter_cols，指定 values_only 参数来返回单元格值

# 以行的维度 返回第一行到第三行 第4列到第5列的单元格的值
# 每个row就是一个tuple，元素为此行的值
for row in ws.iter_rows(min_row=1, max_row=3, min_col=4, max_col=6, values_only=True):
    print(row)

# 以列的维度 返回第3列到第五列 第1行到第2行的单元格的值
# 每个col就是一个tuple，元素为此列的值
for col in ws.iter_cols(min_col=3, max_col=5, min_row=None, max_row=2, values_only=True):
    print(col)