代碼源碼：
框架結(jié)構(gòu)

核心代碼

excel用例demo

excel數(shù)據(jù)處理

from configureUtil.LogUtil import getlog
logger = getlog(targetName='HandleData')
import xlrd
from openpyxl import load_workbook,workbook
from openpyxl.styles import Font, colors
import openpyxl
import os
# from Common.FunctionStart import MoveSpace
# from openpyxl import load_workbook
# from openpyxl.reader.excel import load_workbook
# from openpyxl.styles import Color, Font, Alignment
# from openpyxl.styles import colors
'''
1、cope一份用例所保存的excel，當(dāng)做執(zhí)行環(huán)境保證測(cè)試數(shù)據(jù)清潔。
2、讀取excle數(shù)據(jù)，返回dick形式的結(jié)果。
'''

class ExcelHander():
    '''
    excel操作類，對(duì)外提供取excle返回dick結(jié)果功能、
    新增excel、sheet、
    cope excel功能、
    寫入excel功能等。
    '''
    def __init__(self,filepath):
        self.filepath=filepath
        self.wb=xlrd.open_workbook(filepath)#加載excel
        self.sheet_names=self.wb.sheet_names()#獲取excel所有sheet名集合形如：['test', 'test2', 'test3']

    def ExcelDick(self,SheetName):
        '''
        :param SheetName: excel的sheet名字
        :return: 返回讀取excel字典類型數(shù)據(jù)
        '''
        table = self.wb.sheet_by_name(SheetName)
        # 獲取總行數(shù)
        rowNum = table.nrows
        # 獲取總列數(shù)
        colNum = table.ncols

        if rowNum<=1:
            logger.error('總行數(shù)小于等于1行')
        else:
            logger.debug('開(kāi)始解析excel----excel總行數(shù)：%s'%rowNum)
            # 獲取第一行（表頭）
            keys = table.row_values(0)
            print(keys)
            r=[]
            j=1
            for i in range(rowNum-1):
                s={}
                # 從第二行開(kāi)始
                values=table.row_values(j)
                print(values)
                for x in range(colNum):
                    s[keys[x]]=values[x]
                r.append(s)
                j+=1
            # logger.debug('返回列名：%s'%r)
            ExcelDick={}
            ExcelDick[SheetName]=r
            logger.debug('ExcelDick：%s' % ExcelDick)
            return ExcelDick #形如ExcelDick{'sheetName':[{列名：values},{列名：values}]}

    def sheet_method(self,work_book, add_sheet=[]):
        wk = work_book
        # rename default sheet
        ss_sheet = wk["Sheet"]
        # ss_sheet = wk.get_sheet_by_name('Sheet')
        ss_sheet.title = add_sheet[0]
        for i in range(1, len(add_sheet)):
            # add new sheet
            wk.create_sheet(add_sheet[i])
        # switch to active sheet
        # sheet_num = wk.get_sheet_names()
        sheet_num = wk.sheetnames
        last_sheet = len(sheet_num) - 1
        sheet_index = sheet_num.index(sheet_num[last_sheet])
        wk.active = sheet_index

    def CreateExcel(self,filepath,add_sheet=[]):
        '''
        :param filepath: excel地址
        :return: 無(wú)
        '''
        # 新建一個(gè)工作簿
        p1=os.path.exists(filepath)#判斷是否存在
        if p1:
            os.remove(filepath)
        wb2 = workbook.Workbook()
        self.sheet_method(wb2,add_sheet)
        logger.debug('新建excle:%s' % filepath)
        wb2.save(filepath)

    def CopeExcel(self,filepath,newexcelPath,i=0):
        '''
        :param filepath: 原excel地址
        :param newexcelPath: 新excel地址
        :param SheetName: 原sheet的名字
        :return: 無(wú)
        '''
        # 讀取數(shù)據(jù)
        logger.debug('讀取數(shù)據(jù)excle:%s' % filepath)
        source = openpyxl.load_workbook(filepath)
        target = openpyxl.load_workbook(newexcelPath)
        sheets1 = source.sheetnames
        sheets2 = target.sheetnames
        logger.info('源sheet列表:%s,目標(biāo)sheet列表:%s'%(sheets1,sheets2))
        sheet1 = source[sheets1[i]]
        logger.debug('獲取sheet:%s' % sheet1)
        sheet2 = target[sheets2[i]]
        table = self.wb.sheet_by_name(sheets1[i])
        # 獲取總行數(shù)
        max_row = table.nrows
        # 獲取總列數(shù)
        max_cloumn = table.ncols
        for m in list(range(1, max_row + 1)):
            for n in list(range(97, 97 + max_cloumn)):  # 字母a=97
                n = chr(n)
                i = '%s%d' % (n, m)
                cell1 = sheet1[i].value  # 獲取文本數(shù)據(jù)
                # log.debug('獲取文本數(shù)據(jù):%s'%cell1)
                sheet2[i].value = cell1
        logger.debug('保存數(shù)據(jù)')
        target.save(newexcelPath)  # 保存數(shù)據(jù)
        source.close()
        target.close()

    def WriteExcel(self,filepath,row,cloumn,values,i):
        '''
        :param filepath: excel地址
        :param row: 行號(hào)
        :param cloumn: 列號(hào)
        :param values: 值
        :param i: sheet的索引
        :return: 無(wú)
        '''
        excelpath = load_workbook(filepath)
        sheets = excelpath.sheetnames
        excelpath[sheets[i]].cell(row, cloumn).value = values
        excelpath.save(filepath)
        logger.debug('寫數(shù)據(jù)完成：sheet：%s 行：%s，列：%s，值：%s' % (sheets[i],row, cloumn, values))



