Read and write excel file in Selenium using Apache POI

In this tutorial, we will learn how to Read and Write excel files in Selenium.

We will cover below topics in this tutorial

Introduction to Apache POI
Java Interfaces in Apache POI
Classes in Apache POI (for .xls extension)
Classes in Apache POI (for .xlsx extension)
How to read a specific cell value?
How to read the entire Excel sheet?
Write Data into Excel File in Selenium

Introduction to Apache POI

Selenium support only web UI automation so for reading and wring excel file , third party tool or API is required for e.g. Apache POI , JExcel etc.

Apache provides a famous library POI to read or write an Excel. This library is used to read and write both XLS and XLSX file format of Excel.

An HSSF implementation is provided by the POI library to read XLS files.

And XSSF implementation is provided by the POI library to read XLSX files.

Below Is the maven dependency if you are using Maven project and needs to be added in pom.xml



    org.apache.poi
    poi
    5.0.0

And if it is a generic project then POI jar file can be download from the http://poi.apache.org/download.html and you can add jar file in the project.

Below are few classes and interfaces if you are using .xls and .xlsx file

Java Interfaces in Apache POI

Workbook– XSSFWorkbook and HSSFWorkbook classes implement this interface.
Sheet– This interface is implemented to read the sheet inside the workbook
Row– This interface is used to identify the row inside the sheet of the different types of excel sheet
Cell– This interface is implemented to identify the corresponding cell of the given row of the different types of excel sheet

Classes in Apache POI (for .xls extension)

HSSFWorkbook: Is a class representation of XLS file.
HSSFSheet: representing a sheet in an XLS file.
HSSFRow :representing a row in the sheet of XLS file.
HSSFCell: representing a cell in a row of XLS file.

Classes in Apache POI (for .xlsx extension)

XSSFWorkbook: representation of XLSX file.
XSSFSheet: representing a sheet in an XLSX file.
XSSFRow: representing a row in the sheet of XLSX file.
XSSFCell: representing a cell in a row of XLSX file.

How to read a specific cell value?

Let’s take an example to show how to read any specific cell , so in below table we have to read 5th cell value of third row .

//Code Snippet

package com.test;
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;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

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

//Create an object of File class to open xlsx file
File file = new File("E:\\Selenium\\ExcelData\\TestData.xlsx");

//Create an object of FileInputStream class to read excel file
FileInputStream inputStream = new FileInputStream(file);

//Creating workbook instance that refers to .xls file
XSSFWorkbook wb=new XSSFWorkbook(inputStream);

//Creating a Sheet object using the sheet Name
XSSFSheet sheet=wb.getSheet("TestSheet1");

//Create a row object to retrieve third row
XSSFRow row2=sheet.getRow(3);

//Create a cell object to retreive 5th cell for second row
XSSFCell cell=row2.getCell(5);

//Get the address in a variable
String address= cell.getStringCellValue();

//Printing the address
System.out.println("Address is :"+ address);
}
}

How to read the entire Excel sheet?

For reading complete excel data , you can iterate over each cell of the row, present in the sheet.

So for this we have to count total no of rows in the sheet and total number of columns .

We will use below logic/code for total row count.

Total no of Rows  = Last Row Number -First Row Number
int rowCount=sheet.getLastRowNum()-sheet.getFirstRowNum();

Let’s try to print the entire data present in the sheet using the below code:

package com.test;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

public class ReadExcel {
public static void main(String args[]) throws IOException {
//Create an object of File class to open xlsx file
File file = new File("E:\\Selenium\\ExcelData\\TestData.xls");

//Create an object of FileInputStream class to read excel file
FileInputStream inputStream = new FileInputStream(file);

//creating workbook instance that refers to .xlsx file
XSSFWorkbook wb=new XSSFWorkbook(inputStream);

//creating a Sheet object
XSSFSheet sheet=wb.getSheet("Sheet1");

//get all rows in the sheet
int rowCount=sheet.getLastRowNum()-sheet.getFirstRowNum();

//iterate over all the row to print the data present in each cell.
for(int i=0;i<=rowCount;i++){

//get cell count in a row
int cellcount=sheet.getRow(i).getLastCellNum();

//iterate over each cell to print its value
System.out.println("Row"+ i+" data is :");

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 is used to write data into an Excel file using Selenium.

package com.test;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

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 WriteExcel {
public static void main(String[] args) throws IOException {
String path = System.getProperty("user.dir") + "/src/main/java/com/testData/TestData.xlsx";
FileInputStream fis = new FileInputStream(path);

Workbook workbook = new XSSFWorkbook(fis);

//creating a Sheet object

Sheet sheet=workbook.getSheet("Demo");

int lastRow = sheet.getLastRowNum();

//iterate through each row and set 5th cell value as 'Pass'
for (int i = 1; i &lt;= lastRow; i++) {
Row row = sheet.getRow(i);
//set cell value of 5th cell for each row
Cell cell = row.createCell(5);
cell.setCellValue("Pass");
}
FileOutputStream fos = new FileOutputStream(path);
workbook.write(fos);
fos.close();
}
}

Recommended Articles

Tutorials Hut

Selenium WebDriver