QTP测试框架之_报表

作者：Wally Yu (微博：http://weibo.com/quicktest)

自己在开发QTP测试框架的时候一些对于报表的经验： 

Excel报表：
下载Report Manager:http://www.advancedqtp.com/knowl ... ut-reportermanager/
修改其中的excel报表，封装成两个函数： 

Function initReport()
runtime = Date & "-"& Hour(Now) & Minute(Now)& Second(Now)
folderPath = "D:\QTP_Framework\report\"&runtime
call CreatFolderIfNotExist(folderPath)
call WriteFile_Append("D:\QTP_Framework\report\config.mse",folderPath)
End Function
Function ReportQTP(testCaseName, result, Comment)
fullPath = ReadLastLine("D:\QTP_Framework\report\config.mse")
ReportExcelFile = fullPath &"\report.xls"
CaptureFilePath = fullPath
Environment("TCase") = testCaseName
Call Report(result, comment, ReportExcelFile, CaptureFilePath)
End Function
'GetIP is the IP add of the execution machine
Public Function GetIP
ComputerName="."
Dim objWMIService,colItems,objItem,objAddress
Set objWMIService = GetObject("winmgmts:\\" & ComputerName & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * From Win32_NetworkAdapterConfiguration

Where IPEnabled = True")
For Each objItem in colItems
For Each objAddress in objItem.IPAddress
If objAddress <> "" then
GetIP = objAddress
Exit Function
End If
Next


 Next
End Function
'Report is the function for reporting
'sStatus is the status of execution, valid input are :FAIL?PASS, WARNING
'sDetails is the comment/notes for this execution
Public Function Report(sStatus,sDetails, ReportExcelFile, CaptureFilePath)
Dim fso
Dim oExcel
Dim ExcelFile
Dim TestcaseName
Dim objWorkBook
Dim objSheet
Dim NewTC
Dim Status
Dim temp
Dim PngPath
Set fso = CreateObject("scripting.FileSystemObject")
Set oExcel = CreateObject("Excel.Application")
Status=UCase(sStatus)
oExcel.Visible = false 'True
'Setting excel forms
If Not fso.FileExists(ReportExcelFile)Then
oExcel.Workbooks.Add
'Get the first sheet of excel
Set objSheet = oExcel.Sheets.Item(1)
oExcel.Sheets.Item(1).Select


 With objSheet
.Name = "Testing Result"
'testing results
.Columns("A:A").ColumnWidth = 5
.Columns("B:B").ColumnWidth = 35
.Columns("C:C").ColumnWidth = 12.5
.Columns("D:D").ColumnWidth = 60
.Columns("A:D").HorizontalAlignment = -4131
.Columns("A:D").WrapText = True
'set the font's type and size
.Range("A:D").Font.Name = "Arial"
.Range("A:D").Font.Size = 10
'
.Range("B1").Value = "Testing Results"
.Range("B1:C1").Merge
'set the style of the head of the file
.Range("B1:C1").Interior.ColorIndex = 53
.Range("B1:C1").Font.ColorIndex = 19
.Range("B1:C1").Font.Bold = True
'set the execution date
.Range("B3").Value = "Test Date:"
.Range("B4").Value = "Test Start Time:"
.Range("B5").Value = "Test End Time:"
.Range("B6").Value = "Test Duration: "
.Range("C3").Value = Date
.Range("C4").Value = Time
.Range("C5").Value = Time
.Range("C6").Value = "=R[-1]C-R[-2]C"
.Range("C6").NumberFormat = "[h]:mm:ss;@"
'Set the Borders for the Date & Time Cells
.Range("C3:C8").HorizontalAlignment = 4 'right align


 .Range("C3:C8").Font.Bold = True
.Range("C3:C8").Font.ColorIndex = 7
.Range("B3:C8").Borders(1).LineStyle = 1
.Range("B3:C8").Borders(2).LineStyle = 1
.Range("B3:C8").Borders(3).LineStyle = 1
.Range("B3:C8").Borders(4).LineStyle = 1
'Format the Date and Time Cells
.Range("B3:C8").Interior.ColorIndex = 40
.Range("B3:C8").Font.ColorIndex = 12
.Range("C3:C8").Font.ColorIndex = 7
.Range("B3:A8").Font.Bold = True
.Range("B7").Value = "No Of Testcases:"
.Range("C7").Value = "0"
.Range("B8").Value = "Testing Machine:"
.Range("C8").Value =GetIP()
.Range("B10").Value = "Test Case name"
.Range("C10").Value = "Testing results"
.Range("D10").Value = "Notes"
' set style for Result Summery
.Range("B10:D10").Interior.ColorIndex = 53
.Range("B10:D10").Font.ColorIndex = 19
.Range("B10:D10").Font.Bold = True
'set style for Result Summery
.Range("B10:D10").Borders(1).LineStyle = 1
.Range("B10:D10").Borders(2).LineStyle = 1
.Range("B10:D10").Borders(3).LineStyle = 1
.Range("B10:D10").Borders(4).LineStyle = 1
.Range("B10:D10").HorizontalAlignment = -4131
.Range("C11:C1000").HorizontalAlignment = -4131
.Columns("B:D").Select
' .Columns("B:D").Autofit
.Range("B11").Select


 End With
oExcel.ActiveWindow.FreezePanes = True
oExcel.ActiveWorkbook.SaveAs ReportExcelFile
oExcel.Quit
Set objSheet = Nothing
End If
TestcaseName = Environment("TCase")
Set objWorkBook = oExcel.Workbooks.Open(ReportExcelFile)
Set objSheet = oExcel.Sheets("Testing Result")
With objSheet
Environment.Value("Row") = .Range("C7").Value + 11
NewTC = False
If TestcaseName <> objSheet.Cells(Environment("Row")-1,2).value Then
.Cells(Environment("Row"),2).value = TestcaseName
.Cells(Environment("Row"), 3).Value = Status
.Cells(Environment("Row"), 4).value = sDetails
.Cells(Environment("Row"), 5).value = "click this link to see ScreenShot"
Select Case Status
Case "FAIL"
.Range("C" & Environment("Row")).Font.ColorIndex = 3
temp = Date & "-"& Hour(Now) & Minute(Now)& Second(Now)
PngPath = CaptureFilePath & "\" & temp & ".png"
.Hyperlinks.Add objSheet.Cells(Environment("Row"), 5), PngPath, "",""
Call Capture(temp, CaptureFilePath)
Case "PASS"
.Range("C" & Environment("Row")).Font.ColorIndex = 50



 temp = Date & "-"& Hour(Now) & Minute(Now)& Second(Now)
PngPath = CaptureFilePath & "\" & temp & ".png"
.Hyperlinks.Add objSheet.Cells(Environment("Row"), 5), PngPath, "",""
Call Capture(temp, CaptureFilePath)
Case "WARNING"
.Range("C" & Environment("Row")).Font.ColorIndex = 5
temp = Date & "-"& Hour(Now) & Minute(Now)& Second(Now)
PngPath = CaptureFilePath & "\" & temp & ".png"
.Hyperlinks.Add objSheet.Cells(Environment("Row"), 5), PngPath, "",""
Call Capture(temp, CaptureFilePath)
End Select
NewTC = True
.Range("C7").Value = .Range("C7").Value + 1
'set board
.Range("B" & Environment("Row") & ":D" & Environment("Row")).Borders(1).LineStyle =
1
.Range("B" & Environment("Row") & ":D" & Environment("Row")).Borders(2).LineStyle =
1
.Range("B" & Environment("Row") & ":D" & Environment("Row")).Borders(3).LineStyle =
1
.Range("B" & Environment("Row") & ":D" & Environment("Row")).Borders(4).LineStyle =
1
'set font type and color
.Range("B" & Environment("Row") & ":D" & Environment("Row")).Interior.ColorIndex =
19
.Range("B" & Environment("Row")).Font.ColorIndex = 53
.Range("D" & Environment("Row")).Font.ColorIndex = 41
.Range("B" & Environment("Row") & ":D" & Environment("Row")).Font.Bold = True



 End If
If (Not NewTC) And (Status = "FAIL") Then
.Cells(Environment("Row"), 3).Value = "Fail"
.Range("C" & Environment("Row")).Font.ColorIndex = 3
end If
'update end time
.Range("C5").Value = Time
.Columns("B:D").Select
'.Columns("B:D").Autofit
End With
oExcel.ActiveWindow.FreezePanes = True
'save result
objWorkBook.Save
oExcel.Quit
Set objSheet = Nothing
Set objWorkBook = Nothing
Set oExcel = Nothing
Set fso = Nothing
End Function
Public Function Capture(fileNo, CaptureFilePath)
Dim datestamp
Dim filename
datestamp = Now()
filename = fileNo & ".png"
'filename = Replace(filename,"/","")
'filename = Replace(filename,":","")
filename = CaptureFilePath + "\" + ""&filename
Desktop.CaptureBitmap filename
'Reporter.ReportEvent micFail,"image","<img src='" & filename & "'>"


End Function
'Append to txt file
Public Function WriteFile_Append(pathway,words)
Dim fileSystemObj,fileSpec,logFile,way
Set fileSystemObj = CreateObject("Scripting.FileSystemObject")
fileSpec = pathway
Set logFile = fileSystemObj.OpenTextFile(fileSpec, 8, true)
logFile.WriteLine (CStr(words))
logFile.Close
Set logFile = Nothing
End Function
'Read last line of txt file
Function ReadLastLine(pathway)
Dim fso,myfile
Set fso=CreateObject("scripting.FileSystemObject")
Set myfile = fso.openTextFile(pathway,1,false)
While Not myfile.AtEndOfLine
temp = myfile.ReadLine
Wend
ReadLastLine = temp
End Function
Function CreatFolderIfNotExist(fldr)
Dim fso, msg
Set fso = CreateObject("Scripting.FileSystemObject")
If Not (fso.FolderExists(fldr)) Then
Set f = fso.CreateFolder(fldr)
End If
End Function



HTML报表：
改写印度人写的报表：http://testingfaqs.co.in/QTPHTMLReport.html
通常HTML报表更实用，根据公司的需要改写下面的报表是个很好的选择

' Variables
Dim fso, ts
dim intCnt
Const ForWriting = 2
dim intPass,intFail
dim StartTime
dim stTime
dim enTime
dim objIE
dim strFileURL
'Implementation
OpenFile "C:\Test.html"
AddNewCase 1,"X","X","X","Pass"
AddNewCase 2,"X","X","X","Fail"
AddNewCase 3,"X","X","X","Pass"
CloseFile
' Function To Open the HTML file
Function OpenFile(strFileName)
StartTime = Timer
stTime = Time
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile(strFileName, ForWriting, True)
strFileURL = strFileName
CreateHeader
End Function
' Function To Create Header



Function CreateHeader()
ts.writeline("<html>")
ts.writeline("<title>Test Results</title>")
ts.WriteLine("<head></head>")
ts.WriteLine("<body>")
ts.WriteLine("<font face='Tahoma'size='2'>")
ts.WriteLine("<h1>Test Results</h1>")
ts.writeline("<table border='0' width='100%' height='47'>")
ts.writeline("<tr>")
ts.writeline("<td width='13%' bgcolor='#CCCCFF' align='center'><b><font color='#000000'
face='Tahoma' size='2'>TestCaseID</font></b></td>")
ts.writeline("<td width='24%' bgcolor='#CCCCFF'><b><font color='#000000' face='Tahoma'
size='2'>Objective</font></b></td>")
ts.writeline("<td width='23%' bgcolor='#CCCCFF'><b><font color='#000000' face='Tahoma'
size='2'>Expected Result</font></b></td>")
ts.writeline("<td width='22%' bgcolor='#CCCCFF'><b><font color='#000000' face='Tahoma'
size='2'>Actual Result</font></b></td>")
ts.writeline("<td width='18%' bgcolor='#CCCCFF' align='center'><b><font color='#000000'
face='Tahoma' size='2'>Pass/Fail</font></b></td>")
ts.writeline("</tr>")
End Function
' Function To Add New Case
Function AddNewCase(strTCID,strObjective,strExpectedResult,strActualResult,strPassFail)
ts.writeline("<tr>")
ts.writeline("<td width='13%' bgcolor='#FFFFDC' valign='middle' align='center'>" & strTCID &
"</td>")
ts.writeline("<td width='24%' bgcolor='#FFFFDC' valign='top' align='justify'>" & strObjective &
"</td>")
ts.writeline("<td width='23%' bgcolor='#FFFFDC' valign='top' align='justify'>" & strExpectedResult &
"</td>")
ts.writeline("<td width='22%' bgcolor='#FFFFDC' valign='top' align='justify'>" & strActualResult &
"</td>")
if strPassFail = "Pass" then


ts.writeline("<td width='18%' bgcolor='#FFFFDC' valign='middle' align='center'><b><font
color='Green' face='Tahoma' size='2'>" & strPassFail & "</font></b></td>")
intPass = intPass + 1
else
ts.writeline("<td width='18%' bgcolor='#FFFFDC' valign='middle' align='center'><b><font color='Red'
face='Tahoma' size='2'>" & strPassFail & "</font></b></td>")
intFail = intFail + 1
end if
ts.writeline("</tr>")
End Function
' Function To Footer Info
Function Footer()
Dim EndTime
EndTime = Timer
enTime = Time
ts.writeline("</table>")
ts.writeline("<hr>")
ts.writeline("<table border='0' width='50%'>")
ts.writeline("<tr><td width='100%' colspan='2' bgcolor='#000000'><b><font face='Tahoma' size='2'
color='#FFFFFF'>Summary</font></b></td></tr>")
ts.writeline("<tr><td width='45%' bgcolor='#E8FFE8'><b><font face='Tahoma' size='2'>Total Tests
Passed</font></b></td><td width='55%' bgcolor='#E8FFE8'>" & intPass & "</td></tr>")
ts.writeline("<tr><td width='45%' bgcolor='#FFE6FF'><b><font face='Tahoma' size='2'>Total Tests
Failed</font></b></td><td width='55%' bgcolor='#FFE6FF'>" & intFail & "</td></tr>")
ts.writeline("<tr><td width='45%' bgcolor='#FFFFDC'><b><font face='Tahoma' size='2'>Executed
On</font></b></td><td width='55%' bgcolor= '#FFFFDC'>" & Date & "</td></tr>")
ts.writeline("<tr><td width='45%' bgcolor='#FFFFDC'><b><font face='Tahoma' size='2'>Start
Time</font></b></td><td width='55%' bgcolor= '#FFFFDC'>" & stTime & "</td></tr>")
ts.writeline("<tr><td width='45%' bgcolor='#FFFFDC'><b><font face='Tahoma' size='2'>End
Time</font></b></td><td width='55%' bgcolor= '#FFFFDC'>" & enTime & "</td></tr>")
ts.writeline("<tr><td width='45%' bgcolor='#FFFFDC'><b><font face='Tahoma' size='2'>Execution
Time</font></b></td><td width='55%' bgcolor= '#FFFFDC'>" & CDbl(EndTime - StartTime) &
"</td></tr>")


ts.writeline("</table>")
ts.writeline("</font>")
ts.WriteLine("</body>")
ts.WriteLine("</html>")
End Function
' Function To Close File
Function CloseFile()
Footer
ts.close()
Set objIE = CreateObject("InternetExplorer.Application")
objIE.visible = True
objIE.Navigate strFileURL
End Function