Selenium Step By Step Guide

Reading Excel sheets

Reading excel sheets is an integral part of the automation framework to consume the test data from an external excel file.

Add the Apache Poi library into the project:

We need to import the Apache Poi library for making a connection to read the excel data. Let us add the maven dependency of Apache Poi library to write the code to read the Excel file with our Selenium test code.

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

</dependency>

We can check the latest version of the same in the maven repository.

Now let us create an excel file that contains the username and password of two different users. Let us see the content of excel file below:

Test	Keyword	Object	ObjectType	Value
Login
	OpenURL			url
	ENTERTEXT	userid	XPATH	user1
	ENTERTEXT	password	XPATH	password1
	CLICK	btnLogin	XPATH

Let us see the Excel reading logic in a sample ExcelReader class below:

package TestSuite;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

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

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

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

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

public class ExcelReader {

@SuppressWarnings("resource")

public Sheet readExcel(String filePath,String fileName,String sheetName) throws IOException{

File file = new File(filePath+"\\"+fileName);

FileInputStream inputStream = new FileInputStream(file);

Workbook workbook = null;

String fileExtensionName = fileName.substring(fileName.indexOf("."));

if(fileExtensionName.equals(".xlsx")){

workbook = new XSSFWorkbook(inputStream);

}

else if(fileExtensionName.equals(".xls")){

workbook = new HSSFWorkbook(inputStream);

}

//Read sheet inside the workbook by its name

Sheet sheet = workbook.getSheet(sheetName);

return sheet;

}

As we can see that the “readExcel” method returns a Sheet type, that can be used to get the cell content in the sheet. This data is used by the Test runner class to get data with the help of TestNG.

Selenium Step By Step Guide

Selenium WebDriver

Selenium WebDriver – Advance

Working with Drivers

Reporting

Framework Complete Guide

Reading Excel sheets

Popular Exams

Company

Resources