python之Excel数据读取插入结果和表复制(一)

import xlrd,requests,openpyxl,json
from xlutils.copy import copy

#获取表的第一行作为字典的key
def gettestdatakey():
    table =xlrd.open_workbook('testcase.xlsx')
    data = table.sheet_by_name('paramslogin')
    checkres = data.row_values(0)
    if 'Result' in data.row_values(0):  #取指定的数据
        return checkres[0:checkres.index('Result')]
    else:
        return checkres




#从的二行开始，数组方式返回获取的每行的单元格值
def gettestdatavalue():
    array = []
    table = xlrd.open_workbook('testcase.xlsx')
    data = table.sheet_by_name('paramslogin')
    for i in range(1,data.nrows):
        array.append(data.row_values(i))
    return array

#按照行数{key:value}拼接成列表
def gettestdatadict():
    tmpvalue = gettestdatavalue()
    tmpkey = gettestdatakey()
    dictdata = []
    for i in tmpvalue:
        dictdata.append(dict(zip(tmpkey,i)))
    return dictdata


# 插入返回结果
def gettestresult(resultdata):
    rb = xlrd.open_workbook('testcase.xlsx','w+b')
    rs = rb.sheet_by_name('paramslogin')
    # # rs = rb.sheet_by_index(1)
    cols = rs.ncols
    wb = copy(rb)	# 复制一张表，不扰乱基础数据
    data = wb.get_sheet(1)#指定sheet
    for i in range(rs.nrows):
        if i == 0:
            data.write(0, cols, 'Result')
        else:
            if resultdata[i - 1]['message'] == '成功':         # 断言成功返回pass,否则写结果
                data.write(i,cols,'pass')
            else:
                data.write(i, cols, str(resultdata[i - 1]))		# 指定位置写入数据
    wb.save('testcase.xlsx')