All About Working with datatables and data Sheets in QTP: Methods and properties

Methods for Datatable object

Following are the methods and properties for interaction with data tables in QTP

1. Datatable.AddSheet  

Adds the specified sheet to the run-time Data Table

datatable.AddSheet(strSheetName)

2. DeleteSheet

Deletes the specified sheet from the run-time Data Table.

Datatable.DeleteSheet(strSheetName)

3. Export Method

Saves a copy of the run-time Data Table in the specified location.

datatable.Export(strExcelFile)

4.ExportSheet Method 

Exports a specified sheet of the run-time Data Table to the specified file.

DataTable.ExportSheet(FileName, DTSheet)

5.GetCurrentRow

Returns the current (active) row in the first sheet in the run-time Data Table (global sheet).

row = DataTable.GetCurrentRow

6. GetRowCount Method

Returns the total number of rows in the longest column in the first sheet in the run-time Data Table (global sheet).

DataTable.GetRowCount

7. GetSheet Method:

Returns the specified sheet from the run-time Data Table.

MyParam=DataTable.GetSheet ("MySheet").AddParameter("Rower", "9")

8. GetSheetCount

Returns the total number of sheets in the run-time Data Table.

intSheet = datatable.getSheetCount

9.  Import Method:

 Imports the specified Microsoft Excel file to the run-time Data Table.

DataTable.ImportSheet(FileName, SheetSource, SheetDest)

10. ImportSheetMethod:

Imports a sheet of a specified file to a specified sheet in the run-time Data Table.

DataTable.ImportSheet(FileName, SheetSource, SheetDest)

11.SetCurrentRow Method:

Sets the specified row as the current (active) row in the run-time Data Table.

DataTable.SetCurrentRow(RowNumber)

DataTable.GetSheet("MySheet").SetCurrentRow(2)

12. SetNextRow Method 

Sets the row after the current (active) row as the new current row in the run-time Data Table.

DataTable.SetNextRow

DataTable.GetSheet("MySheet").SetNextRow

13. GlobalSheet Property 

 Returns the first sheet in the run-time Data Table (global sheet).

 DataTable.GlobalSheet.AddParameter "Name", "Nitin"

14. LocalSheet Property

Returns the current (active) local sheet of the run-time Data Table.

DataTable.LocalSheet.AddParameter "Name", "Nitin"

Methods For DTSheet Object:

Below are the methods to work with the specified sheet in datatable.

1. AddParameter Method 

Adds the specified parameter (column) to the sheet in the run-time Data Table, sets the value of the first row to the specified value

DataTable.GetSheet("dtGlobalSheet").AddParameter "Name","Nitin"

DataTable.AddSheet("MySheet").AddParameter("Name", "Nitin")

paramname = DataTable.LocalSheet.AddParameter("Name", "Nitin").Name 

2. DeleteParameter Method

Deletes the specified parameter from the sheet in the run-time Data Table.

DataTable.GetSheet("MySheet").DeleteParameter("Name") 

3. GetCurrentRow Method

Returns the row number of the current (active) row in the run-time Data Table sheet. 

row = DataTable.GetSheet("MySheet").GetCurrentRow 

4. GetParameter Method 

Retrieves the specified parameter from the run-time Data Table sheet. 

DataTable.GetSheet("ActionA").GetParameter("Date").RawValue

GetParameter("ParamName").value retrieves or sets the value of the cell in the current (active) row of the parameter in the run-time Data Table. 

DataTable.GetSheet("Action1").GetParameter("Destination").Value="Pithoragarh"

DataTable.GetSheet("Action1").GetParameter("Destination").ValueByRow(7) - This gets value for parameter Destination in sheet Action 1 in row 7.

5. GetParameterCount Method 

Returns the total number of parameters (columns) in the run-time Data Table sheet.

paramcount = DataTable.GetSheet("Test").GetParameterCount

6. GetRowCount Method

 Returns the total number of rows in the longest column in the run-time Data Table sheet.

 rowcount = DataTable.GetSheet("Test").GetRowCount