Thursday, July 8, 2010

DATATABLE OBJECT in QTP

In QTP scripting there are many objects that are used, One of the Objects used in QTP scripting is DataTable Object.
Datatable Object and related Functions: Basic use of Datatable object is to create an object for adding, deleting, updating data of an excel sheet used.

Description:
The run-time Data Table.
Associated Methods
• AddSheet Method
• DeleteSheet Method
• Export Method
• ExportSheet Method
• GetCurrentRow Method
• GetRowCount Method
• GetSheet Method
• GetSheetCount Method
• Import Method
• ImportSheet Method
• SetCurrentRow Method
• SetNextRow Method
• SetPrevRow Method
Associated Properties
• GlobalSheet Property
• LocalSheet Property
• RawValue Property
• Value Property

DTSheet Object
Description :
A sheet in the run-time Data Table. This object is not a built-in utility object, but can be accessed using one of the following methods or properties:
• DataTable.AddSheet
• DataTable.GetSheet
• DataTable.GlobalSheet
• DataTable.LocalSheet
Notes:
All methods performed on this object apply to the run-time DataTable object only. Changes to the run-time DataTable object are reflected in the test results, but the design time Data Table is not affected.
When working with scripted components, the name of the (single) Data Sheet is identical to the name of scripted component. If you save the scripted component with a new name, the name of the data sheet changes accordingly.
Associated Methods
• AddParameter Method
• DeleteParameter Method
• GetCurrentRow Method
• GetParameter Method
• GetParameterCount Method
• GetRowCount Method
• SetCurrentRow Method
• SetNextRow Method
• SetPrevRow Method
Associated Properties
• Name Property
DTParameter Object
Description :
A parameter (column) in a sheet in the run-time Data Table. This object is not a built-in utility object, but can be accessed using one of the following methods:
• DTSheet.AddParameter
• DTSheet.GetParameter
Note: All methods performed on this object apply to the run-time DataTable object only. Changes to the run-time DataTable object are reflected in the test results, but the design-time Data Table is not affected.
Associated Properties
• Name Property
• RawValue Property • Value Property
• ValueByRow Property

AddSheet Method
Description :
Adds the specified sheet to the run-time Data Table and returns the sheet so that you can directly set properties of the new sheet in the same statement.
Syntax
DataTable.AddSheet(SheetName)
DTSheet Object.
Example
The following example uses the AddSheet method to create the new sheet, "MySheet" in the run-time Data Table and then adds a parameter to the new sheet.
Variable=DataTable.AddSheet ("MySheet").AddParameter("Time", "8:00")

