python 使用openpyxl读取excel数据

openpyxl介绍

​ openpyxl是一个开源项目，它是一个用于读取/写入Excel 2010文档（如xlsx 、xlsm 、xltx 、xltm文件 ）的Python库，如果要处理更早格式的Excel文档（xls），需要用到其它库（如：xlrd、xlwt等），这是openpyxl比较其他模块的不足之处。openpyxl是一款比较综合的工具，不仅能够同时读取和修改Excel文档，而且可以对Excel文件内单元格进行详细设置，包括单元格样式等内容，甚至还支持图表插入、打印设置等内容。

​ python中与excel操作相关的模块：

•  xlrd库：从excel中读取数据，支持xls、xlsx格式
•  xlwt库：对excel进行修改操作，但不支持对xlsx格式的修改
• xlutils库：在xlwt和xlrd中，对一个已存在文件进行修改
•  openpyxl库：主要针对xlsx格式的excel进行读取和编辑

openpyxl安装

1. pip install openpyxl

openpyxl基本概念

• WorkBook：工作簿对象
• Sheet：表单对象
• Cell：表格对象

​ 也就是我们使用excel时可以看到的三个对象，我们操作excel的步骤是打开工作簿，选中表单，对单元格进行操作，而openpyxl的工作原理也是如此。

openpyxl对excel进行操作

新建excel

1. import openpyxl
2.  
3. # 创建一个工作簿
4. workbook = openpyxl.Workbook()
5. # 创建一个表单
6. sheet = workbook.create_sheet('表单1')
7. # 写入一个数据
8. sheet.cell(row=1, column=1, value="python")
9. # 保存
10. workbook.save('test.xlsx')

​ 注意点：如果保存的文件名已存在，将会覆盖原来的文件

打开已存在的文件

• 打开已存在的文件：load_workbook()
• 获取表单：workbook['表单名']
• 读取指定表格：cell(row, column).value

1. import openpyxl
2.  
3. # 打开工作簿
4. workbook = openpyxl.load_workbook('test.xlsx')
5. # 获取表单
6. sheet = workbook['表单1']
7. # 读取指定的单元格数据
8. cell = sheet.cell(row=1, column=1).value
9. print(cell)

​ 运行结果：

1. C:\software\python\python.exe D:/learn/python24/python_base/test.py
2. python
3.  
4. Process finished with exit code 0

读取单个单元格

1. # 方式一：读取A6单元格的值
2. cell1 = sheet['A6'].value
3.  
4. # 方式二：读取第3行,第4列单元格的值
5. cell2 = sheet.cell(row=3, column=4).value

读取多个单元格

1. # 方式一：读取A6单元格的值
2. cell1 = sheet['A6'].value
3.  
4. # 方式二：读取第3行,第4列单元格的值
5. cell2 = sheet.cell(row=3, column=4).value

​ 读取多个单元格时，都是以元组的形式返回

其他相关操作

关键代码	相关描述
cell(row, column, value)	写入数据，三个参数分别是行，列，值
cell(row, column).value	获取指定单元格的值，两个参数分别是行，列
workbook.remove('表单名')	删除表单
del workbook['表单名']	删除表单
workbook.save('文件名')	保存工作簿
sheet.max_row	获取表单数据的总行数
sheet.max_column	获取表单数据的总列数
sheet.rows	获取按行所有的数据
sheet.columns	获取按列所有的数据

注意点：

• openpyxl中列和行的起始标识都是1，不是从0开始
• 从excel中读取出来的数据只有两种类型，即数值类型和字符串类型
• 不要随便在表格中敲空格，会影响判断最大行数和最大列数
• 运行操作excel的代码时，要先关闭在操作系统中打开的相关excel表，否则可能会无法读取/写入数据
• 写入数据时要执行保存

1. import openpyxl
2.  
3. # 打开工作簿
4. wb = openpyxl.load_workbook('test.xlsx')
5. # 获取表单
6. sh = wb['表单1']
7. # 读取指定的单元格数据
8. res1 = sh.cell(row=1, column=1).value
9. print(res1)
10.  
11. # 获取最大行数
12. print(sh.max_row)
13. # 获取最大列数
14. print(sh.max_column)
15.  
16. # 按列读取所有数据，每一列的单元格放入一个元组中
17. print(sh.columns) # 直接打印，打印结果是一个可迭代对象，我们可以转换成列表来查看
18.  
19. # 按行读取所有数据，每一行的单元格放入一个元组中
20. rows = sh.rows
21. # print(list(rows)) # 转换成列表之后打印结果为具体的单元格，如下
22. # [(<Cell '表单1'.A1>, <Cell '表单1'.B1>, <Cell '表单1'.C1>, <Cell '表单1'.D1>, <Cell '表单1'.E1>, <Cell '表单1'.F1>, <Cell '表单1'.G1>),
23. # (<Cell '表单1'.A2>, <Cell '表单1'.B2>, <Cell '表单1'.C2>, <Cell '表单1'.D2>, <Cell '表单1'.E2>, <Cell '表单1'.F2>, <Cell '表单1'.G2>),
24. # (<Cell '表单1'.A3>, <Cell '表单1'.B3>, <Cell '表单1'.C3>, <Cell '表单1'.D3>, <Cell '表单1'.E3>, <Cell '表单1'.F3>, <Cell '表单1'.G3>)]
25.  
26. # 我们可以通过for循环以及value来查看单元格的值
27. for row in list(rows): # 遍历每行数据
28. case = [] # 用于存放一行数据
29. for c in row: # 把每行的每个单元格的值取出来，存放到case里
30. case.append(c.value)
31. print(case)

