We can export data to Excel file in Spring Boot Application using Apache POI library . This library is having classes which help us to write a content into the Excel file. We will export data from the database into the Excel file through the Spring Boot application Using Spring Web, Maven, Spring Data JPA and H2 database. We will create an example project in the Spring Boot web application step-by-step to achieve this export data to an Excel file.
Table of contents
1. Creating Spring Boot Project
2. Configuring Apache POI
3. Maven Dependency
4. Configuring H2 Database
5. Creating a JPA Entity class
6. Creating a JPA Repository
7. Creating a Service
8. Creating an Excel File Generator class
9. Creating a Controller class
10. Creating a view page
11. Inserting Records
12. Run the Spring Boot Project
13. Conclusion
1. Creating Spring Boot Project
We are creating a Spring Boot Project from this Spring Initializr.
Select the following dependencies:
• Spring Web
• Spring Data JPA
• H2 Database
Keep Eclipse IDE ready to export this created Spring Boot project.
Project Structure of Export Data to Excel file in Spring Boot
This is the project structure of Export Data to Excel file in Spring Boot on the Eclipse IDE.
2. Configuring Apache POI
These are dependencies we need to configure for the implementing export data to Excel file in Spring Boot.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.1.0</version>
</dependency>→ Configure these dependencies in the pom.xml file of the project.
3. Maven Dependency
Here are the complete dependencies for the performing export data to Excel file in Spring Boot application on the pom.xml file.
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.example</groupId>
<artifactId>Export_Excel_Spring_Boot_Example</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>
<name>Export_Excel_Spring_Boot_Example</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>17</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>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.1.0</version>
</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>4. Configuring H2 database
We are defining the H2 database configuration and other configurations in the application.properties file.
application.properties
#H2 Database
spring.datasource.url=jdbc:h2:mem:test
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto= update
server.port=8888
server.servlet.context-path= /demo5. Creating a JPA Entity class
We are creating a JPA entity class to create a table in the database for this project.
Student.java
package com.springjava.entity;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
@Entity
public class Student {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
private String studentName;
private String email;
private String mobileNo;
public Student() {
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getMobileNo() {
return mobileNo;
}
public void setMobileNo(String mobileNo) {
this.mobileNo = mobileNo;
}
}6. Creating a JPA Repository
We are creating a JPA repository by extending the JpaRepository interface to perform some persistence operations. The JpaRepository interface is available in Spring Data JPA.
StudentRepository.java
package com.springjava.repo;
import org.springframework.data.jpa.repository.JpaRepository;
import com.springjava.entity.Student;
public interface StudentRepos extends JpaRepository < Student, Long > {
}7. Creating a Service
We creating a StudentService interface and its implementation class StudentServiceImpl.
StudentService.java
package com.springjava.service;
import java.util.List;
import com.springjava.entity.Student;
public interface StudentService {
void addStudent(Student student);
List < Student > getTheListStudent();
}StudentServiceImpl.java
package com.springjava.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.springjava.entity.Student;
import com.springjava.repo.StudentRepos;
@Service
public class StudentServiceImpl implements StudentService {
@Autowired
StudentRepos studentRepo;
@Override
public void addStudent(Student student) {
studentRepo.save(student);
}
@Override
public List < Student > getTheListStudent() {
return studentRepo.findAll();
}
}
8. Creating an Excel File Generator class
We are creating a Java class to export data to Excel file in Spring Boot application that we have created.
ExcelGenerator.java
package com.springjava.util;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.springjava.entity.Student;
public class ExcelGenerator {
private List < Student > studentList;
private XSSFWorkbook workbook;
private XSSFSheet sheet;
public ExcelGenerator(List < Student > studentList) {
this.studentList = studentList;
workbook = new XSSFWorkbook();
}
private void writeHeader() {
sheet = workbook.createSheet("Student");
Row row = sheet.createRow(0);
CellStyle style = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
font.setBold(true);
font.setFontHeight(16);
style.setFont(font);
createCell(row, 0, "ID", style);
createCell(row, 1, "Student Name", style);
createCell(row, 2, "Email", style);
createCell(row, 3, "Mobile No.", style);
}
private void createCell(Row row, int columnCount, Object valueOfCell, CellStyle style) {
sheet.autoSizeColumn(columnCount);
Cell cell = row.createCell(columnCount);
if (valueOfCell instanceof Integer) {
cell.setCellValue((Integer) valueOfCell);
} else if (valueOfCell instanceof Long) {
cell.setCellValue((Long) valueOfCell);
} else if (valueOfCell instanceof String) {
cell.setCellValue((String) valueOfCell);
} else {
cell.setCellValue((Boolean) valueOfCell);
}
cell.setCellStyle(style);
}
private void write() {
int rowCount = 1;
CellStyle style = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
font.setFontHeight(14);
style.setFont(font);
for (Student record: studentList) {
Row row = sheet.createRow(rowCount++);
int columnCount = 0;
createCell(row, columnCount++, record.getId(), style);
createCell(row, columnCount++, record.getStudentName(), style);
createCell(row, columnCount++, record.getEmail(), style);
createCell(row, columnCount++, record.getMobileNo(), style);
}
}
public void generateExcelFile(HttpServletResponse response) throws IOException {
writeHeader();
write();
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
workbook.close();
outputStream.close();
}
}
→ XSSFWorkbook is creating a Workbook that is our Excel file.
→ We create an instance of XSSFWorkbook then we call the “createSheet()” method for creating an Excel file with a name.
→ We are creating Rows and cells of an Excel sheet.
→ After that we are writing data into the Excel sheet.
9. Creating a Controller class
We are creating a Controller class that contains the request URL to export Data to Excel file in Spring Boot application.
StudentController.java
package com.springjava.controller;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import com.springjava.entity.Student;
import com.springjava.service.StudentService;
import com.springjava.util.ExcelGenerator;
@Controller
public class StudentController {
@Autowired
private StudentService studentService;
@GetMapping("/export-to-excel")
public void exportIntoExcelFile(HttpServletResponse response) throws IOException {
response.setContentType("application/octet-stream");
DateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd_HH:mm:ss");
String currentDateTime = dateFormatter.format(new Date());
String headerKey = "Content-Disposition";
String headerValue = "attachment; filename=student" + currentDateTime + ".xlsx";
response.setHeader(headerKey, headerValue);
List <Student> listOfStudents = studentService.getTheListStudent();
ExcelGenerator generator = new ExcelGenerator(listOfStudents);
generator.generateExcelFile(response);
}
}
10. Creating a view page
We are creating a view page under the static folder of the export data to the Excel project
index.html
<!DOCTYPE html>
<html>
<head>
<title>Export Excel File</title>
</head>
<body class="container">
<p>Click on the download button for exporting data into the excel file.</p>
<a href="http://localhost:8888/demo/export-to-excel" class="btn btn-primary">Download the Excel File</a>
</body>
</html>
11. Inserting Records
We are inserting some records into the table by implementing CommandLineRunner when the project starts running.
package com.springjava;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import com.springjava.entity.Student;
import com.springjava.service.StudentService;
@SpringBootApplication
public class ExportExcelSpringBootExampleApplication implements CommandLineRunner {
@Autowired
private StudentService studentService;
public static void main(String[] args) {
SpringApplication.run(ExportExcelSpringBootExampleApplication.class, args);
}
@Override
public void run(String...a) {
for (int i = 0; i <= 10; i++) {
Student student = new Student();
student.setStudentName("Student Name");
student.setEmail("student@mail.com");
student.setMobileNo("XXXXXXXXXX");
studentService.addStudent(student);
}
}
}
12. Run the Spring Boot Project
Right-click on the SpringBootApplication class, then click Run As, and select Java Application.
Type this URL on the browser “http://localhost:8888/demo/” to test.
Click on the Download the Excel File link to download the Excel file.
Check the H2 database Console:
You can type this URL on the browser “http://localhost:8888/demo/h2-console/” for the testing table to be created and whether data is inserted properly or not.
H2 database table:
Refer to this article to learn how to import data from an Excel file in Spring Boot.
13. Conclusion
In this topic, we learned How to transform data into an Excel file from a database in the Spring Boot Application and use the Apache POI library.
Nice
Perfect