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.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

import org.apache.poi.EncryptedDocumentException;
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.ss.usermodel.WorkbookFactory;

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.
#ThanksForReading
#HappyLearning



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.