如何在QTP中比较两行数据表

How can i compare two rows of datatable in QTP

如何在QTP中比较两行数据表

1 个解决方案

#1

1) Do you want to compare data rows in QTP's native DataTable object?

1)您想比较QTP的本机DataTable对象中的数据行吗?

• Use SetCurrentRow method Example: objDataSheet.SetCurrentRow(intRow)

  使用SetCurrentRow方法示例:objDataSheet.SetCurrentRow(intRow)

• Use GetParameter to access column name and cell value sColName = objDataSheet.GetParameter(j).Name Value by index: sCellValue = objDataSheet.GetParameter(j).Name Value by col name: sCellValue = objDataSheet.GetParameter(sColName).Name

  使用GetParameter访问列名和单元格值sColName = objDataSheet.GetParameter(j).Name索引值:sCellValue = objDataSheet.GetParameter(j).Name按名称列出的值:sCellValue = objDataSheet.GetParameter(sColName).Name

2) Do you want to compare 2 rows in 2 distinct Excel worksheets? You can use the following code (taken from my blog http://automationbeyond.wordpress.com , see other examples too)

2)您想比较2个不同的Excel工作表中的2行吗?您可以使用以下代码(取自我的博客http://automationbeyond.wordpress.com,也参见其他示例)

Excel application must be installed on the PC. Comparison goes cell by cell within the occupied range. Mismatching cells are marked red.

必须在PC上安装Excel应用程序。比较在占用范围内逐个细胞地进行。不匹配的细胞标记为红色。

Both workbooks must exist and be defined as the full path with file. Both worksheets must exist and may be defined as a numeric index or string name.

两个工作簿必须存在并定义为文件的完整路径。两个工作表都必须存在,并且可以定义为数字索引或字符串名称。

objParameter is a reserved parameter to implement custom comparison like “ignore case”, “round up numbers”, etc.

objParameter是一个保留参数,用于实现自定义比较,如“忽略大小写”,“向上舍入数字”等。

Public Function ExcelWorksheetCompare(ByVal sWorkbook1, ByVal sWorksheet1, ByVal sWorkbook2, ByVal sWorksheet2, ByVal objParameter)
Dim boolRC, boolSheetExists
Dim FSO, XLHandle
Dim XLBook1, XLBook2, XLSheet1, XLSheet2
Dim Iter, objCell

‘Verify both files exist
Set FSO = CreateObject(”Scripting.FileSystemObject”)
boolRC = FSO.FileExists(sWorkbook1)
If Not boolRC Then
ExcelWorksheetCompare = FALSE
Exit Function
End If
boolRC = FSO.FileExists(sWorkbook2)
If Not boolRC Then
ExcelWorksheetCompare = FALSE
Exit Function
End If
Set FSO = Nothing

Set XLHandle = CreateObject(”Excel.Application”)
XLHandle.DisplayAlerts = False

‘Open workbook1
Set XLBook1 = XLHandle.WorkBooks.Open(sWorkbook1)

‘Verify sheet exists (1)
If isNumeric(sWorksheet1) Then
sWorksheet1 = CInt(sWorksheet1)
If (sWorksheet1 >0) AND (sWorksheet1<=XLBook1.Worksheets.Count) Then
Set XLSheet1 = XLBook1.Worksheets(sWorksheet1)
boolSheetExists = TRUE
Else
boolSheetExists = FALSE
End If
Else
boolSheetExists = FALSE
For Iter = 1To XLBook1.Worksheets.Count
If XLBook1.Worksheets(Iter).Name = sWorksheet1 Then
Set XLSheet1 = XLBook1.Worksheets(Iter)
boolSheetExists = TRUE
End If
Next
End If

If Not boolSheetExists Then
XLBook1.Close
XLHandle.Quit
Set XLBook1 = Nothing
Set XLHandle = Nothing

ExcelWorksheetCompare = FALSE
Exit Function
End If

‘Open workbook2
Set XLBook2 = XLHandle.WorkBooks.Open(sWorkbook2)

‘Verify sheet exists (2)
If isNumeric(sWorksheet2) Then
sWorksheet2 = CInt(sWorksheet2)
If (sWorksheet2 >0) AND (sWorksheet2<=XLBook2.Worksheets.Count) Then
Set XLSheet2 = XLBook2.Worksheets(sWorksheet2)
boolSheetExists = TRUE
Else
boolSheetExists = FALSE
End If
Else
boolSheetExists = FALSE
For Iter = 1To XLBook2.Worksheets.Count
If XLBook2.Worksheets(Iter).Name = sWorksheet2 Then
Set XLSheet2 = XLBook2.Worksheets(Iter)
boolSheetExists = TRUE
End If
Next
End If

If Not boolSheetExists Then
XLBook1.Close
XLBook2.Close
XLHandle.Quit
Set XLSheet1 = Nothing
Set XLBook1 = Nothing
Set XLBook2 = Nothing
Set XLHandle = Nothing

ExcelWorksheetCompare = FALSE
Exit Function
End If

‘Mark range

‘Compare and mark mismatches red
For Each objCell In XLSheet2.UsedRange
If objCell.Value <> XLSheet1.Range(objCell.Address).Value Then
objCell.Interior.ColorIndex = 3
Else
objCell.Interior.ColorIndex = 0
End If
Next

‘Save and close

XLBook1.Close

XLBook2.Save
XLBook2.Close

XLHandle.Quit

Set XLSheet1 = Nothing
Set XLSheet2 = Nothing
Set XLBook1 = Nothing
Set XLBook2 = Nothing
Set XLHandle = Nothing

ExcelWorksheetCompare = TRUE

End Function