How to Read/Write Excel Data using Apache POI Selenium

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 browser automation. 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 1– Download the Apache POI jar file from the official website and click on the Download section. One can download the
Binary Distribution zip file.

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.

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 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.