python读取excel文件

需要安装openpyxl
Pip install openpyxl

将数据存到excel里面，python去操作excel
1、只支持.xlsx这种后缀名----openpyxl只支持这种格式
2、创建.xlsx文件的时候，一定不要在pycharm里创建，会报错。要在外边创建.xlsx文件

读取数据的方式：
方法一：一次性读取所有的数据，对内存的要求会高点
方法二  需要用的时候读取所有的数据，就是对磁盘读写要求高点
方法三：根据excel中标题行去读取数据
读写速度：磁盘 < 内存 < cpu

方法一：一次性读取所有的数据

 

下边的代码是一次性读取所有数据的代码：

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File    : test_suite.py
import HTMLTestRunner
import unittest

from test_excel_case.do_excel import DoExcel
from test_excel_case.test_http import TestHttp

# test_data = [{"url": "https://xxxx.com/xxxx-api/login",
#               "data": {"username": "testlxm", "password": "testlxm"}, "expected": 1000, "method": "post"},
#              {"url": "https://xxxx.com/xxxx-api/login",
#               "data": {"username": "testlxm", "password": "123456"}, "expected": 1000, "method": "post"}]

# 方法一：一次性将用例从exel中读取出来
test_data = DoExcel("test.xlsx", 'test').get_data()

suite = unittest.TestSuite()

#实例的方式加载用例
for item in test_data:
    suite.addTest(TestHttp('test_api', item['method'], item['url'], item['data'],
                           item['expected']))

# 执行用例
with open("test_summer.html", "wb") as file:
    testrunner = HTMLTestRunner.HTMLTestRunner(stream=file, verbosity=2, title=None, description=None)
    testrunner.run(suite)

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File    : http_request.py

import requests


class HttpRequest:

    def http_request(self, url, data, method):
        headers = {'Content-Type': 'application/json;charset=UTF-8'}
        result = None
        if method.lower() == 'post':
            result = requests.request("POST", url, data=data, headers=headers)
        elif method.lower() == 'get':
            result = requests.request("GET", url, data=data, headers=headers)
        else:
            print("错误")

        return result

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File    : test_http.py
import unittest

from test_excel_case import GetData
from test_excel_case.http_request import HttpRequest


class TestHttp(unittest.TestCase):
    def setUp(self):
        pass

    def __init__(self, methodName, method, url, data, expected): #通过初始化函数传参
        super(TestHttp, self).__init__(methodName)  # 父类的方法保留了
        self.url = url
        self.data = data
        self.method = method
        self.expected = expected

    def test_api(self):
        res = HttpRequest().http_request(self.url, self.data, self.method)
        if res.cookies:#如果cookie有的话，那么就更新cookie
            setattr(GetData,'Cookie',res.cookies) #反射

        try:
            self.assertEqual(self.expected,res.json()['code'])
        except AssertionError as e:
            print("test_api's error is {}".format(e))
            raise e
        print(res)

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File    : do_excel.py
from openpyxl import load_workbook


class DoExcel:
    def __init__(self, file_name, sheet_name):
        self.file_name = file_name
        self.sheet_name = sheet_name
        self.sheet_obj = load_workbook(self.file_name)[self.sheet_name]  # 获取一个表单对象

    def get_data(self):
        wb = load_workbook(self.file_name)
        sheet = wb[self.sheet_name]
        test_data = []

        for i in range(1, sheet.max_row + 1):
            sub_data = {}
            sub_data['method'] = sheet.cell(i, 1).value
            sub_data['url'] = sheet.cell(i, 2).value
            sub_data['data'] = sheet.cell(i, 3).value
            sub_data['expected'] = sheet.cell(i, 4).value
            test_data.append(sub_data)
        print(test_data)
        return test_data  # 返回取到的数据,获取一个表单对象


if __name__ == '__main__':
    DoExcel("test.xlsx", 'test').get_data()

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File    : GetData.py


class GetData:
    Cookie = None

测试报告：

方法二  需要用的时候读取所有的数据

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File    : test_suite_02.py

import HTMLTestRunner
import unittest

from test_excel_case.do_excel_02 import DoExcel
from test_excel_case.test_http import TestHttp

#方法二：使用的时候，一条一条的读取用例
t = DoExcel("test.xlsx", 'test')
suite = unittest.TestSuite()

for i in range(1, t.max_row + 1):
    suite.addTest(TestHttp('test_api', t.get_data(i, 1), t.get_data(i, 2), t.get_data(i, 3),
                           t.get_data(i, 4))) #实例的方式加载用例

# 执行用例
with open("test_summer.html", "wb") as file:
    testrunner = HTMLTestRunner.HTMLTestRunner(stream=file, verbosity=2, title=None, description=None)
    testrunner.run(suite)

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File    : do_excel_02.py
from openpyxl import load_workbook


