python之openpyxl快速读取Excel表内容

学习目录

了解下电脑中的excel表格文件格式

安装openpyxl库

使用openpyxl库

读取表格内容 

1 先准备一个表格‘python.xlsx’,表格中包含如下几个sheet页

2 导入openpyxl库的load_workbook函数

3 用一个图展示下读取表格常用的函数（WorkBook和Worksheet类中的函数）

4 分别展示如何按单元格/按行/按列获取值

4.1 几种获取不同sheet页对象的方式

4.2 values属性函数：按行获取每个单元格的值

4.3 cell()函数：返回某个单元格的对象，并且支持设置单元格的值

4.4 rows属性函数：返回每行中的所有的单元格对象，并不是单元格的值

4.4 iter_rows函数：返回指定行和列中的所有的单元格对象或者单元格的值

4.5 columns属性函数：返回每列中的所有的单元格对象，并不是单元格的值

4.6 iter_cols函数：返回指定列和行中的所有的单元格对象或者单元格的值

5 像获取列表元素一样获取表格中的内容

6 支持sheet页对象作为可迭代对象

Excel表格是大家经常用到的文件格式，各行各业都会跟它打交道。之前文章我们介绍了使用xlrd库读取excel表数据，本次本次我们介绍另一个常用的库openpyxl用于读取和写入excel表。

官网文档：

https://openpyxl.readthedocs.io/en/stable/

官网显示，openpyxl是一个用于读/写Excel2010后缀为XLSX/XLSM/XLTX/XLTM文件的python库。

了解下电脑中的excel表格文件格式

微软或者金山的excel表格编辑保存时一般要选择文件后缀，有xls和xlsx两类。

xls和xlsx后缀文件的主要区别：

• 文件格式：xls是二进制格式，而xlsx是基于XML的压缩方式。

• 版本：xls是Excel 2003及以前版本生成的文件格式，而xlsx是Excel 2007及以后版本生成的文件格式。

• 兼容性：xlsx格式向下兼容，而xls格式不支持向后兼容。

安装openpyxl库

pip install openpyxl -i https://mirrors.aliyun.com/pypi/simple/

目前最新版本3.1.5，安装后目录结果如下，初始化文件__init__.py文件如下：

使用openpyxl库

初始化文件__init__.py文件已经导入了load_workbook函数：

该函数主要用于读取excel表数据，使用时我们只需要通过如下命令直接导入即可：

from openpyxl import load_workbook

不过该load_workbook函数经过层层调用，最终还是使用openpyxl.workbook.Workbook类，通过Workbook类可处理不同的sheet页对象(class为openpyxl.worksheet. worksheet.Worksheet)，通过sheet对象的函数来获取表格中的内容。

读取表格内容

1 先准备一个表格‘python.xlsx’,表格中包含如下几个sheet页。

2 导入openpyxl库的load_workbook函数

from openpyxl import load_workbook

#读取python.xlsx表

wb = load_workbook('/Users/Downloads/python.xlsx')

3 用一个图展示下读取表格常用的函数（WorkBook和Worksheet类中的函数）

4 分别展示如何按单元格/按行/按列获取值

4.1 几种获取不同sheet页对象的方式

打开excel表

wb = load_workbook('/Users/Downloads/python.xlsx')

1）使用wb.active属性函数获取sheet对象

#获取表格打开能看到的sheet页，一般是默认第1个，也可以选择其他并保存后，获取的就是保存后的sheet页对象
sheet = wb.active

#指定获取第N个sheet页对象

wb.active = 1

sheet = wb.active

2）使用wb._sheets获取所有sheet页对象列表，并通过index获取单个sheet页对象
sheet = wb._sheets[0]

3）使用wb.sheetnames获取所有sheet页名称，并通过wb[sheetname]获取sheet页对象
sheetnames = wb.sheetnames

print(sheetnames )
sheet = wb['functions'] 
4）使用wb.worksheets获取所有sheet页对象列表，并通过index获取单个sheet页对象

print(wb.worksheets)
sheet = wb.worksheets[0]

4.2 values属性函数：按行获取每个单元格的值

values属性函数返回的是生成器，我们可以循环获取每一行的值（每一行为一个元组tuple）

测试代码：

wb = load_workbook('/Users/Downloads/python.xlsx')
sheet0 = wb.worksheets[0]
for i in sheet0.values:
   print(i)

结果：每一行返回一个元组，每个元组中是每一行中单元格中的值

4.3 cell()函数：返回某个单元格的对象，并且支持设置单元格的值

cell函数的参数：

• row代表第几行，column代表第几列，需要大于等于1，都则会报错，
• value参数如果传值会设置为该单元格的值

以上函数返回的是单元格对象，class为openpyxl.cell.cell.Cell

Cell对象的几个属性：

• value ： 单元格的值
• row：单元格所在的行数
• column：单元格所在的列数
• data_type：单元格内容对应的数据类型

测试代码：

wb = load_workbook('/Users/Downloads/python.xlsx')
sheet0 = wb.worksheets[0]
#获取第2行第7列的单元格对象
cell_ins = sheet0.cell(2,7)
#打印对象对应的属性值
print(cell_ins.row)
print(cell_ins.column)
print(cell_ins.value)
print(cell_ins.data_type)
结果：
2
7
pow()
s

4.4 rows属性函数：返回每行中的所有的单元格对象，并不是单元格的值

