一篇文章教你自动化测试如何解析excel文件？

前言

自动化测试中我们存放数据无非是使用文件或者数据库，那么文件可以是csv，xlsx，xml，甚至是txt文件，通常excel文件往往是我们的首选，无论是编写测试用例还是存放测试数据，excel都是很方便的。那么今天我们就把不同模块处理excel文件的方法做个总结，直接做封装，方便我们以后直接使用，增加工作效率。

 

一、openpyxl

openpyxl是个第三方库，首先我们使用命令 pip install openpyxl 直接安装

注：openpyxl操作excel时，行号和列号都是从1开始计算的

封装代码

"""
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.styles.colors import BLACK
from collections import namedtuple


class ParseExcel(object):
    """解析excel文件"""

    def __init__(self, filename, sheet_name=None):
        try:
            self.filename = filename
            self.sheet_name = sheet_name
            self.wb = load_workbook(self.filename)
            if self.sheet_name is None:
                self.work_sheet = self.wb.active
            else:
                self.work_sheet = self.wb[self.sheet_name]
        except FileNotFoundError as e:
            raise e

    def get_max_row_num(self):
        """获取最大行号"""
        max_row_num = self.work_sheet.max_row
        return max_row_num

    def get_max_column_num(self):
        """获取最大列号"""
        max_column = self.work_sheet.max_column
        return max_column

    def get_cell_value(self, coordinate=None, row=None, column=None):
        """获取指定单元格的数据"""
        if coordinate is not None:
            try:
                return self.work_sheet[coordinate].value
            except Exception as e:
                raise e
        elif coordinate is None and row is not None and column is not None:
            if isinstance(row, int) and isinstance(column, int):
                return self.work_sheet.cell(row=row, column=column).value
            else:
                raise TypeError('row and column must be type int')
        else:
            raise Exception("Insufficient Coordinate of cell!")

    def get_row_value(self, row):
        """获取某一行的数据"""
        column_num = self.get_max_column_num()
        row_value = []
        if isinstance(row, int):
            for column in range(1, column_num + 1):
                values_row = self.work_sheet.cell(row, column).value
                row_value.append(values_row)
            return row_value
        else:
            raise TypeError('row must be type int')

    def get_column_value(self, column):
        """获取某一列数据"""
        row_num = self.get_max_column_num()
        column_value = []
        if isinstance(column, int):
            for row in range(1, row_num + 1):
                values_column = self.work_sheet.cell(row, column).value
                column_value.append(values_column)
            return column_value
        else:
            raise TypeError('column must be type int')

    def get_all_value_1(self):
        """获取指定表单的所有数据(除去表头)"""
        max_row_num = self.get_max_row_num()
        max_column = self.get_max_column_num()
        values = []
        for row in range(2, max_row_num + 1):
            value_list = []
            for column in range(1, max_column + 1):
                value = self.work_sheet.cell(row, column).value
                value_list.append(value)
            values.append(value_list)
        return values

    def get_all_value_2(self):
        """获取指定表单的所有数据(除去表头)"""
        rows_obj = self.work_sheet.iter_rows(min_row=2, max_row=self.work_sheet.max_row,
                                             values_only=True)  # 指定values_only 会直接提取数据不需要再使用cell().value
        values = []
        for row_tuple in rows_obj:
            value_list = []
            for value in row_tuple:
                value_list.append(value)
            values.append(value_list)
        return values

    def get_excel_title(self):
        """获取sheet表头"""
        title_key = tuple(self.work_sheet.iter_rows(max_row=1, values_only=True))[0]
        return title_key

    de