数据驱动在QTP中的运用（二） - 自定义EXCEL文件

本节共有两个实例，第一个是把测试用例的数据写到EXCEL文件中，第二个是通过读取EXCEL文件中的数据，并把执行结果写入到EXCEL文件中。

1．对EXCEL文件进行写操作

 

Option Explicit

 

Dim fso, ddFilePath, i

Dim ExcelBook, ExcelSheet

 

ddFilePath = Environment.Value("TestDir") & "/ddFile.xls"

 

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(ddFilePath) Then

       fso.DeleteFile(ddFilePath)

End If

wait 3

 

 

Set ExcelBook = CreateObject("Excel.Application")

Set ExcelSheet = CreateObject("Excel.Sheet")

ExcelSheet.Application.visible = False

 

ExcelSheet.ActiveSheet.Cells(1,1).Value = "Agent Name"

ExcelSheet.ActiveSheet.Cells(1,2).Value = "Password"

ExcelSheet.ActiveSheet.Cells(1,3).Value = "Expire Value"

ExcelSheet.ActiveSheet.Cells(1,4).Value = "Fact Value"

ExcelSheet.ActiveSheet.Cells(1,5).Value = "Execute Result"

 

ExcelSheet.ActiveSheet.Cells(2,1).Value = "ad"

ExcelSheet.ActiveSheet.Cells(2,2).Value = "Mercury"

ExcelSheet.ActiveSheet.Cells(2,3).Value = "Agent name must be at least 4 characters long."

 

ExcelSheet.ActiveSheet.Cells(3,1).Value = "Admin"

ExcelSheet.ActiveSheet.Cells(3,2).Value = "Merc"

ExcelSheet.ActiveSheet.Cells(3,3).Value = "Incorrect password. Please try again"

 

ExcelSheet.ActiveSheet.Cells(4,1).Value = "Admin"

ExcelSheet.ActiveSheet.Cells(4,2).Value = "Mercury"

ExcelSheet.ActiveSheet.Cells(4,3).Value = "Flight Reservation"

      

ExcelSheet.SaveAs ddFilePath

ExcelBook.Quit

Set ExcelBook = Nothing

 

 

2．对EXCEL文件进行读写操作

Option Explicit

 

Dim fso, filePath, i

Dim ExcelBook, ExcelSheet, myExcelBook, myExcelSheet

 

filePath = Environment.Value("TestDir") & "/ddFile.xls"

 

Set fso = CreateObject("Scripting.FileSystemObject")

Set ExcelBook = CreateObject("Excel.Application")

Set ExcelSheet = CreateObject("Excel.Sheet")

 

Set myExcelBook = ExcelBook.WorkBooks.Open(filePath)

Set myExcelSheet = myExcelBook.WorkSheets("Sheet1")

 

For i = 2 To 4

       SystemUtil.CloseProcessByName "Flight4a.exe"  

       SystemUtil.Run Environment.Value("ProductDir") & "/samples/flight/app/flight4a.exe"

 

       Dialog("Login").WinEdit("Agent Name:").Set myExcelSheet.Cells(i,1)

       Dialog("Login").WinEdit("Password:").Set myExcelSheet.Cells(i,2)

       Dialog("Login").WinButton("OK").Click

 

       If Dialog("Login").Dialog("Flight Reservations").Exist Then

               myExcelSheet.Cells(i,4).Value = Dialog("Login").Dialog("Flight Reservations").Static("errInfo").GetROProperty("text")

               

              If Dialog("Login").Dialog("Flight Reservations").Static("errInfo").GetROProperty("text") = myExcelSheet.Cells(i,3) Then

                  myExcelSheet.Cells(i,5).Font.Color = vbBlue

                     myExcelSheet.Cells(i,5).Value = "测试成功"

              Else

                     myExcelSheet.Cells(i,5).Font.Color = vbRed

                     myExcelSheet.Cells(i,5).Value = "测试失败"

              End If

 

              Dialog("Login").Dialog("Flight Reservations").WinButton("确定").Click

              Dialog("Login").WinButton("Cancel").Click

             

       Elseif Window("Flight Reservation").Exist Then

              myExcelSheet.Cells(i,4).Value = Window("Flight Reservation").GetROProperty("text")

              myExcelSheet.Cells(i,5).Font.Color = vbBlue

              myExcelSheet.Cells(i,5).Value = "测试成功"

              Window("Flight Reservation").Close

       Else

              logFile.WriteLine "没有窗口弹出，测试失败！"

              ExitAction

       End If     

Next

 

myExcelBook.Save

 

ExcelBook.Quit

Set ExcelBook = Nothing