{
  "request":{
    "username":"register->1",
    "password":"register->1"
  },
  "response":{
    "code":"register->1"
  }
}

#encoding=utf-8
import openpyxl
from openpyxl.styles import Border, Side, Font
import time

class ParseExcel(object):

  def __init__(self):
    self.workbook = None
    self.excelFile = None
    self.font = Font(color = None) # 設(shè)置字體的顏色
    # 顏色對應(yīng)的RGB值
    self.RGBDict = {'red': 'FFFF3030', 'green': 'FF008B00'}

  def loadWorkBook(self, excelPathAndName):
    # 將excel文件加載到內(nèi)存，并獲取其workbook對象
    try:
      self.workbook = openpyxl.load_workbook(excelPathAndName)
    except Exception as err:
      raise err
    self.excelFile = excelPathAndName
    return self.workbook

  def getSheetByName(self, sheetName):
    # 根據(jù)sheet名獲取該sheet對象
    try:
      # sheet = self.workbook.get_sheet_by_name(sheetName)
      sheet = self.workbook[sheetName]
      return sheet
    except Exception as err:
      raise err

  def getSheetByIndex(self, sheetIndex):
    # 根據(jù)sheet的索引號獲取該sheet對象
    try:
      # sheetname = self.workbook.get_sheet_names()[sheetIndex]
      sheetname = self.workbook.sheetnames[sheetIndex]
    except Exception as err:
      raise err
    # sheet = self.workbook.get_sheet_by_name(sheetname)
    sheet = self.workbook[sheetname]
    return sheet

  def getRowsNumber(self, sheet):
    # 獲取sheet中有數(shù)據(jù)區(qū)域的結(jié)束行號
    return sheet.max_row

  def getColsNumber(self, sheet):
    # 獲取sheet中有數(shù)據(jù)區(qū)域的結(jié)束列號
    return sheet.max_column

  def getStartRowNumber(self, sheet):
    # 獲取sheet中有數(shù)據(jù)區(qū)域的開始的行號
    return sheet.min_row

  def getStartColNumber(self, sheet):
    # 獲取sheet中有數(shù)據(jù)區(qū)域的開始的列號
    return sheet.min_column

  def getRow(self, sheet, rowNo):
    # 獲取sheet中某一行,返回的是這一行所有的數(shù)據(jù)內(nèi)容組成的tuple，
    # 下標(biāo)從1開始，sheet.rows[1]表示第一行
    try:
      rows = []
      for row in sheet.iter_rows():
        rows.append(row)
      return rows[rowNo - 1]
    except Exception as err:
      raise err

  def getColumn(self, sheet, colNo):
    # 獲取sheet中某一列,返回的是這一列所有的數(shù)據(jù)內(nèi)容組成tuple，
    # 下標(biāo)從1開始，sheet.columns[1]表示第一列
    try:
      cols = []
      for col in sheet.iter_cols():
        cols.append(col)
      return cols[colNo - 1]
    except Exception as err:
      raise err

  def getCellOfValue(self, sheet, coordinate = None,
            rowNo = None, colsNo = None):
    # 根據(jù)單元格所在的位置索引獲取該單元格中的值,下標(biāo)從1開始,
    # sheet.cell(row = 1, column = 1).value，
    # 表示excel中第一行第一列的值
    if coordinate != None:
      try:
        return sheet[coordinate]
      except Exception as err:
        raise err
    elif coordinate is None and rowNo is not None and \
            colsNo is not None:
      try:
        return sheet.cell(row = rowNo, column = colsNo).value
      except Exception as err:
        raise err
    else:
      raise Exception("Insufficient Coordinates of cell !")

  def getCellOfObject(self, sheet, coordinate = None,
            rowNo = None, colsNo = None):
    # 獲取某個單元格的對象，可以根據(jù)單元格所在位置的數(shù)字索引，
    # 也可以直接根據(jù)excel中單元格的編碼及坐標(biāo)
    # 如getCellObject(sheet, coordinate = 'A1') or
    # getCellObject(sheet, rowNo = 1, colsNo = 2)
    if coordinate != None:
      try:
        # return sheet.cell(coordinate = coordinate)
        return sheet[coordinate]
      except Exception as err:
        raise err
    elif coordinate == None and rowNo is not None and \
            colsNo is not None:
      try:
        return sheet.cell(row = rowNo,column = colsNo)
      except Exception as err:
        raise err
    else:
      raise Exception("Insufficient Coordinates of cell !")

  def writeCell(self, sheet, content, coordinate = None,
    rowNo = None, colsNo = None, style = None):
    #根據(jù)單元格在excel中的編碼坐標(biāo)或者數(shù)字索引坐標(biāo)向單元格中寫入數(shù)據(jù)，
    # 下標(biāo)從1開始，參style表示字體的顏色的名字,比如red，green
    if coordinate is not None:
      try:
        # sheet.cell(coordinate = coordinate).value = content
        sheet[coordinate] = content
        if style is not None:
          sheet[coordinate].\
            font = Font(color = self.RGBDict[style])
        self.workbook.save(self.excelFile)
      except Exception as e:
        raise e
    elif coordinate == None and rowNo is not None and \
            colsNo is not None:
      try:
        sheet.cell(row = rowNo,column = colsNo).value = content
        if style:
          sheet.cell(row = rowNo,column = colsNo).\
            font = Font(color = self.RGBDict[style])
        self.workbook.save(self.excelFile)
      except Exception as e:
        raise e
    else:
      raise Exception("Insufficient Coordinates of cell !")

  def writeCellCurrentTime(self, sheet, coordinate = None,
        rowNo = None, colsNo = None):
    # 寫入當(dāng)前的時間，下標(biāo)從1開始
    now = int(time.time()) #顯示為時間戳
    timeArray = time.localtime(now)
    currentTime = time.strftime("%Y-%m-%d %H:%M:%S", timeArray)
    if coordinate is not None:
      try:
        sheet.cell(coordinate = coordinate).value = currentTime
        self.workbook.save(self.excelFile)
      except Exception as e:
        raise e
    elif coordinate == None and rowNo is not None \
        and colsNo is not None:
      try:
        sheet.cell(row = rowNo, column = colsNo
            ).value = currentTime
        self.workbook.save(self.excelFile)
      except Exception as e:
        raise e
    else:
      raise Exception("Insufficient Coordinates of cell !")

