In this post, we will learn to create a Map from Excel data to use in scripts such as Selenium and Rest Assured tests.
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.
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.
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