致力于测试团队建设和自动化测试开发,欢迎有兴趣者一起研究讨论

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

上一篇 / 下一篇  2008-09-05 08:33:00 / 个人分类:QTP

51Testing软件测试网&y5G R}j+b d

目标:51Testing软件测试网E R5R:Ib

51Testing软件测试网O y3K"jom9P

oracle数据库读取测试环境航班的PNR信息在前台的界面中利用命令查看PNR的信息、状态,同时将PNR和其对应的信息写入Excel中

{D/MqB:`0

R_~?y#d%d+P0方法:

Oz r6^ g,`2b:Rq0

bG^;i#@"UuE9Sw$D01、创建一个ADO链接,讲所有符合条件的PNR导出到Excel中保存51Testing软件测试网N%Rv \ F+nd-Wg4?,O

^ rn w'T$] {1tb02、将Excel导入DataTable中,在前台界面获取每条PNR信息,再写入Excel中

M$EBPPq0

HQA ?VD N0代码没有进行任何封装,在实际框架中可以抽取代码封装成Function进行调用

"\o9o;C-[n1p051Testing软件测试网5x C-E9a@/E }

51Testing软件测试网XX_ L+s&A)N+z

51Testing软件测试网k6G3N$I0r#BI

代码:

0~.G1H$B!F8? k'zk0

\*k%j:q G~%Y ci0Dim Conn51Testing软件测试网\bKT e ZG
Dim Res
&SU*BALt.[0Dim StrCon51Testing软件测试网#M lP0_Y @ M8RL
Dim SqlStr
d(h]q!X0?u4SK0Dim NumOfPNR51Testing软件测试网1Pv'ng${(q
Dim oExcel51Testing软件测试网_'L^/F$z6PsR6fy

XW/~!TIZ!~pTV0ExcelFile = "D:\PNR.xls"51Testing软件测试网U$E/DQ@bOLy

7^*ZWJgb+[B7S%\X0Set Conn = CreateObject("ADODB.CONNECTION")

X-R'ax4~ Fh g051Testing软件测试网s!gC;A'tNuk3J|

StrCon = "Provider=OraOLEDB.Oracle.1;Persist Security Info=True;" &_
+EAk VTGFOh0  "User ID=test;Password=test;Data Source=HKCTS01;"51Testing软件测试网)^y.fx"uYp!Cyiz!a#B
Conn.Open StrCon

b.p1B Tt,R#Ly0

hG F4j^-T0SqlStr = "select PNR from atii.t_at_ticket tt where tt.pnr is not null and tt.ticketorderid " &_51Testing软件测试网!V g:b Am0[(J
  "in(select id from atii.t_at_ticketorder t where t.createtime > trunc(sysdate))"
d#V.m#C4i R}0  51Testing软件测试网 z8~i cu.f]d-eM*j
Set Res = CreateObject("ADODB.RecordSet")51Testing软件测试网hO f%FUY(?N;oe
Res.Open SqlStr,Conn,1,151Testing软件测试网[%]b;O] mE&t

Xe^$vffV0NumOfPNR = Res.RecordCount51Testing软件测试网#i*P-v(^/i&G3pLQ8I1{

51Testing软件测试网-a A:q+c3R-^G

Set fso = CreateObject("scrīpting.FileSystemObject")51Testing软件测试网l7O%Wl!UH
Set ōExcel = CreateObject("Excel.Application")51Testing软件测试网|t$Y7M'A0Zt7b

51Testing软件测试网;il@9P}&d,]9}

