How to Read Data from Excel in TestNG and Selenium

TestNG

When building test automation frameworks with Selenium WebDriver and TestNG, one of the most common challenges is managing large sets of input data. Hardcoding values into test methods may work for small-scale tests, but for enterprise-level or real-world automation, this approach quickly becomes unscalable and brittle.

To solve this, we often turn to data-driven testing, where test inputs are maintained in external files, and Excel is a favourite among QA engineers due to its flexibility and widespread use.

Note: We have also attached a video at the end of this article. In case you are more comfortable with the video version, then please feel free to have a look

In this detailed guide, you’ll learn:

  • Why Excel is useful in automation testing
  • How to read data from Excel using Apache POI
  • How to pass that data into TestNG using the DataProvider feature
  • How to integrate it with Selenium test cases
Table of contents –
Why Use Excel in Test Automation?

Let’s take a moment to understand the value Excel brings to the table.

Flexibility

Testers or business stakeholders can easily modify test input values without touching any Java code. This decoupling of data and logic leads to more maintainable test scripts.

Scalability

Instead of duplicating test cases for different inputs, you define them once and iterate over them with varying data points from Excel. Whether you have 5 or 500 rows of data, the framework remains consistent.

Visibility

Anyone (even those who don’t code) can open an Excel sheet and review the test inputs, enhancing collaboration across teams.

Tools and Technologies Used
  • Selenium – UI automation for web testing
  • TestNG – Testing framework with DataProvider support
  • Apache POI – Java library to read Excel files
  • Maven – Build tool and dependency manager

Let’s start by setting up our Maven project.

Step 1: Project Setup with Maven

You’ll need to include the following dependencies in your pom.xml. These will bring in TestNG, Selenium, and Apache POI:

<dependencies>
    <!-- Selenium -->
    <dependency>
        <groupId>org.seleniumhq.selenium</groupId>
        <artifactId>selenium-java</artifactId>
        <version>4.20.0</version>
    </dependency>

    <!-- TestNG -->
    <dependency>
        <groupId>org.testng</groupId>
        <artifactId>testng</artifactId>
        <version>7.10.1</version>
    </dependency>

    <!-- Apache POI -->
    <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>

Once the dependencies are added, you’re ready to start building the test framework.

Step 2: Creating the Sample Excel File

Create a file named testdata.xlsx under src/main/resources/.

testData.xlsx file

In this file, create a sheet (let’s name it Sheet1) with the following content:

Excel sheet

Here is the sample file

testData Excel fileDownload

Each row here represents a different test scenario.

Step 3: Reading Data from Excel Using Apache POI

To read Excel data in Java, we use the Apache POI library, which provides classes to read .xls and .xlsx files. We’ll write a utility method that reads test data from a given sheet and returns it as a two-dimensional array (Object[][]), which TestNG can consume in a @DataProvider.

Here is the complete utility class with step-by-step inline comments to help you understand what each part of the code does:

Here’s the code for the utility class:

import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.FileInputStream;

public class ExcelUtils {