if __name__ == '__main__':
  # 測試代碼
  pe = ParseExcel()
  pe.loadWorkBook(r'D:\ProgramSourceCode\Python Source Code\WorkSpace\InterfaceFrame2018\inter_test_data.xlsx')
  sheetObj = pe.getSheetByName(u"API")
  print("通過名稱獲取sheet對象的名字：", sheetObj.title)
  # print help(sheetObj.rows)
  print("通過index序號獲取sheet對象的名字：", pe.getSheetByIndex(0).title)
  sheet = pe.getSheetByIndex(0)
  print(type(sheet))
  print(pe.getRowsNumber(sheet)) #獲取最大行號
  print(pe.getColsNumber(sheet)) #獲取最大列號
  rows = pe.getRow(sheet, 1) #獲取第一行
  for i in rows:
    print(i.value)
  # # 獲取第一行第一列單元格內(nèi)容
  # print pe.getCellOfValue(sheet, rowNo = 1, colsNo = 1)
  # pe.writeCell(sheet, u'我愛祖國', rowNo = 10, colsNo = 10)
  # pe.writeCellCurrentTime(sheet, rowNo = 10, colsNo = 11)

import requests
import json

class HttpClient(object):
  def __init__(self):
    pass

  def request(self, requestMethod, requestUrl, paramsType,
        requestData, headers =None, **kwargs):
    if requestMethod == "post":
      print("---", requestData, type(requestData))
      if paramsType == "form":
        response = self.__post(url = requestUrl, data = json.dumps(eval(requestData)),
                 headers = headers, **kwargs)
        return response
      elif paramsType == "json":
        response = self.__post(url = requestUrl, json = json.dumps(eval(requestData)),
                 headers = headers, **kwargs)
        return response
    elif requestMethod == "get":
      request_url = requestUrl
      if paramsType == "url":
        request_url = "%s%s" %(requestUrl, requestData)
      response = self.__get(url = request_url, params = requestData, **kwargs)
      return response

  def __post(self, url, data = None, json = None, headers=None,**kwargs):
    print("----")
    response = requests.post(url=url, data = data, json=json, headers=headers)
    return response

  def __get(self, url, params = None, **kwargs):
    response = requests.get(url, params = params, **kwargs)
    return response

