App & Browser Testing Made Easy

Give your users a seamless experience by testing on 3000+ real devices and browsers. Don't compromise with emulators and simulators

Home Guide How to Read/Write Excel Data using Apache POI Selenium

How to Read/Write Excel Data using Apache POI Selenium

By Neha Vaidya, Community Contributor -

Table of Contents

Automation testers have to automate a large number of end-user actions to monitor, evaluate and verify website functionality. Essentially, they must observe how a site behaves when users interact with its various features and offerings. This article will focus on how to automate one such user action – how to read data from an Excel file in Selenium WebDriver using Java.

Selenium is a widely used automation testing tool for web browser testing. The Java programming language provides different classes or interfaces to perform file manipulation actions.

Apache POI libraries are used to perform such operations. Some of the interfaces to read or write data from external resources are given below:

  1. POIFS (Poor Obfuscation Implementation File System)
  2. HSSF (Horrible Spreadsheet Format)
  3. XSSF (XML Spreadsheet Format)
  4. HPSF (Horrible Property Set Format)
  5. HWPF (Horrible Word Processor Format)
  6. XWPF (XML Word Processor Format)
  7. HSLF (Horrible Slide Layout Format)
  8. HGDF (Horrible Diagram Format)
  9. HDBF (Horrible PuBlisher Format)

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.

Now let’s understand how to configure apache POI Selenium in the system.

Apache POI Installation

Step 1Download the Apache POI jar file from the official website and click on the Download section. One can download the
Binary Distribution zip file.
Download Apache POI

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.Apache POI Selenium - JAR Files

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.
Reading data from excel - output

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 the code, based on the cell and row values, the data will be read and retrieved from the Excel files. Now let’s understand how to write data into the Excel file.

Run Selenium Tests

Write Data into Excel File in Selenium

The code below is used to write data into an Excel file in Selenium.

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.

This is how to read Excel files in Selenium WebDriver using Apache POI. Given how popular Excel sheets are for data cataloging and display, knowing how to handle Excel in Selenium is a valuable skill for testers.  Since automation testing must cover as many user scenarios as possible, it is important to know how to code and execute those scenarios quickly and accurately. This article aims to add to a Selenium tester’s skillset by demonstrating how to read data from Excel in Selenium WebDriver using POI libraries.

Tags
Automation Testing Selenium

Featured Articles

How to read Config Files in Python using Selenium

Understanding Role of Thread.sleep() in Selenium

Curated for all your Testing Needs

Actionable Insights, Tips, & Tutorials delivered in your Inbox
By subscribing , you agree to our Privacy Policy.
thank you illustration

Thank you for Subscribing!

Expect a curated list of guides shortly.