Last Updated on September 8th, 2023

How to convert excel data into List of Java Objects : Poiji API

Almost every Java developer come across one of the most common requirement expected by the clients. The requirement is working with excel either to generate reports or to take input data from excel. As a java developer, we should be aware of the mapping between excel data & java objects. If we can have an API which can directly map excel cell to a field (property) of a java class, then it will become very easy to implement any kind of operation on excel. No surprise ! We have an API called Poiji(A Java Excel API).

It is developed on top of very popular Apache POI just like Spring Boot on top of Spring. It is available in the maven repository. Thanks to the creator of Poiji !. In fact, we are going to learn it’s implementation in our topic ‘How to convert excel data into List of Java Objects : Poiji API’.

Additionally, the interesting thing is that it uses lots of annotations to make our tasks even easier, just like Spring Boot again. Our main focus in this article will be on “How to convert excel data into List of Java Objects : Poiji API”  but we will learn additional things as well. Equally important, we will implement it using Spring Boot Starter Project. You can also use this concept in excel data upload. Furthermore, let’s get into the topic “How to convert excel data into List of Java Objects : Poiji API” using poiji example now.

What all functionalities/features will you get from this article?

1) How to convert Excel cell values into the List of Java Objects?
2) How to map Excel column with java class properties/variables just like JPA/Hibernate etc…?
3) Additionally, How to use annotation based mapping of excel columns to Model class in java using poiji example?
4) Also, How to save excel data into Database using Spring Boot Data JPA in Spring Boot ?
5) Poiji latest version (currently 3.0.0) has been used to implement the Project.
6) Code is successfully tested on JDK8, JDK9 and JDK14 versions.

What will you learn after implementing this application?

1) How to create a Spring Boot standard application that incorporates industry level back-end project design ?
2) Also, how to design a java application including layers using Entity, Service, Repository etc. as in real time project designs ?
3) Where & how to use Annotations: @ExcelCellName, @ExcelCell, @ExcelRow, @Value, @Autowired, @Service, @Repository, @Entity, @Id, @GeneratedValue etc.
4) Then, working with Runner classes in Spring Boot.
5) Implementation of the Poiji API created on top of the most popular Apache POI(Java API for Microsoft Documents).
6) Also working with application.properties file.
7) How to upload data as a batch into database using Spring Boot?
8) Equally important, How to write modular & reusable code?

9) How to implement dynamic code with minimal changes keeping future change requests in mind?
10) How to write code without hard-coding the values?
11) Last but not the least you will learn ‘How to convert excel data into List of Java Objects : Poiji API’.

What is Poiji ?

Poiji is nothing, but a Java library that programmatically maps excel sheet to Java classes. Moreover, it converts excel rows to a list of Java objects. In other words, it converts each row of the excel data into Java object. Poiji internally uses Apache POI to further process this type of conversion. It is very handy library to convert excel to java object.

Why Poiji API?

The Poiji API simplifies the process of reading Excel data in Java applications, offering an spontaneous and effective solution. With its support for various Excel formats, customizable mapping, and high performance, Poiji helps developers to deal with complex data extraction tasks with simplicity.

Software Used in this project?

Below is the list of software that we have used in this poiji example.

♦ STS (Spring Tool Suite) : Version-> 4.7.1.RELEASE
♥ MySQL Database : Version ->8.0.19 MySQL Community Server
♦ JDK8 or later versions (Extremely tested on JDK8, JDK9 and JDK14)

External Dependencies

We will add following ‘poiji’ dependency in pom.xml as it is an external jar to get the features of Microsoft Excel.

<dependency>
    <groupId>com.github.ozlerhakan</groupId>
    <artifactId>poiji</artifactId>
    <version>4.1.1</version>
</dependency>

Pre-requisites

If you want to store excel data into database, you should already have an existing database. Otherwise, create a MySQL database to save the data. If you just want to test the functionality, then you can use our configurations as database name ‘exceldata’ and table name ‘invoice’.

Coding Steps

Working with Poiji API includes 4 simple steps as shown below:

Step#1: Include the Poiji Dependency

To use Poiji in our Java project, we need to include the Poiji dependency in our build configuration. We can typically achieve this by adding the Maven or Gradle dependency, as appropriate for our project.

Step#2: Create Java POJOs

Define Java classes (POJOs) that correspond to the structure of the data we intend to extract from the Excel file. Poiji will map Excel columns to Java object fields based on annotations.

Step#3: Annotate Java Field

Annotate the Java fields with Poiji annotations to specify the column mapping, date formats, and other configurations.

Step#4:Read Excel Data

Use Poiji to read the Excel data and convert it into Java objects. Poiji provides methods to read data from Excel files and return it as a list of Java objects.

Just To test with a poiji example : how Poiji API converts Excel data into List of Objects?

To test the same, we will have only two classes as below:

import com.poiji.annotation.ExcelCellName;
import com.poiji.annotation.ExcelRow;

public class InvoiceExcel {
	
	@ExcelRow                    
    private int rowIndex;
	
	@ExcelCellName("Name") 
	private String name;
	
	@ExcelCellName("Amount")    
	private Double amount;
	
	@ExcelCellName("Number")   
	private String number;
	
	@ExcelCellName("RecievedDate") 
	private String receivedDate;

	@Override
	public String toString() {
		return "InvoiceExcel [rowIndex=" + rowIndex + ", name=" + name + ", amount=" + amount + ", number=" + number
				+ ", receivedDate=" + receivedDate + "]";
	}
	
}

