QTP的那些事--报表自定义(excel,html,xml或者是其他格式的)

原文章地址:

http://www.sqaforums.com/showthreaded.php?Number=567717

设计思路如下:

here comes the excel report, I made the modification from report manager and wrapped into 2 main functions to be called in QTP.
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

转载于:https://www.cnblogs.com/alterhu/archive/2012/01/10/2318557.html

评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值