If  fso.FileExists(ExcelFile) Then51Testing软件测试网nW mR p@3Y
 fso.DeleteFile ExcelFile , True51Testing软件测试网2GW,s(`W5y:mSz g'k"|pm
End If

O,k5p:X%rHN,K,I u0

`xV-H k3rg bDPr0oExcel.Workbooks.Add

8h(Jk2u5u5d0

#o%Ugq9^ ~d#Z0Set ōbjSheet = oExcel.Sheets.Item(1)51Testing软件测试网PUCk \
oExcel.Sheets.Item(1).Select51Testing软件测试网'QG8W Ej6p|

.s+s?R};w*Y4U(o0With objSheet51Testing软件测试网9` [ pIl |'O
 .Name = "PNR"
-}N6hPg,o"`"[Oz y0 51Testing软件测试网 dwI#GHC j ZJ2lx
 .Columns("A:A").ColumnWidth = 5
Kr#K9j2WaoXxa0 .Columns("B:B").ColumnWidth = 10
Rj+NX p$?0 .Columns("C:C").ColumnWidth = 90
1t+t }2u;]~`&y?0 .Columns("A:C").HorizontalAlignment = -413151Testing软件测试网cbW1l)Zwa
 .Columns("A:C").WrapText = True
V'i1MM2I+uTRX0 
gL)P#Xll!Z0 .Range("A:C").Font.Name = "Arial"
a9w6{$Uy"tssaj0 .Range("A:C").Font.Size = 1051Testing软件测试网f}h rkOHBB4K
 .Range("B1").Value = "PNR"51Testing软件测试网3\4w4dE!g"vZ"`,zt
 .Range("C1").Value = "Descrīption"51Testing软件测试网t |0|vN4P X;l X
 51Testing软件测试网&P3[Q#P&l4rm yO
 .Range("B1:C1").Borders(1).LineStyle = 151Testing软件测试网T)InS9F!Z
 .Range("B1:C1").Borders(2).LineStyle = 151Testing软件测试网9m9k4i\5{ [8l
 .Range("B1:C1").Borders(3).LineStyle = 151Testing软件测试网RwkX+v9m,F U
 .Range("B1:C1").Borders(4).LineStyle = 151Testing软件测试网4l%C,W`*yDB
 .Range("B1:C1").HorizontalAlignment = 351Testing软件测试网 s [-eXU6H|
 .Range("B:B").HorizontalAlignment = 3
8iR"e rAi&F.u$I0 51Testing软件测试网c$L!N'P5Kn"g
 .Range("B1:C1").Interior.ColorIndex = 5351Testing软件测试网.Md,t'We;~K
 .Range("B1:C1").Font.ColorIndex = 19
#P(O1R-eD1u-|b0 .Range("B1:C1").Font.Bold = True51Testing软件测试网6u4`R.J^%F_R&@bq
End With51Testing软件测试网}%{*l5T xl
oExcel.ActiveWorkbook.SaveAs ExcelFile51Testing软件测试网Wgxe9Y\#g_E
oExcel.Quit51Testing软件测试网Wjrm\$m+];zva,f+f

51Testing软件测试网l}H&prt

Set ōbjSheet = Nothing 51Testing软件测试网9{%~t+^;BPg

qV o o$vt;Z0Set ōbjWorkBook = oExcel.Workbooks.Open(ExcelFile)
Ee3?d-C/w)Gv2r%g;j0Set ōbjSheet = oExcel.Sheets("PNR")51Testing软件测试网o|zoSPo

51Testing软件测试网D'`I%E;{iD,M

