QTP实例-测试报告代码

M6e.W(O6Bx8BG0自动化测试实际是将测试人员手工进行的工作脚本化，这就需要在测试脚本运行完成后，展示一个明了的测试报告，供相关人员查看测试结果，判断系统的正确性，以下代码实现的是一个EXCEL格式的测试报告，以Function为单位记录脚本的执行情况，若完成则显示Complete，在Comment中显示验证点检查结果情况，以此来判断系统运行的正确行；若由于一些突发的异常情况导致脚本未执行完成，则显示Fail，在Comment中显示Err.Descrīption信息51Testing软件测试网$}.e7yp(HuH7Q P

当然，要达到以上效果，要求在业务脚本的检查点按照指定的格式书写，我的做法是设置一个全局变量记录所有检查点信息，然后传入给日志函数51Testing软件测试网C/jDr6r`$G V

以下是代码，希望对有需要的朋友有所帮助：）51Testing软件测试网2v0Lx0f#iu~

Function ExcelReport(ReportExcelFile,sStatus,sDetails)51Testing软件测试网 m(~@:Z:w[
  '定义变量51Testing软件测试网
tC pKG5K
  Dim fso51Testing软件测试网,a_!ZP/g#O
  Dim oExcel51Testing软件测试网B c E#E@4{
  Dim ExcelFile51Testing软件测试网)D&vXa_ oXN7Kg
  Dim TestcaseName51Testing软件测试网/N@fw%L^^ vGT,r(UB
  Dim objWorkBook
r:C/[3U)Ovrq.@!v0  Dim objSheet51Testing软件测试网 @5tmu.u#Ko,`
u
'  Dim Environment("Row"), Environment("TCRow"), NewTC
b(eaJ"Z,f P0  51Testing软件测试网.b/rn3R9R `
  Set fso = CreateObject("scrīpting.FileSystemObject")51Testing软件测试网)`S R i0sJ_u{
  Set ōExcel = CreateObject("Excel.Application")51Testing软件测试网;bHx+t3bT
'   oExcel.Visible = True
7ma'nU`{+Wk}V0  
,w
A:QNh;VE S(G|0  '设置Excel报告样式51Testing软件测试网C3e1{$Gj*oBHA
  If  Not fso.FileExists(ReportExcelFile) Then51Testing软件测试网9C6n${;EF2wN&M
   oExcel.Workbooks.Add51Testing软件测试网dP;~$Mt0`,n,u
   51Testing软件测试网0P`&b't1C AA']
   '获取工作簿的第一个Sheet页
&^Dw%M_2W0   Set ōbjSheet = oExcel.Sheets.Item(1)
[dOWG0   oExcel.Sheets.Item(1).Select51Testing软件测试网y(d9]y9Q@'v
   51Testing软件测试网!Y+|jp/LY!v.?
   With objSheet51Testing软件测试网7u(i {+c3EN'u2`Q
    '更改sheet名51Testing软件测试网Xn&cx:bU]2Q
    .Name = "Test_Summary"51Testing软件测试网%?qiz:C*S4B'@K
    51Testing软件测试网 eAv/UU8@G9X
    '设置列宽
:]_
@A#Kl*Ia0    .Columns("A:A").ColumnWidth = 5
{4hG t:{9OS0    .Columns("B:B").ColumnWidth = 35
+S3P&A"`z$mK0    .Columns("C:C").ColumnWidth = 1051Testing软件测试网W^K[Id
    .Columns("D:D").ColumnWidth = 6051Testing软件测试网_)q;xGyp
    .Columns("A:D").HorizontalAlignment = -4131
'h
ei%h'P)X#X0    .Columns("A:D").WrapText = True51Testing软件测试网Q:rLu'R ix:l
    51Testing软件测试网1}(M'`)z6B
    '设置显示区域的字体类型和大小
Ds3G;D#z4n/{+O0    .Range("A:D").Font.Name = "Arial"
Fdu8cci0    .Range("A:D").Font.Size = 10
1]w ~2^B0    51Testing软件测试网;MgDI
KMH}(\
    '设置文件头格式
zkB(\Q2u%}UmIQ0    .Range("B1").Value = "Test Result"51Testing软件测试网$r:j9rmTo,w
    .Range("B1:C1").Merge 51Testing软件测试网 c.F0yd7rr
     51Testing软件测试网)L"c/G*J/t~
    '设置文件头格式字体和颜色51Testing软件测试网
\:G%`+fdw&}2B
    .Range("B1:C1").Interior.ColorIndex = 53
`;[b0a Tg:x0    .Range("B1:C1").Font.ColorIndex = 1951Testing软件测试网sDf"sm
    .Range("B1:C1").Font.Bold = True
;bj/c&k]x(j0    
,n%v6w!u&f0    '设置执行的日期和时间51Testing软件测试网3p_o*u7sm
    .Range("B3").Value = "Test Data:"51Testing软件测试网;Hr0m"i\iyv`d
    .Range("B4").Value = "Test Start Time:"
0W1y&L5M*B$u ne ]!o0    .Range("B5").Value = "Test End Time:"
T+j&e4mu-pO'v w7F0    .Range("B6").Value = "Test Duration: "   
puxiJ'Cx0    .Range("C3").Value = Date
BD&K }K2]tf0    .Range("C4").Value = Time51Testing软件测试网U7X'|n+W
    .Range("C5").Value = Time51Testing软件测试网dr"|@]9e
    .Range("C6").Value = "=R[-1]C-R[-2]C"51Testing软件测试网)i uU%Y;l'vwW
    .Range("C6").NumberFormat = "[h]:mm:ss;@"
