自动化测试实际是将测试人员手工进行的工作脚本化，这就需要在测试脚本运行完成后，展示一个明了的测试报告，供相关人员查看测试结果，判断系统的正确性，以下代码实现的是一个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
文章来源于领测软件测试网 https://www.ltesting.net/
