Create A List Of Map From Excel Data In Java – Selenium – API Automation

In this post, we will learn to fetch excel data in a List of Map to achieve Data Driven Testing for Selenium or API automation scripts.

Consider a scenario in which we need to register multiple users with different data. In short need to run a script for different data sets at once. We mostly used excel sheet to pass multiple set of data. Something like below :-

You can see there are three different data sets. Each data set can be considered as a Map and when we add all these maps in to a list then we can get a List of Map and we can iterate over.

Create a Map from excel data I have covered already previously here where excel sheet has only single data set. In above excel we have three data sets.

I have tried to explain the logic of iteration in comments along with codes.

package ReadExcel;

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

import org.apache.poi.EncryptedDocumentException;

public class ReadExcelDataInListOfMap {

	public static List> getExcelDataAsMap(String excelFileName, String sheetName) throws EncryptedDocumentException, IOException {
		// Create a Workbook
		Workbook wb = WorkbookFactory.create(new File("src\\test\\resources\\excelFiles\\"+excelFileName+".xlsx"));
		// Get sheet with the given name "Sheet1"
		Sheet s = wb.getSheet(sheetName);
		// Initialized an empty List which retain order
		List> dataList = new ArrayList<>();
		// Get data set count which will be equal to cell counts of any row
		int countOfDataSet = s.getRow(0).getPhysicalNumberOfCells();
		// Skipping first column as it is field names
		for (int i = 1; i < countOfDataSet; i++) {
			// Creating a map to store each data set individually
			LinkedHashMap data  = new LinkedHashMap<>();
			// Get the row i.e field names count
			int rowCount = s.getPhysicalNumberOfRows();
			// Now we need to iterate all rows but cell should increases once all row is done
			// i.e. (1,1),(2,1),(3,1),(4,1),(5,1) - First iteration
			//      (1,2),(2,2),(3,2),(4,2),(5,2) - Second iteration
			//      (1,3),(2,3),(3,3),(4,3),(5,3) - Third iteration
			for(int j = 1; j> mapDataList = getExcelDataAsMap("ExcelDataToReadInListOfMap","Sheet1");
		for(int k = 0; k

Output :-

Data Set 1 : 
Value of FirstName is  : Amod
Value of LastName is  : Mahajan
Value of Age is  : 29
Value of Company is  : Sapient
Value of Address is  : Bengaluru
Data Set 2 : 
Value of FirstName is  : Animesh
Value of LastName is  : Prashant
Value of Age is  : 30
Value of Company is  : Robert Bosch
Value of Address is  : Katihar
Data Set 3 : 
Value of FirstName is  : Aaditya
Value of LastName is  : Mahajan
Value of Age is  : 26
Value of Company is  : WeInvest
Value of Address is  : Bhopal

You can download/clone above sample project from here.

If you have any doubt, feel free to comment below.
If you like my posts, please like, comment, share and subscribe.

Find all Selenium related post here, all API manual and automation related posts here and find frequently asked Java Programs here.

Many other topics you can navigate through menu.

Leave a Reply

Your email address will not be published. Required fields are marked *