​ 运行结果：

1. C:\software\python\python.exe D:/learn/python24/python_base/test.py
2. case_id
3. 3
4. 7
5. <generator object Worksheet._cells_by_col at 0x0000012FEF90D8B8>
6. ['case_id', 'interface', 'title', 'method', 'url', 'data', 'expected']
7. [1, 'login', '登录成功', 'post', '/member/login', '{"mobile_phone":"15692004245","pwd":"miki12345"}', "{'code': 0, 'msg': 'OK'}"]
8. [2, 'login', '登录失败-手机号为空', 'post', '/member/login', '{"mobile_phone":"","pwd":"miki12345"}', "{'code': 2, 'msg': '手机号码为空'}"]

​ excel表中的原数据如下图：

openpyxl封装

​ 在测试过程中，我们会经常需要访问excel读取测试数据，如果毎访问一次我们就调用一次openpyxl，进行打开excel，选取表单，读取数据等操作，将会增加我们的工作量和代码量。凡是需要重复使用的，我们都可以对它进行二次封装，写成我们自己的版本，还可以在封装过程中加入我们想要的内容，使用的时候直接调用即可。

​ 在封装openpyxl时，读取excel时我写了两种实现方法，第一种是读取数据后，把每条测试数据的键值保存到一个字典中，再把字典存到一个列表，这种方式比较容易理解，但是调用的时候需要多敲几个括号或者引号；第二种是把每条测试数据的键值存到一个实例对象的属性中，再把每个实例存到一个列表。实际上他们的作用是一样，只是第二种方式调用时更简单。

1. # read_excel.py
2. import openpyxl
3.  
4. class CasesData:
5. """用于保存测试用例数据"""
6. pass
7.  
8. class ReadExcel:
9.  
10. def __init__(self, file_name, sheet_name):
11. self.file_name = file_name
12. self.sheet_name = sheet_name
13.  
14. def open(self):
15. self.wb = openpyxl.load_workbook(self.file_name)
16. self.sh = self.wb[self.sheet_name]
17.  
18. def close(self):
19. self.wb.close()
20.  
21. def read_data(self):
22. """按行读取数据，最后返回一个存储字典的列表"""
23. self.open()
24. rows = list(self.sh.rows)
25. titles = []
26. for t in rows[0]:
27. title = t.value
28. titles.append(title)
29. cases = []
30. for row in rows[1:]:
31. case = []
32. for r in row:
33. case.append(r.value)
34. cases.append(dict(zip(titles, case))) # 通过zip聚合打包用例的标题和数据
35. self.close()
36. return cases
37.  
38. def read_data_obj(self):
39. """按行读取数据，最后返回的是一个存储实例对象的列表"""
40. self.open()
41. rows = list(self.sh.rows)
42. titles = []
43. for t in rows[0]:
44. title = t.value
45. titles.append(title)
46. cases = []
47. for row in rows[1:]:
48. case_obj = CasesData() # 创建一个对象用来保存该行用例数据
49. case = []
50. for r in row:
51. case.append(r.value)
52. case_info = dict(zip(titles, case))
53. for k, v in case_info.items():
54. setattr(case_obj, k, v)
55. cases.append(case_obj)
56. self.close()
57. return cases
58.  
59. def write_data(self, row, column, msg):
60. self.open()
61. self.sh.cell(row=row, column=column, value=msg)
62. self.wb.save(self.file_name)
63. self.close()
64.  
65. if __name__ == '__main__':
66. # 直接运行本文件时执行，下面是一个应用实例
67. # 需要读取excel时直接调用ReadExcel类
68. test = ReadExcel('cases.xlsx', 'login')
69. res = test.read_data_obj() # 最后返回的是一个存储实例对象的列表
70. print(res[0].case_id) # 打印第1个用例的case_id
71. print(res[3].title) # 打印第4个用例的title
72.  
73. test2 = ReadExcel('cases.xlsx', 'login')
74. res2 = test2.read_data() # 最后返回一个存储字典的列表
75. print(res2[0]['case_id']) # 打印第1个用例的case_id
76. print(res2[3]['title']) # 打印第4个用例的title

​ 上面封装的openpyxl中，应用实例的excel表结构如下：

以上就是python 使用openpyxl读取excel数据的详细内容，更多关于python openpyxl读取excel数据的资料请关注服务器之家其它相关文章！

原文链接：https://www.cnblogs.com/miki-peng/p/12904383.html#4676720