#!/usr/bin/env python
# encoding: utf-8
# @author: zengzhu
# @file: read_excel.py
# @time: 2020-11-13 23:18
# @desc:
import xlrd3
import os
from common.excel_utils import ExcelUtils
from common.testdata_utils import TestDateUtils
excel_path = os.path.join(os.path.dirname(__file__), 'data/testdata.xlsx')
read_excel = ExcelUtils(excel_path, 'tag_testcase')
all_date = read_excel.get_all_excel_value_by_dict()
dict_01 = {}
for i in all_date:
dict_01.setdefault(i['测试用例编号'], []).append(i)
listaa = []
for k, v in dict_01.items():
# print(k)
# print(v)
dictb = {}
dictb['case_name'] = k
dictb['case_info'] = v
listaa.append(dictb)
# print(listaa)
#!/usr/bin/env python
# encoding: utf-8
# @author: liusir
# @file: read_excel.py
# @time: 2020/7/1 9:13 下午
import os
import xlrd
from common.excel_utils import ExcelUtils
# excel_path = os.path.join( os.path.dirname(__file__) , 'data/test_data.xlsx' )
excel_path = os.path.join( os.path.dirname(__file__) , 'data/datashell.xls' )
excelUtils = ExcelUtils(excel_path,"Sheet1")
print(excelUtils.get_merged_cell_value(4,0) )
print(excelUtils.get_row_count())
sheet_list = []
for row in range(1,excelUtils.get_row_count()): #s
row_dict = {}
row_dict["事件"] = excelUtils.get_merged_cell_value(row,0)
row_dict["步骤序号"] = excelUtils.get_merged_cell_value(row, 1)
row_dict["步骤操作"] = excelUtils.get_merged_cell_value(row, 2)
row_dict["完成情况"] = excelUtils.get_merged_cell_value(row, 3)
# row_dict["事件"] = excelUtils.get_merged_cell_value(row, 0)
# row_dict["自欧典二"] = excelUtils.get_merged_cell_value(row, 1)
# row_dict["字段三"] = excelUtils.get_merged_cell_value(row, 2)
# row_dict["四"] = excelUtils.get_merged_cell_value(row, 3)
# row_dict["五"] = excelUtils.get_merged_cell_value(row, 4)
sheet_list.append( row_dict )
#
# for row in sheet_list:
# print( row )
def __init__(self, test_data_path=test_data_path):
self.test_data_path = test_data_path
self.test_data = ExcelUtils(test_data_path,
'Sheet1').get_sheet_data_by_dict()
def __init__(self, modul_name, page_name, element_path=None):
excl_utils = ExcelUtils(modul_name)
self.element_path = excl_utils.excel_path
self.page_name = page_name
self.sheet = excl_utils.sheet_data
self.nrow = excl_utils.get_row_count
def __init__(self,test_suite_name,test_calss_name):
self.test_calss_name=test_calss_name
self.excel_data=ExcelUtils(excel_path,test_suite_name).get_sheet_data_by_list()
self.testsuitcounts=len(self.excel_data) #祛除行首的值
class TestdataUtils:
def __init__(self, test_data_path=test_data_path):
self.test_data_path = test_data_path
self.test_data_sheet = ExcelUtils(self.test_data_path, 'Sheet1')
self.test_data = self.test_data_sheet.get_sheet_data_by_dict()
self.test_data_by_mysql = SqlUtils().get_mysql_test_case_info()
def get_testcase_data_dict(self):
test_case_list = {}
for row_data in self.test_data:
if row_data['用例执行'] == '是':
test_case_list.setdefault(row_data['测试用例编号'],
[]).append(row_data)
return test_case_list
def def_testcase_data_list(self):
testcase_list = []
for k, v in self.get_testcase_data_dict().items():
one_case_dict = {}
one_case_dict['case_name'] = k
one_case_dict['case_info'] = v
testcase_list.append(one_case_dict)
return tuple(testcase_list)
def get_testcase_data_dict_by_mysql(self):
test_case_list = {}
for row_data in self.test_data_by_mysql:
test_case_list.setdefault(row_data['测试用例编号'], []).append(row_data)
return test_case_list
def def_testcase_data_list_by_mysql(self):
testcase_list = []
for k, v in self.get_testcase_data_dict_by_mysql().items():
one_case_dict = {}
one_case_dict['case_name'] = k
one_case_dict['case_info'] = v
testcase_list.append(one_case_dict)
return tuple(testcase_list)
def get_row_num(self, case_id, case_step_name):
for j in range(len(self.test_data)):
if self.test_data[j]['测试用例编号'] == case_id and self.test_data[j][
'测试用例步骤'] == case_step_name:
break
return j + 1
def get_result_id(self):
for col_id in range(len(self.test_data_sheet.sheet.row(0))):
if self.test_data_sheet.sheet.row(0)[col_id].value == '测试结果':
break
return col_id # 测试结果列号 14
def write_result_to_excel(self, case_id, case_step_name, content='通过'):
row_id = self.get_row_num(case_id, case_step_name)
col_id = self.get_result_id()
self.test_data_sheet.update_excel_data(row_id, col_id, content)
# def clear_result_from_excel(self): # 只能改最后一个
# row_count = self.test_data_sheet.get_row_count()
# for row_id in range(1,row_count):
# self.test_data_sheet.update_excel_data(row_id,14,"")
def clear_result_from_excel(self):
row_count = self.test_data_sheet.get_row_count()
col_id = self.get_result_id()
self.test_data_sheet.clear_excel_column(1, row_count, col_id)
import xlrd
import os
from common.excel_utils import ExcelUtils
"""
使用xlrd读取Excel文件
获取指定格式数据：将每一行数据以字典形式打印，表格总数据以列表形式打印
"""
# 创建测试Excel文件地址
excel_path = os.path.join(os.path.dirname(__file__), 'data/test_data.xlsx')
excelUtils = ExcelUtils(excel_path, 'Sheet1')
# **********************普通方法获取表格数据
# print(excelUtils.get_row_count())
# sheet_list = []
# for row in range(1, excelUtils.get_row_count()): # 表头不算在内
# row_dict = {} # 创建空字典 来存放没行数据
# row_dict['事件'] = excelUtils.get_merged_cell_value(row, 0)
# row_dict['步骤序号'] = excelUtils.get_merged_cell_value(row, 1)
# row_dict['步骤操作'] = excelUtils.get_merged_cell_value(row, 2)
# row_dict['完成情况'] = excelUtils.get_merged_cell_value(row, 3)
# sheet_list.append(row_dict)
#
# # print(sheet_list)
# # 利用循环输出某列的单元格内容
# for row in sheet_list:
# print(row)
# **********************改造方法：使用遍历方式获取(字典 列表)
all_data_list = []
#!/usr/bin/env python
# encoding: utf-8
# @author:lrh
# @file:取合并单元格的数据
# @time:2020/7/1 21:26
import os
import xlrd
from common.excel_utils import ExcelUtils
current_value = os.path.dirname(__file__)
excel_path = os.path.join(current_value, 'data/testl_data.xlsx')
excelUtils = ExcelUtils(excel_path, "Sheet1")
# print(excelUtils.get_merged_cell_value(8,0))
#自己
# sheet_list = []
# for row in range(1,excelUtils.get_row_count()):
# dict = {}
# for col in range(0,excelUtils.get_col_count()):
# # value = excelUtils.get_merged_cell_value(row,col)
# dict[excelUtils.get_merged_cell_value(0,col)] = excelUtils.get_merged_cell_value(row,col)
# sheet_list.append(dict)
#老师办法
all_data_list = []
first_row = excelUtils.sheet.row(0)
for row in range(1, excelUtils.get_row_count()):
row_dict = {}
for col in range(0, excelUtils.get_col_count()):
row_dict[first_row[col].value] = excelUtils.get_merged_cell_value(
row, col)
# encoding: utf-8
#@author: newdream_daliu
#@file: read_test_data.py
#@time: 2020-08-16 17:44
#@desc:
from common.excel_utils import ExcelUtils
file_path = 'D:/P3_PO_UI_Test_Framework/test_datas/test_data_infos.xlsx'
excel_data = ExcelUtils(file_path, 'login_suite').get_sheet_data_by_list()
excel_row = len(excel_data)
test_class_name = 'LoginTest'
# print(excel_data)
# for s in excel_data:
# print(s)
test_data_infos = {} #所有的测试用例
for i in range(1, excel_row):
test_data_info = {} #一条测试用例的数据
if excel_data[i][2].__eq__(test_class_name):
test_data_info['test_name'] = excel_data[i][1]
# 如果执行用例为是，返回 False,如果不执行就返回True
test_data_info['isnot'] = False if excel_data[i][3].__eq__(
'是') else True
test_data_info['excepted_result'] = excel_data[i][4]
test_parameter = {}
for j in range(5, len(excel_data[i])):
#参数里面包含=号，或者长度大于2,才算一个正常的参数（a=1）
if excel_data[i][j].__contains__('=') and len(
excel_data[i][j]) > 2:
# username = test01
def __init__(self,test_suite_name,test_file_name,test_class_name=None,test_data_path=test_data_path):
test_data_path = os.path.join( test_data_path,test_suite_name,test_file_name+'.xlsx' )
self.excel_data = ExcelUtils( test_data_path , test_class_name ).get_sheet_data_by_list()
self.excel_rows = len(self.excel_data)
def __init__(self, test_suite_name, test_class_name):
self.test_class_name = test_class_name
self.excel_data = ExcelUtils(test_data_path,
test_suite_name).get_sheet_data_by_list()
self.test_suite_counts = len(self.excel_data) - 1
#!/usr/bin/env python
#encoding:utf-8
#@author:k
#@file:read_excel.py
#@time:2020/12/28 15:43 下午
import os
import xlrd
from common.excel_utils import ExcelUtils
excel_path = os.path.join(os.path.dirname(__file__), 'data/test_data.xlsx')
excelUtils = ExcelUtils(excel_path, 'Sheet1')
# print(excelUtils.get_merged_cell_value(4, 0))
# print(excelUtils.get_row_count())
sheet_list = []
for row in range(1, excelUtils.get_row_count()): #
row_dict = {}
row_dict['事件'] = excelUtils.get_merged_cell_value(row, 0)
row_dict['步骤序号'] = excelUtils.get_merged_cell_value(row, 1)
row_dict['步骤情况'] = excelUtils.get_merged_cell_value(row, 2)
row_dict['完成情况'] = excelUtils.get_merged_cell_value(row, 3)
sheet_list.append(row_dict)
# for row in sheet_list:
# print(row)
all_data_list = []
first_row = excelUtils.sheet.row(0)
# print(first_row)
for row in range(1, excelUtils.get_row_count()):
row_dict = {}
class TestDataUtils():
def __init__(self, data_path=test_data_path):
"""
:param data_path:excel数据存放路径
"""
self.data_path = data_path
self.test_data_sheet = ExcelUtils(data_path, 'Sheet1')
self.test_data = self.test_data_sheet.get_sheet_data_by_dict()
self.test_data_by_mysql = SqlUtils().get_mysql_test_case_info()
def __get_test_case_data_dict(self):
'''
:return: 返回字典格式数据
'''
use_case_dict = {}
for row_data in self.test_data:
if row_data['用例执行'] == '是':
use_case_dict.setdefault(row_data['测试用例编号'],
[]).append(row_data)
return use_case_dict
def get_test_case_data_list(self):
"""
:return: 封装成字典格式case_id为key，case_info为value
"""
test_case_list = []
for k, v in self.__get_test_case_data_dict().items():
one_case_dict = {}
one_case_dict["case_id"] = k
one_case_dict['case_info'] = v
test_case_list.append(one_case_dict)
return tuple(test_case_list)
def __get_test_case_data_dict_by_mysql(self):
use_case_dict = {}
for row_data in self.test_data_by_mysql:
use_case_dict.setdefault(row_data['测试用例编号'], []).append(row_data)
return use_case_dict
def get_test_case_data_list_by_mysql(self):
test_case_list = []
for k, v in self.__get_test_case_data_dict_by_mysql().items():
one_case_dict = {}
one_case_dict["case_id"] = k
one_case_dict['case_info'] = v
test_case_list.append(one_case_dict)
return tuple(test_case_list)
def get_row_num(self, case_id, case_step_name):
for j in range(len(self.test_data)):
if self.test_data[j]['测试用例编号'] == case_id and self.test_data[j][
'测试用例步骤'] == case_step_name:
break
return j + 1
def get_result_index(self):
for col_index in range(len(self.test_data_sheet.sheet.row(0))):
if self.test_data_sheet.sheet.row(0)[col_index].value == '测试结果':
break
return int(col_index)
def write_result_to_excel(self, case_id, case_step_name, content='通过'):
row_index = self.get_row_num(case_id, case_step_name)
col_index = self.get_result_index()
self.test_data_sheet.update_excel_data(row_index, col_index, content)
def clear_result_from_excel(self):
row_count = self.test_data_sheet.get_row_count()
col_index = self.get_result_index()
self.test_data_sheet.clear_excel_column(1, row_count, col_index)
def __init__(self,test_data_path = test_data_path):
self.test_data_path = test_data_path
self.test_data_sheet = ExcelUtils(test_data_path, "Sheet1")
self.test_data = self.test_data_sheet.get_sheet_data_by_dict()
self.test_data_by_mysql = SqlUtils().get_mysql_test_case_info()
def __init__(self, test_data_path=test_data_path):
self.test_data_path = test_data_path
self.test_data_sheet = ExcelUtils(test_data_path, conf.sheet_name)
self.test_data = self.test_data_sheet.get_sheet_data_by_dict()
def __init__(self, data_path=data_path, sheet_name='tag_testcase'):
self.data_path = data_path
self.sheet_name = sheet_name
self.test_data = ExcelUtils(self.data_path, self.sheet_name).get_all_excel_value_by_dict()
def __init__(self, test_data_path=test_data_file):
self.test_data_path = test_data_path
self.test_data = ExcelUtils(
test_data_path, "Sheet1").get_sheet_data_by_dict() #获取sheet1的所有数据
def __init__(self,test_suite_name,test_file_name,test_class_name,test_data_path=testdata_path):
self.test_data_path=os.path.join(test_data_path,test_suite_name,test_file_name+'.xls')
self.test_class_name=test_class_name
self.excel_data=ExcelUtils(test_class_name,self.test_data_path).get_excel_data_by_list()
self.excel_rows=len(self.excel_data)
def __init__(self, test_data_path=config.CASE_DATA_PATH):
self.test_data_path = test_data_path
self.test_data = ExcelUtils(self.test_data_path,
"Sheet1").get_sheet_data_by_dict()
