Using Excel Sheets in Selenium Projects
Why use Excel? Excel sheets serve as a centralized, non-technical data source for test data. Business analysts or QA managers can update test data without touching code.
Primary use cases:
- ✓Data-Driven Testing — Store login credentials, search terms, form values
- ✓Test Data Management — Maintain datasets for multiple environments
- ✓Result Storage — Write pass/fail status back to the sheet
How to use (Apache POI + TestNG @DataProvider):
Excel structure (testdata.xlsx):
| username | password | expectedTitle |
|---|---|---|
| user1 | pass1 | Dashboard |
| user2 | pass2 | Dashboard |
| invaliduser | wrongpass | Login Error |
ExcelReader utility class:
Java
import org.apache.poi.xssf.usermodel.*;
import java.io.FileInputStream;
public class ExcelUtils {
private XSSFWorkbook workbook;
private XSSFSheet sheet;
public ExcelUtils(String filePath, String sheetName) throws Exception {
FileInputStream fis = new FileInputStream(filePath);
workbook = new XSSFWorkbook(fis);
sheet = workbook.getSheet(sheetName);
}
public String getCellData(int row, int col) {
return sheet.getRow(row).getCell(col).getStringCellValue();
}
public int getRowCount() {
return sheet.getLastRowNum();
}
}
TestNG DataProvider reading from Excel:
Java
public class LoginTest {
ExcelUtils excel;
@DataProvider(name = "loginData")
public Object[][] getLoginData() throws Exception {
excel = new ExcelUtils("src/test/resources/testdata.xlsx", "LoginSheet");
int rows = excel.getRowCount();
Object[][] data = new Object[rows][2];
for (int i = 1; i <= rows; i++) {
data[i-1][0] = excel.getCellData(i, 0); // username
data[i-1][1] = excel.getCellData(i, 1); // password
}
return data;
}
@Test(dataProvider = "loginData")
public void loginTest(String username, String password) {
driver.findElement(By.id("username")).sendKeys(username);
driver.findElement(By.id("password")).sendKeys(password);
driver.findElement(By.id("loginBtn")).click();
}
}
Key benefits:
- ✓Non-developers can manage test data
- ✓No code recompilation when data changes
- ✓Single source of truth for test inputs
- ✓Scales easily to hundreds of data rows