自動化測試實際是將測試人員手工進行的工作腳本化，這就需要在測試腳本運行完成后，展示一個明了的測試報告，供相關人員查看測試結果，判斷系統的正確性，以下代碼實現的是一個EXCEL格式的測試報告，以Function為單位記錄腳本的執行情況，若完成則顯示Complete，在Comment中顯示驗證點檢查結果情況，以此來判斷系統運行的正確行；若由于一些突發的異常情況導致腳本未執行完成，則顯示Fail，在Comment中顯示Err.Descrīption信息
當然，要達到以上效果，要求在業務腳本的檢查點按照指定的格式書寫，我的做法是設置一個全局變量記錄所有檢查點信息，然后傳入給日志函數
以下是代碼，希望對有需要的朋友有所幫助：）
Function ExcelReport(ReportExcelFile,sStatus,sDetails)
'定義變量
Dim fso
Dim oExcel
Dim ExcelFile
Dim TestcaseName
Dim objWorkBook
Dim objSheet
' Dim Environment("Row"), Environment("TCRow"), NewTC
Set fso = CreateObject("scrīpting.FileSystemObject")
Set ōExcel = CreateObject("Excel.Application")
' oExcel.Visible = True
'設置Excel報告樣式
If Not fso.FileExists(ReportExcelFile) Then
oExcel.Workbooks.Add
'獲取工作簿的第一個Sheet頁
Set ōbjSheet = oExcel.Sheets.Item(1)
oExcel.Sheets.Item(1).Select
With objSheet
'更改sheet名
.Name = "Test_Summary"
'設置列寬
.Columns("A:A").ColumnWidth = 5
.Columns("B:B").ColumnWidth = 35
.Columns("C:C").ColumnWidth = 10
.Columns("D:D").ColumnWidth = 60
.Columns("A:D").HorizontalAlignment = -4131
.Columns("A:D").WrapText = True
'設置顯示區域的字體類型和大小
.Range("A:D").Font.Name = "Arial"
.Range("A:D").Font.Size = 10
'設置文件頭格式
.Range("B1").Value = "Test Result"
.Range("B1:C1").Merge
'設置文件頭格式字體和顏色
.Range("B1:C1").Interior.ColorIndex = 53
.Range("B1:C1").Font.ColorIndex = 19
.Range("B1:C1").Font.Bold = True
'設置執行的日期和時間
.Range("B3").Value = "Test Data:"
.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;@"
'設置日期和時間cell的邊界
.Range("C3:C8").HorizontalAlignment = 4 '右邊對齊
' .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
'設置日期和時間Cell的樣式
.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 Function:"
.Range("C7").Value = "0"
.Range("B8").Value = "Test Machine"
.Range("C8").Value = GetIP()
.Range("B10").Value = "TestCase"
.Range("C10").Value = "Finish"
.Range("D10").Value = "Comment"
'為Result Summery設置格式
.Range("B10:D10").Interior.ColorIndex = 53
.Range("B10:D10").Font.ColorIndex = 19
.Range("B10:D10").Font.Bold = True
'為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 = 3
.Range("C11:C1000").HorizontalAlignment = 3
.Columns("B:D").Select
' .Columns("B:D").Autofit
.Range("B11").Select
End With
oExcel.ActiveWindow.FreezePanes = True
oExcel.ActiveWorkbook.SaveAs ReportExcelFile
oExcel.Quit
Set ōbjSheet = Nothing
End If
TestcaseName = Environment("TCase")
' MsgBox TestcaseName
Set ōbjWorkBook = oExcel.Workbooks.Open(ReportExcelFile)
Set ōbjSheet = oExcel.Sheets("Test_Summary")
With objSheet
'設置行數和是否NewTc標識
Environment("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 = sStatus
.Cells(Environment("Row"), 4).value = sDetails
Select Case sStatus
Case "Fail"
.Range("C" & Environment("Row")).Font.ColorIndex = 3
Case "Complete"
.Range("C" & Environment("Row")).Font.ColorIndex = 50
End Select
'NewTC = True
.Range("C7").Value = .Range("C7").Value + 1
'設置邊界
.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
'設置字體和顏色?
.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 (sStatus = "Fail") Then
.Cells(Environment("Row"), 3).Value = "Fail"
.Range("C" & Environment("Row")).Font.ColorIndex = 3
End If
'更新結束時間
.Range("C5").Value = Time
.Columns("B:D").Select
' .Columns("B:D").Autofit
End With
oExcel.ActiveWindow.FreezePanes = True
'保存結果
objWorkBook.Save
oExcel.Quit
Set ōbjSheet = Nothing
Set ōbjWorkBook = Nothing
Set ōExcel = Nothing
Set fso = Nothing
End Function
文章來源于領測軟件測試網 http://www.kjueaiud.com/