def AssembleCase(filepath,newexcelPath):
    '''
    測(cè)試用例組裝工廠
    :return: 測(cè)試用例
    '''
    #新增同名excel、sheet
    test = ExcelHander(filepath)#實(shí)例化
    add_sheet = test.sheet_names#獲取sheetname列表：['sheet1','sheet2']
    test.CreateExcel(newexcelPath, add_sheet)#創(chuàng)建excel及sheet（cope原excel新建空的execle）
    #給excel填充數(shù)據(jù)
    for i in range(len(add_sheet)):
        test.CopeExcel( filepath, newexcelPath, i)
    #按sheet分組，組裝request數(shù)據(jù)
    wb = xlrd.open_workbook(newexcelPath)  # 加載新excel
    sheet_names = wb.sheet_names()  # 獲取excel所有sheet名集合形如：['sheet1', 'sheet2', 'sheet3']
    caselist=[]
    for i in range(len(sheet_names)):
        caselist.append(test.ExcelDick(sheet_names[i]))#返回所有sheet集合，形如ExcelDick[{'sheetName':[{列名：values},{列名：values}]},{'sheetName':[{列名：values},None]
    #接口請(qǐng)求數(shù)據(jù)
    return caselist

def wordFormat(filepath,postition,size,name,bold,italic,i=0):
    '''
    格式化表格數(shù)據(jù)
    postition,位置如A1
    size,字體大小
    name,字體類型名
    color,字體顏色
    bold,是否加粗
    italic,是否斜體
    i，sheet索引
    :param filepath:指定excle
    :return:
    '''
    #激活excle
    wb = openpyxl.load_workbook(filepath)
    sheet1 = wb.worksheets[i]
    italic24Font = Font(size=size, name=name, bold=bold ,italic=italic)
    sheet1[postition].font = italic24Font
    wb.save(filepath)

def backFormat(filepath,n,m,fgColor,i=0):
    '''
    :param n: 行號(hào)
    :param m: 列號(hào)
    :param fgColor: 顏色 # blue 23ff00 greet 6e6fff  red ff0f06
    :param i: sheet索引
    :return:
    '''
    import openpyxl.styles as sty
    wb = openpyxl.load_workbook(filepath)
    sheet1 = wb.worksheets[i]
    sheet1.cell(row=n, column=m).fill = sty.fills.PatternFill(fill_type='solid',fgColor=fgColor)
    wb.save(filepath)

