Home Guide How to Read and Write an Excel File using Selenium WebDriver

How to Read and Write an Excel File using Selenium WebDriver

By Neha Vaidya, Community Contributor -

Table of Contents

Automation testers have to automate a large number of end-user actions in order to monitor, evaluate and verify website functionality. Essentially, they have to 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 Java using Selenium WebDriver.

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 certain 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 offers such a Java API that lets users operate and manoeuvre file formats built on the Office Open XML (OOXML) standard and Microsoft’s OLE2 standard.

In order 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 for creating the appropriate kind of 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 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.
Read and Write Excel Data using Selenium webdriver

Step 2 – Once the zip file is downloaded, extract the zip file 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 is now ready to read and write the data from and to Excel files.
Read Data from excel using Selenium webdriver

Read Data from Excel File in Selenium

The code below is used to read the data from the sample Excel sheet using 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 for Free

Write Data into Excel File in Selenium

The code below is used to write data into an Excel file using 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 with speed and accuracy. 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 Selenium Webdriver

Featured Articles

How to Build and Execute Selenium Projects

Data Driven Framework in Selenium

How to Generate Extent Reports in Selenium

BrowserStack Logo Run Selenium Tests on 2000+ Browsers & Devices Contact us Get Started Free