Create a Map From Excel Data in Java – Selenium – API Automation

In this post, we will learn to create a Map from Excel data to use in scripts such as Selenium and Rest Assured tests.

Introduction

When we write a code, it may require some data to act on. For an example :- If we want to register a user on an application, we require some user data like name, age , address , contact details etc. It is always a good practice to keep logic and data separated.

We use “Excel” extensively to store test data. We are aware that excel stores data as rows and cells and each cell can be uniquely identified using index. I have already covered reading an excel file using Apche POI here.

In this post, we will see a common problem and how can we resolve it.

Problem Statement

Consider below excel data :-

I have stored data as a key value pair. If I need value of “FirstName“, I can pass row as 1st and cell as 1st i.e. (1,1). Similarly if I need value of “Company“, I can pass row as 4th and cell as 1st i.e. (4,1). In short, I am hard coding rows and cells. If I add a new field in between , all indexes will be changed. This way is not recommended.

Solution

Think if I could get all these key-value pair from excel in to Java data structure – Map, so that I could get the value just by passing field name. You can raise a concern what if I do not know the exact field name? It is genuine and I believe accessing data must be easier. I can use a type preferably Enum to define fields. We should always avoid hard coded things in code which could be passed from outside.

Let’s implement the same.

package ReadExcel;

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

	public static LinkedHashMap 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 LinkedHashMap which retain order
		LinkedHashMap data = new LinkedHashMap<>();
		// Get total row count
		int rowCount = s.getPhysicalNumberOfRows();
		// Skipping first row as it contains headers
		for (int i = 1; i < rowCount; i++) {
			// Get the row
			Row r = s.getRow(i);
			// Since every row has two cells, first is field name and another is value.
			String fieldName = r.getCell(0).getStringCellValue();
			String fieldValue = r.getCell(1).getStringCellValue();
			data.put(fieldName, fieldValue);
		}
		return data;
	}

	public static void main(String[] args) throws EncryptedDocumentException, IOException {

		LinkedHashMap mapData = getExcelDataAsMap("ExcelDataToReadInMap","Sheet1");
		for(String s: mapData.keySet())
		{
			System.out.println("Value of "+s +" is : "+mapData.get(s));
		}
	}

}

Output :-

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

This strategy will be helpful in handling dynamic flow. For example :- To register a user you may need multiple fields but do not required all. Some field may ask for some extra details. You can pass all these data setup in excel and let code work on passed data. No need to open excel and get index manually.

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

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

2 thoughts on “Create a Map From Excel Data in Java – Selenium – API Automation

Leave a Reply

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