def excleFormat(filepath):
    '''
    filepath 格式化excle
    :return:
    excel表頭宋體斜體加粗背景色blue 12號(hào) 6e6fff
    其他內(nèi)容宋體背景色無(wú) 11號(hào) ffffff
    成功的 宋體背景色綠色 11號(hào) 23ff00
    失敗 宋體背景色綠色 11號(hào) ff0f06
    '''
    wb = xlrd.open_workbook(filepath)#加載
    sheet_names = wb.sheet_names()  # 獲取excel所有sheet名集合形如：['test', 'test2', 'test3']
    #######字體
    if sheet_names==[]:
        logger.debug('excel是空sheet')
        pass
    else:
        for i,SheetName in enumerate(sheet_names)  :
            table = wb.sheet_by_name(SheetName)
            # logger.debug('獲取第%s個(gè)sheet=%s'%(i,SheetName))
        # 獲取總行數(shù)
            rowNum = table.nrows
            # logger.debug('行數(shù)：%s'%(rowNum))
        # 獲取總列數(shù)
            colNum = table.ncols
            # logger.debug('列數(shù)：%s' % (colNum))
            name='Times New Roman'
            if rowNum<1:
                # logger.debug('空sheet')
                pass
            else:
                for m in list(range(1, rowNum + 1)):
                    for n in list(range(97, 97 + colNum)):  # 字母a=97
                        if m==1:
                            n = chr(n)
                            postition = '%s%d' % (n, m)
                            color='6e6fff'
                            bold=True
                            italic=True
                            size=12
                            # logger.debug('格式第一行數(shù)據(jù)')
                            wordFormat(filepath, postition, size, name, bold, italic, i)
                            fgColor='6e6fff'
                            backFormat(filepath,m, ord(n)-96, fgColor, i)
                        else:
                            n = chr(n)
                            postition = '%s%d' % (n, m)
                            color = '6e6fff'
                            bold = False
                            italic = False
                            size = 11
                            # logger.debug('格式化%s行數(shù)據(jù)'%(m))
                            wordFormat(filepath, postition, size, name, bold, italic, i)
                            cell_value = table.cell_value(m-1,10)
                            if cell_value=='TRUE'or cell_value==1 :
                                fgColor='23ff00'
                                # logger.debug('獲取到結(jié)果：TRUE')
                                backFormat(filepath,m, 11, fgColor, i)
                            elif cell_value=='FLASE' or cell_value==0:
                                fgColor = 'ff0f06'
                                # logger.debug('獲取到結(jié)果：FLASE')
                                backFormat(filepath,m, 11, fgColor, i)
                            else:
                                logger.error('行號(hào)：%s ' % (m - 1))
                                # logger.error('沒(méi)有獲取到結(jié)果：%s'%cell_value)

########背景色


if __name__=='__main__':
    filepath = r'E:\plant\AutoUniversalInterface\Common\TestCase\demo.xlsx'
    newexcelPath=r'E:\plant\AutoUniversalInterface\Common\TestResult\demo.xlsx'
    # 打開(kāi)excel
    postition='A1'
    size=14#大小
    name='Times New Roman'#字體
    color=colors.BLACK#字體顏色
    bold = False #是否加粗
    italic = True #是否斜體
    # headerFormat(filepath, postition,size, name, color,bold,italic)
    # wordFormat(filepath, postition, size, name, bold, italic, i=0)
    # fgColor = '23ff00'
    # n=2
    # m=11
    # backFormat(filepath,n, m, fgColor, i=0)
    # wb = xlrd.open_workbook(filepath)
    # table = wb.sheet_by_name('test')
    #
    # cell_value = table.cell_value(1, 10)
    # print(cell_value)
    excleFormat(newexcelPath)

requests請(qǐng)求封裝

import requests
# 禁用安全請(qǐng)求警告
import urllib3
urllib3.disable_warnings()
import json