rows函数调用iter_rows函数，而iter_rows函数调用的是_cells_by_row函数，最终结果返回的是yield生成器（生成器返回的是每一行中每个单元格Cell对象组成的元组）

测试代码：

wb = load_workbook('/Users/Downloads/python.xlsx')
sheet0 = wb.worksheets[0]
#循环获取每个单元格的值
for i in sheet0.rows: #返回一个元组
  for cell in i: #循环处理每个单元格
    print(cell.value,end=' ')

结果：

4.4 iter_rows函数：返回指定行和列中的所有的单元格对象或者单元格的值

iter_rows函数调用的是_cells_by_row函数，最终结果返回的是yield生成器（生成器返回的是每一行中每个单元格Cell对象或者值组成的元组）

iter_rows函数与rows属性函数不同点在于： iter_rows函数可以传入指定行和列的参数，而且还可以指定是不是返回单元格的值。

• min_row和ma_row传行数，比如1到3行
• min_col和max_col传列数，比如2到4列
• values_only：等于True代表返回单元格的值，默认等于False代表返回单元格对象

测试代码：

1）指定返回第3行到第5行，第3列到第6列的单元格的值（指定参数values_only=True）

wb = load_workbook('/Users/Downloads/python.xlsx')
sheet0 = wb.worksheets[0]
for i in sheet0.iter_rows(min_row=3,max_row=5,min_col=3,max_col=6,values_only=True):
  for value in i:
     print(value,end=' ')

2）指定返回第3行到第5行单元格的对象，并打印对应单元格的值

wb = load_workbook('/Users/Downloads/python.xlsx')
sheet0 = wb.worksheets[0]
for i in sheet0.iter_rows(min_row=3,max_row=5):
  for cell in i:
    print(cell.value,end=' ')

结果：

4.5 columns属性函数：返回每列中的所有的单元格对象，并不是单元格的值

columns函数调用iter_cols函数，而iter_cols函数调用的是_cells_by_col函数，最终结果返回的是yield生成器（生成器返回的也是是每一行中每个单元格Cell对象组成的元组）

测试代码：

wb = load_workbook('/Users/Downloads/python.xlsx')
sheet0 = wb.worksheets[0]
#循环每一列获取每个单元格的值
for i in sheet0.columns:
  for cell in i:
     print(cell.value,end=' ')

结果：

4.6 iter_cols函数：返回指定列和行中的所有的单元格对象或者单元格的值

iter_cols函数调用的是_cells_by_col函数，最终结果返回的是yield生成器（生成器返回的是每一行中每个单元格Cell对象或者值组成的元组）

iter_cols函数与columns属性函数不同点在于： iter_cols函数可以传入指定行和列的参数，而且还可以指定是不是返回单元格的值。

• min_col和max_col传列数，比如2到4列
• min_row和ma_row传行数，比如1到3行
• values_only：等于True代表返回单元格的值，默认等于False代表返回单元格对象

测试代码：

1）指定返回第3行到第5行，第3列到第6列的单元格的值（指定参数values_only=True）

wb = load_workbook('/Users/Downloads/python.xlsx')
sheet0 = wb.worksheets[0]
for i in sheet0.iter_cols(min_row=3,max_row=5,min_col=3,max_col=6,values_only=True):
  for value in i:
    print(value,end=' ')

结果：

2）指定返回第3列到第5列的单元格的对象，并打印对应单元格的值

wb = load_workbook('/Users/Downloads/python.xlsx')
sheet0 = wb.worksheets[0]
for i in sheet0.iter_cols(min_col=3,max_col=5):
  for cell in i:
    print(cell.value,end=' ')

结果：

5 像获取列表元素一样获取表格中的内容。

之前文章我们讲过，在类中实现了__getitem__魔法方法，我们就可以对该类的对象，像获取列表元素等方式来获取对象的内容。

我们可以按照如下获取表中单元格的对象

• 传入单个单元格： 比如A1代表第1行第1列的单元格，D24代表第24行第4列的单元格; 返回的是1格单元格对象
• 传入多行多列：比如A1:D25代表从第1行第1列到第25行第4列这些范围的单元格，返回的是iter_rows函数的结果
• 传入多行：比如1:25代表从第1行到第25行所有的单元格，返回的是iter_rows函数的结果
• 传入多列：比如A:D代表从第1列到第4列所有的单元格，返回的是iter_rows函数的结果

测试代码: 以上4种方式打印下单元格内容

print(sheet0['D2'].value)

for i in sheet0['A1:D4']:
  for cell in i:
    print(cell.value,end=' ')
print('\n')

for i in sheet0['A:B']:
  for cell in i:
    print(cell.value,end=' ')
print('\n')

for i in sheet0['2:3']:
  for cell in i:
    print(cell.value,end=' ')
print('\n')

结果：

6 支持sheet页对象作为可迭代对象

像使用list tuple可迭代对象一样，对sheet页对象作为可迭代对象使用（类中实现了__iter__魔法方法）

测试代码：对sheet2对象循环处理

wb = load_workbook('/Users/Downloads/python.xlsx')
sheet2 = wb.worksheets[2]
for i in sheet2:
  for cell in i:
    print(cell.value,end=' ')

共勉： 东汉·班固《汉书·枚乘传》：“泰山之管穿石，单极之绠断干。水非石之钻，索非木之锯，渐靡使之然也。”

-----指水滴不断地滴，可以滴穿石头；

-----比喻坚持不懈，集细微的力量也能成就难能的功劳。

----感谢读者的阅读和学习，点个赞和关注吧，谢谢大家。