    /**
     * This method reads Excel data and returns it in a 2D Object array format.
     * @param filePath   Path to the Excel file (e.g., "src/main/resources/testdata.xlsx")
     * @param sheetName  Name of the sheet inside Excel (e.g., "Sheet1")
     * @return           2D Object array containing Excel data
     */
    public static Object[][] getExcelData(String filePath, String sheetName) {
        Object[][] data = null;

        try {
            // Step 1: Open the Excel file using FileInputStream
            FileInputStream fis = new FileInputStream(new File(filePath));

            // Step 2: Create a Workbook instance using Apache POI's factory method
            Workbook workbook = WorkbookFactory.create(fis); // Supports both .xls and .xlsx

            // Step 3: Get the desired sheet from the workbook
            Sheet sheet = workbook.getSheet(sheetName);

            // Step 4: Determine number of rows and columns
            int rowCount = sheet.getPhysicalNumberOfRows(); // Total number of rows (including header)
            int colCount = sheet.getRow(0).getLastCellNum(); // Total number of columns in the header row

            // Step 5: Create a 2D array of size [rows - 1][columns]
            // We subtract 1 from rowCount to skip the header row (assumed to be at index 0)
            data = new Object[rowCount - 1][colCount];

            // Step 6: Loop through the rows and columns to read the data
            for (int i = 1; i < rowCount; i++) { // Start from 1 to skip the header row
                Row row = sheet.getRow(i); // Get each row starting from index 1

                for (int j = 0; j < colCount; j++) {
                    Cell cell = row.getCell(j); // Get the cell at column j

                    // Step 7: Read the cell value and store it as a string in the array
                    // cell.toString() safely converts numeric and string values
                    data[i - 1][j] = cell.toString(); // i-1 because our data array starts from 0
                }
            }

            // Step 8: Close resources
            workbook.close();
            fis.close();
        } catch (Exception e) {
            e.printStackTrace(); // Print stack trace if anything goes wrong
        }

        return data; // Return the populated Object[][] array
    }
}

Line-by-Line Summary

  • FileInputStream fis = new FileInputStream(…)
    • Opens the Excel file for reading.
  • WorkbookFactory.create(fis)
    • Creates a Workbook instance that works with both .xls and .xlsx
  • sheet.getPhysicalNumberOfRows()
    • Counts the number of non-empty rows in the sheet (including the header).
  • sheet.getRow(0).getLastCellNum()
    • Gets the number of columns in the header row.
  • data = new Object[rowCount – 1][colCount]
    • Initializes a 2D array for storing data (excluding the header).
  • cell.toString()
    • Reads and converts the cell value to a string. It safely handles different data types like text, numbers, etc.
  • workbook.close() and fis.close()
    • Always close streams to avoid memory/resource leaks.
Step 4: Supplying Excel Data to TestNG Using DataProvider

TestNG’s @DataProvider annotation is the bridge between your test logic and the Excel data. You define a method that returns Object[][], and TestNG will call your test method once for each row of data.

import codekru.ExcelUtils;
import org.testng.annotations.*;

public class CodekruTest {

    @DataProvider(name = "testData")
    public Object[][] testDataProvider() {
        String filePath = "src/main/resources/testdata.xlsx";
        return ExcelUtils.getExcelData(filePath, "Sheet1");
    }

    @Test(dataProvider = "testData")
    public void test1(String url) {
        System.out.println("Url: " + url);

    }

}

Output – 

Url: https://testkru.com/Elements/TextFields
Url: https://testkru.com/Elements/TextMessages
How This Works:
  • TestNG automatically runs the test method multiple times, once for each row in the Excel file.
  • Each column value is passed as a method parameter.
Step 5: Integrating with Selenium WebDriver

Let’s take it one step further and use this data in a UI automation step.

Below is a sample Selenium test that uses the URLs from Excel and opens them:

import codekru.ExcelUtils;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.testng.annotations.*;

public class CodekruTest {

    @DataProvider(name = "testData")
    public Object[][] testDataProvider() {
        String filePath = "src/main/resources/testdata.xlsx";
        return ExcelUtils.getExcelData(filePath, "Sheet1");
    }

    @Test(dataProvider = "testData")
    public void test1(String url) {

        WebDriver driver = new ChromeDriver();
        driver.manage().window().maximize();
        driver.get(url);
        driver.quit();

    }

}

Implementing data-driven testing using Excel files in TestNG and Selenium is a powerful, scalable, and maintainable approach to test automation. It allows you to decouple test logic from data, making your framework cleaner and easier to manage.

Whether you’re testing a login form or validating a complex workflow, this strategy will help you run tests across multiple data sets efficiently.

We hope that you have liked this article. If you have any doubts or concerns, please write to us in the comments or email us at admin@codekru.com.

Video Tutorial
Related Articles –

Guide to DataProviders in TestNG ( Data-driven testing )

Liked the article? Share this on

Leave a Comment

Your email address will not be published. Required fields are marked *