linux上执行excel测试用例,python 搞定Excel测试用例操作

用例示例：

Excel测试用例自动化流程

Excel测试用例读取

接口请求代码构建

测试结果写入到Excel

后续一些辅助性工作

第三方接口库

pip install requests

pip install xlrd

pip install xlwt

pip install xlutils

import xlrd # 第三方库安装 pip install xlrd

#1- 读取Excel测试用例

excelDir = r‘C:\Users\***.xlsx’

#1-1:打开excel

workbook = xlrd.open_workbook(excelDir)

#1-2:查看所有的sheet

print(workbook.sheet_names()) #返回是list

#方法一：

workSheet = workbook.sheet_name()[1] #['接口文档', '新增客户61个', '修改', '删除']

方法二：

workSheet = workbook.sheet_by_name('新增客户61个') #

#读取一行

rows = workSheet.row_value(1)

#clos = workSheet.cell_value(1) # 读取一列

#读取指定单元格

#cellData = workSheet.cell_value(1, 6)# 行, 列

cellData = workSheet.cell(1, 6).value# 行, 列

dataType = workSheet.cell(1, 6).ctype# 单元数据类型 0 1 ：字符串 2：数字 3 4 5

print(rows)

#2- 构建接口对应请求

#3- 测试结果写入excel

import requests

#1-2 构建请求--获取token

token_url = 'http://14.96.181.17:9090/rest/toController'

token_data = {"userName": "J20200101", "password":"123456" }

headers = {"Content-Type": "application/json"}

resp = requests.post(token_url, data = json.dumps(token_data), headers = headers)

token = resp.json()['token']

print('token: ---> ' + token)

#2- 新增用户

addUser_url = 'http://14.96.181.17:9090/rest/ac01CrmController'

addUser_data = json.loads(cellData)#请求参数

print(type(addUser_data))

addUser_headers = {"Content-Type": "application/json", "X-AUTH-TOKEN":token}

addUser_resp = requests.post(addUser_url, data = json.dumps(addUser_data), headers = addUser_headers)

print(addUser_resp.text)

res = addUser_resp.json()["message"]

if res == '成功':

print('新增用户接口测试------>成功 ！，耗时：----> ', addUser_resp.elapsed.total_seconds())

excel_res = 'pass'

else:

print('新增用户接口测试--->失败！')

excle_res = 'fail'

#3- 测试结果写入excel

#import xlutils

from xlutils.copy import copy#复制函数 xls xlsx

#1- 首页打开excel

#workboolWr = xlrd.open_workbook(excelDir)

#2- 复制

workbooWr = copy(workbook)#临时新文件

wrSheet = workbookWr.get_sheet(1)#获取第二个sheet

workSheet.write(1, 9, excel_res)#写单元格

workbooWr.save(r'C:\Users\***01.xlsx')#保存文件

参照使用：

#2- 构建接口对应请求

import requests#导入请求库 (还有如：uitest)

import json

#1- 获取token

#1-2 构建请求

token_url = 'http://14.96.181.17:9090/rest/toController'

token_data = {"userName": "J20200101", "password":"123456" }

headers = {"Content-Type": "application/json"}

resp = requests.post(token_url, data = json.dumps(token_data), headers = headers)

#1- 获取返回状态码

print('请求返回状态码：--------> ', resp.status_code)

#2- 打印下返回的内容

print(resp.text)#字符串

# print(resp.content)#bytes

print('请求头： ------> '， resp.request.headers)

print('请求的body：---------> ', resp.request.body)

#获取token

token = resp.json()['token']

print('token: ---> ' + token)

#2- 新增用户

addUser_url = 'http://14.96.181.17:9090/rest/ac01CrmController'

addUser_data = {

"acc003": "张三",

"aac01u": "1",

"": "",

}

addUser_headers = {"Content-Type": "application/json", "X-AUTH-TOKEN":token}

addUser_resp = requests.post(addUser_url, data = json.dumps(addUser_data), headers = addUser_headers)

res = addUser_resp.json()["message"]

if res == '成功':

print('新增用户接口测试------>成功 ！，耗时：----> ', addUser_resp.elapsed.total_seconds())

else:

print('新增用户接口测试--->失败！')

数据库连接

import MYSQLdb

conn = MYSQLdb.connect(

host = '',

port = '',

user = '',

passwd = '',

db = '',

charset = 'utf8'

)