Facebook

Course Name Start Date Time Duration Registration Link
No Training Programs Scheduled ClickHere to Contact
Please mail To sudhakar@qtpsudhakar.com to Register for any training

Sunday, November 22, 2009

Working with Data Table Utility Object

Working with Data Table Utility Object

The data your test uses is stored in the design-time Data Table, which is displayed in the Data Table pane at the bottom of the screen while you insert and edit steps.

The Data Table has the characteristics of a Microsoft Excel spreadsheet, meaning that you can store and use data in its cells and you can also perform mathematical formulas within the cells. You can use the DataTable, DTSheet and DTParameter utility objects to manipulate the data in any cell in the Data Table. For that you need to follow specific methods and properties to insert or retrieve data from DtSheets.

Before that here is a small comparison of normal Excel and QTP Datatable.

Windows Excel hierarchy

QTP datatable hierarchy

Excel Application

Excel Sheet

Columns

Rows & values

Datatable

DtSheet

DtParameter

Value & Valuebyrow

Applicable Methods and Properties on Data Table Utility Object

Datatable

DtSheet

DtParameter

Methods

AddSheet

DeleteSheet

Export

ExportSheet

GetCurrentRow

GetRowCount

GetSheet

GetSheetCount

Import

ImportSheet

SetCurrentRow

SetNextRow

SetPrevRow

Properties

GlobalSheet

LocalSheet

RawValue

Value

Methods

AddParameter

DeleteParameter

GetCurrentRow

GetParameter

GetParameterCount

GetRowCount

SetCurrentRow

SetNextRow

SetPrevRow

Properties

Name

Properties

Name

RawValue

Value

ValueByRow

These are all the methods and properties have to use for doing any operation in QTP.

How to apply Methods & Properties

For Example you’re creating an Excel sheet in windows with some employee information.

clip_image002

To create this information you need to create a new excel sheet, column and add values in all columns. If we observe the step by step process of this example

Windows

QTP

1. Create an Excel Sheet

2. Add Column to the sheet

3. Add values to the columns

1. Datatable.Addsheet

2. AddParameter

3. Value & valuebyrow

Script Ex:- Adding Sheet

Set dtSheet=Datatable.AddSheet(“Demo”) ‘ Creating Sheet

Set dtEmpid=dtSheet.Addparameter(“EmpId”,””) ‘ Creating Columns

Set dtEmpName=dtSheet.Addparameter(“EmpName”,””)

Set dtEmpSal=dtSheet.Addparameter(“Sal”,””)

For row=1 to 5

dtSheet.setcurrentrow(row)

dtEmpid.value=row ‘ Adding values to the Specific Rows

dtEmpName.value=”emp”&row

dtEmpSal.value= RandomNumber(3000,10000)

Next

Datatable.ExportSheet “C:\Test.xls”,”Demo”

Copy this code in to QTP and execute it. After executing you will find an excel sheet in C:\.

Accessing data from the Sheet

Suppose already you had a sheet in your drive and you want to access that data to use in QTP. In this case you need to import that external sheet into QTP and then you have to use getsheet & getparameter methods to access the data.

Script Ex:- Accessing Data

Datatable.Addsheet(“demo”)

datatable.ImportSheet dtPath, , SheetSource, “demo”

Set dg=datatable.GetSheet(“demo”)

ColumnCount=dg.getparametercount

RowCount=dg.getrowcount

For Column =1 to ColumnCount

Cname=dg.getparameter(Column).name

Set pmg=dg.getparameter(Cname)

For Row =1 to RowCount

Val=pmg.valuebyrow(Row)

Msgbox (val)

next

Next

Script Ex:- Copy Odd Values from one sheet

clip_image004Suppose if you have some values like this…. In this if you want to copy odd values, and then you need to follow this code.

Set dtSheet=datatable.GetSheet(Sheetname)

Set dtColumn=dtSheet.getparameter(“Numbers”)

RowCount=dtSheet.getrowcount

For Row =1 to RowCount

Val= dtColumn.valuebyrow(Row)

If Val Mod 2 <>0 then

Msgbox (Val)

End If

next

In this Script for Sheetname you have to provide the sheet name from which sheet you’re going to get the data.

Excel Object Model

Excel object Model is to automate the excel operations. Using QTP we can create sheets, But using Excel Object Model we can do full pledged operations what ever we are doing on normal excel sheet in windows.

Here is a sample Script to know how we can work with EOM

Set Excel=CreateObject("Excel.Application")

Set ExcelSheet = CreateObject("Excel.Sheet")

ExcelSheet.Application.Visible = True

For row=1 to 10

ExcelSheet.ActiveSheet.Cells(row,1).Value = "This is column A, row"&row

Next

ExcelSheet.SaveAs "C:\TEST.XLS"

ExcelSheet.Application.Quit

Set ExcelSheet = Nothing

To know more about Excel Object Model go to...

Open Excel->Help menu-> Microsoft Excel Help-> Table of Contents-> Microsoft Excel Visual Basic Reference->Microsoft Excel Object Model

Some Interesting Questions…

  1. Copy Odd Rows from a sheet
  2. Copy employees whose age is above 50
  3. Get Diagonal values from a sheet
  4. Copy data from one sheet to another sheet without changing column names
  5. Eliminate duplicate rows in a column
  6. sort the values in a column

____________________________________________________________________________

Please send your Suggestions and Doubts to my yahoo group http://in.groups.yahoo.com/group/qtpsudhakar

No comments :

Post a Comment