How to Write Data from Excel File into a HashMap using Java and Apache POI

How to Write Data from Excel File into a HashMap using Java and Apache POI?

by

In this article, let us look at how to write data from an excel file into a HashMap using Apache POI.

Apache POI library in Java:

ApachePOI is an abbreviation for Poor Obfuscation Implementation, a Java API for reading and writing Microsoft documents. It includes the Wordbook, Sheet, Row, and Cell classes and interfaces. Apache POI may be used to access files with the ‘xlsx’ extension as well as files with the ‘xlsx’ extension.

Apache POI is a free and open-source Java library for creating and manipulating Microsoft Office file formats. The following file formats should be able to be created, modified, and displayed/read using POI. For example, because java does not include built-in support for interacting with Excel files, we must search for open-source APIs to complete the job.

In Java, a HashMap is a form of collection that consists of a key-value pair. Apache POI is an open-source Java library that is used to execute a variety of operations on Microsoft Excel, MS Office, and other applications.

Also Read: Data Structures and Algorithms Lecture Notes

Pre-requisites:

We required the following software on your computers to work with these.

  • Check to see if your machine has Java; if not, obtain the most recent Java SDK version from here.
  • Create a Maven project.
  • Add apache poi dependency from the maven repository website.

Writing Data from Excel File into a HashMap using Java and Apache POI

Here we use STS(Spring Tool Suite) to write data from HashMap to an excel sheet

You can either use MyEclipse or STS(SpringToolSuite) for this. Here I am using STS.

STS:

Spring Tool Suite (STS) is a Java IDE designed specifically for the development of Spring-based enterprise applications. It is simpler, quicker, and more convenient. Most notably, it is built on the Eclipse IDE. STS is a free, open-source software platform powered by VMware. Spring Tools 4 represents the next generation of Spring tooling for your preferred development environment. It was largely redesigned from the bottom up to give world-class support for developing Spring-based enterprise apps, regardless of whether you choose Eclipse, Visual Studio Code, or Theia IDE.

Note:

If you are using MyEclipse then create a maven project.

STS directly imports all the classes from maven if we just add dependencies.

Step#1: Open STS(SpringToolSuite) and create a spring stater project say WriteExceltoHashMap in a package com.sheetstips.

Step#2: Add the spring web dependency and click on Finish. Now the project is created successfully.

Step#3: Open the pom.xml file and add the following apache poi dependency from the maven repository.

https://mvnrepository.com/artifact/org.apache.poi/poi

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.12</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.12</version>
</dependency>

Step#4: Create a new class say ExcelDatatoHashMap in the src/main/java folder with the same package com.sheetstips.

Step#5: Write the below code in the above ExcelDatatoHashMap class

Approach:

  • Import all the required libraries using the import keyword.
  • Import HashMap library using the import keyword.
  • Import apache poi library using the import keyword.
  • Create a class say ExcelDatatoHashMap.
  • Inside the Main driver method, Create an object for the FileInputStream by passing the input excel file path as an argument to it (Here it reads the given Excel file).
  • Select the worksheet to work with from the above workbook(excel file) and
  • Store it in a variable
  • Create a HashMap object using the new keyword.
  • Iterate till the number of the rows of the sheet using the for loop (Here we use getLastRowNum() function to get number of rows in the excel file).
  • Get the cell value of the current row and convert it to String Value using the getRow(), getCell(), getNumericCellValue() functions.
  • Here this value is the key for the hashmap
  • Get the cell value of the current row and convert it to String Value using the getRow(),getCell(),getStringCellValue() functions.
  • Here this value is the value for the hashmap.
  • Store the above key and value to the hashmap using the put() function
  • Create an iterator to print all the entries of the hashmap
  • Loop till the hashmap iterator has next value using the hasNext() function
  • Get the hashmap corresponding key and value pairs using the next() function
  • Print the corresponding key and value of the hashmap using the getKey() and getValue() functions
  • Close the above workbook using the close() function
  • Close the above-opened input file using the close() function.
  • The Exit of the Program

Below is the implementation:

package com.sheetstips;

//Import all the required libraries using the import keyword
import java.io.FileInputStream;
import java.io.IOException;
//Import HashMap library using the import keyword
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;

//Import apache poi library using the import keyword
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

//Create a class say ExcelDatatoHashMap
public class ExcelDatatoHashMap {
    // Main driver method
    public static void main(String[] args) throws IOException {
        
        // Create an object for the FileInputStream by passing the input excel file
        // path as an argument to it (Here it reads the given Excel file)
        FileInputStream gvn_excelfile = new FileInputStream("sampleExcelFile.xlsx");

        XSSFWorkbook workbokObj = new XSSFWorkbook(gvn_excelfile);
        System.out.println(workbokObj);
        // Select the worksheet to work with from the above workbook(excel file) and
        // store it in a variable
        XSSFSheet worksheet = workbokObj.getSheet("Sheet1");

        // Create an HashMap object using the new keyword.
        HashMap<String, String> map = new HashMap<String, String>();

        // Iterate till the number of the rows of the sheet using the for loop
        // (Here we use getLastRowNum() function to get number of rows in the excel
        // file)
        for (int r = 0; r <= worksheet.getLastRowNum(); r++) {
            // Get the cell value of the current row and convert it to String Value using
            // the getRow(),getCell(),getNumericCellValue() functions
            // Here this value is the key for the hashmap
            String key = worksheet.getRow(r).getCell(0).getStringCellValue();
            // Get the cell value of the current row and convert it to String Value using
            // the getRow(),getCell(),getStringCellValue() functions
            // Here this value is the value for the hashmap
            String value = worksheet.getRow(r).getCell(1).getStringCellValue();
            // Store the above key and value to the hashmap using the put() function
            map.put(key, value);
        }

        // Create an iterator to print all the entries of the hashmap
        Iterator<Entry<String, String>> new_Iterator = map.entrySet().iterator();

//Loop till the hashmap iterator has next value using the hasNext() function
        while (new_Iterator.hasNext()) {
            // Get the hashmap corresponding key and value pairs using the next() function
            Map.Entry<String, String> new_Map = (Map.Entry<String, String>) new_Iterator.next();
//Print the corresponding key and value of the hashmap using the getKey() and getValue() functions
            System.out.println(new_Map.getKey() + "|" + new_Map.getValue());
        }

        // Close the above workbook using the close() function
        workbokObj.close();
        // Close the above opened input file using the close() function
        gvn_excelfile.close();
    }
}

Step#6: Now run the code by Right-clicking on the project and click on run –> Run as spring boot App

Output:

1|Sheetstips
2|Btechgeeks
3|Python-Programs
4|PythonArrays


 


		


				
			




			

				


		

		
Leave a Comment