def get_excel_item(self,sheet,row):
"""
"""
item = {}
col_titles = sheet.row_values(0,0)
col_values = sheet.row_values(row,0)
for (title,value) in zip(col_titles,col_values):
if title != "":
item[title] = value
return item
def readExcel(file_path):
'''''
读取excel测试用例的函数
:param file_path:传入一个excel文件，或者文件的绝对路径
:return:返回这个excel第一个sheet页中的所有测试用例的list
'''
try:
book = xlrd.open_workbook(file_path)#打开excel
#无需每次滑动开场动画
# wd1.openApp()
# wd1.clickByPoint()
except :
log2.logger.error('路径不在或者excel不正确')
#
# except Exception as e:
# # #如果路径不在或者excel不正确，返回报错信息
# # log2.Logger.error('路径不在或者excel不正确')
# print ('路径不在或者excel不正确',e)
# return e
else:
print ('进入读取excel')
sheet = book.sheet_by_index(0)#取第一个sheet页
rows= sheet.nrows#取这个sheet页的所有行数
print ('进入读取excel,总共行数：',rows)
case_list = []#保存每一条case
for i in range(rows):
if i !=0:
#把每一条测试用例添加到case_list中
case_list.append(sheet.row_values(i))
p =os.path.abspath(os.path.join(os.path.dirname(os.path.realpath(__file__)),".."))
excelPath=lambda x:os.path.join(p, "configure", x)
interfaceTest(case_list,excelPath('DaoLend_case1.xlsx'))
def analysisSheet2List(self, sheet: sheet.Sheet, fieldlist: list):
"解析sheet"
if sheet.nrows == 0:
return []
mergeDic, lastrow = self.mergedCell(sheet)
firstrow = sheet.row_values(lastrow)
head = {}
for i in range(len(firstrow)):
cell = sheet.cell(lastrow, i)
if (lastrow, i) in mergeDic:
cell = sheet.cell(*mergeDic[(lastrow, i)])
val = self.cell2Str(cell)
if val in fieldlist:
head[val] = i
#logger.info(head)
sheetList = []
for i in range(lastrow + 1, sheet.nrows):
rowdic = self.line2Dic(sheet, i, head)
if rowdic:
sheetList.append(rowdic)
#logger.info(sheetList)
return sheetList
def xls_to_csv(source_filename, dest_fileobj):
with xlrd.open_workbook(source_filename) as workbook:
datemode = workbook.datemode
sheets = workbook.sheets()
if len(sheets) != 1:
raise UnsupportedConversion("Excel workbook has %d sheets" %
len(sheets))
sheet, = sheets
writer = csv.writer(dest_fileobj)
for row_num in range(sheet.nrows):
values = sheet.row_values(row_num)
for col_num, cell_type in enumerate(sheet.row_types(row_num)):
if cell_type == xlrd.sheet.XL_CELL_DATE:
# Decode dates into ISO-8601 strings
values[col_num] = xlrd.xldate_as_datetime(
values[col_num],
datemode,
).isoformat()
elif cell_type == xlrd.sheet.XL_CELL_NUMBER:
# Avoid forced decimal point on integers
values[col_num] = '{0:g}'.format(values[col_num])
writer.writerow(values)
def handle_date(path, pro_name, pro_type, pro_num):
print('解析 excel:{} 开始......'.format(path))
data_arr = []
# excel_path = r'C:/Users/yamei/Desktop/原材料/付款/A280/A280 V3.1 30K付款计划20190522.xlsx'
excel_path = r'' + str(path)
book = xlrd.open_workbook(excel_path)
sheet = book.sheet_by_index(0)
rows = sheet.nrows
cols = sheet.ncols
print('sheet rows,cols:', rows, cols)
for i in range(2, sheet.nrows):
first_cell = sheet.cell(i, 0)
# 第一行非数字,则说明此行数据是不重要的
if not is_number(first_cell.value) and first_cell.value != '':
continue
data = {
'产品名称': pro_name, '型号': pro_type, '台数': pro_num, '公司名称': '',
'结算方式': '', '预计交货时间': ''
}
data['公司名称'] = sheet.row_values(i-1)[1] if sheet.row_values(i)[1] == '' else sheet.row_values(i)[1]
data['结算方式'] = sheet.row_values(i)[4]
sub_time = sheet.cell(i, 7)
if sub_time.ctype == 2 or sub_time.ctype == 3:
sub_time_value = xldate_as_datetime(sub_time.value, 0).strftime('%Y-%m-%d')
data['预计交货时间'] = sub_time_value
else:
data['预计交货时间'] = sheet.row_values(i)[7]
for col in range(8, cols):
# print('row, cols is:', i, col)
# 空白列,跳过
if sheet.cell(1, col).value == '':
continue
time_result = time_formate(sheet.cell(1, col))
if time_result is None:
# data[sheet.row_values(1)[col]] = sheet.row_values(i)[col]
# 供应商主营业务,备注列 丢弃
continue
else:
# 时间字符串在4位,6位时间字符串则表示该列的值类型为时间类型
if col > 8 and time_result.__len__() <= 4:
before_time = time_formate(sheet.cell(1, col - 1))
# 时间列为递增序列,若果出现后一个时间比前一个时间小,则必然是过了一个年份
if not before_time is None and int(before_time) < int(time_result):
print(time_result + '转换为: 2019' + time_result)
time_result = '2019' + time_result
else:
if not before_time is None and int(before_time) > int(time_result):
print(time_result + '转换为: 2020' + time_result)
time_result = '2020' + time_result
else:
# 第一列时间
if time_result.__len__() <= 4:
print(time_result + '转换为: 2019' + time_result)
time_result = '2019' + time_result
data[time_result] = sheet.row_values(i)[col]
data_arr.append(data)
print(data)
print('size:', data_arr.__len__(), data_arr)
print('解析 excel:{} 结束......'.format(path))
return data_arr
class ExpectResultConfig(object):
__single_expect_result_headers = [
"审计类型", "字段名", "配置类型", "期待值配置", "期待数量配置", "期待数据目录"
]
__single_expect_result_sheet = "singleExpectResult"
__single_expect_result_start_row = 1
__group_expect_result_headers = [
"审计类型", "主字段名", "配置类型", "常量字段名列表", "常量值列表", "分组字段名列表", "分组字段值配置",
"分组字段数据目录"
]
__group_expect_result_sheet = "groupExpectResult"
__group_expect_result_start_row = 1
__single_expect_result_file = MessageConfig.single_expect_result_file
__group_expect_result_file = MessageConfig.group_expect_result_file
assert __single_expect_result_file is not None, "'single_expect_result_file' is required"
assert __group_expect_result_file is not None, "'group_expect_result_file' is required"
single_expect_json = None
group_expect_json = None
if __single_expect_result_file.endswith(".json"):
__file = open(__single_expect_result_file, "rb")
single_expect_json = json.load(__file)
__file.close()
elif __single_expect_result_file.endswith(".xls"):
single_expect_json = {"logonProperties": [], "accessProperties": []}
book = xlrd.open_workbook(__single_expect_result_file, 'r+b')
sheet = book.sheet_by_name(__single_expect_result_sheet)
for index in range(__single_expect_result_start_row, sheet.nrows):
__data = dict(
zip(__single_expect_result_headers, sheet.row_values(index)))
__auditType = __data[__single_expect_result_headers[0]]
__configType = __data[__single_expect_result_headers[2]]
__json_row = {
"propertyName": __data[__single_expect_result_headers[1]],
"configType": __configType
}
if __configType == "str":
__json_row['expectResult'] = {
"expectValues":
json.loads(__data[__single_expect_result_headers[3]]),
"expectNums":
json.loads(__data[__single_expect_result_headers[4]])
}
if __configType == "file":
__json_row['expectResult'] = {
"expectValueFiles":
json.loads(__data[__single_expect_result_headers[3]]),
"expectNumFiles":
json.loads(__data[__single_expect_result_headers[4]])
}
if __configType == "dir":
__json_row['expectResult'] = {
"dataDir": __data[__single_expect_result_headers[5]],
"expectValueSuffix":
__data[__single_expect_result_headers[3]],
"expectNumSuffix":
__data[__single_expect_result_headers[4]]
}
if __auditType == AuditType.LOGON.value:
single_expect_json['logonProperties'].append(__json_row)
if __auditType == AuditType.ACCESS.value:
single_expect_json['accessProperties'].append(__json_row)
pass
if __group_expect_result_file.endswith(".json"):
__file = open(__group_expect_result_file, "rb")
group_expect_json = json.load(__file)
__file.close()
elif __group_expect_result_file.endswith(".xls"):
group_expect_json = {"logonProperties": [], "accessProperties": []}
book = xlrd.open_workbook(__group_expect_result_file, 'r+b')
sheet = book.sheet_by_name(__group_expect_result_sheet)
for index in range(__group_expect_result_start_row, sheet.nrows):
__data = dict(
zip(__group_expect_result_headers, sheet.row_values(index)))
__auditType = __data[__group_expect_result_headers[0]]
__configType = __data[__group_expect_result_headers[2]]
__json_row = {
"propertyName": __data[__group_expect_result_headers[1]],
"configType": __configType,
"expectResult": {
"groupColumns":
json.loads(__data[__group_expect_result_headers[5]]),
"groupInvariantColumns":
json.loads(__data[__group_expect_result_headers[3]]),
"groupInvariantValues":
json.loads(__data[__group_expect_result_headers[4]])
}
}
if __configType == "str":
__json_row['expectResult']["groupValues"] = json.loads(
__data[__group_expect_result_headers[6]])
if __configType == "file":
__json_row['expectResult']["groupFileValues"] = json.loads(
__data[__group_expect_result_headers[6]])
if __configType == "dir":
__json_row['expectResult']["groupSuffixValues"] = json.loads(
__data[__group_expect_result_headers[6]])
__json_row['expectResult']["groupDataDir"] = __data[
__group_expect_result_headers[7]]
if __auditType == AuditType.LOGON.value:
group_expect_json['logonProperties'].append(__json_row)
if __auditType == AuditType.ACCESS.value:
group_expect_json['accessProperties'].append(__json_row)
pass
assert single_expect_json, "'single_expect_json' is required"
assert group_expect_json, "'group_expect_json' is required"