Interview #191: Selenium - Write code to read data from excel File

Selenium WebDriver by itself does not support reading Excel files. However, in real-world test automation projects, we often need to fetch test data from external sources, and Excel is a common choice. To read from Excel in Java-based Selenium projects, we typically use libraries like:

• Apache POI – Most widely used for .xls and .xlsx files.
• JExcel (JXL) – Older and supports only .xls.

This explanation focuses on Apache POI, the most modern and feature-rich solution.

Disclaimer: For QA-Testing Jobs, WhatsApp us @ 91-6232667387

🔷 Apache POI Overview

Apache POI provides Java APIs for reading and writing Microsoft Office documents. For Excel:

• HSSF – Used for .xls (Excel 97-2003)
• XSSF – Used for .xlsx (Excel 2007+)
• WorkbookFactory – A smart factory to handle both types.

✅ 1. Add Apache POI Dependencies

If you're using Maven, add the following to pom.xml:

<dependencies>
    <!-- Apache POI Core -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.5</version>
    </dependency>

    <!-- Apache POI for Excel (.xlsx) -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.5</version>
    </dependency>
</dependencies>        

✅ 2. Excel File Example

Let’s assume you have the following Excel file TestData.xlsx:

✅ 3. Java Code to Read Excel Data Using Apache POI

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;

public class ExcelReader {

    public static void main(String[] args) throws IOException {
        String filePath = "src/test/resources/TestData.xlsx";
        FileInputStream fis = new FileInputStream(filePath);

        Workbook workbook = new XSSFWorkbook(fis); // For .xlsx
        Sheet sheet = workbook.getSheet("Sheet1"); // Sheet name or index

        int rows = sheet.getPhysicalNumberOfRows();
        int cols = sheet.getRow(0).getLastCellNum();

        for (int i = 1; i < rows; i++) { // i=1 to skip header
            Row row = sheet.getRow(i);
            for (int j = 0; j < cols; j++) {
                Cell cell = row.getCell(j);

                switch (cell.getCellType()) {
                    case STRING:
                        System.out.print(cell.getStringCellValue() + " ");
                        break;
                    case NUMERIC:
                        System.out.print(cell.getNumericCellValue() + " ");
                        break;
                    case BOOLEAN:
                        System.out.print(cell.getBooleanCellValue() + " ");
                        break;
                    default:
                        System.out.print("Unknown ");
                }
            }
            System.out.println(); // New line for each row
        }

        workbook.close();
        fis.close();
    }
}        

✅ 4. Using Excel Data in Selenium Test

You can create a utility method to return data as a 2D array for use with data providers in TestNG.

🔹 Excel Utility Class

public class ExcelUtils {

    public static String[][] getSheetData(String filePath, String sheetName) throws IOException {
        FileInputStream fis = new FileInputStream(filePath);
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheet(sheetName);

        int rowCount = sheet.getPhysicalNumberOfRows();
        int colCount = sheet.getRow(0).getLastCellNum();

        String[][] data = new String[rowCount - 1][colCount];

        for (int i = 1; i < rowCount; i++) {
            Row row = sheet.getRow(i);
            for (int j = 0; j < colCount; j++) {
                data[i - 1][j] = row.getCell(j).getStringCellValue();
            }
        }

        workbook.close();
        fis.close();

        return data;
    }
}        

🔹 Use with TestNG DataProvider

import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class LoginTest {

    @DataProvider(name = "loginData")
    public Object[][] loginData() throws IOException {
        return ExcelUtils.getSheetData("src/test/resources/TestData.xlsx", "Sheet1");
    }

    @Test(dataProvider = "loginData")
    public void loginTest(String username, String password) {
        System.out.println("Logging in with: " + username + " / " + password);
        // Selenium code here
    }
}        

🔍 Tips

• Always close workbook and input streams to avoid memory leaks.
• Handle file not found and null pointer exceptions gracefully.
• For large Excel files, consider using Streaming API (SXSSF) from Apache POI.

🔚 Conclusion

Reading data from Excel in Selenium is an essential skill for data-driven testing. Using Apache POI, you can extract data from .xls or .xlsx files and inject it into your test cases using frameworks like TestNG or JUnit. This approach enhances reusability, scalability, and test coverage of your Selenium test suite.