Store Excel Data With HashMap – What is HashMap & How it can be used for storing excel data?
HashMap is known as HashMap because it uses a technique called Hashing. It stores data into the (Key, Value) form which is easy and navigable. This tutorial will help you understand how we can use the hashmap concept to store excel data in selenium or Java. Each step has been explained in detail with the screenshot which makes it easy to learn and implement this in your application.
Let’s try to understand how we will store excel sheet data into Map. In the below excel sheet, each row can be identified by S.No (Note: You can pick any other column as a unique column). In our case, we will be using S.No as the primary key to identify each Row data. Let us dive into – store excel data with HashMap.
Our Initial Approach to store Row Data using Hash Map:
Now we will focus on a single row. Each row data basically consist of cell value which is identified as a column name and their corresponding value. So, we take the column Header as Key and their corresponding value can be stored as value. In other words, cell data can be also stored in the Map. Key will be column Header and Value of Key will be Cell Value.
So basically we will use 2 Hashmap, 1 Map for storing Excel sheet Complete data, and 2nd map for storing data of each row. So, the declaration will be in the following manner:
Map<String, Map<String, String>> excelSheetData; Map<String, String> rowData;
Refer below image which will help you to understand in a better way:
Intellij Debugger View shows How two Maps two maps stored excel data:
Hope requirement are clear. Even not then you can still follow below mentioned steps, and assure that you will understand the concept with further steps & explanation.
Pre requisite:
- Selenium or Java Project should be set up on the Machine.
- Good to have basic idea of Excel sheet.
- Read our blog on Excel Reader
Java/Selenium Program for Excel Reader:
Now here are the steps you have to follow for complete excel reader code:
Note: This blog is written for .xls file so we used HSSF library if you want to write for .xlsx file then use XSSF library.
Adding Apache POI Maven Depedency:
Step 1 :
Add Apache POI Maven dependency in POM.xml file (For Selenium Project Only else Refer Steps – i) & ii) mentioned below) & then continue from Step 2:
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
i. If you are using Java project then you have to go Apache Maven website, download POI Jar, and add in to your project.
Website link : https://poi.apache.org/download.html
Download .zip file if you are using windows and download .gz file in case of Linux user.
ii. Unzip the downloaded zip file and add the below jar file into your project and build. List of Jars mentioned required:
- xmlbeans-2.3.0.jardom4j-1.6.1.jar
- poi-3.10-FINAL.jar
- poi-ooxml-3.10-FINAL.jar
- commons-codec-1.5.jar
- poi-ooxml-schemas-3.10-FINAL.jar
- xml-apis-1.0.b2.jar
- stax-api-1.0.1.jar
- xmlbeans-2.3.0.jar
- dom4j-1.6.1.jar
Step 2:
Go to Project and create ExcelReader class in desired package.
Step 3:
Decide class level variables and make constructors.- Class level constructor used while object creation. Generally we pass value as argument at the time of object creation. In this tutorial we took FilePath and sheet index as constructor and we will pass at the time of object creation.
private String filePath;
private int sheetIndex;
ExcelReader(String filePath, int sheetIndex) {
this.filePath = filePath;
this.sheetIndex = sheetIndex;
}Step 4: Reading Excel Sheet
i. To read the excel sheet first we have to load an excel sheet using File Input stream.
FileInputStream fis = new FileInputStream(filePath);
ii. Then we have to create workbook object and pass input stream as input parameter.
HSSFWorkbook workbook = new HSSFWorkbook(fis);
iii. Now access desired sheet using sheet index. To access sheet use method getSheetAt method.
HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
Here is the complete method code: this method returns the desired sheet on the basis of the sheet index.
Step 5:
Write a method to read cell numeric, Boolean, or string. This method reads any cell type data. Pass get Cell object as a parameter and it will return String of any type of Cell Data.
public String getCellValueAsString(Cell cell) {
String cellValue = null;
switch (cell.getCellType()) {
case NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case STRING:
cellValue = cell.getStringCellValue();
break;
case BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA:
cellValue= cell.getCellFormula();
case BLANK:
cellValue="BLANK";
default:
cellValue ="DEFAULT";
}
return cellValue;
}Step 6:
Write Method to get compete excel data into Map. Refer below for code explanation:
6.i) Create sheet object and getSheet using Sheet Index.
HSSFSheet sheet = getSheet();
6.ii) Use ArrayList to store column Header
List <String> columnHeader = new ArrayList <String>();
6.iii) Now make row Object using getRow(0); As Row 0 is used for column Header
Row row = sheet.getRow(0);
6.iv) Iterate cell over the first Row and get Cell Value as String and Add-in above ArrayList. This code adds all the column headers in ArrayList.
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
columnHeader.add(cellIterator.next().getStringCellValue());
}6.v) Create a nested Map to store complete excel sheet Data
Map<String, Map<String, String>> completeSheetData = new HashMap<String, Map<String, String>>();
6.vi) Write a nested loop to iterate over each row and each Cell
int rowCount = row.getLastCellNum();
int columnCount = row.getLastCellNum();
for (int i = 1; i <= rowCount; i++) {
Map<String, String> singleRowData = new HashMap<String, String>();
Row row1 = sheet.getRow(i);
for (int j = 0; j < columnCount; j++) {
// Make Cell object, get cell text and add in rowMap
}
// Add each rowMap in complete excelData Map and
}6.vii) For each Row iteration create a Row object:
Row row1 = sheet.getRow(i);
6.viii) Create a Map to store each cell row data. Key – Column Header and Value – Respective Header
Map<String, String> singleRowData = new HashMap<String, String>();
6.ix) Make each cell object
Cell cell = row1.getCell(j);
6.x) Get each cell value and get the corresponding column header from Step 6.iv ArrayList and put in each Row Map.
singleRowData.put(columnHeader.get(j), getCellValueAsString(cell));
6.xi) After storing each cell data in above row Map then finally add as a value in completeSheetData Map and S.No(as key) and iterate this activity for all the row data
Refer code for Method:
public Map<String, Map<String, String>> getExcelAsMap() throws IOException {
HSSFSheet sheet = getSheet();
Map<String, Map<String, String>> completeSheetData = new HashMap<String, Map<String, String>>();
List<String> columnHeader = new ArrayList<String>();
Row row = sheet.getRow(0);
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
columnHeader.add(cellIterator.next().getStringCellValue());
}
int rowCount = row.getLastCellNum();
int columnCount = row.getLastCellNum();
for (int i = 1; i <= rowCount; i++) {
Map<String, String> singleRowData = new HashMap<String, String>();
Row row1 = sheet.getRow(i);
for (int j = 0; j < columnCount; j++) {
Cell cell = row1.getCell(j);
singleRowData.put(columnHeader.get(j), getCellValueAsString(cell));
}
completeSheetData.put(String.valueOf(i), singleRowData);
}
return completeSheetData;
}Complete Code of Excel Reader:
package stepDef;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.sl.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.*;
public class ExcelReader2 {
private String filePath;
private int sheetIndex;
ExcelReader2(String filePath, int sheetIndex) {
this.filePath = filePath;
this.sheetIndex = sheetIndex;
}
private HSSFSheet getSheet() throws IOException {
FileInputStream fis = new FileInputStream(filePath);
HSSFWorkbook workbook = new HSSFWorkbook(fis);
HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
return sheet;
}
public Map<String, Map<String, String>> getExcelAsMap() throws IOException {
HSSFSheet sheet = getSheet();
Map<String, Map<String, String>> completeSheetData = new HashMap<String, Map<String, String>>();
List<String> columnHeader = new ArrayList<String>();
Row row = sheet.getRow(0);
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
columnHeader.add(cellIterator.next().getStringCellValue());
}
int rowCount = row.getLastCellNum();
int columnCount = row.getLastCellNum();
for (int i = 1; i <= rowCount; i++) {
Map<String, String> singleRowData = new HashMap<String, String>();
Row row1 = sheet.getRow(i);
for (int j = 0; j < columnCount; j++) {
Cell cell = row1.getCell(j);
singleRowData.put(columnHeader.get(j), getCellValueAsString(cell));
}
completeSheetData.put(String.valueOf(i), singleRowData);
}
return completeSheetData;
}
public String getCellValueAsString(Cell cell) {
String cellValue = null;
switch (cell.getCellType()) {
case NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case STRING:
cellValue = cell.getStringCellValue();
break;
case BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA:
cellValue= cell.getCellFormula();
case BLANK:
cellValue="BLANK";
default:
cellValue ="DEFAULT";
}
return cellValue;
}
}
public String getSheetName(int index) throws IOException {
FileInputStream file = new FileInputStream(filePath);
HSSFWorkbook workbook = new HSSFWorkbook(file);
String sheet = workbook.getSheetName(index);
return sheet;
}
public int getSheetCount() throws IOException {
FileInputStream file = new FileInputStream(filePath);
HSSFWorkbook workbook = new HSSFWorkbook(file);
int sheetCount = workbook.getNumberOfSheets();
return sheetCount;
}
public int totolColumnCount() throws IOException {
HSSFSheet sheet = getSheet();
Row row = sheet.getRow(0);
int lastColumnNum = row.getLastCellNum();
return lastColumnNum;
}
public String getCellValueAsString(Cell cell) {
String cellValue = null;
switch (cell.getCellType()) {
case NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case STRING:
cellValue = cell.getStringCellValue();
break;
case BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA:
cellValue= cell.getCellFormula();
case BLANK:
cellValue="BLANK";
default:
cellValue ="DEFAULT";
}
return cellValue;
}
}Step 7: Prepare test data in .xls file
Step 8: Create another class with main method to test ExcelReader class method. Refer below code for
package stepDef;
import java.io.IOException;
import java.util.Map;
public class ExcelReaderTest2 {
public static void main(String[] args) throws IOException {
ExcelReader ex = new ExcelReader("C:\\Users\\ajit\\Desktop \\userData.xls", 0);
Map<String, Map<String, String>> excelData =ex.getExcelAsMap();
System.out.println("Excel Data for 2nd row and column- AccountNo : "+excelData.get("2").get("AccountNo"));
System.out.println("excelData as Map: "+excelData);
}
}Step 9: Run above test method and verify result: Refer below for console output
Well done! you completed code to read excel data into Map. Now it helped you to read all the excel data on one go and return as Map.
Hope you enjoyed this article. Feel free to reach out to us if you face any issue or share your feedback on thoughtcoders2020@gmail.com.
[mailpoet_form id=”2″]
