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