QTP实例-从数据库读取数据进行处理并生成报表

j)bTG#Rvt M"C(v0代码：

x@`3R&vR0Dim Conn51Testing软件测试网.I _ Hw)v4M2`R\I
Dim Res
mn%t-RQ#W1sC5bc0Dim StrCon
SR/Jf$o#~#l"S1u0Dim SqlStr51Testing软件测试网F$L[JNf.x
Dim NumOfPNR
#_qMg k3q,L0Dim oExcel51Testing软件测试网
x`m$Rl%T;D%x$A?_

@#Y.ce5|;GLp-RS0ExcelFile = "D:\PNR.xls"51Testing软件测试网0[8W3^b8^qzULw

Z+]7lPKN._0Set Conn = CreateObject("ADODB.CONNECTION")

,X^1d$U:|~9Ec)I0StrCon = "Provider=OraOLEDB.Oracle.1;Persist Security Info=True;" &_51Testing软件测试网|9CV4LfP
  "User ID=test;Password=test;Data Source=HKCTS01;"51Testing软件测试网Fonl#B
Conn.Open StrCon

SqlStr = "select PNR from atii.t_at_ticket tt where tt.pnr is not null and tt.ticketorderid " &_
QEq"E|]X0  "in(select id from atii.t_at_ticketorder t where t.createtime > trunc(sysdate))"51Testing软件测试网@0PXOTTO
  
O!j&f3dSTh&`-d0Set Res = CreateObject("ADODB.RecordSet")51Testing软件测试网,R4h|3^&}fQ
Res.Open SqlStr,Conn,1,1

Fa4~1CAgUfM0NumOfPNR = Res.RecordCount51Testing软件测试网3`2nSr_9H{

Set fso = CreateObject("scrīpting.FileSystemObject")
yfDH+eJ7s0Set ōExcel = CreateObject("Excel.Application")

If  fso.FileExists(ExcelFile) Then
&[dKM0dQVC0 fso.DeleteFile ExcelFile , True51Testing软件测试网u|0J:}(S!Aa9@
End If

/D%K.]SG'n sd0oExcel.Workbooks.Add51Testing软件测试网|'yu6SE1B`
V)m9B

