Read Test Data From Excel Sheet In Selenium Using POI

In any test design framework, test data management is the essence. That is why even TestNg has in-built @DataProvider along with Parameterization through the testing.xml file. Earlier we discussed test data management through JSON. Today we will learn how parameterization can be implemented through the Excel sheet. Therefore, firstly we will discuss how to read test data from excel sheet and in the next tutorial we will learn about writing data to the excel sheet.

Before we jump to the tutorial which discusses how to read test data from excel sheet, I would like to recommend you Parameterization technique 1 which was discussed earlier.

Parameterization Technique 1

What are POI and JXL?

If you are familiar with Selenium, then you must have heard about POI and JXL. If you don’t know so I will still tell you. 😊

POI and JXL is the JAR file which helps us to create a coding environment in the IDE to read test data from excel sheet and writing to the excel sheet. Now you may ask which one to use?

I will recommend you POI jar file because JXL is not in an active state which means it never got the latest update since 2009. Hence, it may cause trouble to read different versions of the excel file. Whereas POI is the upgraded one. Some of the drawbacks of the JXL includes:

It does not support XLSX format
Spreadsheets are generated in Excel 2000 format
It does not support Excel 2007 and beyond

Apache poi read test data

How to set up POI jar file in my project?

This should be your next question. If you are using either Maven or Gradle then you can just create the dependency in the pom.xml and Gradle.build file respectively. Here is the dependency definition:

Maven:

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>

Gradle:

compile group: 'org.apache.poi', name: 'poi', version: ‘4.0.0’

If you are not using any of them (Maven or Gradle) then you should directly download the JAR file and Add them as the External JAR file.

Download the latest POI from the Apache site: Click here to download.

Understanding POI Jar file

Once you have set the Jar file then you need to understand the implementations inside it. So that you could utilize them in your project.

Interfaces inside the POI

Workbook– This interface is implemented to instantiate different excel file representation (xls or xlsx).
Sheet– This interface is implemented to read the sheet inside the workbook of the different types of excel sheet (xls or xlsx).
Row– This interface is implemented to identify the row inside the sheet of the different types of excel sheet (xls or xlsx).
Cell– This interface is implemented to identify the corresponding cell of the given row of the different types of excel sheet (xls or xlsx).

Classes inside the POI

XSSFWorkbook- This class implement Workbook interfaces for XLSX file representation.
HSSFWorkbook- This class implement Workbook interface for XLS file representation.
XSSFSheet- This class implement Sheet interface for XLSX file representation.
HSSFSheet- This class implement Sheet interface for XLS file representation.
XSSFRow- This class implement Row interface for XLSX file representation.
HSSFRow- This class implement Row interface for XLS file representation.
XSSFRow- This class implement Cell interface for XLSX file representation.
HSSFRow- This class implement Cell interface for XLS file representation.

How to implement the POI Jar file to read test data from the Excel sheet?

Implementation is quite easier. You first define the path of the excel sheet, then either follow the sequential steps of interfaces defined above or the classes. Just for the example, here is the sample code which read first row and column from the excel sheet.

  public void readExcelData() throws IOException {
	
	  String path = "data/TestDataSheet.xlsx";
	  
	  FileInputStream fis = new FileInputStream(path);
	  
	  Workbook workbook = new XSSFWorkbook(fis);
	  
	  Sheet sheet = workbook.getSheetAt(0);
	  
	  Row row = sheet.getRow();
	  
	  Cell cell = row.getCell(0);
	  
	  String value = cell.getStringCellValue();
	  
	  System.out.println(value);
  }

In this code, we just implemented method inside each of the interfaces. It will be easier for you too to remember them with common word like Sheet, Row, Cell and further you have to call the methods only. If you are creating the instance of workbook then here, you must give attention with XSSFWorkbook or HSSFWook and remaining code is easier to understand.

Let’s have a look at the code which read test data from the entire row and column. We will use them in a loop.

package com.inviul.selenium.project;


import java.io.FileInputStream;
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 ReadExcelSheetData {

	public static void main(String[] args) throws IOException {
		  String path = "data/TestDataSheet.xlsx";
		  
		  FileInputStream fis = new FileInputStream(path);
	
		  Workbook workbook = new XSSFWorkbook(fis);
		
		  Sheet sheet = workbook.getSheetAt(0);
		  
		  int lastRow = sheet.getLastRowNum();
		  
		  System.out.println("Last row- "+lastRow);
		  
		  for(int i=0; i<=lastRow; i++){
			  
			  Row row = sheet.getRow(i);
			  
			  int lastCell = row.getLastCellNum();
			  
			  for(int j=0; j<lastCell; j++){
				  
				  Cell cell = row.getCell(j);
				  
				  String value = cell.getStringCellValue();
				  
				  System.out.println(value);
			  }
			  
			  System.out.println();
			  
			  
		  }
		  
		  

	}

}

Here is the console output:

read test data from excel console output