Mar 17, 20234 min read

Apache POI – Read and Write Excel File in Selenium

Introduction

When the web applications have multiple end users with their own data, it becomes essential to test it with diverse data sets. Mostly, when we test an application manually, we run the same scenario for various test data. For automation also, the strategy will be same. This is called Data Driven testing. The key advantage of having such type of testing is that the test data and code is stored separately. By this, any changes in code will not affect the data and vice versa.

Apache POI (Poor Obfuscation Implementation)

To create and organize varying data sets, Microsoft Excel is most convenient tool. However, Selenium in Java does not have any built-in support for working with excel files. In order to handle excel sheets there is an open-source Java API, called as Apache POI, that offers a collection of libraries that help to read, write and manipulate the excel files.

Some of the important features of Apache POI are as follows:

· Contains HSSF implementation for Excel '97(-2007) file format i.e XLS.

· Contains XSSF implementation should be used for Excel 2007 OOXML (.xlsx) file format.

· Apache POI HSSF and XSSF API provides mechanisms to read, write or modify excel spreadsheets.

· Apache POI also provides SXSSF API that is an extension of XSSF to work with very large excel sheets.

· It requires less memory and is suitable when working with very large spreadsheets and heap memory is limited.

· Provides stream-based processing, which can handle large files and requires less memory.

· Provides exceptional support for additional excel features such as working with Formulas, creating cell styles by filling colors and borders, fonts, headers and footers, data validations, images, hyperlinks etc.

Most used Interfaces and classes in API POI

Reading and writing the test data from Excel sheet to Selenium tests

Let us take an example of the testcase of login to LinkedIn by reading excel sheet data and writing the Result back to it.

Below is the sample data:

Step 1: Add Apache POI libraries to your project

There are two ways to add these libraries:

1. If you are using Maven, then simply add Apache POI dependency in POM.xml

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

</dependency>

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

<artifactId>poi-ooxml</artifactId>

</dependency>

For latest dependency version, go to MVNRepository.com

2. Download the latest version POI jars https://poi.apache.org/download.html & download the latest zip file.

Unzip the jar file, and add the following jars to your project and configure them.

· dom4j-1.6.1.jar

· poi-3.10-FINAL-20140208.jar

· poi-ooxml-3.10-FINAL-20140208.jar

· poi-ooxml-schemas-3.10-FINAL-20140208.jar

· xmlbeans-2.3.0.jar

In this blog I have used this method to integrate the libraries.

Step 2: Create Packages within the project

Create 3 packages in the project:

1. Testdata– this package will contain the excel sheet of testdata.

2. Testcases– this package will contain the testcase classes for the implementation.

3. Utilities– this package will contain all the methods related to Excel Sheet read and write operations along with initializing the Workbook.

Here is how project structure looks:

Step 3: Initialize the Workbook and implement read operations in ConfigExcel.java

Below will the code for this class:

package Utilities;

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

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

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

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

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

public class ConfigExcel {

private static HSSFWorkbook workbook;

private static HSSFSheet sheet;

private static HSSFRow row;

private static HSSFCell cell;

public void setExcelFile(String excelFilePath,String sheetName) throws IOException {

//Create an object of File class to open xls file

File file = new File(excelFilePath);

//Create an object of FileInputStream class to read excel file

FileInputStream inputStream = new FileInputStream(file);

//creating workbook instance that refers to .xls file

workbook=new HSSFWorkbook(inputStream);

//creating a Sheet object

sheet=workbook.getSheet(sheetName);

}

public String getCellData(int rowNumber,int cellNumber){

//getting the cell value from rowNumber and cell Number

cell =sheet.getRow(rowNumber).getCell(cellNumber);

//returning the cell value as string

return cell.getStringCellValue();

}

public int getRowCountInSheet(){

int rowcount = sheet.getLastRowNum()-sheet.getFirstRowNum();

return rowcount;

}

public void setCellValue(int rowNum,int cellNum,String cellValue,String excelFilePath) throws IOException {

//creating a new cell in row and setting value to it

sheet.getRow(rowNum).createCell(cellNum).setCellValue(cellValue);

FileOutputStream outputStream = new FileOutputStream(excelFilePath);

workbook.write(outputStream);

}

Some of the methods used in above class:

· setExcelFile to initialize the Excel Workbook.

· getCellValue to retrieve the value present in a particular cell in the file.

Let us create one constants class which will have the constant values in a file. So that if the file path is changed, it can be updated here in one file instead of in all testcases.

package Utilities;

public class Constants {

public static final String URL = "https://www.linkedin.com";

public static final String Path_TestData = "C:\\Users\\twilight\\DataFromExcel\\src\\testData";

public static final String File_TestData = "LoginininLogin.xls";

}

Step 4: Implementing the testcase in Selenium

Below will be the code for loginTest class

package testCases;

import org.openqa.selenium.By;

import org.openqa.selenium.JavascriptExecutor;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.WebElement;

import org.openqa.selenium.chrome.ChromeDriver;

import Utilities.Constants;

import Utilities.ConfigExcel;

import java.io.IOException;

import java.util.concurrent.TimeUnit;

public class LoginTest {

//creating object of ExcelUtils class

static ConfigExcel excelUtils = new ConfigExcel();

//using the Constants class values for excel file path

static String excelFilePath =Constants.Path_TestData+Constants.File_TestData;

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

//set the Chrome Driver path

System.setProperty("webdriver.chrome.driver","E:\\Projects\\chromedriver.exe");

//Creating an object of ChromeDriver

WebDriver driver = new ChromeDriver();

driver.get(Constants.URL);

WebElement Username=driver.findElement(By.id("session_key"));

WebElement password=driver.findElement(By.id("session_password"));

WebElement signIn=driver.findElement(By.xpath("//*[@type='submit']"));

//calling the ExcelUtils class method to initialise the workbook and sheet

excelUtils.setExcelFile(excelFilePath,"LinkedInLogin");

//iterate over all the row to print the data present in each cell.

for(int i=1;i<=excelUtils.getRowCountInSheet();i++)

{

Username.sendKeys(excelUtils.getCellData(i,0));

password.sendKeys(excelUtils.getCellData(i,1));

//Click on submit button

signIn.click();

String actualTitle = driver.getTitle();

if(actualTitle.contains("Feed | LinkedIn"))

{

//if the sign in is successful then title will contain Feed | LinkedIn and write Pass in the excel sheet

excelUtils.setCellValue(i,2,"PASS",excelFilePath);

}

else

{

// if not sign in successfully, write Fail in the excel sheet

excelUtils.setCellValue(i,2,"PASS",excelFilePath);

}

//closing the driver

driver.quit();

}

Though LoginTest class performs the actions on Sign In page, all the methods related to excel is handled by ConfigExcel class. After this you can run this class as Junit and you will get output in the excel sheet whether each test data entry pass or failed. This can be used as test report.

Takeaway

· Apache POI plays a vital role in making input and output from excel sheets for Selenium Data Driven Automation. In this blog, we saw how to read and write excel files using such powerful Java API.

· Apache POI enables you to run script multiple times with different data sets with all the data maintained at a single location. This saves us time and maintaining the test scripts becomes easy.