if __name__ == "__main__":
  hc = HttpClient()
  res = hc.request("get", "http://39.106.41.11:8080/getBlogContent/", "url",'2')
  print(res.json())

import hashlib

def md5_encrypt(text):
  m5 = hashlib.md5()
  m5.update(text.encode("utf-8"))
  value = m5.hexdigest()
  return value

if __name__ == "__main__":
  print(md5_encrypt("sfwe"))

import logging
import logging.config
from config.public_data import baseDir

# 讀取日志配置文件
logging.config.fileConfig(baseDir + "\config\Logger.conf")
# 選擇一個日志格式
logger = logging.getLogger("example02")#或者example01

def debug(message):
  # 定義dubug級別日志打印方法
  logger.debug(message)

def info(message):
  # 定義info級別日志打印方法
  logger.info(message)

def warning(message):
  # 定義warning級別日志打印方法
  logger.warning(message)

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
from ProjVar.var import *

import os
import smtplib
from email import encoders
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
from email.utils import formataddr

def send_mail():
  mail_host="smtp.126.com" #設(shè)置服務(wù)器
  mail_user="testman1980"  #用戶名
  mail_pass="wulaoshi1980"  #口令
  sender = 'testman1980@126.com'
  receivers = ['2055739@qq.com',"testman1980@126.com"] # 接收郵件，可設(shè)置為你的QQ郵箱或者其他郵箱
  # 創(chuàng)建一個帶附件的實(shí)例
  message = MIMEMultipart()
  message['From'] = formataddr(["光榮之路吳老師", "testman1980@126.com"])
  message['To'] = ','.join(receivers)
  subject = '自動化測試執(zhí)行報(bào)告'
  message['Subject'] = Header(subject, 'utf-8')
  message["Accept-Language"]="zh-CN"
  message["Accept-Charset"]="ISO-8859-1,utf-8,gbk"
  # 郵件正文內(nèi)容
  message.attach(MIMEText('最新執(zhí)行的自動化測試報(bào)告，請參閱附件內(nèi)容！', 'plain', 'utf-8'))

  # 構(gòu)造附件1，傳送測試結(jié)果的excel文件
  att = MIMEBase('application', 'octet-stream')
  att.set_payload(open(ProjDirPath+"\\testdata\\testdata.xlsx", 'rb').read())
  att.add_header('Content-Disposition', 'attachment', filename=('gbk', '', "自動化測試報(bào)告.xlsx"))
  encoders.encode_base64(att)
  message.attach(att)
  """
  # 構(gòu)造附件2，傳送當(dāng)前目錄下的 runoob.txt 文件
  att2 = MIMEText(open('e:\\a.py','rb').read(), 'base64', 'utf-8')
  att2["Content-Type"] = 'application/octet-stream'
  att2["Content-Disposition"] = 'attachment; filename="a.py"'
  message.attach(att2)
  """
  try:
    smtpObj = smtplib.SMTP(mail_host)
    smtpObj.login(mail_user, mail_pass)
    smtpObj.sendmail(sender, receivers, message.as_string())
    print("郵件發(fā)送成功")
  except smtplib.SMTPException as e:
    print("Error: 無法發(fā)送郵件", e)

if __name__ == "__main__":
  send_mail()

import os
# 整個項(xiàng)目的根目錄絕對路勁
baseDir = os.path.dirname(os.path.dirname(__file__))

# 獲取測試數(shù)據(jù)文件的絕對路徑
file_path = baseDir + "/TestData/inter_test_data.xlsx"

API_apiName = 2
API_requestUrl = 3
API_requestMothod = 4
API_paramsType = 5
API_apiTestCaseFileName = 6
API_active = 7