C L l)I.Oti0    
oq!N5[1L2H z9m,]K0    '设置日期和时间cell的边界51Testing软件测试网;ZK LnQ:k
    .Range("C3:C8").HorizontalAlignment = 4 '右边对齐
4nC8A:lBbUYRhQ1Ca0'     .Range("C3:C8").Font.Bold = True
%c7t:bGx,H0v-Y0'     .Range("C3:C8").Font.ColorIndex = 7
?F)j&j$X S0    .Range("B3:C8").Borders(1).LineStyle = 1
){-YA'S*Y~.B^7iN0    .Range("B3:C8").Borders(2).LineStyle = 1
,Ft^#](})X)d0    .Range("B3:C8").Borders(3).LineStyle = 1
JkBJ1xs
o0    .Range("B3:C8").Borders(4).LineStyle = 1
3wK!~-d/\0    
m(R+BzAdm+O&]0    '设置日期和时间Cell的样式
0^4R#_G)C\;Z2l;NI"y0    .Range("B3:C8").Interior.ColorIndex = 40
W9e6ed?z0    .Range("B3:C8").Font.ColorIndex = 12
md @Kc'e@(M`.?3E(t0    .Range("C3:C8").Font.ColorIndex = 7
D(r1xbmM$]0    .Range("B3:A8").Font.Bold = True51Testing软件测试网C)U sCNGV&J ~9C
    51Testing软件测试网'Q!j @,n4lc{*c
    .Range("B7").Value = "No Of Function:"
M7Dpxj(my0    .Range("C7").Value = "0"

M(Of4L6y)~]0    .Range("B8").Value = "Test Machine"
C,p7G\T8Q%C ~a0    .Range("C8").Value = GetIP()51Testing软件测试网"j8HQ8z&H2N2q
    .Range("B10").Value = "TestCase"51Testing软件测试网vQ.`v}VM
    .Range("C10").Value = "Finish"
2JL j/c*uHyS DH0    .Range("D10").Value = "Comment"
B5h#K|a0    51Testing软件测试网$PhQ1|n-n
    '为Result Summery设置格式51Testing软件测试网4g0q-Cw] ey.F
    .Range("B10:D10").Interior.ColorIndex = 53
(O%DMf b,_}7a0    .Range("B10:D10").Font.ColorIndex = 1951Testing软件测试网Tc,Rb#M[9V9z? Fe
    .Range("B10:D10").Font.Bold = True
.[ v yRM0h,XP0`d0  
+Mm7_-R?fCVU*^V0    '为Result Summery设置边界
-_m B.UR mj0    .Range("B10:D10").Borders(1).LineStyle = 151Testing软件测试网 Hmv a6dv
    .Range("B10:D10").Borders(2).LineStyle = 151Testing软件测试网:wKWb'Zb
    .Range("B10:D10").Borders(3).LineStyle = 151Testing软件测试网5d$p,| d;w
    .Range("B10:D10").Borders(4).LineStyle = 1
L
U"nJb5zQAf0    .Range("B10:D10").HorizontalAlignment = 3
:o\ aO
fC t0    .Range("C11:C1000").HorizontalAlignment = 3
O/OR;K"R!u5T_0 
)i+u~mg7d0    .Columns("B:D").Select51Testing软件测试网mPi/}H'y$}k~
'     .Columns("B:D").Autofit
.e9vU'k1rd
p0    .Range("B11").Select  51Testing软件测试网Q/wID;^:y7Z&n
   End With51Testing软件测试网4i4so3p$ESgWz
   oExcel.ActiveWindow.FreezePanes = True51Testing软件测试网zfsty]"cJ
   oExcel.ActiveWorkbook.SaveAs ReportExcelFile
&`([I)a*k9_k$|2uyH*`0   oExcel.Quit
!rv#E`5l;^@8uUf0   51Testing软件测试网&@u0L2]ry
   Set ōbjSheet = Nothing51Testing软件测试网c-h8^6b?
  End If
kO.r.Z{j qsS3f0  
(ni`0`)a;P?m)MS0  TestcaseName = Environment("TCase")
D2T"S%h9w$}G3c0'   MsgBox TestcaseName51Testing软件测试网+DLy0|9`N5}^
  Set ōbjWorkBook = oExcel.Workbooks.Open(ReportExcelFile)
