QTP实例-测试报告代码

v I7D G%h0z2K{0转载处：http://www.51testing.com/?16178751Testing软件测试网 yk$w,Kt%O n
   自动化测试实际是将测试人员手工进行的工作脚本化，这就需要在测试脚本运行完成后，展示一个明了的测试报告，供相关人员查看测试结果，判断系统的正确性，以下代码实现的是一个EXCEL格式的测试报告，以Function为单位记录脚本的执行情况，若完成则显示Complete，在Comment中显示验证点检查结果情况，以此来判断系统运行的正确行；若由于一些突发的异常情况导致脚本未执行完成，则显示Fail，在Comment中显示Err.Descrīption信息
A7b@W'nFl+l0   当然，要达到以上效果，要求在业务脚本的检查点按照指定的格式书写，我的做法是设置一个全局变量记录所有检查点信息，然后传入给日志函数以下是代码，希望对有需要的朋友有所帮助：）51Testing软件测试网lGB9y @7`-S
Function ExcelReport(ReportExcelFile,sStatus,sDetails)

jeW-t] sXC0  '定义变量
g&]g\a_0  Dim fso51Testing软件测试网b+m(se$^PyP
  Dim oExcel
^m }5e
zwj0  Dim ExcelFile
4\kk|H*`2Q _0  Dim TestcaseName
3`/}R4J/L*U3T0  Dim objWorkBook

~fY6Oj!sO t#B0  Dim objSheet51Testing软件测试网;FcaX#sB
'  Dim Environment("Row"), Environment("TCRow"), NewTC51Testing软件测试网%G%V!X.\g'\%D4|}'N
 
