How to Read Excel File in Selenium projects

In this topic we will learn how to read data from an Excel file in Java

There may be several approaches to this implementation.

As we know excel file is not similar to a word file because the data is organized in rows and columns. The Core Java Development Kit, aka JDK, does not provide in-built libraries to read or write data to specific file extensions like - Microsoft Excel or Word documents. We have to rely on a third-party library, and I used Apache POI.

In this section, let us learn how to read data from an Excel file with the combination of Java IO packages and Apache POI library.

What is Apache POI?

POI stands for “Poor Obfuscation Implementation”.

Apache POI is the most commonly used Maven Repository for Java based projects. It is a library containing Java classes that gives users an API for working with Microsoft document extensions like .xls and .xlsx.

Users can easily create, modify and read/write data into excel files.

The below maven dependencies needs to be added to your projects in pom.xml file.

The latest versions can be downloaded from the Maven Repositories.

Following are some of Java Classes available in the Apache POI library, for working with XLSX file

• XSSFWorkbook: It is a class representing a XLSX file.

• XSSFSheet: It is a class representing a sheet in an XLSX file.

• XSSFRow: It is a class representing a row in the sheet of XLSX file.

• XSSFCell: It is a class representing a cell in a row of XLSX file.

Steps to Read from the XLSX File -

1. Create an excel file (for example ExcelRead.xlsx) and fill some data in it.

2. Create an app.properties file where you can configure application properties like - excel file path (ExcelRead.xlsx).

3. Create a Java class PropertiesReader which is used to store all the application properties loaded from app.properties file and you can use these values across the program.

You can write the code as below :

4. Create a Java class (for example ExcelReader) and write the following code.

package excelUtil;

import java.io.FileInputStream;

import java.util.Properties;

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

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 ExcelReader {

static String path;

Properties properties;

static String sheetname;

public static FileInputStream excelFile;

public static XSSFWorkbook excelWbook;

public static XSSFSheet excelWSheet;

public static XSSFCell cell;

public static XSSFRow row;

// Create an object for the PropertiesReader class and loadProperties from the app.properties file into an object of type java.util.Properties.

public ExcelReader() {

PropertiesReader propUtil = new PropertiesReader();

properties = propUtil.loadProperties();

path = properties.getProperty("Testdata.excel.path");

sheetname = properties.getProperty("sheetName");

}

// This method opens the excel file at the provided path

// path and sheetname variables are initialized in the constructor

public static void setexcelFileInfo() throws Exception {

// Open Excel File

excelFile = new FileInputStream(path);

// Open Excel Workbook

excelWbook = new XSSFWorkbook(excelFile);

// Access the required data sheet

excelWSheet = excelWbook.getSheet(sheetname);

}

//This function will read the data from the specified excel sheet and return data as a string of two dimensional array

public static String[][] getDataFromExcelSheet() throws Exception {

try {

setexcelFileInfo();

int rowCount = getRowCount();

int cellCount = getColumnCount(1);

String[][] cellData = new String[rowCount][cellCount];

//this outer for loop will iterate for each row in the excel in the same order, from top to bottom

for (int i = 1; i <= rowCount; i++) {

//this inner loop will iterate for each column of the above row in the same order, from left to right

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

cellData[i - 1][j] = getCellData(i, j);

}

}

//here the cell data will now have data same as excel

return cellData;

} catch (Exception e) {

return new String[0][0];

} finally {

if (excelWbook != null)

excelWbook.close();

if (excelFile != null)

excelFile.close();

}

}

// This function is to get the row count using POI's getLastRowNum function

public static int getRowCount() throws Exception {

try {

int rowCount = excelWSheet.getLastRowNum();

return rowCount;

} catch (Exception e) {

return 0;

}

}

// This function is to get the cell count using POI's getLastCellNum function

public static int getColumnCount(int RowNum) throws Exception {

try {

// Row = ExcelWSheet.getRow(RowNum).getLastCellNum();

row = excelWSheet.getRow(RowNum);

int cellCount = row.getLastCellNum();

return cellCount;

} catch (Exception e) {

return 0;

}

}

// This function will read the actual data at a specified row and column using POI DataFormatter.

public static String getCellData(int RowNum, int ColNum) throws Exception {

try {

cell = excelWSheet.getRow(RowNum).getCell(ColNum);

DataFormatter formatter = new DataFormatter();

String CellData = formatter.formatCellValue(cell);

return CellData;

} catch (Exception e) {

return "";

}

}

}

5. In this example, I am creating a positive and negative automation Testcase using TestNG.

package stepDefinition;

import java.util.Properties;

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.WebElement;

import org.openqa.selenium.chrome.ChromeDriver;

import org.testng.annotations.AfterMethod;

import org.testng.annotations.BeforeMethod;

import org.testng.annotations.Test;

import excelUtil.ExcelReader;

import io.github.bonigarcia.wdm.WebDriverManager;

public class Login {

ExcelReader excelreader;

Properties properties;

String sheetDetails;

WebElement name;

WebElement password;

WebElement email_id;

String[][] userDetails;

WebDriver driver;

//intialize the excel data into BeforeMethod for using it later in testcases

@BeforeMethod

public void setup() throws Exception {

WebDriverManager.chromedriver().setup();

driver = new ChromeDriver();

userDetails = ExcelReader.getDataFromExcelSheet();

}

//In this method we need to use the data from the array using specific array indexes as needed

@Test

public void login_should_work() {

name = driver.findElement(By.name("txtUserLoginName"));

password = driver.findElement(By.name("txtUserPassword"));

// finding email id element

email_id = driver.findElement(By.name("txtUserEmailAddress"));

// reading name and password from excel file

name.sendKeys(userDetails[1][0]);

password.sendKeys(userDetails[1][1]);

// clicking on login button

driver.findElement(By.name("btnLoginSubmit")).click();

}

//In this method we need to use the data from the array using specific array indexes as needed

@Test

public void login_should_not_work() {

name = driver.findElement(By.name("txtUserLoginName"));

password = driver.findElement(By.name("txtUserPassword"));

// finding email id element

email_id = driver.findElement(By.name("txtUserEmailAddress"));

// reading username and password from excel file

//Here the username password details are for negative scenario

name.sendKeys(userDetails[2][0]);

password.sendKeys(userDetails[2][1]);

// clicking on login button

driver.findElement(By.name("btnLoginSubmit")).click();

}

@AfterMethod

public void tearDown() {

driver.quit();

}

}

I hope this blog will help you.

Please follow my blogs, Thank you.