excel操作

从excel里面读取接口参数，请求，对响应做出比较，将响应内容、比较结果回写到excel （可以加入ｅｘｃｅｌ配色、比较的容错、发送邮件）

read.py

# -*- coding:utf-8 -*-
import xlrd
import xlwt
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
def open_excel(file= 'he.xls'):
    try:
        data = xlrd.open_workbook(file)
        return data
    except Exception,e:
        print str(e)

def byname(file = 'he.xls',name=u'input'):
    data = open_excel(file)
    table = data.sheet_by_name(name)
    nrows = table.nrows
#    print nrows
    list = []
    for row in range(1, nrows):
#        print table.row_values(row)
        app = []
        for a in table.row_values(row):
            app.append(a)
        list.append(app)
    return list
    #print list
def conpare(file = 'he.xls',name=u'result'):
    data = open_excel(file)
    table = data.sheet_by_name(name)
    nrows = table.nrows
    list = []
    for row in range(1,nrows):
        app = []
        app.append(table.row_values(row)[2])
        app.append(table.row_values(row)[3])
        list.append(app)
    return list
def number(file = 'he.xls',name=u'result'):
    data = open_excel(file)
    table = data.sheet_by_name(name)
    nrows = table.nrows
    return nrows

def main():
    tables = byname('he.xls',u'input')




if __name__ == '__main__':
    main()

write.py

# -*- coding:utf-8 -*-
import xlrd
from xlutils.copy import copy
import sys
import read
reload(sys)
sys.setdefaultencoding('utf-8')

def write_excel():
    try:
        data = xlrd.open_workbook('he.xls',formatting_info=True)
        w = copy(data)
      #加入初始化模块
        return w
    except Exception,e:
        print str(e)

def write(i,res):
    res = res.decode('utf-8')
    if len(res)>2000:
        res = res[:2000]
    try:
        table = write_excel()
        table.get_sheet(1).write(i+1,3,res)
        table.save('he.xls')
    except Exception,e:
        print str(e)

def com_write(i,res):
    try:
        table = write_excel()
        table.get_sheet(1).write(i+1,4,res)
        table.save('he.xls')
    except Exception,e:
        print str(e)
def default():
    try:
        table = write_excel()
        number = int(read.number())
        for i in range(1,number):
            table.get_sheet(1).write(i,3,'')
            table.get_sheet(1).write(i,4,'')
        table.save('he.xls')
    except Exception, e:
        print str(e)

compare.py

# -*- coding: utf-8 -*-
import read
import sys
import write

reload(sys)
sys.setdefaultencoding('utf-8')


def do_compare():
    com_list = read.conpare()
    len_list = len(com_list)
    try:
        for i in range(len_list):
            print i
            if com_list[i][0] in com_list[i][1]:
                print "pass"
                write.com_write(i,"pass")
            else:
#                print com_list[i][0]
#                print com_list[i][1]
                print "error"
                write.com_write(i,"error")
        return "ok"
    except Exception,e:
        print str(e)
        return "error, the reason is %s",str(e)

test.py

# -*- coding:utf-8 -*-
import urllib
import urllib2
import read,write
import sys
import compare

reload(sys)
sys.setdefaultencoding('utf-8')
#write.default()
req=read.byname()
print len(req)
def metod(i):
    if 'GET' in req[i][2] or 'get' in req[i][2]:
        return 1
    elif 'POST' in req[i][2] or 'post' in req[i][2]:
        return 2
    else:
        return 0

for i in range(len(req)):
    aa = metod(i)
    print i
    if aa == 1:
        url = req[i][3]+req[i][4]
        res = urllib.urlopen(url).read()
        print url
        write.write(i,res)
    elif aa == 2:
        data = (req[i][9])
        if not data:
            print "error! has not data!"
            write.write(i,'error,no data')
            continue
        url = req[i][3]+req[i][4]
        print url
        data = (req[i][9])
        data = urllib.quote(str(data))
        res = urllib2.Request(url,data)
        value = urllib2.urlopen(res).read()
#        print value
        write.write(i,value)
    else:
        write.write(i,"the metod is error!")
compare.do_compare()