Export Data to Excel file in Spring Boot
In this topic, we will learn to export data into an excel file from a database(H2 Database) in Spring Boot. For this, we will use the Apache POI library for exporting excel. We will create an example project in Spring Boot step-by-step to achieve this export data to an excel file.
Table of content:
1. Keep eclipse IDE ready
2. Configuring Apache POI
3. Create a Spring Boot Starter Project
4. Define Database Connection in application.properties
5. Create Entity class
6. Create a Repository
7. Create a Service
8. Create an Excel File Generator class
9. Create a Controller class
10. Create a view page on the static folder of the export data to the excel project
11. Create CommandLineRunner on SpringBootApplication class
12. Run the Project
13. Conclusion
1. Keep eclipse IDE ready
2. Create a Spring Boot Starter Project
Select Spring Web, Spring Data JPA, and H2 Database dependencies for the project.
3. Configuring Apache POI:
<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.
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>
<name>Export_Excel_Spring_Boot_Example</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>11</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. Define Database Connection in application.properties
#H2 Database
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=admin
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= /demo
5. Create Entity class
Student.java:
package com.example.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. Create a Repository
StudentRepository.java:
package com.example.repo;
import org.springframework.data.jpa.repository.JpaRepository;
import com.example.entity.Student;
public interface StudentRepos extends JpaRepository < Student, Long > {
}
7. Create a Service
StudentService.java:
package com.example.service;
import java.util.List;
import com.example.entity.Student;
public interface StudentService {
void addStudent(Student student);
List < Student > getTheListStudent();
}
StudentServiceImpl.java:
package com.example.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.example.entity.Student;
import com.example.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. Create an Excel File Generator class
ExcelGenerator.java:
package com.example.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.example.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 are creating an instance of XSSFWorkbook then we are calling 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. Create a Controller class
StudentController.java:
package com.example.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.example.entity.Student;
import com.example.service.StudentService;
import com.example.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. Create a view page on 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. Create CommandLineRunner on SpringBootApplication class
package com.example;
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.example.entity.Student;
import com.example.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 Project
- Right Click on the SpringBootApplication class (ExportExcelSpringBootExampleApplication)
- Click on Run as Java Application.
- Check the 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.
Type this URL on the browser "http://localhost:8888/demo/" for testing the database's table of data is exporting properly or not.
Click on the Download Excel File link to download the excel file.
13. Conclusion
In this topic, we learned about How to transform data into an excel file from a database in the Spring Boot Application and use the Apache POI library?
Leave your thought here
Your email address will not be published. Required fields are marked *
Comments(1)
venki
nice.