CASE_requestData = 1
CASE_relyData = 2
CASE_responseCode = 3
CASE_responseData = 4
CASE_dataStore = 5
CASE_checkPoint = 6
CASE_active = 7
CASE_status = 8
CASE_errorInfo = 9

# 存儲請求參數(shù)里面依賴的數(shù)據(jù)
REQUEST_DATA = {}

# 存儲響應(yīng)對象中的依賴數(shù)據(jù)
RESPONSE_DATA = {}

if __name__=="__main__":
  print(file_path)
  print(baseDir)

from config.public_data import REQUEST_DATA, RESPONSE_DATA
from utils.md5_encrypt import md5_encrypt

REQUEST_DATA = {"用戶注冊":{"1":{"username":"zhangsan", "password":"dfsdf23"},
            "headers":{"cookie":"asdfwerw"}}}
RESPONSE_DATA = {"用戶注冊":{"1":{"code":"00"}, "headers":{"age":2342}}}

class GetRely(object):
  def __init__(self):
    pass

  @classmethod
  def get(self, dataSource, relyData, headSource = {}):
    print(type(dataSource))
    print(dataSource)
    data = dataSource.copy()
    for key, value in relyData.items():
      if key == "request":
        #說明應(yīng)該去REQUEST_DATA中獲取
        for k, v in value.items():
          interfaceName, case_idx = v.split("->")
          val = REQUEST_DATA[interfaceName][case_idx][k]
          if k == "password":
            data[k] = md5_encrypt(val)
          else:
            data[k] = val
      elif key == "response":
        # 應(yīng)該去RESPONSE_DATA中獲取
        for k, v in value.items():
          interfaceName, case_idx = v.split("->")
          data[k] = RESPONSE_DATA[interfaceName][case_idx][k]
      elif key == "headers":
        if headSource:
          for key, value in value.items():
            if key == "request":
              for k, v in value.items():
                for i in v:
                  headSource[i] = REQUEST_DATA[k]["headers"][i]
            elif key == "response":
              for i, val in value.items():
                for j in val:
                  headSource[j] = RESPONSE_DATA[i]["headers"][j]
    return "%s" %data

if __name__ == "__main__":
  s = {"username": "", "password": "","code":""}
  h = {"cookie":"123", "age":332}
  rely = {"request": {"username": "用戶注冊->1", "password": "用戶注冊->1"},
      "response":{"code":"用戶注冊->1"},
      "headers":{"request":{"用戶注冊":["cookie"]},"response":{"用戶注冊":["age"]}}
      }
  print(GetRely.get(s, rely, h))

from config.public_data import RESPONSE_DATA, REQUEST_DATA

