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 -

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

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

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

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

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 and write the data into Excel Files using Selenium Webdriver and Apache POI. 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 guiding automation effort with Excel files.

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