You are here
Home > Spring Boot >

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

 How To Convert Excel Data Into List Of Java Objects : Poiji APIAlmost 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.

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.

'poiji' dependency in pom.xml
<dependency>
	<groupId>com.github.ozlerhakan</groupId>
	<artifactId>poiji</artifactId>
	<version>3.0.0</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

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:

InvoiceExcel .java

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 + "]";
	}
	
}
ExcelDataToJavaListTest.java
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
com.dev.springboot.entity Invoice.java create Entity class to map excel columns and also database tables
com.dev.springboot.repo InvoiceRepository.java create Repository Interface to do database operations
com.dev.springboot.service ExcelPoijiService.java create Interface to implement service
com.dev.springboot.service ExcelPoijiServiceImpl.java create Excel Poiji Service Implementation
com.dev.springboot ExcelDataUploadRunner create Runner class to run logic only once
src/main/resources application.properties update properties file to declare common properties in the project

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
Invoice.java
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;

}
InvoiceRepository.java (Interface)
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> {

}
ExcelPoijiService.java (Interface)
package com.dev.springboot.service;

import java.util.List;

import com.dev.springboot.entity.Invoice;

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

}
ExcelPoijiServiceImpl.java
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;
	}
}
ExcelDataUploadRunner.java
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:

 How To Convert Excel Data Into List Of Java Objects : Poiji API

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:

img_33_excel5

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.

close

2 thoughts on “How to convert excel data into List of Java Objects : Poiji API

  1. What if entity class contains the other classes(has a relationship). In that case a @excelcellname won’t be sufficient. Right?

    @Entity
    Public class Person {
    @excelcellname(“name”)
    Private string name
    //Now what to do about this address which is other custom class.
    Private List personAddresses

    }

Leave a Reply

Top