✅ Reading Test Data from Excel Using Apache POI
Apache POI is the most common Java library for handling Excel files (.xls and .xlsx).
Below are the essential steps and examples for reading data.
1. Add Apache POI Dependencies
For Maven:
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.5</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.5</version>
</dependency>
</dependencies>
2. Basic Example: Read an Excel Cell
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
public class ExcelReader {
public static void main(String[] args) throws Exception {
FileInputStream fis = new FileInputStream("TestData.xlsx");
Workbook workbook = WorkbookFactory.create(fis);
Sheet sheet = workbook.getSheet("Sheet1");
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
System.out.println(cell.getStringCellValue());
workbook.close();
fis.close();
}
}
3. Utility Method to Read Any Cell
public static String getCellValue(String filePath, String sheetName, int rowNum, int colNum) {
try {
FileInputStream fis = new FileInputStream(filePath);
Workbook wb = WorkbookFactory.create(fis);
Sheet sheet = wb.getSheet(sheetName);
Cell cell = sheet.getRow(rowNum).getCell(colNum);
DataFormatter formatter = new DataFormatter();
String value = formatter.formatCellValue(cell);
wb.close();
fis.close();
return value;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
Usage:
String username = getCellValue("TestData.xlsx", "Login", 1, 0);
System.out.println(username);
4. Read All Rows & Columns (Data-Driven Testing)
Converts entire sheet into a 2D array (common for TestNG DataProviders):
public static Object[][] readSheetData(String filePath, String sheetName) throws Exception {
FileInputStream fis = new FileInputStream(filePath);
Workbook workbook = WorkbookFactory.create(fis);
Sheet sheet = workbook.getSheet(sheetName);
int rows = sheet.getPhysicalNumberOfRows();
int cols = sheet.getRow(0).getPhysicalNumberOfCells();
Object[][] data = new Object[rows - 1][cols]; // skipping header row
DataFormatter formatter = new DataFormatter();
for (int i = 1; i < rows; i++) {
Row row = sheet.getRow(i);
for (int j = 0; j < cols; j++) {
data[i - 1][j] = formatter.formatCellValue(row.getCell(j));
}
}
workbook.close();
fis.close();
return data;
}
Using with TestNG:
@DataProvider(name = "excelData")
public Object[][] excelData() throws Exception {
return readSheetData("TestData.xlsx", "Login");
}
@Test(dataProvider = "excelData")
public void loginTest(String username, String password) {
System.out.println(username + " | " + password);
}
5. Handling Different Cell Types
Using DataFormatter handles most cases best, but if you need explicit type handling:
switch (cell.getCellType()) {
case STRING:
value = cell.getStringCellValue();
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell))
value = cell.getDateCellValue().toString();
else
value = String.valueOf(cell.getNumericCellValue());
break;
case BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA:
value = cell.getCellFormula();
break;
default:
value = "";
}
6. Best Practices for Test Data Handling
✔ Keep test data in a dedicated /resources folder
✔ Use a reusable Excel utility class
✔ Avoid hard-coding row/column counts
✔ Use header row and find columns dynamically if sheet structure may change
✔ Consider switching to CSV or JSON for large data sets (faster, simpler)
Learn Selenium with JAVA Training in Hyderabad
Read More
🔌 Data Handling and Advanced Concepts
How to Create a Base Test Class in Java
Reusable Methods in Java for Selenium Tests
How to Use Java Streams in Selenium Automation
Visit Our Quality Thought Institute in Hyderabad
Subscribe by Email
Follow Updates Articles from This Blog via Email
No Comments