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