QTP：testcase和testcase的操作函数及模板

Dim excelApp
Dim excelWorkBook
Dim excelSheet
Dim fso 'fileSystemObject
backslant = "\"


FXPGTestCasePath = "D:\ERM\TestData\FXBS"
FXPGTestResultPath = "D:\ERM\Report\FXBS\FXPG"


FXBSTestCasefileName = "*****TestCase.xls"
FXBSTestDatafileName = "*****TestData.xls"


Function createExcelFile()
    set excelApp = CreateObject("Excel.Application")
    excelApp.Workbooks.Add
    excelApp.Visible = true
        
End Function

Function openExcell(filepath,fileName)
   set excelApp = CreateObject("Excel.Application")
   Set fso = CreateObject("Scripting.FileSystemObject")

    If not fso.FileExists(filepath+backslant+fileName) Then
        createExcelFile
        saveExcelFile excelApp, filepath,fileName
        else
        excelApp.Workbooks.Open(filepath+backslant+fileName)
        excelApp.Visible = true
    end if
    Set excelWorkBook = excelApp.ActiveWorkbook
    Set excelSheet = excelApp.ActiveSheet
   
End Function

'获取testcase的值
Function writeActualResult(excelSheet,row, column, result)
   Set excelSheet = excelApp.ActiveSheet
   excelSheet.Cells(row, column) = result
End Function


Function closeExcel( filepathx,fileNamex)
       Set excelWorkBook = excelApp.ActiveWorkbook
    Set excelSheet = excelApp.ActiveSheet
       
    If not fso.FolderExists(filepathx) Then
        fso.CreateFolder(filepathx)
    End If

    If fso.FileExists(filepathx+backslant+fileNamex) Then
        If excelWorkBook.FullName = (filepathx+backslant+fileNamex) Then
            excelWorkBook.Save
        else           
            'fso.DeleteFile(filepath+backslant+fileName)               
            excelWorkBook.SaveAs filepathx+backslant+fileNamex
        end if
    End If

    excelWorkBook.Close   
    excelApp.Quit
    Set excelApp = nothing
    set fso = nothing
End Function

Function saveExcelFile( filepath,fileName)
    Set excelWorkBook = excelApp.ActiveWorkbook
    Set excelSheet = excelApp.ActiveSheet
   
    If excelWorkBook.FullName = (filepath+backslant+fileName) Then
        excelWorkBook.Save
        else
            Set fso = createObject("scripting.FileSystemObject")
   
            If not fso.FolderExists(filepath) Then
                fso.CreateFolder(filepath)
            End If
       
            If fso.FileExists(filepath+backslant+fileName) Then
                fso.DeleteFile(filepath+backslant+fileName)   
            End If       
            excelWorkBook.SaveAs filepath+backslant+fileName
           
    End If
End Function

Function exportFile(currentName, savefilename)

   If fso.FileExists(savefilename) Then
       fso.DeleteFile savefilename
   End If

   excelApp.Workbooks(currentName).SaveAs savefilename
End Function
' for test case
Dim caseIDColumn, testSummaryColumn, teststepColumn,exceptresultColumn, actualresultColumn, noteColumn
caseIDColumn = 1
testSummaryColumn = 2
teststepColumn = 3
exceptresultColumn = 4
actualresultColumn = 5
noteColumn = 6

Function testSummary(sheetName,caseID)
   rows = excelApp.Worksheets(sheetName).UsedRange.rows.Count
   For i =1 to rows
       tempValue = excelApp.Sheets(sheetName).cells(i,caseIDColumn)
       If strcomp(trim(tempValue), trim(caseID)) =0 Then
           testSummary = excelApp.Sheets(sheetName).cells(i,testSummaryColumn)
           Exit for
       End If
   Next  
   If len(testSummary) = 0 Then
       reporter.ReportEvent micDone, "case","the case:"+caseID+"不存在"
   End If
End Function