class DoExcel:
    def __init__(self, file_name, sheet_name):
        self.file_name = file_name
        self.sheet_name = sheet_name
        self.sheet_obj = load_workbook(self.file_name)[self.sheet_name]  # 获取一个表单对象
        self.max_row = self.sheet_obj.max_row

    def get_data(self, i, j):
        """ 根据传入的坐标来获取值    """
        return self.sheet_obj.cell(i, j).value


if __name__ == '__main__':
    res = DoExcel("test.xlsx", 'test').get_data(1, 1)
    print(res)

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File    : test_http.py
import unittest

from test_excel_case import GetData
from test_excel_case.http_request import HttpRequest


class TestHttp(unittest.TestCase):
    def setUp(self):
        pass

    def __init__(self, methodName, method, url, data, expected): #通过初始化函数传参
        super(TestHttp, self).__init__(methodName)  # 父类的方法保留了
        self.url = url
        self.data = data
        self.method = method
        self.expected = expected

    def test_api(self):
        res = HttpRequest().http_request(self.url, self.data, self.method)
        if res.cookies:#如果cookie有的话，那么就更新cookie
            setattr(GetData,'Cookie',res.cookies) #反射

        try:
            self.assertEqual(self.expected,res.json()['code'])
        except AssertionError as e:
            print("test_api's error is {}".format(e))
            raise e
        print(res)

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File    : http_request.py

import requests


class HttpRequest:

    def http_request(self, url, data, method):
        headers = {'Content-Type': 'application/json;charset=UTF-8'}
        result = None
        if method.lower() == 'post':
            result = requests.request("POST", url, data=data, headers=headers)
        elif method.lower() == 'get':
            result = requests.request("GET", url, data=data, headers=headers)
        else:
            print("错误")

        return result

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File    : GetData.py


class GetData:
    Cookie = None

方法三：excel带表头，获取数据的时候，根据表头信息去读取（比较灵活）

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File    : test_suite_03.py

import HTMLTestRunner
import unittest

from test_excel_case.do_excel_03 import DoExcel
from test_excel_case.test_http import TestHttp

# 方法三：excel表中带表头，根据表头去获取数据
test_data = DoExcel("test.xlsx", 'test').get_data()
suite = unittest.TestSuite()

for item in test_data:
    suite.addTest(TestHttp('test_api', item['method'], item['url'], item['data'],
                           item['expected']))

# 执行用例
with open("test_summer.html", "wb") as file:
    testrunner = HTMLTestRunner.HTMLTestRunner(stream=file, verbosity=2, title=None, description=None)
    testrunner.run(suite)

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File    : do_excel_03.py
from openpyxl import load_workbook


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

    # 获取第一行的标题行
    def get_header(self):
        wb = load_workbook(self.file_name)
        sheet = wb[self.sheet_name]
        header = []  # 存储标题行
        for j in range(1, sheet.max_column + 1):
            header.append(sheet.cell(1, j).value)
        return header

    # 根据传入的坐标来获取值
    def get_data(self):
        wb = load_workbook(self.file_name)
        sheet = wb[self.sheet_name]

        header = self.get_header()  # 拿到header 是一个列表，索引是从0开始的
        print(header)
        test_data = []

        for i in range(2, sheet.max_row + 1):
            sub_data = {}
            for j in range(1, sheet.max_column + 1):
                sub_data[header[j-1]] = sheet.cell(i, j).value
            test_data.append(sub_data)
        print(test_data)
        return test_data  # 返回取到的数据,获取一个表单对象


if __name__ == '__main__':
    res = DoExcel("test.xlsx", 'test').get_data(1, 1)
    print(res)

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File    : test_http.py
import unittest

from test_excel_case import GetData
from test_excel_case.http_request import HttpRequest


class TestHttp(unittest.TestCase):
    def setUp(self):
        pass

    def __init__(self, methodName, method, url, data, expected): #通过初始化函数传参
        super(TestHttp, self).__init__(methodName)  # 父类的方法保留了
        self.url = url
        self.data = data
        self.method = method
        self.expected = expected

    def test_api(self):
        res = HttpRequest().http_request(self.url, self.data, self.method)
        if res.cookies:#如果cookie有的话，那么就更新cookie
            setattr(GetData,'Cookie',res.cookies) #反射

        try:
            self.assertEqual(self.expected,res.json()['code'])
        except AssertionError as e:
            print("test_api's error is {}".format(e))
            raise e
        print(res)

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File    : http_request.py

import requests


class HttpRequest:

    def http_request(self, url, data, method):
        headers = {'Content-Type': 'application/json;charset=UTF-8'}
        result = None
        if method.lower() == 'post':
            result = requests.request("POST", url, data=data, headers=headers)
        elif method.lower() == 'get':
            result = requests.request("GET", url, data=data, headers=headers)
        else:
            print("错误")

        return result

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File    : GetData.py


class GetData:
    Cookie = None