v r?)A,nK0  Set ōbjSheet = oExcel.Sheets("Test_Summary")51Testing软件测试网?j5^?G$k?3G$z:S
  
s:?,mdRn%nfU4Vw~0  With objSheet51Testing软件测试网9zM2TO1z%\
   '设置行数和是否NewTc标识
X6WfDe"n0   Environment("Row") = .Range("C7").Value + 11
'b JpJ2{,gfL7n"A0   'NewTC = False
6oKb+T0t-{Aq0   51Testing软件测试网~(UO_}~N z!m1E/pe)G
   If TestcaseName <> objSheet.Cells(Environment("Row")-1,2).value Then51Testing软件测试网/eS;RYM,Y
    .Cells(Environment("Row"),2).value = TestcaseName
F)XS;F8\:RW7U;Iv0    .Cells(Environment("Row"), 3).Value = sStatus51Testing软件测试网o2e;] Q%a6[/I$~
    .Cells(Environment("Row"), 4).value = sDetails
3M3r!kD&{'W-X0    51Testing软件测试网_Kr/c/S3ER5`3J
    Select Case sStatus
&[)]Z-X Q(],WK
t
_-P0     Case "Fail"
d,jD'[b0      .Range("C" & Environment("Row")).Font.ColorIndex = 3
%E9M.L `6to#s} K0     Case "Complete"51Testing软件测试网3C D*D ?6sk/tAL
      .Range("C" & Environment("Row")).Font.ColorIndex = 50
V9l }6?4h$e;zff0    End Select
/N,[#WTb\0    
.@T?Q&YX(G0    'NewTC = True51Testing软件测试网} |}U|;my0]
    .Range("C7").Value = .Range("C7").Value + 151Testing软件测试网7\ \$[0Y{Q IH
    '设置边界
0URK4ZI9E%w']Le0    .Range("B" & Environment("Row") & ":D" & Environment("Row")).Borders(1).LineStyle = 1
;hbd&D$A6q0    .Range("B" & Environment("Row") & ":D" & Environment("Row")).Borders(2).LineStyle = 151Testing软件测试网1z pQ8O qOX
    .Range("B" & Environment("Row") & ":D" & Environment("Row")).Borders(3).LineStyle = 1
A/E@+M9RlK&O0    .Range("B" & Environment("Row") & ":D" & Environment("Row")).Borders(4).LineStyle = 1
J?2ft"D9o#?0    '设置字体和颜色?
*V7SH,X@3|(CV0    .Range("B" & Environment("Row") & ":D" & Environment("Row")).Interior.ColorIndex = 1951Testing软件测试网6ac.|J:Zx%D
    .Range("B" & Environment("Row")).Font.ColorIndex = 53
-ouG6I3I-nt4t0    .Range("D" & Environment("Row")).Font.ColorIndex = 41

xy$l||r0    .Range("B" & Environment("Row") & ":D" & Environment("Row")).Font.Bold = True
}@r\ P:Wo,\2K0   End If
im%|x|(~9F'F0   51Testing软件测试网l$^|-C2S(fJ
   If (Not NewTC) And (sStatus = "Fail") Then
"[9b/xuKh0    .Cells(Environment("Row"), 3).Value = "Fail"51Testing软件测试网z0@)T"aAZz
    .Range("C" & Environment("Row")).Font.ColorIndex = 3
o cv6]5M M4C0   End If
(QI+sig;K+l0   51Testing软件测试网 NNt\%z}%hj
   '更新结束时间51Testing软件测试网%Yp}*T mK
   .Range("C5").Value = Time
^ I+P2Dm2YiDt"|&pX0   
P#J_}:lX YL&W0   .Columns("B:D").Select
B|[d:J0'    .Columns("B:D").Autofit51Testing软件测试网"S"cN1n,nh{
  End With
o:A"L-Y#Lf0  oExcel.ActiveWindow.FreezePanes = True51Testing软件测试网0jR|(C_
{K1|Ph
  
D^$F/HIXXq0  '保存结果
3D?P)\_yk6\0e0  objWorkBook.Save
;v6x'i\]^0  oExcel.Quit51Testing软件测试网!\YfSv)a
  
Tc(N s2PQ
|'s0  Set ōbjSheet = Nothing51Testing软件测试网 Y e{e3~ Y
  Set ōbjWorkBook = Nothing51Testing软件测试网!oE#|O%`_
  Set ōExcel = Nothing51Testing软件测试网.Z5h3V.J"wy
  Set fso = Nothing51Testing软件测试网3C:gc'|bB4A7z'cZ
End Function51Testing软件测试网 Sd3x[A4K&wPA"?

运行结果如图显示：

k z#SR.g^P}%}0

d)p-s:e
[-L$GK9BP051Testing软件测试网m\chtD

!ye g,Ke4BH0本文为zte_boy原创，转载请注明出处：http://www.51testing.com/?161787