^ d.zD"q a0  Set fso = CreateObject("scrīpting.FileSystemObject")
S!N(QSoBy0  Set ōExcel = CreateObject("Excel.Application")51Testing软件测试网(L(t'_)]!L&t@&]Y
' oExcel.Visible = True51Testing软件测试网#Ns$o] g+{]3y*~_
 
+JEPm[Y's2B0  '设置Excel报告样式
;c@N
Phm&|E$S0  If  Not fso.FileExists(ReportExcelFile) Then
U$`WFp(U:[0   oExcel.Workbooks.Add
#q#V5w)p${
b0  51Testing软件测试网0]7i1WI1sx2j0rL
   '获取工作簿的第一个Sheet页51Testing软件测试网
@^8P;|:NBFof
   Set ōbjSheet = oExcel.Sheets.Item(1)
*\!B8i:?f8A8u}3E0   oExcel.Sheets.Item(1).Select51Testing软件测试网4v0adv_0k5ohe1D}
  51Testing软件测试网"`8s9iX nGd\ Ww$O
   With objSheet
e{$X(c8g}J:ja0    '更改sheet名51Testing软件测试网$eyN$^N4N)U)Nu
    .Name = "Test_Summary"
9iV6rJ3t.I6y h4gc0   51Testing软件测试网};Zm z6Ig
    '设置列宽
*wmwMv"aI;x(t0    .Columns("A:A").ColumnWidth = 5
*R"j%Kvt(^Z0    .Columns("B:B").ColumnWidth = 3551Testing软件测试网p*a8FF;z
    .Columns("C:C").ColumnWidth = 10
0_b}GAf0    .Columns("D:D").ColumnWidth = 60
wFm5D:a6T
by,xO0    .Columns("A:D").HorizontalAlignment = -413151Testing软件测试网:h8cbzyP
    .Columns("A:D").WrapText = True51Testing软件测试网@bP.pm fY
   51Testing软件测试网EiO?r"A@A
    '设置显示区域的字体类型和大小
;Fy;{A(k0    .Range("A:D").Font.Name = "Arial"
1ez
|2D'F0    .Range("A:D").Font.Size = 10
*m%]j+T[SDt'F0   
"]L(|&PF*[5y4p0    '设置文件头格式51Testing软件测试网r9iv u1z
    .Range("B1").Value = "Test Result"51Testing软件测试网h dWt$g{9k.D
    .Range("B1:C1").Merge
,pY/O3tD&A/PM0    
$j:\+v2WW$Z'e0s~)C+z0    '设置文件头格式字体和颜色51Testing软件测试网B$T AP
xbj LvuN
    .Range("B1:C1").Interior.ColorIndex = 5351Testing软件测试网8K6yR\%DjR
    .Range("B1:C1").Font.ColorIndex = 1951Testing软件测试网*jL.~$fZ8]
    .Range("B1:C1").Font.Bold = True
-F8~?I#]*J Ji0   
_`#j`o)F3e0    '设置执行的日期和时间51Testing软件测试网1A:s5az(t,D&E4N
    .Range("B3").Value = "Test Data:"
ZF8t8t @d,B2H1u0    .Range("B4").Value = "Test Start Time:"
P e9y!DX!R'N0    .Range("B5").Value = "Test End Time:"
2~ko*N_]0    .Range("B6").Value = "Test Duration: "   
6f
_E'c7Bz%{0    .Range("C3").Value = Date51Testing软件测试网:\H"a I7L~C_
    .Range("C4").Value = Time

n,C%j$u)@&h0    .Range("C5").Value = Time
P]#Y_2i6n#c8T0    .Range("C6").Value = "=R[-1]C-R[-2]C"
R(FYfc0    .Range("C6").NumberFormat = "[h]:mm:ss;@"

Xek-[5l0   51Testing软件测试网K9[&dE\(R~
    '设置日期和时间cell的边界
OI$td%A
K \0X qG)e0    .Range("C3:C8").HorizontalAlignment = 4 '右边对齐51Testing软件测试网P5fUL6C&I$R n
'     .Range("C3:C8").Font.Bold = True51Testing软件测试网 bg*lj9Xe"J
'     .Range("C3:C8").Font.ColorIndex = 7
W:G4HB9HdicEj A0    .Range("B3:C8").Borders(1).LineStyle = 1
3Yo
r]8Y"u0    .Range("B3:C8").Borders(2).LineStyle = 1
lk0X3r9q*w2z$bu"Z4pY0    .Range("B3:C8").Borders(3).LineStyle = 151Testing软件测试网_4nz'f7e8sR[
    .Range("B3:C8").Borders(4).LineStyle = 151Testing软件测试网0E'eD%M-boY'b^"kJ/F
   51Testing软件测试网{m+`ZOL4g
    '设置日期和时间Cell的样式51Testing软件测试网3D4G2J$s!p_Y5D
    .Range("B3:C8").Interior.ColorIndex = 4051Testing软件测试网1DV:|%w | @7S6^8N!a
    .Range("B3:C8").Font.ColorIndex = 12
z @)hYV0vG)l0    .Range("C3:C8").Font.ColorIndex = 751Testing软件测试网xJ {o'\
    .Range("B3:A8").Font.Bold = True
*]xG(o yr!a8^0   51Testing软件测试网:\AZ,H nK q3@0{
    .Range("B7").Value = "No Of Function:"51Testing软件测试网!bZ k}_"qiu l@J1t
    .Range("C7").Value = "0"51Testing软件测试网-j0b7_$u sU_K` T
    .Range("B8").Value = "Test Machine"
2C dxe]7UZ L0    .Range("C8").Value = GetIP()51Testing软件测试网 {7`^}c:j
    .Range("B10").Value = "TestCase"
'C,e9t:tk.F0    .Range("C10").Value = "Finish"
3DFQ,k.H WA/w0    .Range("D10").Value = "Comment"
LOU$u`"~0   51Testing软件测试网 F? J`[3l
u
    '为Result Summery设置格式51Testing软件测试网.ttS
l;w)R1v!fGQ
    .Range("B10:D10").Interior.ColorIndex = 5351Testing软件测试网8fQR6ul,a-a
    .Range("B10:D10").Font.ColorIndex = 19
*UX%wMd0    .Range("B10:D10").Font.Bold = True
*HY2P5kV!\`V0 
t)K?!uX7Z0    '为Result Summery设置边界
;i5TZ.u-D#H0    .Range("B10:D10").Borders(1).LineStyle = 151Testing软件测试网+FUfW
j%ar9FS"p
    .Range("B10:D10").Borders(2).LineStyle = 1
0U1A;pZd7P-i0    .Range("B10:D10").Borders(3).LineStyle = 1
1y5]alg U @;b0    .Range("B10:D10").Borders(4).LineStyle = 151Testing软件测试网"YyR+dXW+a
    .Range("B10:D10").HorizontalAlignment = 351Testing软件测试网}Y ^9?lp%N
    .Range("C11:C1000").HorizontalAlignment = 3
9W*i`1D(@0 
'Dh/Kx3Nt)M0    .Columns("B:D").Select
-t%V~ tW0'     .Columns("B:D").Autofit51Testing软件测试网2c1[)^Bwj ~7x
    .Range("B11").Select 
@/yD H RIi q y[0   End With51Testing软件测试网B1x:Uxz8q'[@
   oExcel.ActiveWindow.FreezePanes = True
4~xZ'c$FR(@0   oExcel.ActiveWorkbook.SaveAs ReportExcelFile51Testing软件测试网$l/R'`#ebt5t
   oExcel.Quit51Testing软件测试网,oM;|,t3cZ?
  51Testing软件测试网 ^$@G-lJiI[$Oc7op
   Set ōbjSheet = Nothing51Testing软件测试网4zJM$u7b,d/M AJ%n
  End If51Testing软件测试网]-\+hto!yf8w
 51Testing软件测试网'A@LHP.xlG
  TestcaseName = Environment("TCase")51Testing软件测试网^H,Str@u
'   MsgBox TestcaseName51Testing软件测试网o1g&@FV'jI'{
  Set ōbjWorkBook = oExcel.Workbooks.Open(ReportExcelFile)
9K*]2J5Hi5` VQ'|r0  Set ōbjSheet = oExcel.Sheets("Test_Summary")
qw(K|C'_u0 51Testing软件测试网qzdI0L
  With objSheet51Testing软件测试网{k? w A3bk
   '设置行数和是否NewTc标识
{]`)w"N:LO,z0   Environment("Row") = .Range("C7").Value + 11
At
~3V,tU5m?Ll0   'NewTC = False
!T%{ Q1n|gHxTk0  51Testing软件测试网$@I A@8};teRF
   If TestcaseName <> objSheet.Cells(Environment("Row")-1,2).value Then51Testing软件测试网 V ]8P"rBP)V4{
    .Cells(Environment("Row"),2).value = TestcaseName
x+D3xn.i@p0    .Cells(Environment("Row"), 3).Value = sStatus
O n6a&N$EA5i0    .Cells(Environment("Row"), 4).value = sDetails51Testing软件测试网
h(uBa2x^smo8\
   
&Y#i1B\JKwP&~0    Select Case sStatus51Testing软件测试网7K2[v5rpC+z
     Case "Fail"
&t)K?N^0      .Range("C" & Environment("Row")).Font.ColorIndex = 351Testing软件测试网1z Mv$U/[/U$G e
     Case "Complete"51Testing软件测试网cvZI@"ps
      .Range("C" & Environment("Row")).Font.ColorIndex = 50
*BF(w$wq{1?0    End Select
?2k5RAm }W9j0   51Testing软件测试网Q"e6|'~
r^
    'NewTC = True
6t7a(N`+Rt0    .Range("C7").Value = .Range("C7").Value + 1
aD5? A H*j0]:T#A0    '设置边界51Testing软件测试网P^S0bv:^3P+]C
    .Range("B" & Environment("Row") & ":D" & Environment("Row")).Borders(1).LineStyle = 1
x~;UkeW'xya"U5?B0    .Range("B" & Environment("Row") & ":D" & Environment("Row")).Borders(2).LineStyle = 151Testing软件测试网v^wNp7j l
    .Range("B" & Environment("Row") & ":D" & Environment("Row")).Borders(3).LineStyle = 1
h*O$T#N)t0p+N%Y0dy0    .Range("B" & Environment("Row") & ":D" & Environment("Row")).Borders(4).LineStyle = 1
6I7B
iD:Cv0    '设置字体和颜色?
V?LD8f+b0    .Range("B" & Environment("Row") & ":D" & Environment("Row")).Interior.ColorIndex = 1951Testing软件测试网+k#g1g(m)o D~#c3W j
    .Range("B" & Environment("Row")).Font.ColorIndex = 5351Testing软件测试网7M'i0u$rWuX
    .Range("D" & Environment("Row")).Font.ColorIndex = 4151Testing软件测试网4{]!D4L^ p
    .Range("B" & Environment("Row") & ":D" & Environment("Row")).Font.Bold = True

X!n'Z!c C&qAm p0   End If
%Eg*Sf xq[i0  
(Fq&c;f+hdKk/g0   If (Not NewTC) And (sStatus = "Fail") Then
.p ~[EM0    .Cells(Environment("Row"), 3).Value = "Fail"
8P#]S5h,l0    .Range("C" & Environment("Row")).Font.ColorIndex = 3
!`r1K/u%S:Y&K.~0   End If
/f&N-^!v7e~-e7j'_0  51Testing软件测试网Gg8QI;q-dFnP
   '更新结束时间
`N|@ej,?-y0   .Range("C5").Value = Time
t`O9Ar-` ^;T0  51Testing软件测试网7V&H`-~/kz
   .Columns("B:D").Select
!wm1kR9Q*A6l-`h0'    .Columns("B:D").Autofit51Testing软件测试网2FmRJ'P'h!A8g]
  End With51Testing软件测试网ZU'{/}7my0g
  oExcel.ActiveWindow.FreezePanes = True51Testing软件测试网p+Slx6z[0{xV6F
 51Testing软件测试网'`|JYP+l@B k
  '保存结果51Testing软件测试网:A2[hm%DAC)Uk
  objWorkBook.Save
R$Wl:On0~9{E\UB0  oExcel.Quit51Testing软件测试网6DR|+z#W,A
 
H;` Iz`w$]H0  Set ōbjSheet = Nothing51Testing软件测试网O Ua`8X2c6yi
  Set ōbjWorkBook = Nothing51Testing软件测试网C
WIN.cl$~b Y
  Set ōExcel = Nothing
)kdN3TYL x-h3U{H M0  Set fso = Nothing
YxI2R'C1rx3]0End Function51Testing软件测试网0a!oM8Bc0ss$eH
运行结果如图显示：51Testing软件测试网^\w5M&J y#_(^(_!l

!WZ7nvKH051Testing软件测试网0N8mqCQ&_J3K