We are working on the marathon of tutorials to maintain parameterization efficiently. There is a very wide scope of data science and data analytics these days. Experts basically look for test data management in any Automation framework, so it’s our responsibility to efficiently maintain the test data. Today we will learn how to store data from excel sheet to the HashMap so that we can retrieve data effectively by its key whenever required.
There could be many techniques but data maintenance and storage through HashMap has its own advantage.
Pre-Requisites
- You must have good knowledge of Java Collections Framework.
Before we jump to the tutorial, you should go through below links which discuss the application of Apache POI in Excel sheet manipulation:
- How to read data from the Excel sheet in Selenium using Apache POI?
- How to write data to the Excel sheet in Selenium using Apache POI?
Exclusive Post: How to install Selenium IDE in Google Chrome?
Functional Specification to Store data in HashMap from Excel sheet
What basically we will do?
We generally define two columns against many rows so here if we consider the 0th cell as the fixed cell which is the real attribute like URL, Search text, etc then the 1st cell will hold its values. Right? Hence, we got key and value pairs.
Now we will store data (value) from 0th Cell as Key in HashMap, whereas data from the 1st cell will be stored as the value in the HashMap. Did you get the logic?
This is the micro level designing. Our goal is to store data map inside another HashMap. This is done to maintain the one to many relationships. Therefore, there will be two HashMap. One HashMap will have String as Key and another HashMap as its corresponding value.
In first HashMap, we can give Excel file/sheet name as Key and data inside this Excel sheet as the corresponding value. Make sense!
If you wish you can use three HashMap. Like one HashMap as Workbook name as Key and Sheet map as values and further Sheet map will have real test data. So, it totally depends on the strategy of Automation framework architect.
A coded representation of the Functional specification
HashMap 1 (Excel file name with data map)
Map<String, Map<String, String>> excelFileMap = new HashMap<String, Map<String, String>>();
HashMap 2 (Test data map)
This map will basically store all the test data. Once all the data are put in this map then the map will be further put into excelFileMap.
Map<String, String> dataMap = new HashMap<String, String>();
excelFileMap.put(“fileName”, dataMap);
Java code to store data in HashMap (Basic uses of Collections framework)
This image contains the Attributes and value pairs in the excel sheet. Our goal is to store all the attributes as key and corresponding values as the value in a HashMap.
Once we store them then we can retrieve each value by passing the attributes in the method. Here is the code:
package com.inviul.selenium.project;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
public class ReadExcelSheetData {
public static Map<String, Map<String, String>> setMapData() throws IOException {
String path = "data/TestDataSheet.xlsx";
FileInputStream fis = new FileInputStream(path);
Workbook workbook = new XSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
int lastRow = sheet.getLastRowNum();
Map<String, Map<String, String>> excelFileMap = new HashMap<String, Map<String,String>>();
Map<String, String> dataMap = new HashMap<String, String>();
//Looping over entire row
for(int i=0; i<=lastRow; i++){
Row row = sheet.getRow(i);
//1st Cell as Value
Cell valueCell = row.getCell(1);
//0th Cell as Key
Cell keyCell = row.getCell(0);
String value = valueCell.getStringCellValue().trim();
String key = keyCell.getStringCellValue().trim();
//Putting key & value in dataMap
dataMap.put(key, value);
//Putting dataMap to excelFileMap
excelFileMap.put("DataSheet", dataMap);
}
//Returning excelFileMap
return excelFileMap;
}
//Method to retrieve value
public static String getMapData(String key) throws IOException{
Map<String, String> m = setMapData().get("DataSheet");
String value = m.get(key);
return value;
}
}
Main method which calls getMapData(String arg) by passing the key:
package com.inviul.selenium.project;
import java.io.IOException;
public class ReadSheetData {
public static void main(String[] args) throws IOException {
ReadExcelSheetData r = new ReadExcelSheetData();
String val = r.getMapData("search");
System.out.println("Value of the keyword (search) is- "+val);
}
}
Here is the console output:
If you face any difficulties in understanding the code then feel free to ask your queries in the comment below, even you can share your feedback.
Have you joined our Facebook group?
Join now
Can we try it in Maven project?
Ofcourse..I have done this in Maven project itself. 🙂
Where can i found a code for writing a HashMap in excel?
In this article itself, I have given the sample code.
I have a question. I am automating a salesforce application. Th functionality says :
1. Login via login.salesforce.com
2. Click on Setup on Top Right
3. In Left Side quick Search Box – Type – “Bulk Data load Jobs”.
4. Open the link visible.
5. Scan the whole page.
6. Click on Each of Job ID .
7. Fetch 2 fields “Records Processed” and “Completed Batches” information.
I have automated steps 1 to 7, in step6, i have created a url which appends job id and this will open a new page hence fetching the data of the 2 fields and printing them to console.
My question is : My code is taking only 1 job Id and creating a url and fetching the fields, there are 47 job id in the table, it does not pick the rest one. Also how can i export these job id’s and its fields to excel ?
please help me
I am also currently working in Salesforce automation, Can I see your code?
I can definitely help you out.
hi,
ia have an excel file with 5 columns and 50 rows, how can i store all the values into hashmap
I think this article is self-sufficient. Please try whatever mentioned in the above tutorials.
It will solve your problem.
Hi Avinash,
Due to this line Cell valueCell = row.getCell(1); it will fetch only 1st value. But what if, we have multiple values?
For e.g Keyword search has 10 values. How to fetch the other 9 values?
In that case, you need to iterate over the entire column.
Can you please provide the code for iterating to get all the values in each row.?
Would you write a blog how to write key value pair values in excel using Apache poi
This article is about reading data using POI and storing it in hashmap. Where is your confusion?
Hi, thanks for the information. Could you please tell me how to store data in Hashmap when there are more than two columns in excel. As Key cannot store duplicate data.
Hi Avinash,
Please let me know the significance of storing the values to Hashmap from Excel file using Apache POI API. What exactly we achieve here.
Thanks,
Nikhil N V
It improves the performance of the test execution.
Thanks Avinash
Return excelfilemap
Does not return all excel key value pair
Eg
Key : name
Value: ram , raj, rahul
Please help
Please share your full problem statement.
what is version of poi-ooxml jar used
You can pick any compatible version.
How do you handle errors in the Excel file? What if one of the Excel file values happens to be a boolean because of an error?
Due to this line Cell valueCell = row.getCell(1); it will fetch only 1st value. But what if, we have multiple values?
For e.g Keyword search has 10 values. How to fetch the other 9 values?
Can please post any links for iterate over the entire column or else logic to fectch entire row based the one value
which poi lib you are using?..for me its not working
Above code is not working for POI 5.0 version(Latest version). But its working fine for version 4.*; Thanks buddy..It helped me lot.Keep it up.
Getting NPE , while fetching the data from excel file
java.lang.NullPointerException
at utility.ExcelUtils.setMapData(ExcelUtils.java:103)
at utility.ExcelUtils.getMapData(ExcelUtils.java:126)
at testClasses.TestCase.Openurl(TestCase.java:51)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
Please help
how to validate the excel data(having key , value pair) with my actual data which i fetch from webpage
Many ways. You can do parallely thru iteration. Or read all excel data at one go and store in a data structure and then compare..
what if the value is Integer in excel.It will fail..solution for that?
no, you can do typecasting here.