With objSheet51Testing软件测试网$MN-Q f/Av3p
 Row = 251Testing软件测试网0jYz r7[ uQ
 For i = 0 To NumOfPNR - 1
,lpY:a zK0  .Cells(Row,2).Value = Res.Fields("PNR").Value

g:b5wB.f#gf051Testing软件测试网$Q8~+@kE.G'Hz

  .Range("B" & Row & ":C" & Row).Borders(1).LineStyle = 151Testing软件测试网(T`dG aLp
  .Range("B" & Row & ":C" & Row).Borders(2).LineStyle = 1
d0U8U`4rU$W q7B0  .Range("B" & Row & ":C" & Row).Borders(3).LineStyle = 151Testing软件测试网F4cg B[E%tr7`
  .Range("B" & Row & ":C" & Row).Borders(4).LineStyle = 1

*h Geg _P+Z0m051Testing软件测试网1Z L&O$m1wJ @ @;P'l*N Y!m

  .Range("B" & Row & ":C" & Row).Interior.ColorIndex = 1951Testing软件测试网{Ep2BB.k_
  .Range("B" & Row).Font.ColorIndex = 5351Testing软件测试网8X:rnGY2E8\
  .Range("C" & Row).Font.ColorIndex = 41
ypao]+]D;r w0  .Range("B" & Row & ":C" & Row).Font.Bold = True
w.h!_4b.xt0  51Testing软件测试网/IuoM ab ^
  Res.MoveNext
$z2Pf+gn+a8b H\e0  Row = Row + 1
nw0O7A,D:q0 Next
A ?`-bG['?0End With51Testing软件测试网7LT]L0s;~
objWorkBook.Save
]&D/\7C7?|RM0oExcel.Quit

\*M ~(l,f$S'MP4mk1D051Testing软件测试网 YU4u4^^hG,L)V

Set ōbjSheet = Nothing51Testing软件测试网bSU9|f"zF%vhZ.h
Set ōbjWorkBook = Nothing
D| Dl"YYF0Set Res = Nothing
px q2`0Rgo0Set Conn = Nothing51Testing软件测试网#xAI sfjUm u3e&x'_.D

51Testing软件测试网$ukKH6r`

DataTable.ImportSheet ExcelFile , 1 , Environment("ActionName")' "Action1"
x0KY qV5U y4u0NRow = DataTable.GetSheet(Environment("ActionName") ).GetRowCount
7[+|dJArt,a0j = 2
,c,?;kg ]7b4Z p0SystemUtil.CloseProcessByName "IEXPLORE.EXE"51Testing软件测试网8S8C8C:NI"l
Set IE = CreateObject("InternetExplorer.Application")
kM8XY5^#S0IE.Visible = True51Testing软件测试网.Jm8}9hRr2L X
For k =1 to NRow
gYzC$} D0 IE.Navigate "http://10.10.1.25/test0605.htm"51Testing软件测试网*["bb.w yo

51Testing软件测试网EVEg _Ps,WAJ

 DataTable.SetCurrentRow(k)51Testing软件测试网 BNE&sVJ+t L"_
 Browser("CreationTime:=0").Page("index:=0").WebEdit("name:=CMD").Set "RT" & DataTable("PNR" , "Action1")
-h/Go0`*~R5KSn9w v&d0 Browser("CreationTime:=0").Page("index:=0").WebButton("name:= 确认 ","index:=1").Click51Testing软件测试网%Vjd c9ip
 StrPnr = Browser("CreationTime:=0").Page("index:=0").WebElement("html tag:=BODY").GetROProperty("innertext")51Testing软件测试网 X/[:W!z o;tg
' msgbox StrPnr51Testing软件测试网0E-hf~T1X
 Set ōbjWorkBook = oExcel.Workbooks.Open(ExcelFile)
8nKaDxt}r0 Set ōbjSheet = oExcel.Sheets("PNR")
*J i-w xXT,? z$H0 objSheet.Cells(j,3).Value = StrPnr
(t oq6g cL5O9`4[2T9v0 objSheet.Cells(j,3).Font.ColorIndex = 4151Testing软件测试网+}? u5r-u!N"yn6m;}
 j = j + 151Testing软件测试网(a@2K'k/k
 51Testing软件测试网 jN8Lf_O)?0b7Z/t
 objWorkBook.Save
H9K)tMaV0 oExcel.Quit51Testing软件测试网 OYh6GM2?,BIEN
 Set ōbjSheet = Nothing
L;aDR$ImH0 Set ōbjWorkBook = Nothing51Testing软件测试网c$wK(mmSi b|)|
Next
@M"x0[8iv.U0Set IE = Nothing51Testing软件测试网}'[I~c#h
Set ōExcel = Nothing51Testing软件测试网9G `VB"Y]:s'n;wG
Set fso = Nothing

O;DGv/ds I i0

B^.F2P#z0运行结果:51Testing软件测试网l%}r%@N

8L;[+]`S0

"j_$\4[m\&n-E0

TAG: QTP

引用 删除 jiyan111   /   2008-09-10 16:12:53
报错啊
刘沛的测试空间 引用 删除 zte_boy   /   2008-09-09 16:47:48
业务看不懂没关系,能看懂实现就可以了
引用 删除 joeyu22   /   2008-09-09 16:16:53
帅哥,看不懂啊
哈哈
 

评分:0

我来说两句

日历

« 2022-01-14  
      1
2345678
9101112131415
16171819202122
23242526272829
3031     

数据统计

  • 访问量: 76119
  • 日志数: 79
  • 图片数: 1
  • 建立时间: 2008-05-18
  • 更新时间: 2009-06-04

RSS订阅

Open Toolbar