def callInterface(session,url, param=None,parammode='data', method='post', headers=None,verify=False,  jsonResponse=True):
    """
    封裝的http請(qǐng)求方法，接受session url, param, method， verify, headers 發(fā)起http請(qǐng)求并返回接口返回的json
    :param session: requests session 對(duì)象
    :param url: 請(qǐng)求地址
    :param param: 請(qǐng)求參數(shù)
    :param parammode:請(qǐng)求參數(shù)傳入方式 data/json
    :param method: 請(qǐng)求方式 默認(rèn)post
    :param verify: ssl檢驗(yàn) 默認(rèn)關(guān)
    :param headers: http headers
    :param jsonResponse: 是否json格式response標(biāo)志
    :return: 接口返回內(nèi)容/None
    """
    logger.debug(f'開(kāi)始調(diào)用接口:{url},參數(shù)為:{param}')
    res = None
    returnJson = None
    if method == 'POST':
        logger.debug(f'請(qǐng)求方法為:%s'%method)
        if parammode=='data':
            logger.debug(f'請(qǐng)求參數(shù)類型為:%s' % parammode)
            res = session.post(url, data=param, verify=verify, headers=headers)
        elif parammode=='json':
            logger.debug(f'請(qǐng)求參數(shù)類型為:%s' % parammode)
            res = session.post(url, json=param, verify=verify, headers=headers)
    elif method == 'GET':
        logger.debug(f'請(qǐng)求方法為:%s' % method)
        res = session.get(url, params=param, verify=verify, headers=headers)
    try:
        if res.status_code == 200:
            logger.debug(f'接口響應(yīng)碼：200')
            if jsonResponse:
                try:
                    returnJson = res.json()
                except (TypeError, json.JSONDecodeError) as e:
                    logger.error(f'請(qǐng)求接口:{url}出錯(cuò),返回:{res.text}')
                    logger.error(e)
                    return {'fail':str(e)}
            else:
                try:
                    returnJson = {'returnText': res.text[:1000]}
                except Exception as e:
                    logger.error('請(qǐng)求接口出錯(cuò)')
                    logger.error(e)
                    return {}
        else:
            logger.error('請(qǐng)求接口失??！響應(yīng)碼非200！')
        logger.debug(f'接口調(diào)用完成 返回:{returnJson}')
        return returnJson
    except Exception as e:
        return {'fail':str(e)}

檢查點(diǎn)函數(shù)

"""
檢查中心
1、檢查結(jié)果是否包含于預(yù)期結(jié)果
2、結(jié)果的sql執(zhí)行結(jié)果是否等于預(yù)期
3、沒(méi)有檢查條件走默認(rèn)檢查
"""
from configureUtil.LogUtil import getlog
logger = getlog(targetName='CheckPoint')
from configureUtil.DataManangerl import DBmananger

#判斷dict1的key是否存在dict2中
def KeyExist(dict1,dict2):
    n = 0
    if dict1=={}:
        return False
    for key in dict1.keys():
        if key in dict2.keys():
            pass
            n=n+1
            if n==len(dict1):
                return True
        else:
            return False

#判斷dict2是否包含dict1
def Compare_ListDict(dick1, dick2):
    flag = False
    n = 0
    keyexist=KeyExist(dick1, dick2)
    if keyexist ==True:
        for i in dick1.keys():
            n=n+1
            if dick1[i] != dick2[i]:
                break
            elif n==len(dick1):
                flag = True
    elif dick1=={}:
        flag = True
    else:
        pass
    return flag

"""判斷sql結(jié)果是否為()"""

def JudgeSqlEmpty(sql,env):
    '''
    :param sql: 需要執(zhí)行sql
    :param env: 執(zhí)行環(huán)境
    :return: 執(zhí)行結(jié)果：true或者false
    '''
    try:
        result=DBmananger(env).callMysql(sql)
        logger.debug(result)
    except Exception as e:
        logger.error(e)
        result=()
    if result==():
        return False
    else:
        return True

數(shù)據(jù)處理工廠

# 預(yù)置條件、用例依賴處理中心：
# 1、用例依賴標(biāo)志
# 2、數(shù)據(jù)處理：支持sql增刪改查操作
from configureUtil.LogUtil import getlog
from Common.FunctionStart import MoveSpace
logger = getlog(targetName='Precondition')
def DataSplit(SplitSign=';',StringOne=''):

    if isinstance(StringOne,str):
        result=StringOne.split(SplitSign,-1)
    else:
        result=[]
    result=list(filter(None, result))#去掉列表空字符及None
    return result