package com.dev.springboot.util;

import java.io.File;
import java.util.List;

import com.poiji.bind.Poiji;

public class ExcelDataToJavaListTest {

	public static void main(String[] args) {
		
		File file = new File("D:/ExcelUploadRepo/InvoiceDetailsSheet.xlsx");
		List<InvoiceExcel> invoices = Poiji.fromExcel(file, InvoiceExcel.class);
		System.out.println("Printing List Data: " +invoices);

	}

}

On running above test class, below is the output:

Printing List Data: 
[
InvoiceExcel [rowIndex=1, name=Inv1, amount=3500.0, number=CGP_Inv101, receivedDate=8/30/20],
InvoiceExcel [rowIndex=2, name=Inv2, amount=2424.0, number=BBR_Inv201, receivedDate=8/30/20],
InvoiceExcel [rowIndex=3, name=Inv3, amount=3424.0, number=OGK_Inv304, receivedDate=8/31/20], 
InvoiceExcel [rowIndex=4, name=Inv4, amount=4424.75, number=NKL_Inv345, receivedDate=8/30/20],
InvoiceExcel [rowIndex=5, name=Inv5, amount=5424.0, number=ABC_Inv101, receivedDate=8/29/20]
]

To test how Poiji API converts Excel data into List of Objects and save the data into database?

Step#1 : Create Project in STS

If you are new in Spring Boot go to Internal Link to create a sample project in spring boot. While creating project in STS, add 2 starters ‘MySqL Driver’ and  ‘Spring Data JPA’. You can also add ‘Spring Boot DevTools’ optionally.

Step#2 : Writing classes & updating application.properties

Package/Location

Class/Interface name

Create/Update

Purpose

Below are the codes for each file

application.properties
--------------------------------------------------------------------
#DB Connection Properties
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/exceldata
spring.datasource.username=root
spring.datasource.password=devs

# Data JPA Properties
spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

# Excel file as input for storing it's data
filePath=D:/ExcelUploadRepo/InvoiceDetailsSheet.xlsx

package com.dev.springboot.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

import com.poiji.annotation.ExcelCell;
import com.poiji.annotation.ExcelCellName;

@Entity
public class Invoice {
	
	@Id
	@GeneratedValue
	private Integer id;
	
	@ExcelCellName("Name") // ("Name") is the column name in excel
	private String name;
	
	@ExcelCell(1)    // (1) indicates excel column # 1
	private Double amount;
	
	@ExcelCell(2)   // (2) indicates excel column # 2
	private String number;
	
	@ExcelCellName("RecievedDate") // ("RecievedDate") is the column name in excel
	private String receivedDate;

}

package com.dev.springboot.repo;

import org.springframework.data.jpa.repository.JpaRepository;

import com.dev.springboot.entity.Invoice;

public interface InvoiceRepository extends JpaRepository<Invoice, Integer> {

}

package com.dev.springboot.service;

import java.util.List;

import com.dev.springboot.entity.Invoice;

public interface ExcelPoijiService {
	
	List<Invoice> getListfromExcelData();

}

package com.dev.springboot.service;

import java.io.File;
import java.util.List;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import com.dev.springboot.entity.Invoice;
import com.poiji.bind.Poiji;

@Service
public class ExcelPoijiServiceImpl implements ExcelPoijiService{


	@Value("${filePath}")
	public String FILE_PATH;
	
	public List<Invoice> getListfromExcelData() {
		File file = new File(FILE_PATH);
		List<Invoice> invoices = Poiji.fromExcel(file, Invoice.class);
		return invoices;
	}
}

package com.dev.springboot;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

import com.dev.springboot.repo.InvoiceRepository;
import com.dev.springboot.service.ExcelPoijiService;

@Component
public class ExcelDataUploadRunner implements CommandLineRunner {
	
	@Autowired
	InvoiceRepository repo;
	
	@Autowired
	ExcelPoijiService excelPoijiService;
	
	@Override
	public void run(String... args) {
		
		repo.saveAll(excelPoijiService.getListfromExcelData());
	}

}

Once all above files created in STS, your project structure should look similar to below screenshot:

Running application

In order to run the application, right click on Project and then select Run As >> Spring Boot App.

Testing Results

You can check the saved records into database accordingly, something like below screen:

FAQ

How to do our own implementation from this Example with minimal changes?

You need to change values of properties entered in the application.properties file
1) First update the values of first 4 properties as per your database
2) Then update the upload file path with file name in the value of property ‘filePath’

Below are the changes in java files :
3) To store excel records into database you need to create new Entity class and new Repository class in place of ‘Invoice.java’ & ‘InvoiceRepository.java’ accordingly as per your requirements.
4) Then you need to change all occurrences of entity class name used in ‘ExcelPoijiService.java’ and ‘ExcelPoijiServiceImpl.java’ respectively. It’s ‘Invoice’ in our case.
5) Finally interesting thing is that you don’t need to do any change in Runner class ‘ExcelDataUploadRunner.java’

Can we use this implementation in a real project?

Of course, If your project team allows you to use Poiji API, then you can try this implementation in a real project with modification described above as per your requirement.

What are the changes do we need to do if we want to use this functionality in our real project?

In fact, you can go through the minimal modifications suggested in section ‘How to do our own implementation from this Example with minimal changes?’ of this article and you are done.

⇒ Furthermore, If you face any issue on running this project, don’t hesitate to put your comments below. Also, If want to learn more on Poiji API, kindly visit here.