class RelyDataStore(object):
  def __init__(self):
    pass

  @classmethod
  def do(cls, storePoint, apiName, caseId, request_source = {}, response_source = {}, req_headers={}, res_headers = {}):
    for key, value in storePoint.items():
      if key == "request":
        # 說明需要存儲的依賴數(shù)據(jù)來自請求參數(shù),應(yīng)該將數(shù)據(jù)存儲到REQUEST_DATA
        for i in value:
          if i in request_source:
            val = request_source[i]
            if apiName not in REQUEST_DATA:
              # 說明存儲數(shù)據(jù)的結(jié)構(gòu)還未生成，需要指明數(shù)據(jù)存儲結(jié)構(gòu)
              REQUEST_DATA[apiName]={str(caseId): {i: val}}
            else:
              #說明存儲數(shù)據(jù)結(jié)構(gòu)中最外層結(jié)構(gòu)已存在
              if str(caseId) in REQUEST_DATA[apiName]:
                REQUEST_DATA[apiName][str(caseId)][i] = val
              else:
                # 說明內(nèi)層結(jié)構(gòu)不完整，需要指明完整的結(jié)構(gòu)
                REQUEST_DATA[apiName][str(caseId)] = {i: val}
          else:
            print("請求參數(shù)中不存在字段" + i)
      elif key == "response":
        #說明需要存儲的依賴數(shù)據(jù)來自接口的響應(yīng)body,應(yīng)該將數(shù)據(jù)存儲到RESPONSE_DATA
        for j in value:
          if j in response_source:
            val = response_source[j]
            if apiName not in RESPONSE_DATA:
              # 說明存儲數(shù)據(jù)的結(jié)構(gòu)還未生成，需要指明數(shù)據(jù)存儲結(jié)構(gòu)
              RESPONSE_DATA[apiName]={str(caseId): {j: val}}
            else:
              #說明存儲數(shù)據(jù)結(jié)構(gòu)中最外層結(jié)構(gòu)已存在
              if str(caseId) in RESPONSE_DATA[apiName]:
                RESPONSE_DATA[apiName][str(caseId)][j] = val
              else:
                # 說明內(nèi)層結(jié)構(gòu)不完整，需要指明完整的結(jié)構(gòu)
                RESPONSE_DATA[apiName][str(caseId)] = {j: val}
          else:
            print("接口的響應(yīng)body中不存在字段" + j)
      elif key == "headers":
        for k, v in value.items():
          if k == "request":
            # 說明需要往REQUEST_DATA變量中寫入存儲數(shù)據(jù)
            for item in v:
              if item in req_headers:
                header = req_headers[item]
                if "headers" in REQUEST_DATA[apiName]:
                  REQUEST_DATA[apiName]["headers"][item] = header
                else:
                  REQUEST_DATA[apiName]["headers"] = {item: header}
          elif k == "response":
            # 說明需要往RESPONSE_DATA變量中寫入存儲數(shù)據(jù)
            for it in v:
              if it in res_headers:
                header = res_headers[it]
                if "headers" in RESPONSE_DATA[apiName]:
                  RESPONSE_DATA[apiName]["headers"][it] = header
                else:
                  RESPONSE_DATA[apiName]["headers"] = {item: header}
    print(REQUEST_DATA)
    print(RESPONSE_DATA)


if __name__ == "__main__":
  r = {"username": "srwcx01", "password": "wcx123wac1", "email": "wcx@qq.com"}
  req_h = {"cookie":"csdfw23"}
  res_h = {"age":597232}
  s = {"request": ["username", "password"], "response": ["userid"],"headers":{"request":["cookie"],
    "response":["age"]}}
  res = {"userid": 12, "code": "00"}
  RelyDataStore.do(s, "register", 1, r, res, req_headers=req_h, res_headers=res_h)
  print(REQUEST_DATA)
  print(RESPONSE_DATA)

import re

class CheckResult(object):
  def __init__(self):
    pass

  @classmethod
  def check(self, responseObj, checkPoint):
    responseBody = responseObj.json()
    # responseBody = {"code": "", "userid": 12, "id": "12"}
    errorKey = {}
    for key, value in checkPoint.items():
      if key in responseBody:
        if isinstance(value, (str, int)):
          # 等值校驗(yàn)
          if responseBody[key] != value:
            errorKey[key] = responseBody[key]
        elif isinstance(value, dict):
          sourceData = responseBody[key]
          if "value" in value:
            # 模糊匹配校驗(yàn)
            regStr = value["value"]
            rg = re.match(regStr, "%s" %sourceData)
            if not rg:
              errorKey[key] = sourceData
          elif "type" in value:
            # 數(shù)據(jù)類型校驗(yàn)
            typeS = value["type"]
            if typeS == "N":
              # 說明是整形校驗(yàn)
              if not isinstance(sourceData, int):
                errorKey[key] = sourceData
      else:
        errorKey[key] = "[%s] not exist" %key
    return errorKey

if __name__ == "__main__":
  r = {"code": "00", "userid": 12, "id": 12}
  c = {"code": "00", "userid": {"type": "N"}, "id": {"value": "\d+"}}
  print(CheckResult.check(r, c))

from config.public_data import *

