DataProvider with external data in Selenium Java - Build an Automation Script

Test login functionality with multiple user credentials from external Excel file

Preconditions (3)

Step 1: Open the login page URL in a browser.

Step 2: For each row in the Excel sheet 'LoginData':

Step 3: - Enter the 'username' value into the username input field with id 'username'.

Step 4: - Enter the 'password' value into the password input field with id 'password'.

Step 5: - Click the login button with id 'loginBtn'.

Step 6: - Verify that the URL changes to 'https://example.com/dashboard' indicating successful login.

Step 7: - Log out by clicking the logout button with id 'logoutBtn' to prepare for next iteration.

✅ Expected Result: For each set of credentials from the Excel file, the user successfully logs in and is redirected to the dashboard page.

Automation Requirements - TestNG with Selenium WebDriver

Assertions Needed:

Verify current URL equals 'https://example.com/dashboard' after login

Verify login button is clickable before clicking

Verify logout button is present before clicking logout

Best Practices:

Use TestNG DataProvider to supply test data from external Excel file

Use Page Object Model to separate page interactions

Use explicit waits to wait for elements to be clickable or visible

Close browser after all tests

Handle exceptions gracefully

Automated Solution

Selenium Java

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.support.ui.ExpectedConditions;
import org.openqa.selenium.support.ui.WebDriverWait;
import org.testng.Assert;
import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

import java.io.FileInputStream;
import java.io.IOException;
import java.time.Duration;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class LoginTest {
    private WebDriver driver;
    private WebDriverWait wait;

    @BeforeClass
    public void setUp() {
        // Set path to chromedriver executable if needed
        driver = new ChromeDriver();
        wait = new WebDriverWait(driver, Duration.ofSeconds(10));
        driver.manage().window().maximize();
    }

    @DataProvider(name = "loginData")
    public Object[][] getLoginData() throws IOException {
        String filePath = "src/test/resources/UserCredentials.xlsx";
        FileInputStream fis = new FileInputStream(filePath);
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheet("LoginData");

        int rowCount = sheet.getPhysicalNumberOfRows();
        Object[][] data = new Object[rowCount - 1][2]; // exclude header row

        for (int i = 1; i < rowCount; i++) {
            Row row = sheet.getRow(i);
            Cell usernameCell = row.getCell(0);
            Cell passwordCell = row.getCell(1);
            data[i - 1][0] = usernameCell.getStringCellValue();
            data[i - 1][1] = passwordCell.getStringCellValue();
        }
        workbook.close();
        fis.close();
        return data;
    }

    @Test(dataProvider = "loginData")
    public void testLogin(String username, String password) {
        driver.get("https://example.com/login");

        WebElement usernameInput = wait.until(ExpectedConditions.visibilityOfElementLocated(By.id("username")));
        WebElement passwordInput = driver.findElement(By.id("password"));
        WebElement loginBtn = wait.until(ExpectedConditions.elementToBeClickable(By.id("loginBtn")));

        usernameInput.clear();
        usernameInput.sendKeys(username);
        passwordInput.clear();
        passwordInput.sendKeys(password);
        loginBtn.click();

        wait.until(ExpectedConditions.urlToBe("https://example.com/dashboard"));
        Assert.assertEquals(driver.getCurrentUrl(), "https://example.com/dashboard", "User should be redirected to dashboard after login");

        WebElement logoutBtn = wait.until(ExpectedConditions.elementToBeClickable(By.id("logoutBtn")));
        logoutBtn.click();

        wait.until(ExpectedConditions.urlToBe("https://example.com/login"));
    }

    @AfterClass
    public void tearDown() {
        if (driver != null) {
            driver.quit();
        }
    }
}

This test class uses TestNG and Selenium WebDriver to automate login tests with multiple credentials from an Excel file.

setUp(): Initializes ChromeDriver and WebDriverWait before tests.

getLoginData(): Reads username and password pairs from the Excel file 'UserCredentials.xlsx' using Apache POI library. It skips the header row and returns a 2D Object array for the DataProvider.

testLogin(): For each username and password, it opens the login page, waits for input fields and login button, enters credentials, clicks login, waits for dashboard URL, asserts the URL is correct, then logs out to reset state.

tearDown(): Closes the browser after all tests.

Explicit waits ensure elements are ready before interaction. Assertions verify the expected URL after login. The Page Object Model is not fully implemented here for simplicity but can be added for better structure.

Common Mistakes - 4 Pitfalls

Hardcoding test data inside the test method instead of using DataProvider

Using Thread.sleep() instead of explicit waits

Not closing the Excel file streams after reading data

Using brittle locators like absolute XPath

Bonus Challenge

Now add data-driven testing with 3 different inputs in the Excel file and verify login success or failure message accordingly.

Show Hint

Practice

1. What is the main purpose of using @DataProvider in Selenium tests?

easy

A. To locate web elements on the page

B. To create a new browser instance for each test

C. To generate test reports automatically

D. To run the same test multiple times with different input data

Solution

1. Step 1: Understand the role of DataProvider

   @DataProvider is used to supply different sets of data to a test method so it can run multiple times with varied inputs.

2. Step 2: Compare options with this purpose

   Only To run the same test multiple times with different input data describes running the same test multiple times with different data, which matches the purpose of DataProvider.

