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.
Table of Contents (Click on links below to navigate)
- 1 What all functionalities/features will you get from this article?
- 2 What will you learn after implementing this application?
- 3 What is Poiji ?
- 4 Software Used in this project?
- 5 Pre-requisites
- 6 Coding Steps
- 7 Running application
- 8 Testing Results
- 9 How to do our own implementation from this Example with minimal changes?
- 10 Can we use this implementation in a real project ?
- 11 What are the changes do we need to do if we want to use this functionality in our real project ?
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)
We will add following ‘poiji’ dependency in pom.xml as it is an external jar to get the features of Microsoft Excel.
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’.
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:
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
|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
Once all above files created in STS, your project structure should look similar to below screenshot:
In order to run the application, right click on Project and then select Run As >> Spring Boot App.
You can check the saved records into database accordingly, something like below screen:
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.