故事背景

讀取一個(gè)Excel中的一條數(shù)據(jù)用例，請(qǐng)求接口，然后返回結(jié)果并反填到excel中。過程中會(huì)生成請(qǐng)求回來的文本，當(dāng)然還會(huì)生成一個(gè)xml文件。具體的excel文件如下：

代碼方案

# -*- coding: UTF-8 -*- from xml.dom import minidomimport xlrdimport openpyxlimport requestsimport jsonimport sysimport HTMLParserimport osimport reimport codecsimport timeimport datetimereload(sys)sys.setdefaultencoding('utf-8')class OptionExcelData(object):  """對(duì)Excel進(jìn)行操作，包括讀取請(qǐng)求參數(shù)，和填寫操作結(jié)果"""  def __init__(self, excelFile,excelPath=''):    self.excelFile = excelFile    self.excelPath = excelPath    self.caseList = []  """  傳入：傳入用例Excel名稱  返回：[],其中元素為{},每個(gè){}包含行號(hào)、城市、國家和期望結(jié)果的鍵值對(duì)  """  def getCaseList(self,excelFile,excelPath=''):    readExcel = xlrd.open_workbook(fileName)              #讀取指定的Excel    try:      table = readExcel.sheet_by_index(0)               #獲取Excel的第一個(gè)sheet      trows = table.nrows                       #獲取Excel的行數(shù)      for n in range(1,trows):        tmpdict = {}                        #把一行記錄寫進(jìn)一個(gè){}        tmpdict['id'] = n                      #n是Excel中的第n行        tmpdict['CityName'] = table.cell(n,2).value        tmpdict['CountryName'] = table.cell(n,3).value        tmpdict['Rspect'] = table.cell(n,4).value        self.caseList.append(tmpdict)    except Exception, e:      raise    finally:      pass    return self.caseList  """  傳入：請(qǐng)求指定字段結(jié)果，是否通過，響應(yīng)時(shí)間  返回：  """  def writeCaseResult(self,resultBody,isSuccess,respTime,/    excelFile,theRow,theCol=5):    writeExcel = openpyxl.load_workbook(excelFile)           #加載Excel，后續(xù)寫操作    try:      wtable = writeExcel.get_sheet_by_name('Sheet1')         #獲取名為Sheet1的sheet      wtable.cell(row=theRow+1,column=theCol+1).value = resultBody  #填寫實(shí)際值      wtable.cell(row=theRow+1,column=theCol+2).value = isSuccess   #填寫是否通過      wtable.cell(row=theRow+1,column=theCol+3).value = respTime   #填寫響應(yīng)時(shí)間      writeExcel.save(excelFile)    except Exception, e:      raise    finally:      passclass GetWeather(object):  """獲取天氣的http請(qǐng)求"""  def __init__(self, serviceUrl,requestBody,headers):    self.serviceUrl = serviceUrl    self.requestBody = requestBody    self.headers = headers    self.requestResult = {}  """  傳入：請(qǐng)求地址，請(qǐng)求體，請(qǐng)求頭  返回：返回{}，包含響應(yīng)時(shí)間和請(qǐng)求結(jié)果的鍵值對(duì)  """  def getWeath(self,serviceUrl,requestBody,headers):    timebefore = time.time()                      #獲取請(qǐng)求開始的時(shí)間，不太嚴(yán)禁    tmp = requests.post(serviceUrl,data=requestBody,/      headers=headers)    timeend = time.time()                        #獲取請(qǐng)求結(jié)束的時(shí)間    tmptext = tmp.text    self.requestResult['text'] = tmptext                #記錄響應(yīng)回來的內(nèi)容    self.requestResult['time'] = round(timeend - timebefore,2)     #計(jì)算響應(yīng)時(shí)間    return self.requestResultclass XmlReader:  """操作XML文件"""  def __init__(self,testFile,testFilePath=''):    self.fromXml = testFile    self.xmlFilePath = testFilePath    self.resultList = []  def writeXmlData(self,resultBody,testFile,testFilePath=''):    tmpXmlFile = codecs.open(testFile,'w','utf-16')           #新建xml文件    tmpLogFile = codecs.open(testFile+'.log','w','utf-16')       #新建log文件    tmp1 = re.compile(r'/<.*?/>')                   #生成正則表達(dá)式:<*?>    tmp2 = tmp1.sub('',resultBody['text'])               #替換相應(yīng)結(jié)果中的<*?>    html_parser = HTMLParser.HTMLParser()    xmlText = html_parser.unescape(tmp2)                #轉(zhuǎn)換html編碼    try:      tmpXmlFile.writelines(xmlText.strip())             #去除空行并寫入xml      tmpLogFile.writelines('time: '+/        str(resultBody['time'])+'/r/n')               #把響應(yīng)時(shí)間寫入log      tmpLogFile.writelines('text: '+resultBody['text'].strip())   #把請(qǐng)求回來的文本寫入log    except Exception, e:      raise    finally:      tmpXmlFile.close()      tmpLogFile.close()  """返回一個(gè)list"""  def readXmlData(self,testFile,testFilePath=''):    tmpXmlFile = minidom.parse(testFile)    root = tmpXmlFile.documentElement    tmpValue = root.getElementsByTagName('Status')[0]./    childNodes[0].data    return tmpValue                           #獲取特定字段并返回結(jié)果，此處選取Statusif __name__ == '__main__':  requesturl = 'http://www.webservicex.net/globalweather.asmx/GetWeather'  requestHeadrs = {"Content-Type":"application/x-www-form-urlencoded"}  fileName = u'用例內(nèi)容.xlsx'  ed = OptionExcelData(fileName)   testCaseList = ed.getCaseList(ed.excelFile)  for caseDict in testCaseList:    caseId = caseDict['id']    cityName = caseDict['CityName']    countryName = caseDict['CountryName']    rspect = caseDict['Rspect']    requestBody = 'CityName='+cityName+'&CountryName='+countryName    getWeather = GetWeather(requesturl,requestBody,requestHeadrs)    #獲取請(qǐng)求結(jié)果    tmpString = getWeather.getWeath(getWeather.serviceUrl,/      getWeather.requestBody,getWeather.headers)    xd = XmlReader(str(caseId) + '.xml')    #把請(qǐng)求內(nèi)容寫入xml和log    xd.writeXmlData(tmpString,xd.fromXml)    response = xd.readXmlData(str(caseId) + '.xml')    respTime = tmpString['time']    if response == rspect:      theResult = 'Pass'    else:      theResult = 'False'   ed.writeCaseResult(response,theResult,respTime,fileName,caseId)