Random Row from Excel Data Source

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.

    Basic test

  2. Right click step 2 and select Edit in Code.

  3. Enter the below code into the coded step. The searchQuery.xlsx Excel file contains five rows of data.

    • 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.
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;
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