DeleteSheet Method
Description
Deletes the specified sheet from the run-time Data Table.
Syntax
DataTable.DeleteSheet SheetID
Example
The following example uses the DeleteSheet method to delete the sheet, "MySheet" from the run-time Data Table.
DataTable.DeleteSheet "MySheet"
Export Method
Description
Saves a copy of the run-time Data Table in the specified location.
Syntax
DataTable.Export(FileName)
Example
The following example uses the Export method to save a copy of the test's Data Table in C:\flights.xls.
DataTable.Export ("C:\flights.xls")
ExportSheet Method
Description
Exports a specified sheet of the run-time Data Table to the specified file.
If the specified file does not exist, a new file is created and the specified sheet is saved.
If the current file exists, but the file does not contain a sheet with the specified sheet name, the sheet is inserted as the last sheet of the file.
If the current file exists and the file contains the specified sheet, the exported sheet overwrites the existing sheet.
Syntax
DataTable.ExportSheet(FileName, DTSheet)
Example
The following example uses the ExportSheet method to save the first sheet of the run-time Data Table to the name.xls file.
DataTable.ExportSheet "C:\name.xls" ,1
GetCurrentRow Method
Description
Returns the current (active) row in the first sheet in the run-time Data Table (global sheet).
Syntax
DataTable.GetCurrentRow
Return Value
Number
Example
The following example uses the GetCurrentRow method to retrieve the row currently being used in run-time Data Table and writes it to the report.
row = DataTable.GetCurrentRow
Reporter.ReportEvent 1, "Row Number", row
GetRowCount Method
Description
Returns the total number of rows in the longest column in the first sheet in the run-time Data Table (global sheet).
Syntax
DataTable.GetRowCount
Example
The following example uses the GetRowCount method to find the total number of rows in the longest column of the MySheet run-time data sheet and writes it to the report.
rowcount = DataTable.GetSheet("MySheet").GetRowCount
Reporter.ReportEvent 2, "There are " &rowcount, "rows in the data sheet."
GetSheet Method
Description
Returns the specified sheet from the run-time Data Table.
Syntax
DataTable.GetSheet(SheetID)
Return Value
DTSheet Object
Example
The following example uses the GetSheet method to return the "MySheet" sheet of the run-time Data Table in order to add a parameter to it.
MyParam=DataTable.GetSheet ("MySheet").AddParameter("Time", "8:00")
You can also use this to add a parameter to the "MySheet" local sheet (note that no value is returned).
DataTable.GetSheet ("MySheet").AddParameter "Time", "8:00"
GetSheetCount Method
Description
Returns the total number of sheets in the run-time Data Table.
Syntax
DataTable.GetSheetCount
Return Value
Number
Example
The following example uses the GetSheetCount method to find the total number of sheets in the run-time Data Table and writes it to the report.
sheetcount = DataTable.GetSheetCount
Reporter.ReportEvent 0, "Sheet number", "There are " & sheetcount & " sheets in the Data Table."
Import Method
Description
Imports the specified Microsoft Excel file to the run-time Data Table.
Notes:
The imported table must match the test. The column names must match the parameters in the test, and the sheet names must match the action names.
If you import an Excel table containing combo box or list cells, conditional formatting, or other special cell formats, the formats are not imported and the cell is displayed in the Data Table with a fixed value.
Syntax
DataTable.Import(FileName)
Example
The imported table replaces all data in the existing run-time Data Table (including all data sheets).
The following example uses the Import method to import the flights.xls table to the run-time Data Table.
DataTable.Import ("C:\flights.xls")
ImportSheet Method
Description
Imports a sheet of a specified file to a specified sheet in the run-time Data Table. The data in the imported sheet replaces the data in the destination sheet (see SheetDest argument).
Notes:
The column headings in the sheet you import must match the Data Table parameter names in the action for which the sheet is being imported. Otherwise, your test or component may fail.
The sheet you import automatically takes the name of the sheet it replaces.
If you import an excel sheet containing combo box or list cells, conditional formatting, or other special cell formats, the formats are not imported and the cell is displayed in the Data Table with a fixed value.
Syntax
DataTable.ImportSheet(FileName, SheetSource, SheetDest)
Example
The following example uses the ImportSheet method to import the first sheet of the name.xls table to the name sheet in the test's run-time Data Table.
DataTable.ImportSheet "C:\name.xls" ,1 ,"name"
SetCurrentRow Method
Description
Sets the specified row as the current (active) row in the run-time Data Table.
Note: You can only set a row that contains at least one value.
Syntax
DataTable.SetCurrentRow(RowNumber)
Example
The following example uses the SetCurrentRow method to change the active row to the second row in the global run-time Data Table.
DataTable.SetCurrentRow (2)
SetNextRow Method
Description
Sets the row after the current (active) row as the new current row in the run-time Data Table.
Note: You can only set a row that contains at least one value. If the current row is the last row in the Data Table, applying this method sets the first row in the Data Table as the new current row.
Syntax
DataTable.SetNextRow
Example
The following example uses the SetNextRow method to change the active row to the next row in the run-time Data Table.
DataTable.SetNextRow
SetPrevRow Method
Description
Sets the row above the current (active) row as the new current (active) row in the run-time Data Table.
Note: If the current row is the first row in the Data Table, applying this method sets the last row in the Data Table as the new current row.
Syntax
DataTable.SetPrevRow
Example
The following example uses the SetPrevRow method to change the active row to the previous row in the global run-time Data Table.
DataTable.SetPrevRow
GlobalSheet Property
Description
Returns the first sheet in the run-time Data Table (global sheet).
Syntax
DataTable.GlobalSheet
Example
The following example uses the GlobalSheet property to return the global sheet of the run-time Data Table in order to add a parameter (column) to it.
ParamValue=DataTable.GlobalSheet.AddParameter("Time", "5:45")
You can also use this method to add a parameter to the global sheet (note that no value is returned).
DataTable.GlobalSheet.AddParameter "Time", "5:45"
LocalSheet Property
Description
Returns the current (active) local sheet of the run-time Data Table.
Syntax
DataTable.LocalSheet
Example
The following example uses the LocalSheet property to return the local sheet of the run-time Data Table in order to add a parameter (column) to it.
MyParam=DataTable.LocalSheet.AddParameter("Time", "5:45")
RawValue Property
Description
Retrieves the raw value of the cell in the specified parameter and the current row of the run-time Data Table. The raw value is the actual string written in a cell before the cell has been computed, such as the actual text from a formula.
Syntax
DataTable.RawValue ParameterID [, SheetID]
Example
The following example uses the RawValue property to find the formula used in the current row of the Date column in the ActionA sheet in the run-time Data Table. The statement below returns the value: =NOW()
FormulaVal=DataTable.RawValue ("Date", "ActionA")
Value Property
Description
DataTable default property. Retrieves or sets the value of the cell in the specified parameter and the current row of the run-time Data Table.
Note: This property returns the computed value of the cell. For example, if the cell contains a formula, the method returns True or False.
Syntax
To find the value:
DataTable.Value(ParameterID [, SheetID])
or
DataTable(ParameterID [, SheetID])
To set the value:
DataTable.Value(ParameterID [, SheetID])=NewValue
or
DataTable(ParameterID [, SheetID]) =NewValue
Example
The following example uses the Value property to set the value in the current row of the Destination parameter (column) in the "ActionA" sheet in the run-time Data Table.
DataTable.Value ("Destination", "ActionA")="New York"
The following example uses the Value property to set the value in the current row of the second parameter (column) in the third sheet.
DataTable.Value (2,3)="New York"
Note: You could omit the word Value in the statements above, because Value is the default property for the DataTable object.
The following example uses the default property to set the value in the current row of the Destination parameter (column) in the current (active) local sheet.
DataTable("Destination", dtlocalSheet)="New York"

