Spring Boot Download Excel Example (2024)

In this tutorial, we'll demonstrate how to generate Excel and export data from the database using JPA in Spring Boot with Apache POI library.

Q: What is Apache POI?
Ans:

The Apache Foundation, which is a collection of many Java libraries, offers the Apache POI (Poor Obfuscation Implementation) API. These libraries provide the ability to read, write, and edit a variety of Microsoft files, including word, excel, and powerpoint documents.

Create Spring Boot application

Create Spring Boot application from Spring Initializr.

Project Structure

Add Dependencies

Add org.apache.poi dependency in 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.3.1.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.techgeeknext</groupId>
	<artifactId>spring-boot-export-excel</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>spring-boot-export-excel</name>
	<description>Spring Boot Export Excel Example</description>

	<properties>
		<java.version>1.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>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>5.0.0</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>5.0.0</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>2.11.0</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

Application Properties

Add database connection details in application.properties file.

spring.datasource.url=jdbc:mysql://localhost/employeetestdb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.platform=mysql
spring.datasource.initialization-mode=always

## Hibernate Properties
# The SQL dialect makes Hibernate generate better SQL for the chosen database
# spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = create-drop

#spring.jpa.defer-datasource-initialization=true
spring.sql.init.mode=always

Test Data for Excel Document

There must be some data in the database in order to generate documents and export data using JPA. We will now enter some test data in database. Hibernate generates the schema automatically; the data.sql file is then executed to populate the table with test data.

INSERT INTO employees(id, name, role) VALUES(1, 'User1', 'Admin');
INSERT INTO employees(id, name, role) VALUES(2, 'User2', 'Supervisor');
INSERT INTO employees(id, name, role) VALUES(3, 'User3', 'Tester');
INSERT INTO employees(id, name, role) VALUES(4, 'User4', 'HR');
INSERT INTO employees(id, name, role) VALUES(5, 'User5', 'Developer');

Data Model

Create Employee class, contains id, name and role.

package com.techgeeknext.model;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Entity
@Table(name = "employees")
public class Employee {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private long id;

	@Column(name = "name")
	private String name;

	@Column(name = "role")
	private String role;
}

JPA Repository

Create EmployeeRepository interface that extends JpaRepository.

package com.techgeeknext.repository;

import com.techgeeknext.model.Employee;
import org.springframework.data.jpa.repository.JpaRepository;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {
}

Controller to generate the Excel File

Create the rest endpoint to generate the Excel File and to extract data from database using JPA repository.

package com.techgeeknext.controller;

import com.techgeeknext.repository.EmployeeRepository;
import com.techgeeknext.util.ExcelGeneratorUtility;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

@RestController
public class EmployeeController {

    @Autowired
    EmployeeRepository employeeRepository;


    @GetMapping("/excel")
    public void employeeDetailsReport(HttpServletResponse response) throws IOException {

        DateFormat dateFormat = new SimpleDateFormat("YYYY-MM-DD:HH:MM:SS");
        String fileType = "attachment; filename=employee_details_" + dateFormat.format(new Date()) + ".xls";
        response.setHeader("Content-Disposition", fileType);
        response.setContentType(MediaType.APPLICATION_OCTET_STREAM.getType());

        ExcelGeneratorUtility.employeeDetailReport(response, employeeRepository.findAll());
    }
}

Excel Generator Utility

We will create common utility class to write the data to Excel File.

package com.techgeeknext.util;

import com.techgeeknext.model.Employee;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

public class ExcelGeneratorUtility {

    public static void employeeDetailReport(HttpServletResponse response, List<Employee> employees) {
        try(Workbook workbook = new XSSFWorkbook()){
            Sheet sheet = workbook.createSheet("Employee TechGeekNext Example");

            CellStyle cellStyle = workbook.createCellStyle();


            //set border to table
            cellStyle.setBorderTop(BorderStyle.MEDIUM);
            cellStyle.setBorderRight(BorderStyle.MEDIUM);
            cellStyle.setBorderBottom(BorderStyle.MEDIUM);
            cellStyle.setBorderLeft(BorderStyle.MEDIUM);
            cellStyle.setAlignment(HorizontalAlignment.LEFT);


            // Header
            Row row = sheet.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue("Id");
            cell.setCellStyle(cellStyle);


            Cell cell1 = row.createCell(1);
            cell1.setCellValue("Name");
            cell1.setCellStyle(cellStyle);


            Cell cell2 = row.createCell(2);
            cell2.setCellValue("Role");
            cell2.setCellStyle(cellStyle);


            //Set data
            int rowNum = 1;
            for (Employee emp : employees) {
                Row empDataRow = sheet.createRow(rowNum++);
                Cell empIdCell = empDataRow.createCell(0);
                empIdCell.setCellStyle(cellStyle);
                empIdCell.setCellValue(emp.getId());

                Cell empNameCell = empDataRow.createCell(1);
                empNameCell.setCellStyle(cellStyle);
                empNameCell.setCellValue(emp.getName());

                Cell empRoleCell = empDataRow.createCell(2);
                empRoleCell.setCellStyle(cellStyle);
                empRoleCell.setCellValue(emp.getRole());
            }

            //write output to response
            workbook.write(response.getOutputStream());

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

Test Spring Boot to Generate and Export Excel

1. Start the Spring Boot Application by running spring-boot:run or by running main class.

2. Generate Excel

   Use GET method with end point http://localhost:8080/excel which will generate and download the Excel file.

3. Download/Export Excel File

   After using the above rest end point, the following Excel file will be generated.

Download Source Code

The full source code for this article can be found below.

1. Download it here - Spring Boot Download Excel File Example