3. Final Answer:

   To run the same test multiple times with different input data -> Option D

4. Quick Check:

   DataProvider = multiple test runs with data [OK]

Hint: DataProvider runs tests repeatedly with varied inputs [OK]

Common Mistakes:

• Confusing DataProvider with browser setup
• Thinking DataProvider locates elements
• Assuming DataProvider generates reports

2. Which of the following is the correct syntax to declare a DataProvider method that reads data from an external CSV file?

easy

A. @DataProvider(name = "csvData")
public Object[][] getData() {
// code to read CSV and return Object[][]
}

B. @DataProvider
public void getData() {
// code to read CSV
}

C. @DataProvider(name = "csvData")
public void getData() {
return new Object[][] {};
}

D. @DataProvider(name = "csvData")
public Object getData() {
// code to read CSV
}

Solution

1. Step 1: Check DataProvider method signature

   A DataProvider method must return Object[][] and be public. @DataProvider(name = "csvData") public Object[][] getData() { // code to read CSV and return Object[][] } matches this.

2. Step 2: Verify other options

   @DataProvider public void getData() { // code to read CSV } returns void, which is invalid. @DataProvider(name = "csvData") public void getData() { return new Object[][] {}; } returns void but tries to return data, invalid. @DataProvider(name = "csvData") public Object getData() { // code to read CSV } returns Object, not Object[][], invalid.

3. Final Answer:

   @DataProvider(name = "csvData") public Object[][] getData() { // code to read CSV and return Object[][] } -> Option A

4. Quick Check:

   DataProvider method returns Object[][] [OK]

Hint: DataProvider must return Object[][] and be public [OK]

Common Mistakes:

• Using void return type for DataProvider
• Returning Object instead of Object[][]
• Missing @DataProvider annotation name

3. Given the following DataProvider method reading CSV data, what will be the output when the test runs?

@DataProvider(name = "csvData")
public Object[][] getData() {
  return new Object[][] {
    {"user1", "pass1"},
    {"user2", "pass2"}
  };
}

@Test(dataProvider = "csvData")
public void loginTest(String username, String password) {
  System.out.println(username + ":" + password);
}

medium

A. user1
pass1
user2
pass2

B. user1:pass1
user2:pass2

C. user1pass1user2pass2

D. Compilation error due to DataProvider

Solution

1. Step 1: Understand DataProvider data structure

   The DataProvider returns two sets of data: {"user1", "pass1"} and {"user2", "pass2"}.

2. Step 2: Analyze test method output

   The test runs twice, printing username and password separated by a colon each time, resulting in two lines: "user1:pass1" and "user2:pass2".

3. Final Answer:

   user1:pass1 user2:pass2 -> Option B

4. Quick Check:

   Each data set prints username:password [OK]

Hint: Each data row runs test once, printing combined values [OK]

Common Mistakes:

• Expecting all data printed in one line
• Confusing array structure with output format
• Assuming compilation error without reason

4. Identify the error in this DataProvider method that reads data from a CSV file:

@DataProvider(name = "csvData")
public Object[][] getData() {
  List lines = readCsv("data.csv");
  Object[][] data = new Object[lines.size()][];
  for (int i = 0; i <= lines.size(); i++) {
    data[i] = lines.get(i);
  }
  return data;
}

medium

A. The loop condition should be < instead of <= to avoid IndexOutOfBoundsException

B. The method should return List instead of Object[][]

C. The DataProvider annotation is missing parameters

D. The data array size is incorrect and should be lines.size() + 1

Solution

1. Step 1: Analyze the for loop condition

   The loop runs from i = 0 to i <= lines.size(), which means it tries to access data[lines.size()] causing IndexOutOfBoundsException.

2. Step 2: Correct the loop condition

   The loop should run while i < lines.size() to stay within valid array indices.

3. Final Answer:

   The loop condition should be < instead of <= to avoid IndexOutOfBoundsException -> Option A

4. Quick Check:

   Loop index must be less than array size [OK]

Hint: Loop index must be less than array length, not less or equal [OK]

Common Mistakes:

• Using <= in loops causing index errors
• Returning wrong data type from DataProvider
• Misunderstanding DataProvider annotation usage

5. You want to use a DataProvider that reads test data from a CSV file and filters out rows where the first column is empty before running tests. Which approach correctly implements this?

hard

A. Return the entire CSV data as Object[][] without filtering, and filter inside the test method

B. Filter rows after test execution by skipping tests with empty first column

C. Read all CSV rows into a List, filter out rows with empty first column, then convert filtered list to Object[][] and return

D. Use a DataProvider that returns null for rows with empty first column

Solution

1. Step 1: Understand filtering before test execution

   Filtering data in the DataProvider ensures only valid data sets run tests, keeping tests clean and efficient.

2. Step 2: Implement filtering in DataProvider

   Read CSV into a list, remove rows with empty first column, then convert to Object[][] to supply filtered data to tests.

3. Final Answer:

   Read all CSV rows into a List, filter out rows with empty first column, then convert filtered list to Object[][] and return -> Option C

4. Quick Check:

   Filter data in DataProvider before tests run [OK]

Hint: Filter data inside DataProvider, not inside test method [OK]

Common Mistakes:

• Filtering data inside test method instead of DataProvider
• Returning null for filtered rows causing errors
• Skipping tests after execution instead of filtering input