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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
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<LinkedHashMap<String, String>> 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<LinkedHashMap<String, String>> 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<String, String> 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<rowCount ; j++) { Row r = s.getRow(j); // Field name is constant as 0th index String fieldName = r.getCell(0).getStringCellValue(); String fieldValue = r.getCell(i).getStringCellValue(); // Add data in map data.put(fieldName, fieldValue); } // Add map to list after each iteration dataList.add(data); } return dataList; } public static void main(String[] args) throws EncryptedDocumentException, IOException { List<LinkedHashMap<String, String>> mapDataList = getExcelDataAsMap("ExcelDataToReadInListOfMap","Sheet1"); for(int k = 0; k<mapDataList.size() ; k++) { System.out.println("Data Set "+ (k+1) +" : "); for(String s: mapDataList.get(k).keySet()) { System.out.println("Value of "+s +" is : "+mapDataList.get(k).get(s)); } System.out.println("========================================================"); } } } |
Output :-
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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.