def todict(func,SplitSign=';',StringOne=''):
    '''
       數(shù)據(jù)分離器
       :param StringOne: string類型入?yún)?       :param SplitSign: 分離標(biāo)志：如以冒號(hào)分離，則傳入":"
       :return: 分離list結(jié)果：{[{'SQL':[,]}],['$':[,]]}
       '''
    MoveSpace(StringOne)
    SplitSign = ';'
    SplitSign1 = (func(SplitSign,StringOne))
    # print(SplitSign1)
    dict = {}
    list1 = []
    list2 = []
    list3 = []
    # print(SplitSign1)
    import re
    # keys=re.findall(r'SQL:',StringOne,re.I)+re.findall(r'(\$[a-z]+):',StringOne,re.I)
    for i in SplitSign1:
        values = i.split(':')
        list1.append(MoveSpace(values[-1]))  # values
        list2.append(MoveSpace(values[0]))  # keys
        if MoveSpace(values[0]) in dict.keys():
            for i in dict[MoveSpace(values[0])]:
                list3.append(i)
            list3.append(MoveSpace(values[-1]))
            dict[MoveSpace(values[0])] = list3
        else:
            list4=[]
            list4.append(MoveSpace(values[-1]))
            dict[MoveSpace(values[0])] = list4
    return dict

發(fā)送郵件函數(shù)文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-695704.html

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
from email import encoders
from email.mime.base import MIMEBase
from email.utils import parseaddr, formataddr
from configureUtil.DataFile import mailenv

# 格式化郵件地址
def formatAddr(s):
    name, addr = parseaddr(s)
    return formataddr((Header(name, 'utf-8').encode(), addr))
def sendMail(body, attachment,title='接口自動(dòng)化測(cè)試報(bào)告'):
    # smtp_server = 'smtp.163.com'
    smtp_server = 'mail.suishouji.com'
    smtp_server = mailenv['smtp_server']
    from_mail = mailenv['from_mail']
    mail_pass =mailenv['smtp_server']
    mail_passwd=mailenv['mail_passwd']
    to_mail = mailenv['to_mail']
    cc_mail = mailenv['cc_mail']
    # 構(gòu)造一個(gè)MIMEMultipart對(duì)象代表郵件本身
    msg = MIMEMultipart()
    # msg = MIMEText('hello, send by Python...', 'plain', 'utf-8')
    # Header對(duì)中文進(jìn)行轉(zhuǎn)碼
    msg['From'] = formatAddr('<%s>' % (from_mail))
    msg['To'] = formatAddr('<%s>' % (to_mail))
    msg['Cc'] = formatAddr('<%s>' % (cc_mail))
    msg['Subject'] = Header('%s'% title).encode()
    # to_mail = to_mail.split(',')
    # cc_mail= cc_mail.split(',')
    to_mail.extend(cc_mail)
    # plain代表純文本
    msg.attach(MIMEText(body, 'plain', 'utf-8'))
    # 二進(jìn)制方式模式文件
    for i in range(len(attachment)):
        with open(attachment[i], 'rb') as f:
            # MIMEBase表示附件的對(duì)象
            mime = MIMEBase('text', 'txt', filename=attachment[i])
            # filename是顯示附件名字,加上必要的頭信息:
            mime.add_header('Content-Disposition', 'attachment', filename=attachment[i])
            mime.add_header('Content-ID', '<0>')
            mime.add_header('X-Attachment-Id', '0')
            # 獲取附件內(nèi)容
            mime.set_payload(f.read())
            # 用Base64編碼:
            encoders.encode_base64(mime)
            # 作為附件添加到郵件
            msg.attach(mime)
            # msg.attach(MIMEText(html, 'html', 'utf-8'))
    try:
        server = smtplib.SMTP(smtp_server, "25")
        server.set_debuglevel(1)
        server.login(from_mail, mail_passwd)
        server.sendmail(from_mail,to_mail,msg.as_string())  # as_string()把MIMEText對(duì)象變成str
        logger.info ("郵件發(fā)送成功！")
        server.quit()
    except smtplib.SMTPException as e:
        logger.error ("Error: %s" % e)

到了這里，關(guān)于接口自動(dòng)化測(cè)試系列-excel管理測(cè)試用例的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！