Reading Test Data from Excel using Apache POI

 What is Apache POI?

Apache POI is a popular Java library used to read and write Microsoft Office files, including Excel (.xls and .xlsx). It’s often used in test automation or data-driven testing to read test data from Excel sheets.

How to Read Data from Excel Using Apache POI

Add Apache POI to your project dependencies

If you’re using Maven, add these to your pom.xml:

xml

Copy

Edit

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi</artifactId>

    <version>5.2.3</version>

</dependency>

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi-ooxml</artifactId>

    <version>5.2.3</version>

</dependency>

(Replace version with the latest stable one if needed.)

Sample Code to Read Excel Data

java

Copy

Edit

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) {

        String excelFilePath = "TestData.xlsx";  // Path to your Excel file

        

        try (FileInputStream fis = new FileInputStream(excelFilePath);

             Workbook workbook = new XSSFWorkbook(fis)) {  // For .xlsx files

            Sheet sheet = workbook.getSheetAt(0);  // Get first sheet

            // Iterate through rows

            for (Row row : sheet) {

                // Iterate through cells in each row

                for (Cell cell : row) {

                    switch (cell.getCellType()) {

                        case STRING:

                            System.out.print(cell.getStringCellValue() + "\t");

                            break;

                        case NUMERIC:

                            if (DateUtil.isCellDateFormatted(cell)) {

                                System.out.print(cell.getDateCellValue() + "\t");

                            } else {

                                System.out.print(cell.getNumericCellValue() + "\t");

                            }

                            break;

                        case BOOLEAN:

                            System.out.print(cell.getBooleanCellValue() + "\t");

                            break;

                        case FORMULA:

                            System.out.print(cell.getCellFormula() + "\t");

                            break;

                        case BLANK:

                            System.out.print("BLANK\t");

                            break;

                        default:

                            System.out.print("UNKNOWN\t");

                    }

                }

                System.out.println();

            }

        } catch (IOException e) {

            e.printStackTrace();

        }

    }

}

Explanation:

FileInputStream opens the Excel file.

Workbook represents the entire Excel file (XSSFWorkbook is for .xlsx files; use HSSFWorkbook for older .xls).

Sheet gets the sheet you want (0 = first sheet).

Iterate over each row and cell.

Read cell values depending on the type (string, numeric, boolean, formula, etc.).

Print or process the data as needed.

Notes:

For .xls files (Excel 97-2003), use HSSFWorkbook instead of XSSFWorkbook.

You can extend this to read specific rows or columns, or to load data into Java objects.

Always close the file streams properly (try-with-resources helps here).

Handle different data types carefully to avoid exceptions.

Learn Selenium JAVA Course 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 IHUB Talent Training in Hyderabad

Get Directions