class TestdataUtils(): 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 __get_testcase_data_dict(self): testcase_dict = {} for row_data in self.test_data: if row_data['用例执行'] == '是': testcase_dict.setdefault( row_data['测试用例编号'],[] ).append( row_data ) return testcase_dict 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_id"] = k one_case_dict["case_info"] = v testcase_list.append( one_case_dict ) return testcase_list #sql数据源 def __get_testcase_data_dict_by_mysql(self): testcase_dict = {} for row_data in self.test_data_by_mysql: testcase_dict.setdefault(row_data['测试用例编号'], []).append(row_data) return testcase_dict 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_id"] = 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 write_result_to_excel(self, case_id, case_step_name, content="通过"): row_id = self.get_row_num(case_id, case_step_name) self.test_data_sheet.update_excel_data(row_id, 14, content) #清空测试结果 def clear_result_from_excel(self): # 方法思路是对的,但是xlutils不支持 # row_count = self.test_data_sheet.get_row_count() # # self.test_data_sheet.update_excel_data(2, 14, "") # for row_id in range(1,row_count): # 1--5 # self.test_data_sheet.update_excel_data( row_id,14,"") row_count = self.test_data_sheet.get_row_count() self.test_data_sheet.clear_excel_column(1,row_count,14)
class TestdataUtils(): 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 __get_testcase_data_dict(self): testcase_dict = {} for row_data in self.test_data: if row_data['用例执行'] == '是': testcase_dict.setdefault(row_data['测试用例编号'], []).append(row_data) return testcase_dict 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_id'] = 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 def write_result_to_excel(self, case_id, case_step_name, content='通过'): '''把测试结果写入excel''' 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): '''清空测试结果''' col_id = self.get_result_id() row_count = self.test_data_sheet.get_row_count() #获取sheet行 self.test_data_sheet.clear_excel_colum(1, row_count, col_id)
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) all_data_list.append(row_dict) print('------------------------------') for row in all_data_list: print(row)
#!/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 )
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)
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)