QTP实例-测试报告代码

上一篇 / 下一篇 2011-09-15 14:28:03 / 个人分类：QTP积累学习

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