python读取Excel内容

读取Excel封装类

from openpyxl import load_workbook

class ReadExcel:      # 读取Excel里面的内容

    def __init__(self, file_name, sheet_name):
        self.file_name = file_name
        self.sheet_name = sheet_name

    def get_title(self):     # 读取Excel里面的title数据
        wb = load_workbook(self.file_name)     # 打开Excel工作簿
        sheet1 = wb[self.sheet_name]
        title = []               # 定义一个空列表，将读取的title字段进行存储
        for i in range(1, sheet1.max_column+1):
            title.append(sheet1.cell(1, i).value)
        return title

    def do_excel(self):
        wb = load_workbook(self.file_name)
        sheet1 = wb[self.sheet_name]
        title = self.get_title()   # 调用title内容
        all_data = []
        for j in range(2, sheet1.max_row+1):      # 获取最大行数，加入循环
            row_data = {}
            for i in range(3, sheet1.max_column+1):  # 获取最大列数，进行嵌套循环
                row_data[title[i-1]] = sheet1.cell(j, i).value  # 把拿到的数据进行字典的键对值匹配
            all_data.append(row_data)
        return all_data

 

对Excel的内容进行数据驱动实例化和序列化

import unittest
from API_Excel.Http_Request.http_request import HttpRequest
from API_Excel.Read_Excel_Data.read_excel_data import ReadExcel
from ddt import ddt, data

# 读取Excel数据
file = 'E:\自动化练习\dai_test\API_Excel\Read_Excel_Data\data.xlsx'
test_data = ReadExcel(file, 'TestCase').do_excel()


"""API请求的测试类"""
@ddt
class TestHttpRequest(unittest.TestCase):

    def __init__(self, methodName, url, method,  param, expected ):     # 初始化参数
        super(TestHttpRequest,self).__init__(methodName)    # 超继承多个测试类
        self.url = url
        self.method = method
        self.param = param
        self.expected = expected


    def setUp(self):
        print('API测试开始执行')

    def tearDown(self):
        print('API测试结束执行')
    @data(*test_data)
    def test_http_request(self, item):    # 测试请求函数
        res = HttpRequest().http_request(item['url'], item['method'] , eval(item['param']))
        try:
            self.assertEqual(item['expected'], str(res))
        except Exception as e:
            print('请求发生错误-[{0}]'.format(e))
            raise e
        else:
            print('执行结果是{0}'.format(res))

 

以上代码仅供自己引用   不保证直接运行、