51Testing软件测试论坛

标题: 【seagull1985-QTP】EXCEL测试报告生成二 [打印本页]

---

作者: seagull1985    时间: 2016-8-5 09:44
标题: 【seagull1985-QTP】EXCEL测试报告生成二

接 【seagull1985-QTP】EXCEL测试报告生成一      因为代码过长，导致贴子字符数过长，所以分开2个贴子发。

代码PART2     衔接代码PART1

1. Public Sub Report (sStatus, sStepName,sExpected,sActual, sDetails)
   
2. 
   
3. Dim TestcaseName
   
4. Dim Row, TCRow, NewTC
   
5. 
   
6. 'TestcaseName = Environment("ActionName") & " > Iteration (Test-Action) = " & Environment("TestIteration") & "-" & Environment("ActionIteration")
   
7. TestcaseName = sStepName & " > Iteration (Test-Action) = " & Environment("TestIteration") & "-" & Environment("ActionIteration")
   
8. 
   
9. 'Open the Result file
   
10. ' Set objWorkBook = oEngine.Workbooks.Open (oSettings("File"))
    
11. 'Select the Summery Sheet
    
12. Set objSheet = oEngine.Sheets("Test_Summary")
    
13. oEngine.Sheets("Test_Summary").Select
    
14. 
    
15. 
    
16. With objSheet
    
17. 'Note the Row No. on which to Report the result
    
18. Row = .Range("C8").Value + 2*.Range("C7").Value + 2
    
19. TCRow = .Range("C7").Value + 11
    
20. NewTC = False
    
21. .Range("F" & TCRow).Font.ColorIndex = 3
    
22. .Range("E" & TCRow).Font.ColorIndex = 50
    
23. .Range("G" & TCRow).Font.ColorIndex = 46
    
24. 
    
25. 
    
26. 'Check if it is a new Tetstcase
    
27. If objSheet.Cells(TCRow - 1, 2).Value <> TestcaseName Then
    
28. .Cells(TCRow, 2).Value = TestcaseName
    
29. oEngine.ActiveSheet.Hyperlinks.Add objSheet.Cells(TCRow, 2), "", "Test_Result!A" & Row+1, TestcaseName
    
30. 
    
31. .Cells(TCRow, 3).Value = sStatus
    
32. .Range("F" & TCRow).Value = "0"
    
33. .Range("E" & TCRow).Value = "0"       
    
34. .Range("G" & TCRow).Value = "0"       
    
35. 
    
36. 
    
37. Select Case sStatus
    
38. Case "Fail"
    
39. .Range("C" & TCRow).Font.ColorIndex = 3       
    
40. .Range("F" & TCRow).Value = "1"
    
41. Case "Pass"
    
42. .Range("C" & TCRow).Font.ColorIndex = 50       
    
43. .Range("E" & TCRow).Value = "1"       
    
44. Case "Warning"
    
45. .Range("C" & TCRow).Font.ColorIndex = 46
    
46. .Range("G" & TCRow).Value = "1"
    
47. End Select
    
48. 
    
49. 
    
50. 'The first step.
    
51. .Cells(TCRow, 4).Value = 1 '(D,11)
    
52. 
    
53. NewTC = True
    
54. 
    
55. 'it is a new Testcase
    
56. 
    
57. .Range("C7").Value = .Range("C7").Value + 1 'count the cases.
    
58. 
    
59. 'Set the Borders for the Result Header
    
60. .Range("B" & TCRow & ":G" & TCRow).Borders(1).LineStyle = 1
    
61. .Range("B" & TCRow & ":G" & TCRow).Borders(2).LineStyle = 1
    
62. .Range("B" & TCRow & ":G" & TCRow).Borders(3).LineStyle = 1
    
63. .Range("B" & TCRow & ":G" & TCRow).Borders(4).LineStyle = 1
    
64. 'Set color and Fonts for the Header
    
65. .Range("B" & TCRow & ":G" & TCRow).Interior.ColorIndex = 2
    
66. .Range("B" & TCRow).Font.ColorIndex = 23
    
67. .Range("B" & TCRow & ":G" & TCRow).Font.Bold = True
    
68. 
    
69. 
    
70. '====NOT A NEW CASE================================================================
    
71. 
    
72. Else
    
73. .Range("D" & TCRow-1).Value = .Range("D" & TCRow-1).Value + 1
    
74. Select Case sStatus
    
75. Case "Fail"       
    
76. .Range("F" & TCRow-1).Value = .Range("F" & TCRow-1).Value +1
    
