How to Read/Write Excel Data using Apache POI Selenium

Automated tests often require input and verification data stored in Excel files, but reading and updating this data reliably can be challenging. Apache POI provides the APIs needed to work with Excel files directly in Java, making it ideal for use with Selenium WebDriver.

This article explains how to read and write Excel files in both .xls and .xlsx formats using Apache POI in Selenium.

What is Apache POI?

Apache POI is an open-source Java library often utilized to create and handle Microsoft Office-based files. Users can leverage POI to perform various operations (modify, create, display, read) on specific file formats (Excel files being one of them).

Since Java does not offer built-in support for Excel files, testers need open-source APIs to work with them. Apache POI provides a Java API that lets users operate and maneuver file formats built on the Office Open XML (OOXML) standard and Microsoft’s OLE2 standard.

To create or maintain Excel Workbooks, Apache POI provides a ”Workbook” as a super-interface of all classes. It belongs to org.apache.poi.ss.usermodel package. It uses WorkbookFactory class to create the appropriate workbook (i.e. HSSFWorkbook or XSSFWorkbook). The two classes which implement the “Workbook” interface are given below:

• HSSFWorkbook– Methods of this class are used to read or write data to Microsoft Excel file in .xls format.
• XSSFWorkbook– Methods of this class are used to read/write data to Microsoft Excel and OpenOffice XML files in .xls or .xlsx format.

Apache POI Installation

Step 1– Download the Apache POI jar file from the official website.

Step 2 – Once the zip file is downloaded, extract it and save it.

Step 3 – Configure the build path in Eclipse and add all the POI external jars listed below.

Once all the Jar files are added, the user can read and write the data from and to Excel files.

Note: Apache POI has two sets of classes for working with Excel files:

• HSSF (HSSFWorkbook/HSSFSheet/HSSFRow/HSSFCell): For .xls files (Excel 97–2003).
• XSSF (XSSFWorkbook/XSSFSheet/XSSFRow/XSSFCell): For .xlsx files (Excel 2007 and later).

Choose the right one based on your file format.

Read Data from Excel File in Selenium

The code below is used to read the data from the sample Excel sheet in Selenium. This is the excel sheet data that will be used for reading data in this example.

import org.openqa.selenium.WebDriver;
import org.openqa.selenium.remote.DesiredCapabilities;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class BrowserStackReadExcelTest {
public static void main (String [] args) throws IOException{
//Path of the excel file
FileInputStream fs = new FileInputStream("D:\\DemoFile.xlsx");
//Creating a workbook
XSSFWorkbook workbook = new XSSFWorkbook(fs);
XSSFSheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
System.out.println(sheet.getRow(0).getCell(0));
Row row1 = sheet.getRow(1);
Cell cell1 = row1.getCell(1);
System.out.println(sheet.getRow(0).getCell(1));
Row row2 = sheet.getRow(1);
Cell cell2 = row2.getCell(1);
System.out.println(sheet.getRow(1).getCell(0));
Row row3 = sheet.getRow(1);
Cell cell3 = row3.getCell(1);
System.out.println(sheet.getRow(1).getCell(1));
//String cellval = cell.getStringCellValue();
//System.out.println(cellval);
}
}

In this example, specific rows and columns are accessed to read individual cell values. To read the entire Excel file, loop through all rows and columns, as shown below.

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

public class ApachePOI {

    public static void main(String[] args) throws IOException {

        // Path of the Excel file

        File file = new File("E:\\TestData\\TestData.xls");

        // Create an object of FileInputStream to read the Excel file

        FileInputStream inputStream = new FileInputStream(file);

        // Create workbook instance for .xls files

        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);

        // Get the sheet

        HSSFSheet sheet = workbook.getSheet("STUDENT_DATA");

        // Get total rows

        int rowCount = sheet.getLastRowNum() - sheet.getFirstRowNum();

        // Loop through all rows

        for (int i = 0; i <= rowCount; i++) {

            // Get total columns in the row

            int cellCount = sheet.getRow(i).getLastCellNum();

            System.out.println("Row " + i + " data:");

            // Loop through columns in the row

            for (int j = 0; j < cellCount; j++) {

                System.out.print(sheet.getRow(i).getCell(j).getStringCellValue() + ",");

            }

            System.out.println();

        }

    }

}

Write Data into Excel File in Selenium

The code below shows how to write data into an Excel file using Apache POI. It opens an existing workbook, iterates through each row, and creates a new cell in the third column (index 2) to write the value WriteintoExcel.

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.openqa.selenium.remote.DesiredCapabilities;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteintoExcel {
public static void main(String[] args) throws IOException {
String path = "D://DemoFile.xlsx";
FileInputStream fs = new FileInputStream(path);
Workbook wb = new XSSFWorkbook(fs);
Sheet sheet1 = wb.getSheetAt(0);
int lastRow = sheet1.getLastRowNum();
for(int i=0; i<=lastRow; i++){
Row row = sheet1.getRow(i);
Cell cell = row.createCell(2);

cell.setCellValue("WriteintoExcel");

}

FileOutputStream fos = new FileOutputStream(path);
wb.write(fos);
fos.close();
}

}

In the code below, based on the cell value WriteintoExcel, data will be written as depicted below.

How BrowserStack Helps in Selenium Testing

BrowserStack provides a cloud platform where Selenium tests can run on 3,500+ browsers and devices. When you use Apache POI to manage test data, you can feed that data into Selenium scripts and execute them on BrowserStack’s infrastructure.

This allows you to test the same scenarios across different environments, such as Chrome on Windows or Safari on macOS, without setting up a local device lab. Its seamless integration with CI/CD pipelines means automated tests can be triggered with every build, providing quick feedback and ensuring consistent quality across environments.

Talk to an Expert

Conclusion

Apache POI allows you to read and write test data from Excel files, making it easy to build data‑driven Selenium tests. This separates test logic from test data and simplifies maintaining or updating test cases.

BrowserStack adds value to this setup by providing a cloud environment to run those Selenium tests across many browsers and devices. Its support for parallel execution and seamless CI/CD integration makes it ideal for teams that want to run data‑driven tests quickly and reliably across different platforms.

Run Selenium Tests