def write_result(wbObj, sheetObj, responseData, errorKey, rowNum):
  try:
    # 寫響應(yīng)body
    wbObj.writeCell(sheetObj, content="%s" %responseData,
            rowNo = rowNum, colsNo=CASE_responseData)
    # 寫校驗(yàn)結(jié)果狀態(tài)及錯誤信息
    if errorKey:
      wbObj.writeCell(sheetObj, content="%s" %errorKey,
            rowNo=rowNum, colsNo=CASE_errorInfo)
      wbObj.writeCell(sheetObj, content="faild",
              rowNo=rowNum, colsNo=CASE_status, style="red")
    else:
      wbObj.writeCell(sheetObj, content="pass",
              rowNo=rowNum, colsNo=CASE_status, style="green")
  except Exception as err:
    raise err

#encoding=utf-8
import requests
import json
from action.get_rely import GetRely
from config.public_data import *
from utils.ParseExcel import ParseExcel
from utils.HttpClient import HttpClient
from action.data_store import RelyDataStore
from action.check_result import CheckResult
from action.write_result import write_result
from utils.Log import *

def main():
  parseE = ParseExcel()
  parseE.loadWorkBook(file_path)
  sheetObj = parseE.getSheetByName("API")
  activeList = parseE.getColumn(sheetObj, API_active)
  for idx, cell in enumerate(activeList[1:], 2):
    if cell.value == "y":
      #需要被執(zhí)行
      RowObj = parseE.getRow(sheetObj, idx)
      apiName = RowObj[API_apiName -1].value
      requestUrl = RowObj[API_requestUrl - 1].value
      requestMethod = RowObj[API_requestMothod - 1].value
      paramsType = RowObj[API_paramsType - 1].value
      apiTestCaseFileName = RowObj[API_apiTestCaseFileName - 1].value

      # 下一步讀取用例sheet表，準(zhǔn)備執(zhí)行測試用例
      caseSheetObj = parseE.getSheetByName(apiTestCaseFileName)
      caseActiveObj = parseE.getColumn(caseSheetObj, CASE_active)
      for c_idx, col in enumerate(caseActiveObj[1:], 2):
        if col.value == "y":
          #需要執(zhí)行的用例
          caseRowObj = parseE.getRow(caseSheetObj, c_idx)
          requestData = caseRowObj[CASE_requestData - 1].value
          relyData = caseRowObj[CASE_relyData - 1].value
          responseCode = caseRowObj[CASE_responseCode - 1].value
          responseData = caseRowObj[CASE_responseData - 1].value
          dataStore = caseRowObj[CASE_dataStore -1].value
          checkPoint = caseRowObj[CASE_checkPoint - 1].value

          #發(fā)送接口請求之前需要做一下數(shù)據(jù)依賴的處理
          if relyData:
            logging.info("處理第%s個接口的第%s條用例的數(shù)據(jù)依賴！")
            requestData = GetRely.get(eval(requestData), eval(relyData))
          httpC = HttpClient()
          response = httpC.request(requestMethod=requestMethod,
                       requestData=requestData,
                       requestUrl=requestUrl,
                       paramsType=paramsType
                       )
          # 獲取到響應(yīng)結(jié)果后，接下來進(jìn)行數(shù)據(jù)依賴存儲邏輯實(shí)現(xiàn)
          if response.status_code == 200:
            responseData = response.json()
            # 進(jìn)行依賴數(shù)據(jù)存儲
            if dataStore:
              RelyDataStore.do(eval(dataStore), apiName, c_idx - 1, eval(requestData), responseData)
            # 接下來就是校驗(yàn)結(jié)果
            else:
              logging.info("接口【%s】的第【%s】條用例，不需要進(jìn)行依賴數(shù)據(jù)存儲！" %(apiName, c_idx))
            if checkPoint:
              errorKey = CheckResult.check(response, eval(checkPoint))
              write_result(parseE, caseSheetObj, responseData, errorKey, c_idx)
          else:
            logging.info("接口【%s】的第【%s】條用例，執(zhí)行失敗，接口協(xié)議code非200！" %(apiName, c_idx))
        else:
          logging.info("第%s個接口的第%s條用例，被忽略執(zhí)行！" %(idx -1, c_idx-1))
    else:
      logging.info("第%s行的接口被忽略執(zhí)行！" %(idx -1))


if __name__=="__main__":
  main()