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")

我们拿到了workbook对象wb后，调用create_sheet()方法，就能在相对应的excel中创建一个sheet,执行完上面的语句后，我们打开excel，可以看见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操作的封装其实很灵活，根据自己的需求去封装不同的操作。好啦，今天就学习到这里啦！
打完收工。