AddParameter Method
Description
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, and returns the parameter so that you can directly set or retrieve properties of the new parameter in the same statement.
Syntax
DTSheet.AddParameter(ParameterName, Value)
Return Value
DTParameter Object
Example
The following example uses the AddParameter method to create the new Parameter, "Arrival" within the new sheet, MySheet of the run-time Data Table, and sets the first cell in the column as "New York". Because the method also returns the newly created parameter, it is possible to use methods or check properties of the new sheet within the same statement.
ParamName=DataTable.AddSheet("MySheet").AddParameter("Arrival", "New York").Name
Note that if a parameter with the name "Arrival" already exists in the sheet, the example above will return "Arrival1" as the actual name assigned to the new parameter.
DeleteParameter Method
Description
Deletes the specified parameter from the sheet in the run-time Data Table.
Syntax
DTSheet.DeleteParameter(ParameterID)
Example
The following example uses the DeleteParameter method to delete the parameter, "Arrival" from the "MySheet" sheet of the run-time Data Table.
DataTable.GetSheet("MySheet").DeleteParameter("Arrival")
Note that deleting a parameter from the sheet will cause the run to fail if a corresponding parameter exists in a step.
GetCurrentRow Method
Description
Returns the row number of the current (active) row in the run-time Data Table sheet.
Syntax
DTSheet.GetCurrentRow
Return Value
Number
Example
The following example uses the GetCurrentRow method to retrieve the row currently being used by the run-time Data Table and writes it to the report.
row = DataTable.GetSheet("MySheet").GetCurrentRow
Reporter.ReportEvent 1, "Row Number", row
GetParameter Method
Description
Retrieves the specified parameter from the run-time Data Table sheet.
Syntax
DTSheet.GetParameter(ParameterID)
Return Value
DTParameter Object.
Example
The following example uses the GetParameter method to return the "Destination" parameter from the run-time Data Table sheet: MySheet.
DataTable.GetSheet("MySheet").GetParameter("Destination")
GetParameterCount Method
Description
Returns the total number of parameters (columns) in the run-time Data Table sheet.
Syntax
DTSheet.GetParameterCount
Return Value
Number
Example
The following example uses the GetParameterCount method to find the total number of parameters (columns) in the run-time Data Table sheet (MySheet) and writes it to the report.
paramcount = DataTable.GetSheet("MySheet").GetParameterCount
Reporter.ReportEvent 2, "There are " &paramcount, "columns in the data sheet."
GetRowCount Method
Description
Returns the total number of rows in the longest column in the run-time Data Table sheet.
Syntax
DTSheet.GetRowCount
Return Value
Number
Example
The following example uses the GetRowCount method to find the total number of rows in the first column of the run-time Data Table sheet (MySheet) and writes it to the report.
rowcount = DataTable.GetSheet("MySheet").GetRowCount
Reporter.ReportEvent 2, "There are " &rowcount, "rows in the data sheet."
SetCurrentRow Method
Description
Sets the specified row as the current (active) row in the run-time Data Table.
Note: You can only set a row that contains at least one value.
Syntax
DTSheet.SetCurrentRow(RowNumber)

