Python之openpyxl处理excel数据
1.前言
在测试工作中，我们不可避免的就是跟测试数据打交道，那么说到数据，我们又不可避免的要跟excel打交道。在工作中，用excel存储测试数据以及测试结果是非常非常常见的。其实，python中有好多专门针对Excel进行数据处理的库，比如：xlrd、xlwt、xlutils、openpyxl以及大数据中常用的pandas等等，他们的侧重点各有不同。我们简单的列举一下：
| 库名 | 作用 |
|---|---|
| xlrd | 从excel中读取数据，支持xls,xlsx |
| xlwt | 从excel进行修改操作，不支持对xlsx格式的修改 |
| xlutils | 在xlrd和xlwt中，对一个已存在的文件进行修改 |
| openpyxl | 主要针对xlsx格式的excel进行读取和编辑 |
| pandas | 可对csv进行操作，主要用于大数据分析 |
对于上面这些，我们只需要了解，以后大家可以根据不同的需要去调用不同的库，这里不多做解释。今天，我们要学习的是用openpyxl对Excel中的数据进行处理。
2.Excel处理的基本元素
-路径： python中Excel的路径是指他所在的位置，比如：D:\pycharmProject\python_self_study
-文件，工作簿：工作簿指的就是一个以.xlsx或者.xls结尾的文件。对应openpyxl中的WorkBook。
-表格：Excel中的表格指的是Excel文件中的一个sheet,对应openpyxl中的WorkSheet。
-单元格:单元格就是一个sheet中很多小格子中的一个，对应openpyxl中的cell。
-行和列:行和列是一个sheet中又内容的行数和列数。注意：sheet中的行和列都是以1开始的。
3.对Excel进行操作
我们要操作的excel.png
3.1 安装openpyxl模块并导入
第一步很简单，我们要使用模块，必须先安装此模块，所以我们在cmd命令行下输入命令：pip install openpyxl来安装模块，或者也可以在pythoncharm中的settings下的ProjectInterpreter中下载。下载完成后导入
from openpyxl import load_workbook
3.2 workbook
3.2.1 当excel存在时，加载excel中的workbok
# 加载workbook
wb = load_workbook("original_data.xlsx")
print(wb)
3.2.2 创建workbook
# 创建workbook,并且保存
wb = Workbook()
wb.save("test.xlsx")
3.3 获取worksheet
3.3.1 通过索引获取sheet
# 通过索引获取sheet
sheets = wb.worksheets
print(sheets)
sheet1 = sheets[0]
print(sheet1)
sheet2 = sheets[1]
print(sheet2)
我们通过workbook的worksheets属性获取他的所有sheet对象，然后根据索引获取自己想要的那个sheet,查看打印如下，第0个sheet为divide,第一个sheet为minus
"D:\Program Files\Python37\python.exe" D:/pycharmProject/python_self_study/classes/demo_11_excel.py
<openpyxl.workbook.workbook.Workbook object at 0x0420D790>
[<Worksheet "divide">, <Worksheet "minus">]
<Worksheet "divide">
<Worksheet "minus">
Process finished with exit code 0
3.3.2 通过名字获取sheet
# 通过名字获取sheet
wb = load_workbook("original_data.xlsx")
sheet1 = wb["divide"]
print(sheet1)
sheet2 = wb["minus"]
print(sheet2)
我们拿到workbook对象以后，直接通过在后面加[sheetname]的方式获取所要的sheet。打印结果如下
"D:\Program Files\Python37\python.exe" D:/pycharmProject/python_self_study/classes/demo_11_excel.py
<Worksheet "divide">
<Worksheet "minus">
Process finished with exit code 0
3.3.3 通过active获取当前选中的sheet
# 通过active获取当前选中的sheet
wb = load_workbook("original_data.xlsx")
sheet = wb.active
print(sheet)
workbook的active属性拿到的是当前被选择的sheet。查看打印如下
"D:\Program Files\Python37\python.exe" D:/pycharmProject/python_self_study/classes/demo_11_excel.py
<Worksheet "divide">
Process finished with exit code 0
3.3.4 创建sheet
wb.create_sheet("mysheet")
3.4 行和列
sheet中的行和列很好理解，这里我们要掌握的是，最大行和最大列，表示的是一个sheet中有数据的行数和列数。
所以我们调用sheet的max_row属性和max_column属性就可以拿到相对的值，代码如下：
3.5 单元格cell
单元格中主要是三个元素，行，列和值。我们可以通过固定的行号和列号获取单元格中的值，也可以通过行号和列号修改单元格内的数据。
3.5.1 获取某一单元格的值
这里我们假设获取第一行第一列的值，其实就是获取到sheet里的1A单元格的值，也就是case_id。
3.5.2 设置某一单元格的值
设置某一单元格的值就是要设置行号、列号和值，如下图所示：
4.Excel操作进阶
学习了上面openpyxl中对excel的基础知识，其实我们对于数据的读取、写入都可以进行了，接下来，我们就对Excel操作进行简单的封装，代码如下：
class ExcelManual:
def __init__(self, file_path):
self.file_path = file_path
self.wb = load_workbook(file_path)
self.live_sheet = None
def select_sheet(self, name):
"""
选择表单
:param name: 表单名称
:return:
"""
self.live_sheet = self.wb[name]
return self.live_sheet
def read_cell_value(self, row, column):
"""
读取一个单元格的数据内容
:param row: 行
:param column: 列
:return:
"""
if isinstance(self.live_sheet, Worksheet):
return self.live_sheet.cell(row, column).value
def write_value_in_cell(self, row, column, value):
"""
往单元格中写入数据
:param row:行
:param column:列
:param value:值
:return:
"""
self.live_sheet.cell(row, column, value)
def read_row_value(self, row_num):
"""
读取一行的数据功能
:return:
"""
if isinstance(self.live_sheet, Worksheet):
max_row = self.live_sheet.max_row
if row_num > max_row:
print("行数超过表单中的最大行数")
return
max_column = self.live_sheet.max_column
data_list = []
for i in range(max_column):
data_list.append(self.live_sheet.cell(row_num, i + 1).value)
return data_list
def read_value_by_sheet(self, sheet_name):
"""
获取表单中的所有数据
:param sheet_name:
:return:
"""
current_sheet = self.wb[sheet_name]
if isinstance(current_sheet, Worksheet):
return list(current_sheet.values)
def close(self):
"""操作完一定要保存关闭才有效"""
self.wb.save(self.file_path)
self.wb.close()
对于Excel操作的封装其实很灵活，根据自己的需求去封装不同的操作。好啦，今天就学习到这里啦！
打完收工。
