Data table formatting QTP

When data is entered into the data table it automatically formats the value using the best possible matching format. For example, if "12345678901" is entered into a cell then it would be auto formatted to "1.23456789E+010". In situations where the formats are important the data should be entered with care. If data entered in the cell start with a single quote (') then it is always treated as a text and no format conversion is performed.

We can also define a specific format by right clicking the cell or an entire column and then picking a specific format from the popup context menu.

Problem : How to access a parameter from the global data sheet There are a variety of ways to access a parameter from the global data table,most of which are presented in the following code snippet:

'Methods of getting a Data Table value
val = DataTable.Value("ParamName".dtGlobalSheet)
val = DataTable.Value("ParamName" ,"GlbSaSr")
'By giving the sheet index starting from 1 for the global sheet val = DataTable.
Value("ParamName" ,1)
' Sheet name or id is a optional parameter and is assumed
' to be as for global data sheet in case not provided val = DataTable. Value ("ParamName")
' Value property is the default property of the DataTable object
' soDatal"able("ParamName".dtGlobalSheet) is
' equivalent to DataTable.Value("ParamName".dtGlobalSheet)
val = DataTable("ParaiiNaiie" .dtGlobalSheet)
val =DataTableO'ParamName")
'Using the data table object model
val = DataTable.GlobalSheet.GetParameter("ParamName") .Value
'Using the data table object model
val = DataTable.GlobalSheet.GetParameter(ParamName").ValueByRow(l)

Problem, How to access a parameter from a Local data sheet

'Various methods to get data table value
val = DataTable.Value("ParamName".dtLocalSheet)
val = DataTableCParaaNaae" .dtLocalSheet)
Val = DataTable("ParamName""<LocalActionName >")
'The local sheet of the action which is executing this statement val = DataTable.LocalSheet.GetParameter("ParamName").value

Problem . How to check if a Sheet exists

'Function to check if DataTable sheet exists Function isSheetExists(sheetName) On error resume next
isSheetExists = TRUE
Set objSheet= DataTable.GetSheet(sheetName)
'In case error occured sheet does not exist
If err.numbero<>0then
isSheetExists = FALSE
End if End Function

Problem. How to preserve format of data output to a data table

'This would be modified to 1.23456789E+010 due to auto formatting DataTable
("ParastiMaae"*) = "12345678901"
'This will not be auto formatted and will be treated a, s text DataTable("ParamName") = """ & "123456789®!"Problem. How to check if a parameter exists in a specific sheet 'Check if a parameter existsin data table
Function isParameterExists(sheetName, paramName)
On error resume next
isParameterExists= TRUE Err.clear
ParamTotal = DataTable.GetSheet(sheetName).GetParameter(paramName) 'In case of error; the parameter does not exist If err.number<>0 then
isParameterExists = False End if End Function

Problem . How to export contents of a WebTable to a data sheet. Let's assume that the first row of the data table contains the columns heading. We then add those as parameters of the data table:

'Variable declaration Dim i.j
Dim rowCount.colCountDim cellText, objTable
'Get table object
Set objTable = Browser(" ") .Page(" ") .WebTableC")
'Get the row count of the webtablerowCount = objTable.RowCount
'Get the column count of the webtable header row colCount = objTable.ColumnCount(l)
'create a output sheet
Set outSheet = DataTable.AddSheet ("Output")
'Create Parameters based on the 1st row of the web table For i = 2 to colCount
cellText = objTable.GetCellData(l,i)
'Note in case the CellText contains space in between 'then QTP will automatically convert it to a "_" character outSheet.AddParametercellText,"" Next
'Skip first row as we assumed it to be a header row For i = 2 to rowCount
outSheet.SetCurrentRow i-1
're-calculate the column count as some rows "have different column sizes colCount = objTable.ColumnCount(i)
V For j = 2 to colCount
cellText = objTable.GetCellData(i,j)

'We are using index here to avoid >the problem of 'the "_." issue if cell text has spaces or new line chars 'then we will get an error, to overcome that we can also use 'outSheet.GetParameter(Replace(cellText," ","_")).Value outSheet.GetParameter(j-l).value = cellTextNext Next

Problem. How to get value of a parameter from any specific row in the data table.

We use the ValueByRow method to get value for any row 'Get a value by row'Get a value by row

Problem . How to execute a script for all Global Data Table iterations, when the script is set to run for only one iteration:

In case we want to manually repeat the cod/ for each iteration, we need to write a bit code.
'Declare variable Dim i, iCount
'Get the global sheet object
Set oGlobal= DataTable.GlobalSheet
'Get # of rows
iCount = oGlobal.GetRowCount
For i = 1 to iCount
'Set the current row
oGlobal.SetCurrentRow i
'Execute the code to be repeated here
MsgboxDataTable("UserName") Next

Problem . How to get the number of columns that contain data:

To solve this problem we need to utilize the excel formula COUNTA. We add a parameter to the data table with the formula and then read its value:
'Add a new parameter with the formula
'For Columns 1 of data table use A1:A65536
'For column 2 of data table use B1:B65536 andso on

DataTable.GlobalSheet.AddParameter "Men","=COUNTA(AX:A65536)"
'Get the new value
MsgboxDataTable ("Ness")

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

QTP Topics