Function teststep(sheetName,caseID, stepNo)
   rows = excelApp.Worksheets(sheetName).UsedRange.rows.Count

   For i =1 to rows
       tempValue = excelApp.Sheets(sheetName).cells(i,caseIDColumn)
       If strcomp(trim(tempValue), trim(caseID)) =0 Then
           teststep = excelApp.Sheets(sheetName).cells((i+stepNo-1),teststepColumn)
           Exit for
       End If
   Next  
End Function


Function exceptresult(sheetName,caseID, stepNo)
   rows = excelApp.Worksheets(sheetName).UsedRange.rows.Count

   For i =1 to rows
       tempValue = excelApp.Sheets(sheetName).cells(i,caseIDColumn)
       If strcomp(trim(tempValue), trim(caseID)) =0 Then
           exceptresult = excelApp.Sheets(sheetName).cells((i+stepNo-1), exceptresultColumn)
           Exit for
       End If
   Next  
End Function

Function actualresult(sheetName,caseID, stepNo, result)
   rows = excelApp.Worksheets(sheetName).UsedRange.rows.Count

   For i =1 to rows
       tempValue = excelApp.Sheets(sheetName).cells(i,caseIDColumn)
       If strcomp(trim(tempValue), trim(caseID)) =0 Then
           excelApp.Sheets(sheetName).cells((i+stepNo-1), actualresultColumn) = result
           Exit for
       End If
   Next  
End Function

Function note(sheetName,caseID, stepNo)
   rows = excelApp.Worksheets(sheetName).UsedRange.rows.Count

   For i =1 to rows
       tempValue = excelApp.Sheets(sheetName).cells(i,caseIDColumn)
       If strcomp(trim(tempValue), trim(caseID)) =0 Then
           note = excelApp.Sheets(sheetName).cells((i+stepNo-1), noteColumn)
           Exit for
       End If
   Next 

End Function

'获取testdata的值
caseIDColumn = 1
variableColumn = 3
variableVauleColumn = 4

Function getVariableValue(sheetName,startCaseID, endCaseID,variableName)
   Set excelSheet=excelApp.ActiveSheet
   rows = excelSheet.UsedRange.rows.Count

   For i =1 to rows
       tempValue = excelApp.Sheets(sheetName).cells(i,caseIDColumn)
       If strcomp(trim(tempValue), trim(startCaseID)) =0 Then
           startLine = i          
       End If

           tempValue = excelApp.Sheets(sheetName).cells(i,caseIDColumn)
       If strcomp(trim(tempValue), trim(endCaseID)) =0 Then
           endLine = i       
           Exit for
       End If
   Next 

   If startLine>= endLine Then
       Exit function
   End If

   For i = startLine to endLine
           tempValue = excelApp.Sheets(sheetName).cells(i,variableColumn)
        If strcomp(trim(tempValue), trim(variableName)) =0 Then
            getVariableValue = excelApp.Sheets(sheetName).cells(i,variableVauleColumn)
            Exit for
        End If
   Next
End Function



' 实例
sheetName = "testCaseData"
startCaseID = "用例_001"
endCaseID = "用例_002"

openExcell FXPGTestCasePath, FXPGTestDatafileName

url = getVariableValue(sheetName,startCaseID, endCaseID,"url")
usename = getVariableValue(sheetName,startCaseID, endCaseID,"usename")
password = getVariableValue(sheetName,startCaseID, endCaseID,"password")

closeExcel FXPGTestCasePath, FXPGTestDatafileName

sheetName = "用例"
openExcell FXPGTestCasePath, FXPGTestCasefileName

Reporter.ReportEvent micDonel, "用例概要", testSummary(sheetName,startCaseID)

'' 1. 登陆系统；
tempResult = login (url, usename,password)
step = teststep(sheetName,startCaseID, 1)
expectResult = exceptresult(sheetName,startCaseID, 1)
If tempResult Then
    Reporter.ReportEvent micPass, step, expectResult
    actualresult sheetName,startCaseID, 1, "pass"
    else
    Reporter.ReportEvent micFail, step, "失败." + expectResult
    actualresult sheetName,startCaseID, 1, "failed"
    exitAction()
End If

closeExcel FXPGTestCasePath, FXPGTestCasefileName

logoff