python读取Excel

#1、导入requests模块
importrequests
#从 class_12_19.do_excel1导入read_data函数
fromdo_excel2 importread_data
fromdo_excel2 importwrite_data
fromdo_excel2 importcount_case
#定义http请求函数
COOKIE=None
defhttp_request2(method,url,data):
ifmethod=='get':
print('发起一个get请求')
result=requests.get(url,data,cookies=COOKIE)
else:
print('发起一个post请求')
result=requests.post(url,data,cookies=COOKIE)
returnresult #返回响应体

None, 'msg': '登录成功'}
#从Excel读取到多条测试数据
sheets='login','recharge','withdraw'
forsheet1 insheets:
max_row=count_case(sheet1)
print(max_row)
forcase_id inrange(1,max_row):
data=read_data(sheet1,case_id)
print('读取到第{}条测试用例:'.format(data0))
print('测试数据 ',data)
#print(type(data2))
#调用函数发起http请求
result=http_request2(data4,data2,eval(data3))
print('响应结果为 ',result.json())
ifresult.cookies:
COOKIE=result.cookies
#将测试实际结果写入excel
#write_data(case_id+1,6,result'code')
write_data(sheet1,case_id+1,7,str(result.json()))
#对比测试结果和期望结果
ifresult.json()'code'==str(data5):
print('测试通过')
#将用例执行结果写入Excel
write_data(sheet1,case_id+1,8,'Pass')
else:
write_data(sheet1,case_id+1,8,'Fail')
print('测试失败')

fromopenpyxl importload_workbook
#读取测试数据
#将excel中每一条测试用例读取到一个列表中
#读取一条测试用例——写到一个函数中
defread_data(sheet_name,case_id):

workbook1=load_workbook('test_case2.xlsx')

sheet1=workbook1sheet_name
print(sheet1)
test_case=[] #用来存储每一行数据，也就是一条测试用例
test_case.append(sheet1.cell(case_id+1,1).value)
test_case.append(sheet1.cell(case_id+1,2).value)
test_case.append(sheet1.cell(case_id+1,3).value)
test_case.append(sheet1.cell(case_id+1,4).value)
test_case.append(sheet1.cell(case_id+1,5).value)
test_case.append(sheet1.cell(case_id+1,6).value)
returntest_case #将读取到的用例返回
#调用函数读取第1条测试用例，并将返回结果保存在data中

#将测试结果写会excel
defwrite_data(sheet_name,row,col,value):
workbook1=load_workbook('test_case2.xlsx')
sheet=workbook1sheet_name
sheet.cell(row,col).value=value
workbook1.save('test_case2.xlsx')
#统计测试用例的行数
defcount_case(sheet_name):
workbook1=load_workbook('test_case2.xlsx')
sheet=workbook1sheet_name
max_row=sheet.max_row #统计测试用例的行数
returnmax_row

importcsv # 导入scv库，可以读取csv文件
fromselenium importwebdriver
importunittest
fromtime importsleep
importtime
importos
importHTMLTestRunner
importcodecs
importsys
dr =webdriver.Chrome()
classtestLo(unittest.TestCase):
defsetUp(self):
pass
deftest_login(self):
'''登陆测试'''
path ='F:\\Python_test\\'

#data = csv.reader(codecs.open(my_file, 'r', encoding='UTF-8',errors=
'ignore'))
with codecs.open(my_file, 'r', encoding='UTF-8',errors='ignore') as f:
data=csv.reader((line.replace('\x00','') forline inf))

print(my_file)
foruser indata:
print(user)
dr.get('https://passport.cnblogs.com/user/signin')

dr.find_element_by_id('input1').send_keys(user0)

dr.find_element_by_id('input2').send_keys(user1)
dr.find_element_by_id('signin').click()
sleep(1)
print('\n'+'测试项：'+user2)
dr.get_screenshot_as_file(path +user3 +".jpg")
try:
assertdr.find_element_by_id(user4).text
try:
error_message =dr.find_element_by_id(user4).text
self.assertEqual(error_message, user5)
print('提示信息正确！预期值与实际值一致:')
print('预期值：'+user5)
print('实际值:'+error_message)
except:
print('提示信息错误！预期值与实际值不符：')
print('预期值：'+user5)
print('实际值:'+error_message)
except:
print('提示信息类型错误,请确认元素名称是否正确！')
deftearDown(self):
dr.refresh()

dr.quit()
ifname =="main":

report_title =u'登陆模块测试报告'

desc =u'登陆模块测试报告详情：'

date =time.strftime("%Y%m%d")
time =time.strftime("%Y%m%d%H%M%S")

path ='F:\\Python_test\\'+date +"\\login\\" + time + "\\"

report_path =path +"report.html"

ifnotos.path.exists(path):
os.makedirs(path)
else:
pass

testsuite =unittest.TestSuite()

testsuite.addTest(testLo("test_login"))

report =open(report_path, 'wb')
#with open(report_path, 'wb') as report:
runner =HTMLTestRunner.HTMLTestRunner(stream=report, title=report_title,
description=desc)
runner.run(testsuite)

report.close()

#data.to_csv('result_utf8_no_bom.csv',encoding='utf_8')#导出的结果不能别excel正确识别
data.to_csv('result_utf8_with_bom.csv',encoding='utf_8_sig')