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.