springjava.com
  • Core Java
  • Spring Core
  • Spring Boot
  • Spring MVC
  • Angular Tutorial

Related Posts

  • Spring Boot Annotations How to Upload a File in Spring Boot JSP How to create a Spring Boot project in VS Code How to create a Spring Boot project in IntelliJ IDEA How to create a Spring Boot project in STS How to Create and Setup Spring Boot Project in Eclipse Spring Boot CRUD Operations with JSP Spring Boot Pagination & Sorting with REST API Spring Boot CRUD with Thymeleaf Sending Mail Using Gmail SMTP Server In Spring Boot
Spring Boot

Export Data to Excel file in Spring Boot

Last Updated: 18-04-2022 Springjava

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.

db_to_excel_export_to_in_spring_boot

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. 

db_to_excel_export_to_in_spring_boot

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.

h2_db_export_to_excel_in_spring_boot

table-structure

Type this URL on the browser "http://localhost:8888/demo/" for testing the database's table of data is exporting properly or not.

run-application

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

table-data

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?

What is Spring Boot How to Create a Spring Boot Project in IDE Spring MVC with Spring Boot Configure

Comments(1)

  1. venki

    nice.

    13-09-2022 12:26:22

Leave your thought here

Your email address will not be published. Required fields are marked *

springjava_com
  • springjavateam@gmail.com
  • springjava.com
Learn
    • Core Java
    • Spring Core
    • Spring Boot
    • Spring MVC
    • Angular Tutorial
Quick links
  • About Us
  • Contact Us
  • Privacy Policy
Subscribe to Blog via Email
Subscribe
© 2021 Spring Java. All Rights Reserved.
springjava.com
  • Core Java
  • Spring Core
  • Spring Boot
  • Spring MVC
  • Angular Tutorial
  • About Us
  • Contact Us