Excel data reading (Apache POI) in Selenium Java

Introduction

We read Excel files to get test data easily without changing code. It helps run tests with many inputs.

You want to test a login page with many usernames and passwords stored in Excel.

You have a list of products and prices in Excel to check on a website.

You want to run the same test many times with different data from Excel.

You want to keep test data separate from test code for easy updates.

Syntax

Selenium Java

FileInputStream file = new FileInputStream("path/to/file.xlsx");
Workbook workbook = new XSSFWorkbook(file);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(rowIndex);
Cell cell = row.getCell(cellIndex);
String value = cell.getStringCellValue();
workbook.close();
file.close();

Use XSSFWorkbook for .xlsx files and HSSFWorkbook for .xls files.

Always close workbook and file streams to avoid memory leaks.

Examples

Reads the first cell in the first row from the first sheet.

Selenium Java

FileInputStream file = new FileInputStream("data/testdata.xlsx");
Workbook workbook = new XSSFWorkbook(file);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
String username = cell.getStringCellValue();
workbook.close();
file.close();

Reads a numeric value from the third cell in the second row of the sheet named 'Users'.

Selenium Java

FileInputStream file = new FileInputStream("data/testdata.xlsx");
Workbook workbook = new XSSFWorkbook(file);
Sheet sheet = workbook.getSheet("Users");
Row row = sheet.getRow(1);
Cell cell = row.getCell(2);
double price = cell.getNumericCellValue();
workbook.close();
file.close();

Sample Program

This program opens an Excel file named 'testdata.xlsx', reads the first cell in the first sheet, and prints its content.

Selenium Java

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

public class ExcelReadExample {
    public static void main(String[] args) {
        try (FileInputStream file = new FileInputStream("testdata.xlsx");
             Workbook workbook = new XSSFWorkbook(file)) {

            Sheet sheet = workbook.getSheetAt(0);
            Row row = sheet.getRow(0);
            Cell cell = row.getCell(0);
            String data = cell.getStringCellValue();

            System.out.println("First cell data: " + data);

        } catch (Exception e) {
            System.out.println("Error reading Excel file: " + e.getMessage());
        }
    }
}

Important Notes

Make sure the Excel file path is correct and accessible.

Check the cell type before reading to avoid errors (string, numeric, boolean).

Use try-with-resources to automatically close streams and workbook.

Summary

Apache POI lets you read Excel files to get test data.

Use Workbook, Sheet, Row, and Cell objects to access data.

Always close resources to keep your tests clean and efficient.