自动化获取表格数据---综合运用

自动化执行表格用例数据步骤：
一、先写方法

1、加载Excel表格

2、获取Excel的所有sheet

3、获取sheet的某一个单元格的数据

4、获取最大的行数

5、获取行的数据

6、写入数据

7、获取某一列的数据

8、获取行号

9、获取Excel的所有数据

代码示例：

import os.path

import openpyxl


class HandExcel:
    def load_excel(self, excel_name):
        '''
        加载表格
        '''
        base_path = os.path.dirname(os.path.abspath('.'))
        print(base_path+excel_name)
        open_excel = openpyxl.load_workbook(base_path + excel_name, data_only=True)
        return open_excel

    def get_sheet_data(self, index=None, excel_name=None):
        '''
        获取表格里面的所有工作表
        '''
        if excel_name == None:
            print("--excel_name == None")
        sheet_name = self.load_excel(excel_name=excel_name).sheetnames
        if index == None:
            index = 0
        data = self.load_excel(excel_name=excel_name)[sheet_name[index]]
        return data

    def get_cell_value(self, row, column, excel_name):
        '''
        获取表格的某个单元格的值
        '''
        data = self.get_sheet_data(index=None, excel_name=excel_name).cell(row=row, column=column).value
        return data

    def get_rows(self, excel_name):
        '''
        获取最大行数
        '''
        row = self.get_sheet_data(index=None, excel_name=excel_name).max_row
        return row

    def get_rows_value(self, excel_name, row):
        '''
        获取行的值
        '''
        row_list = []
        for i in self.get_sheet_data(index=None, excel_name=excel_name)[row]:
            row_list.append(i.value)
        return row_list

    def excel_write_data(self, row, column, value, excel_name):
        '''
        获取单元格对象，写入数据
        '''
        base_path = os.path.dirname(os.path.abspath('.'))
        wb = self.load_excel(excel_name)
        wr = wb.active
        wr.cell(row, column, value)
        wb.save(base_path + excel_name)

    def get_columns_value(self, key=None, excel_name=None):
        '''
        获取某一列数据
        '''
        column_list = []
        if key == None:
            key = 'A'
        column_list_value = self.get_sheet_data(index=None, excel_name=excel_name)[key]
        for i in column_list_value:
            column_list.append(i.value)
        return column_list

    def get_rows_number(self, case_id, excel_name):
        '''
        :return: 获取行号
        '''
        numbers = 1
        cols_data = self.get_columns_value(key=None, excel_name=excel_name)
        for col_data in cols_data:
            if case_id == col_data:
                return numbers
            numbers = numbers + 1
        return numbers

    def get_excel_data(self, excel_name):
        '''
        获取表格数据
        :param excel_name:
        :return:
        '''
        data_list = []
        for i in range(self.get_rows(excel_name=excel_name)):
            data_list.append(self.get_rows_value(row=i + 2, excel_name=excel_name))
        return data_list


hand_excel = HandExcel()

二、写测试用例,执行即可

1、使用数据驱动测试@ddt.ddt

代码：

import unittest
from decimal import Decimal

import ddt

from handle_excel import hand_excel

data = hand_excel.get_excel_data("/test_data/cesuan.xlsx") 


@ddt.ddt
class CeSuanTest(unittest.TestCase):
    @ddt.data(*data)
    def test_Case(self, data):
        ...
        ...
        ...