Export Data to Excel file in Spring Boot

In this topic, we will learn how to export data to Excel file in Spring Boot Application. For this, we will use the Apache POI library for exporting Excel. 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.

export_data_to_excel_file_in_spring_boot

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.

export_data_to_excel_file_in_spring_boot

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= /demo

5. 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 (ExportExcelSpringBootExampleApplication)
          • Click on Run as Java Application

Type this URL on the browser “http://localhost:8888/demo/” to test.

export_data_to_excel_file_in_spring_boot

Click on the Download the Excel File link to download the Excel file.

export_data_to_excel_file_in_spring_boot

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.

export_data_to_excel_file_in_spring_boot

H2 database table:

export_data_to_excel_file_in_spring_boot

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.

2 thoughts on “Export Data to Excel file in Spring Boot”

Leave a Comment