Import data from Excel file in the Spring Boot application use the Apache POI library to read the Excel file data and parse the data of the file then we need to store that data into the Java List. In this topic, we will learn how to import Excel data to the database in Spring Boot. We will create a restful web service example of a spring boot application using Spring Web, Spring Data JPA, Lombok, H2 database and Apache POI. We will create an Excel sheet with some data in Microsoft Excel software on our computer to upload and then import data from it to the H2 database using Spring Boot Application.
The above diagram shows a process for importing an Excel file to the Spring Boot Application and providing JSON data as output. For this, we will create one rest API web service for uploading an Excel file and another rest API web service for giving responses in JSON form. Let’s begin to implement
1. Creating an Excel File
We are creating an Excel file with some data. Here is the image of the Excel file(student.xlsx) that we created for uploading through the application.
2. Creating a Spring Boot Starter Project
We are creating a Spring Boot Application from the web tool Spring Initializr or you can create from IDE(STS, vs code etc.) which you are using.
Add the following dependencies:
• Spring Web
• Lombok
• H2 Database
• Spring Data JPA
3. Keep the IDE ready
We are importing this created application into our Eclipse IDE or you can import it into another IDE you use.
Project Structure of Import data from Excel file
4. Configuring Apache POI dependency
We are configuring this dependency into the pom.xml file of that Spring Boot Application.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.1.0</version>
</dependency>→ This dependency library provides us with classes to read Excel Sheet data.
4. Maven Dependency
Here is the complete pom.xml file with the Apache POI dependency of this application.
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.3</version>
<relativePath /> <!-- lookup parent from repository -->
</parent>
<groupId>com.springjava</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.1.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>5. Defining the Configurations
We are configuring the H2 database configuration and configuration Multipart file for Servlet in the application.properties file.
application.properties
# H2 Database Configuration
spring.datasource.url=jdbc:h2:mem:test
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=update
spring.h2.console.enabled=true
# Multipart File Configuration
spring.servlet.multipart.max-file-size=2MB
spring.servlet.multipart.max-request-size=2MB→ This is spring.servlet.multipart.max-file-size for each request of max file size.
→ This is spring.servlet.multipart.max-request-size for max request size of multipart or form-data.
6. Creating a JPA Entity class
We are creating a JPA entity class to create a table through Java code and inserting Excel sheet data into the table. This class has some properties(id, studentName, email and mobileNo).
Student.java
package com.springjava.entity;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import lombok.Data;
@Data
@Entity
public class Student{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String studentName;
private String email;
private String mobileNo;
}→ The @Data annotation is a Lombok annotation which generates a constructor, setter method, getter method, etc. for this Java Bean class.
→ This @Entity annotation generates a table in the created database.
→ This @Id annotation is used to create the Primary key column for the table.
→ This @GeneratedValue annotation is used to increment the column.
→ The GenerationType.IDENTITY is used to assign primary keys for the entity using the database identity column.
7. Creating a JPA Repository
We are creating a JPA Repository to use predefined query methods ((saveAll(),findAll(), etc.)to perform persistence operations through Java code.
StudentRepository.java
package com.springjava.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import com.springjava.entity.Student;
public interface StudentRepository extends JpaRepository<Student, Integer> {
}→ The JpaRepository provides a query method instead of a query. This interface is available in Spring Data JPA.
8. Creating a class to read Excel data
We are creating a class that reads Excel sheet data and stores it in the List.
ExcelUtility.java
package com.springjava.util;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import com.springjava.entity.Student;
public class ExcelUtility {
public static String TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
static String[] HEADERs = { "ID", "Student Name", "Email", "Mobile No." };
static String SHEET = "student";
public static boolean hasExcelFormat(MultipartFile file) {
if (!TYPE.equals(file.getContentType())) {
return false;
}
return true;
}
public static List<Student> excelToStuList(InputStream is) {
try {
Workbook workbook = new XSSFWorkbook(is);
Sheet sheet = workbook.getSheet(SHEET);
Iterator<Row> rows = sheet.iterator();
List<Student> stuList = new ArrayList<Student>();
int rowNumber = 0;
while (rows.hasNext()) {
Row currentRow = rows.next();
// skip header
if (rowNumber == 0) {
rowNumber++;
continue;
}
Iterator<Cell> cellsInRow = currentRow.iterator();
Student stu = new Student();
int cellIdx = 0;
while (cellsInRow.hasNext()) {
Cell currentCell = cellsInRow.next();
switch (cellIdx) {
case 0:
stu.setId((int) currentCell.getNumericCellValue());
break;
case 1:
stu.setStudentName(currentCell.getStringCellValue());
break;
case 2:
stu.setEmail(currentCell.getStringCellValue());
break;
case 3:
stu.setMobileNo(currentCell.getStringCellValue());
break;
default:
break;
}
cellIdx++;
}
stuList.add(stu);
}
workbook.close();
return stuList;
} catch (IOException e) {
throw new RuntimeException("fail to parse Excel file: " + e.getMessage());
}
}
}→ In this class, we create two methods hasExcelFormat(MultipartFile file) and excelToStuList(InputStream is).
→ This hasExcelFormat(MultipartFile file) method checks whether the file format is Excel or not.
→ This excelToStuList(InputStream is) method reads the Excel Sheet data returns list of students.
→ Creating Workbook from the InputStream
Workbook workbook = new XSSFWorkbook(InputStream is);→ Create a Sheet from the workbook.getSheet() method
Sheet sheet = workbook.getSheet(SHEET);→ Get an Iterator with a Row from the sheet.iterator() method
Iterator<Row> rows = sheet.iterator();
Iterating each Row over Cells
Iterator<Row> rows = sheet.iterator();
int rowNumber = 0;
while (rows.hasNext()) {
Row currentRow = rows.next();
if (rowNumber == 0) {
rowNumber++;
continue;
}
Iterator<Cell> cellsInRow = currentRow.iterator();→ Iterating each Cell and using getNumericCellValue(), getStringCellValue()… methods to read and parse the content of the cell of the sheet.
9. Creating Service
We are creating a service interface with two methods declaring(save() and findAll()).
StudentService.java
package com.springjava.service;
import java.util.List;
import org.springframework.web.multipart.MultipartFile;
import com.springjava.entity.Student;
public interface StudentService {
void save(MultipartFile file);
List<Student> findAll();
}We are creating a service implementation class with @Service annotation.
StudentServiceImpl.java
package com.springjava.service;
import java.io.IOException;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import com.springjava.entity.Student;
import com.springjava.repository.StudentRepository;
import com.springjava.util.ExcelUtility;
@Service
public class StudentServiceImpl implements StudentService {
@Autowired
StudentRepository stuRepo;
@Override
public void save(MultipartFile file) {
try {
List<Student> stuList = ExcelUtility.excelToStuList(file.getInputStream());
stuRepo.saveAll(stuList);
} catch (IOException ex) {
throw new RuntimeException("Excel data is failed to store: " + ex.getMessage());
}
}
@Override
public List<Student> findAll() {
return stuRepo.findAll();
}
}→ This save(MultipartFile file) method stores Excel data in a database.
→ This findAll() method reads database data and returns List<Student>.
10. Creating a Controller
We are creating a controller StudentController class to create Rest Apis.
StudentController.java
package com.springjava.controller;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import com.springjava.entity.Student;
import com.springjava.service.StudentService;
import com.springjava.util.ExcelUtility;
@RestController
@RequestMapping("/api")
public class StudentController {
@Autowired
StudentService stuService;
@PostMapping("/excel/upload")
public ResponseEntity<?> uploadFile(@RequestParam("file") MultipartFile file) {
String message = "";
if (ExcelUtility.hasExcelFormat(file)) {
try {
stuService.save(file);
message = "The Excel file is uploaded: " + file.getOriginalFilename();
return ResponseEntity.status(HttpStatus.OK).body(message);
} catch (Exception exp) {
message = "The Excel file is not upload: " + file.getOriginalFilename() + "!";
return ResponseEntity.status(HttpStatus.EXPECTATION_FAILED).body(message);
}
}
message = "Please upload an excel file!";
return ResponseEntity.status(HttpStatus.BAD_REQUEST).body(message);
}
@GetMapping("/student-list")
public ResponseEntity<?> getStudents() {
Map<String, Object> respStu = new LinkedHashMap<String, Object>();
List<Student> studList = stuService.findAll();
if (!studList.isEmpty()) {
respStu.put("status", 1);
respStu.put("data", studList);
return new ResponseEntity<>(respStu, HttpStatus.OK);
} else {
respStu.clear();
respStu.put("status", 0);
respStu.put("message", "Data is not found");
return new ResponseEntity<>(respStu, HttpStatus.NOT_FOUND);
}
}
}→ The @RestController annotation is used to make this class RestController to create Rest Apis.
→ The @RequestMapping annotation is used to make the base URL.
→ The @Autowired annotation is used to inject Service.
→ The @PostMapping annotation is used to receive HTTP post requests.
→ The @GetMapping annotation is used to receive HTTP requests.
→ The POST/excel/upload: uploadFile().
→ The GET/student-list: getStudents().
11. Run the Spring Boot Application and Check
Right-click on the DemoApplication.java, then click RunAs and select Java Application.
Let’s test Rest Apis on the Postman
POST: http://localhost:8080/api/excel/upload
Test and Check H2 Database
Check the H2 database console and browse this URL “http://localhost:8080/h2-console” for data inserted in the table or not.
See the table below:
GET: http://localhost:8080/api/student-list
FAQ
Q1: How do I send an Excel file to rest API in Spring Boot?
Ans. Create a rest API service method with @PostMapping annotation which accepts MultipartFile requests.
Q2: How do I insert data from an Excel file into the database in Spring Boot?
Ans. By using the Apache POI library to read the content of the Excel and save it in the List and then that List inserts it into the database by Spring Data JPA in Spring Boot.
Q3: How do I export database data in an Excel file in Spring Boot?
Ans. By using the Apache POI library to export the database into an Excel file in Spring Boot.
Refer to this article to learn how to export data to Excel in Spring Boot.
Conclusion
In this topic, we learnt how to import Excel data to a database using Spring Boot with Rest Apis and Apache POI library.