_!b&c+W)h`0Set ōbjSheet = oExcel.Sheets.Item(1)51Testing软件测试网"R!p] ?'W.C:R}Fhymm
oExcel.Sheets.Item(1).Select

With objSheet
@5dz3p*YB]0 .Name = "PNR"51Testing软件测试网6`*pRWesj{
 
%?
m R2JE }RC0 .Columns("A:A").ColumnWidth = 551Testing软件测试网[y\&ke ToS
 .Columns("B:B").ColumnWidth = 10
s5CrUK$^&c K/~;x0 .Columns("C:C").ColumnWidth = 90
M }V S(lYK0 .Columns("A:C").HorizontalAlignment = -4131
.d8z&F1w/iq9VMQT0 .Columns("A:C").WrapText = True
v.vqh u9`)ohI0 51Testing软件测试网L.d9E^t0ILb%wh
 .Range("A:C").Font.Name = "Arial"51Testing软件测试网B `iD(I#b
 .Range("A:C").Font.Size = 10
d1v
Q m,kG3k0 .Range("B1").Value = "PNR"
`ldAkrCKy)b0 .Range("C1").Value = "Descrīption"
t
D9C"~4Y5c0 51Testing软件测试网EA2w'g8^x6r4t)h
 .Range("B1:C1").Borders(1).LineStyle = 151Testing软件测试网v&]K0A'i'E6b@@#[[%C
 .Range("B1:C1").Borders(2).LineStyle = 151Testing软件测试网nBnB:z
e,k
 .Range("B1:C1").Borders(3).LineStyle = 1
/G/YQ%dJ8psfJ)nH0 .Range("B1:C1").Borders(4).LineStyle = 151Testing软件测试网 @/j ?p-m(W
 .Range("B1:C1").HorizontalAlignment = 3
8DSk*G:q0 .Range("B:B").HorizontalAlignment = 3
;ZZGk:~1R%[0 
rr y3ay;~4z"_J3Gf0 .Range("B1:C1").Interior.ColorIndex = 53
P1u7]#^g5N!}1i0 .Range("B1:C1").Font.ColorIndex = 1951Testing软件测试网LPc,de
ir
 .Range("B1:C1").Font.Bold = True
;{{WrS0End With
l1@;D~,Zt0oExcel.ActiveWorkbook.SaveAs ExcelFile
-YR Vu!x0oExcel.Quit

f_(U2nd?6c f0Set ōbjSheet = Nothing 

Set ōbjWorkBook = oExcel.Workbooks.Open(ExcelFile)51Testing软件测试网sTS&v~a
Set ōbjSheet = oExcel.Sheets("PNR")

Q K1V|B/S H0With objSheet
+\x L_5mg(cKc,u
|5N0 Row = 251Testing软件测试网;l8Kg(Jo
w v%s
 For i = 0 To NumOfPNR - 1
0d9[w9J#i+H0  .Cells(Row,2).Value = Res.Fields("PNR").Value

  .Range("B" & Row & ":C" & Row).Borders(1).LineStyle = 151Testing软件测试网v8l+?4y{1Qd
  .Range("B" & Row & ":C" & Row).Borders(2).LineStyle = 151Testing软件测试网(k9v/w:soAt*Jf
  .Range("B" & Row & ":C" & Row).Borders(3).LineStyle = 1
O(| LEp6vbx:[H0u0  .Range("B" & Row & ":C" & Row).Borders(4).LineStyle = 151Testing软件测试网"kMH3k8e6\I

M1g:uBd#mA:RP0Z9o0  .Range("B" & Row & ":C" & Row).Interior.ColorIndex = 19
-C5}7J`IhJ0  .Range("B" & Row).Font.ColorIndex = 5351Testing软件测试网x/yt;OJ/S1ZS
  .Range("C" & Row).Font.ColorIndex = 41
^ C{Z
|?lV0  .Range("B" & Row & ":C" & Row).Font.Bold = True

o,z/Rf*lqDg~0  51Testing软件测试网d E M5_yZ!_
  Res.MoveNext51Testing软件测试网
K%@9G!q#fT(S
  Row = Row + 1
ishA;b0 Next
~f#A]Ai(PN0End With51Testing软件测试网@q?g;cci4@
objWorkBook.Save
@k]-mo0oExcel.Quit51Testing软件测试网:PU5V w0@_.u$r}

Set ōbjSheet = Nothing51Testing软件测试网(sii,S u
g#w+wY
Set ōbjWorkBook = Nothing51Testing软件测试网eyj:?:z
Set Res = Nothing
5@5u
\nbP:S%y:R0Set Conn = Nothing51Testing软件测试网.wJVjY

DataTable.ImportSheet ExcelFile , 1 , Environment("ActionName")' "Action1"51Testing软件测试网KrtM.J#\
NRow = DataTable.GetSheet(Environment("ActionName") ).GetRowCount51Testing软件测试网-E5}eT_Y-Z7N }\?
j = 2
3o
p-H{)pQ2df![0SystemUtil.CloseProcessByName "IEXPLORE.EXE"51Testing软件测试网)D)u;Fr&Dr8N
Set IE = CreateObject("InternetExplorer.Application")51Testing软件测试网*r!N]7lF4S!Q
IE.Visible = True51Testing软件测试网;? eI:X"PV
For k =1 to NRow
[kS3U4_q-U0 IE.Navigate "http://10.10.1.25/test0605.htm"51Testing软件测试网1ZDzPG5cd%a

8j0dqq$V*_N s0 DataTable.SetCurrentRow(k)
.FR1~ \5FFV-S0 Browser("CreationTime:=0").Page("index:=0").WebEdit("name:=CMD").Set "RT" & DataTable("PNR" , "Action1")51Testing软件测试网}q,x3eK+Bb
 Browser("CreationTime:=0").Page("index:=0").WebButton("name:= 确认 ","index:=1").Click51Testing软件测试网Q3w:_E_
 StrPnr = Browser("CreationTime:=0").Page("index:=0").WebElement("html tag:=BODY").GetROProperty("innertext")
Jc)CVhX2kB0' msgbox StrPnr51Testing软件测试网dt*a*XL@\ Hk
 Set ōbjWorkBook = oExcel.Workbooks.Open(ExcelFile)
gN:]F$z;vP;Eb0 Set ōbjSheet = oExcel.Sheets("PNR")51Testing软件测试网8v`Sv&_/B
 objSheet.Cells(j,3).Value = StrPnr51Testing软件测试网I+J4R'cc#n Fh#v
 objSheet.Cells(j,3).Font.ColorIndex = 41
._hb \]F0 j = j + 1

`H2B(eJ5q[Wm0 51Testing软件测试网.p_"GKg
 objWorkBook.Save51Testing软件测试网A;Oh4T(R7z3A m(h
 oExcel.Quit
6a/qv6HeZ`0 Set ōbjSheet = Nothing51Testing软件测试网/p LH j CK*b!LB
 Set ōbjWorkBook = Nothing51Testing软件测试网9m4@)|V
l3X \
Next51Testing软件测试网,Q,Ayr9aS+SN*t
Set IE = Nothing
TOU?(@+X1DZI7O/q0Set ōExcel = Nothing51Testing软件测试网2`/pW@G6Jf9kI
Set fso = Nothing