Testing concept

Read Excel data from .xls and .xlsx file type using POI

Posted on Updated on

As we know that file is very most important in our day to day life, we frequently use to create a file, open it and update or delete something from file. Same thing in selenium automation as well we need to manipulate the file.

So, In this blog we will learn how to install the poi jar file, what jar file are required for that and how to perform the read operation on excel with the help of JAVA IO package and APACHE POI library.

POI library is enough to read write both XLS and XLSX file.

Note : For .XLS file read write, we can use .JXL jar file but it won’t support to .xlsx file.

Let’s begin the journey: – 

Step 1. If you haven’t eclipse then Install the Eclipse latest version.

Step 2. If you haven’t added WebDriver then download the webdriver jar file and add to library.

Step 3. If you are using Maven then use the following dependencies.

<!– https://mvnrepository.com/artifact/org.apache.poi/poi –>







Or else you can directly download latest POI jar file from https://poi.apache.org/download.html

Step 4. Once the downloaded jar file then unzip the file and add library file into your project.

Right click on your project -> Build Path -> Configure Build Path->Library -> Add External Jar-> select the below listed jar file from unzip file -> ok



Add all the listed jar file below.








Below are java interfaces and classes that we will use for read/write xls and xlsx file in POI

XSSFWorkbook : – Is a class representation of XLSX file.

HSSFWrokbook  : – Is a class represent of XLS file.

XSSFSheet :- XSSFSheet classes implement this interface represent XLSX file’s sheet.

HSSFSheet : – HSSFSheet classes implement this interface XLS file’s sheet.

XSSFRow :- XSSFSheet classes implement this interface represent to a row of XLSX file.

HSSFRow : – HSSFSheet classes implement this interface represent to a row of XLS file.

XSSFCell :- XSSFSheet classes implement this interface represent a cell in a row of XLSX file.

HSSFCell : – HSSFSheet classes implement this interface represent a cell in a row of XLS file.

For Example.


Read Data from Excel file to Login with Salesforce


package Account;


import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;

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

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.xssf.usermodel.extensions.XSSFCellFill;

import org.apache.poi.ss.usermodel.Workbook;

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.WebElement;

import org.openqa.selenium.chrome.ChromeDriver;


public class excelSheetReadWrite {

WebDriver driver;

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

// TODO Auto-generated method stub

String file=”D://Selenium Projects//Credential.xlsx”;

FileInputStream fis= new FileInputStream(file);

XSSFWorkbook workbook= new XSSFWorkbook(fis);

XSSFSheet sheet= workbook.getSheet(“Sheet1”);

/*Reading data from Excel



String userName=sheet.getRow(1).getCell(1).toString();




//Open Chrome browser

WebDriver driver;



driver = new ChromeDriver();


//Login With Salesforce using Webdriver


//XSSFRow r= sheet.getRow(1);

int noOfColumns = sheet.getRow(1).getLastCellNum();

String[] Headers = new String[noOfColumns];

for(int i=0;i < noOfColumns ;i++){



for(int j=0; j< noOfColumns;j++){




if(Headers[j].equals(“Password”)){                     driver.findElement(By.id(“password”)).sendKeys(sheet.getRow(1).getCell(j).getStringCellValue());