Read login credentials from Excel and verify login page input fields

Preconditions (4)

Step 1: Open the Excel file 'credentials.xlsx'

Step 2: Read the username and password from the second row of sheet 'LoginData'

Step 3: Open browser and navigate to 'https://example.com/login'

Step 4: Find the username input field and enter the username from Excel

Step 5: Find the password input field and enter the password from Excel

Step 6: Verify that the username input field contains the correct username

Step 7: Verify that the password input field contains the correct password

✅ Expected Result: Username and password fields on the login page contain the values read from the Excel file

Automation Requirements - Selenium WebDriver with TestNG and Apache POI

Assertions Needed:

Assert username input field value equals Excel username

Assert password input field value equals Excel password

Best Practices:

Use explicit waits for element visibility

Use Page Object Model for page interactions

Close Excel file and browser after test

Handle exceptions for file reading

Automated Solution

Selenium Java

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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.Test;

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

public class ExcelDataLoginTest {
    private WebDriver driver;
    private WebDriverWait wait;
    private String username;
    private String password;

    @BeforeClass
    public void setUp() throws IOException {
        // Read Excel file
        try (FileInputStream fis = new FileInputStream("credentials.xlsx")) {
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet sheet = workbook.getSheet("LoginData");
            Row row = sheet.getRow(1); // second row (index 1)
            Cell userCell = row.getCell(0); // Username column
            Cell passCell = row.getCell(1); // Password column
            username = userCell.getStringCellValue();
            password = passCell.getStringCellValue();
            workbook.close();
        }

        // Setup WebDriver
        driver = new ChromeDriver();
        wait = new WebDriverWait(driver, Duration.ofSeconds(10));
    }

    @Test
    public void testLoginFieldsWithExcelData() {
        driver.get("https://example.com/login");

        // Wait for username field and enter username
        WebElement usernameField = wait.until(ExpectedConditions.visibilityOfElementLocated(By.id("username")));
        usernameField.clear();
        usernameField.sendKeys(username);

        // Wait for password field and enter password
        WebElement passwordField = wait.until(ExpectedConditions.visibilityOfElementLocated(By.id("password")));
        passwordField.clear();
        passwordField.sendKeys(password);

        // Assert username field value
        Assert.assertEquals(usernameField.getAttribute("value"), username, "Username input mismatch");

        // Assert password field value
        Assert.assertEquals(passwordField.getAttribute("value"), password, "Password input mismatch");
    }

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

This test reads username and password from an Excel file using Apache POI in the @BeforeClass setup method. It opens the Excel file credentials.xlsx, accesses the sheet LoginData, and reads the second row's first two cells for username and password.

Then it opens the browser and navigates to the login page URL. It waits explicitly for the username and password input fields to be visible before interacting with them. It enters the read username and password into the respective fields.

Assertions check that the input fields contain the exact values read from Excel, ensuring the data was correctly loaded and entered.

The @AfterClass method closes the browser to clean up resources.

This structure follows best practices: explicit waits avoid timing issues, resource cleanup is done, and exceptions during file reading are handled by try-with-resources.

Common Mistakes - 4 Pitfalls

Hardcoding Excel file path without handling exceptions

Using Thread.sleep() instead of explicit waits for elements

Not closing the workbook or file input stream

Using brittle XPath selectors instead of stable IDs or names

Bonus Challenge

Now add data-driven testing to run the login field verification with 3 different username/password pairs from the Excel sheet

Show Hint