77. Case "Pass"       
    
78. .Range("E" & TCRow-1).Value = .Range("E" & TCRow-1).Value +1       
    
79. Case "Warning"       
    
80. .Range("G" & TCRow-1).Value = .Range("G" & TCRow-1).Value +1       
    
81. End Select
    
82. End If
    
83. 
    
84. If (Not NewTC) And (sStatus = "Fail") Then
    
85. .Cells(TCRow-1, 3).Value = "Fail"
    
86. .Range("C" & TCRow-1).Font.ColorIndex = 3
    
87. 
    
88. End If
    
89. 
    
90. If (Not NewTC) And (sStatus = "Warning") Then
    
91. 
    
92. If .Cells(TCRow-1, 3).Value = "Pass" Then
    
93. .Cells(TCRow-1, 3).Value = "Warning"
    
94. .Range("C" & TCRow-1).Font.ColorIndex = 46
    
95. End If
    
96. 
    
97. End If
    
98. 
    
99. 
    
100. .Range("C8").Value = .Range("C8").Value + 1
     
101. 'Update the End Time
     
102. .Range("C5").Value = Time
     
103. 
     
104. 'Set Column width
     
105. .Columns("B:D").Select
     
106. .Columns("B:D").Autofit
     
107. End With
     
108. 
     
109. 'Select the Result Sheet
     
110. Set objSheet = oEngine.Sheets("Test_Result")
     
111. oEngine.Sheets("Test_Result").Select
     
112. With objSheet
     
113. 'Enter the Result
     
114. If NewTC Then
     
115. .Range("A" & Row & ":E" & Row).Interior.ColorIndex = 37
     
116. .Range("A" & Row & ":E" & Row).Merge
     
117. Row = Row + 1
     
118. .Range("A" & Row & ":E" & Row).Merge       
     
119. .Range("A" & Row).Value = TestcaseName
     
120. 'Set color and Fonts for the Header
     
121. .Range("A" & Row & ":E" & Row).Interior.ColorIndex = 2
     
122. .Range("A" & Row & ":E" & Row).Font.ColorIndex = 23
     
123. .Range("A" & Row & ":E" & Row).Font.Bold = True
     
124. Row = Row + 1
     
125. End If
     
126. .Range("A" & Row).Value = sStepName
     
127. 'oEngine.Selection.ShapeRange.ScaleWidth 1.72, msoFalse, msoScaleFromTopLeft
     
128. 'oEngine.Selection.ShapeRange.ScaleHeight 1.81, msoFalse, msoScaleFromTopLeft
     
129. 
     
130. .Range("B" & Row).Value = sStatus
     
131. .Range("B" & Row).Font.Bold = True       
     
132. 
     
133. Select Case sStatus
     
134. Case "Pass"
     
135. .Range("A" & Row & ":E" & Row).Font.ColorIndex = 50
     
136. 
     
137. Case "Fail"
     
138. .Range("A" & Row & ":E" & Row).Font.ColorIndex = 3
     
139. 
     
140. Case "Warning"
     
141. .Range("A" & Row & ":E" & Row).Font.ColorIndex = 46
     
142. 
     
143. End Select
     
144. 
     
145. .Range("B" & Row).Font.Bold = True
     
146. .Range("C" & Row).Value = sExpected
     
147. .Range("D" & Row).Value = sActual
     
148. .Range("E" & Row).Value = sDetails
     
149. 
     
150. 'Set the Borders
     
151. .Range("A" & Row & ":E" & Row).Borders(1).LineStyle = 1
     
152. .Range("A" & Row & ":E" & Row).Borders(2).LineStyle = 1
     
153. .Range("A" & Row & ":E" & Row).Borders(3).LineStyle = 1
     
154. .Range("A" & Row & ":E" & Row).Borders(4).LineStyle = 1
     
155. .Range("A" & Row & ":E" & Row).VerticalAlignment = -4160
     
156. 
     
157. End With
     
158. oEngine.Sheets("Test_Summary").Select
     
159. oEngine.Sheets("Test_Summary").Range("B1").Select
     
160. 'Save the Workbook
     
161. objWorkBook.Save
     
162. 
     
163. 
     
164. End Sub
     
165. 
     
166. End Class
     
167. 
     
168. Set oReporterManager = new excelreporterengine
     

复制代码

---

欢迎光临 51Testing软件测试论坛 (http://bbs.51testing.com/)

Powered by Discuz! X3.2