In Indian IT companies, Excel rules. Business analysts, managers, and QA leads all think in spreadsheets. They want to open a file, add a row, and see a new test case run. Apache POI is the Java library that reads and writes Excel files. It has been the industry standard for over 15 years.
<!-- Add to pom.xml inside <dependencies> -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.5</version>
<scope>test</scope>
</dependency>poi-ooxml handles .xlsx files (modern Excel format). It pulls in poi (core) and xmlbeans as transitive dependencies. You do not need to add those separately.
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
import org.testng.Assert;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
public class BankingLoginExcelTest extends BaseTest {
@DataProvider(name = "loginDataFromExcel")
public Object[][] readExcelData() throws IOException {
List<Object[]> data = new ArrayList<>();
String filePath =
"src/test/resources/test-data/login-data.xlsx";
try (FileInputStream fis = new FileInputStream(filePath);
Workbook workbook = new XSSFWorkbook(fis)) {
Sheet sheet = workbook.getSheet("BankingLogin");
if (sheet == null) {
throw new RuntimeException(
"Sheet 'BankingLogin' not found in "
+ filePath);
}
// Row 0 is header, start from row 1
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (row == null) continue;
String username = getCellValue(row.getCell(0));
String password = getCellValue(row.getCell(1));
String expected = getCellValue(row.getCell(2));
String errorMsg = getCellValue(row.getCell(3));
data.add(new Object[] {
username, password, expected, errorMsg
});
}
}
return data.toArray(new Object[0][]);
}
private String getCellValue(Cell cell) {
if (cell == null) return "";
return switch (cell.getCellType()) {
case STRING -> cell.getStringCellValue();
case NUMERIC -> {
double num = cell.getNumericCellValue();
// Avoid "123.0" for whole numbers
if (num == Math.floor(num)) {
yield String.valueOf((long) num);
}
yield String.valueOf(num);
}
case BOOLEAN -> String.valueOf(
cell.getBooleanCellValue());
case BLANK -> "";
default -> "";
};
}
@Test(dataProvider = "loginDataFromExcel")
public void testBankingLogin(String username, String password,
String expected,
String errorMsg) {
navigateTo("/banking");
LoginPage loginPage = new LoginPage(driver);
loginPage.loginAs(username, password);
if ("success".equals(expected)) {
Assert.assertTrue(
new DashboardPage(driver).isLoaded(),
"Dashboard should load for: " + username);
} else {
Assert.assertTrue(loginPage.isErrorDisplayed(),
"Error should appear for: " + username);
if (!errorMsg.isEmpty()) {
Assert.assertEquals(
loginPage.getErrorMessage(), errorMsg);
}
}
}
}The getCellValue() helper is not optional. Without it, calling getStringCellValue() on a numeric cell throws IllegalStateException. Every cell type needs its own handling. This is the single most common Apache POI bug in automation frameworks.
Q: Which library do you use for reading Excel files and why?
A: We use Apache POI, specifically the poi-ooxml artifact for .xlsx files. It is the industry standard for Java-Excel integration — stable, well-documented, and handles all cell types (string, numeric, boolean, date, formula). We have a reusable ExcelUtils class with a getCellValue() helper that safely handles every cell type including null cells and dates.
In interviews, they always ask about the getCellValue() helper. Be ready to explain how you handle different cell types — especially the numeric-to-string conversion where you avoid the ".0" suffix for whole numbers.
Key Point: Apache POI reads Excel files. Always use a getCellValue() helper to handle different cell types safely.