Random Row from Excel Data Source

PROBLEM

Is there a way to data drive a step in a test script so that each time the script is run, the test step will select a random row from the Excel data source?

SOLUTION

In code, generate a random number based on the number of rows. Then use the corresponding text from that row's cell in the applicable test step.

 

  1. Create a basic test against Bing.com.

    2.  

     

  2. Right click step 2 and select Customize Step in Code.
  3. Enter the below code into the coded step. The searchQuery.xlsx Excel file contains five rows of data.

    4.  

    • Note: Ensure you Add an Assembly Reference to Microsoft.Office.Interop.Excel. You can download a version of that file on Microsoft's website that matches your version of MS Office.

 

C#
Random random = new Random();
int num = random.Next(1, 6);
 
string input = @"C:\Data\searchQuery.xlsx";
 
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook inputBook = app.Workbooks.Open(input, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Worksheet inputSheet = (Microsoft.Office.Interop.Excel.Worksheet)((inputBook.Worksheets).get_Item(1));
             
string value = ((Microsoft.Office.Interop.Excel.Range)inputSheet.Cells[num, 1]).Text as string;
app.Quit();
app = null;
 
Pages.Bing.SbFormQText.Text = value;

 

Visual Basic
Dim random As New Random()
Dim num As Integer = random.[Next](1, 6)
 
Dim input As String = "C:\Data\searchQuery.xlsx"
 
Dim app As New Microsoft.Office.Interop.Excel.Application()
Dim inputBook As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Open(input, 0, False, 5, "", "", _
    False, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", True, False, 0, _
    True, False, False)
Dim inputSheet As Microsoft.Office.Interop.Excel.Worksheet = DirectCast((inputBook.Worksheets.Item(1)), Microsoft.Office.Interop.Excel.Worksheet)
 
Dim value As String = TryCast(DirectCast(inputSheet.Cells(num, 1), Microsoft.Office.Interop.Excel.Range).Text, String)
app.Quit()
app = Nothing
 
Pages.Bing.SbFormQText.Text = value