Example
The following example uses the SetCurrentRow method to change the active row to the second row in the MySheet run-time data sheet.
DataTable.GetSheet("MySheet").SetCurrentRow(2)
SetNextRow Method
Description
Sets the row after the current (active) row as the new current row in the run-time Data Table sheet.
Note: You can only set a row that contains at least one value. If the current row is the last row in the Data Table, applying this method sets the first row in the Data Table as the new current row.
Syntax
DTSheet.SetNextRow
Example
The following example uses the SetNextRow method to change the active row to the next row in the run-time Data Table.
DataTable.GetSheet("MySheet").SetNextRow
SetPrevRow Method
Description
Sets the row above the current (active) row as the new current (active) row in the run-time Data Table sheet.
Syntax
DTSheet.SetPrevRow
Example
The following example uses the SetPrevRow method to change the active row to the previous row in the run-time data sheet.
DataTable.GetSheet("MySheet").SetPrevRow
Name Property
Description
Returns the name of the run-time data sheet.
Syntax
DTSheet.Name
Example
The following example uses the Name method to return the name of the active run-time data sheet and writes it in the report.
Sheetname = DataTable.LocalSheet.Name
Reporter.ReportEvent 1, "The Active Sheet is", Sheetname
Name Property
Description
The name of the parameter (column) in the run-time Data Table.
Syntax
DTParameter.Name
Example
The following example uses the Name method to return the name of the newly created parameter in the run-time Data Table and writes it in the report.
Dim paramname
paramname = DataTable.LocalSheet.AddParameter("Food", "pizza").Name
Reporter.ReportEvent 1, "The New Parameter name is", paramname
RawValue Property
Description
The raw value of the cell in the current row of the parameter in the run-time Data Table. The raw value is the actual string written in a cell before the cell has been computed, such as the actual text from a formula.
Syntax
DTParameter.RawValue
Example
The following example uses the RawValue property to find the formula used in the current row of the Date column in the ActionA sheet of the run-time Data Table. The statement below returns the value: =NOW()
FormulaVal=DataTable.GetSheet("ActionA").GetParameter("Date").RawValue
Value Property
Description
Parameter default property. Retrieves or sets the value of the cell in the current (active) row of the parameter in the run-time Data Table.
Note: This method returns the computed value of the cell. For example, if the cell contains a formula, the method returns True or False.
Syntax
To find the value:
DTParameter.Value or DTParameter
To set the value:
DTParameter.Value =newvalue or DTParameter =newvalue
Example
The following example uses the Value property to set the value in the current row of the Destination parameter (column) in the "ActionA" sheet of the run-time Data Table.
DataTable.GetSheet("ActionA").GetParameter("Destination").Value="New York"
Note: You could omit the word Value in the statement above, because Value is the default property for the DTParameter object.
ValueByRow Property
Description
Retrieves the value of the cell in the specified row of the parameter in the run-time Data Table.
Syntax
DTParameter.ValueByRow(RowNum)
Example
The following example uses the ValueByRow property to find the value in the 4th row of the Destination parameter (column) in the "ActionA" sheet of the run-time Data Table.
DataTable.GetSheet("ActionA").GetParameter("Destination").ValueByRow(4)



Source : QTP Help
.

4 comments:

  1. Its really very use ful for any Automation tester using QTP tool...

    Tremendous work Venkat, Keep it up.

    ReplyDelete
  2. Hi
    Good job.It s really nice article
    Thanks

    ReplyDelete
  3. Hi,
    Good reference for QTP Datatable . Highly appreciate your efforts.
    Thanks a lot!!

    ReplyDelete
  4. its really a very good reference on DataTable. Great work. thanks a lot.

    ReplyDelete