Spring Boot Download Excel File Export from MySQL Database

Tags: Spring Boot Download Apache POI Excel

Introduction

In this tutorial, we are going to learn how to implement a download function for a Spring Boot web application to allow users download excel files, which the Excel file content export from a MySQL database table. Via step by step tutorial we learn how to implement the feature using different Java libraries such as Apache POI, Spring Data JPA, Spring Web, etc.

Create New Spring Boot Web Project

Open Spring Tool Suite IDE select menu File > New > Spring Starter Project.

On the New Spring Starter Project popup input new project information.

  • Name: spring-boot-download-excel-mysql
  • Group: dev.simplesolution
  • Artifact: spring-boot-download-excel-mysql
  • Version: 1.0.0
  • Description: Spring Boot Download Excel from MySQL
  • Package: dev.simplesolution

New Spring Starter Project

On the New Spring Starter Project Dependencies popup choose dependencies:

  • Spring Data JPA
  • MySQL Driver
  • Thymeleaf
  • Spring Web

New Spring Starter Project Dependencies

Keep the information on next window as default and click Finish

New Spring Starter Project Finish

You can also creating new Spring Boot project using Spring initializr online tool at start.spring.io

Add Apache POI dependency to Spring Boot web project

To create Excel files in Java applications we will use the Apache POI library.

To use Apache POI library in the Gradle build project, add the following dependency into the build.gradle file.

compile group: 'org.apache.poi', name: 'poi-ooxml', version: '4.0.1'

To use Apache POI library in the Maven build project, add the following dependency into the pom.xml file.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>

Create Test Database on MySQL Server

In this sample application we are going to implement a download feature to download a list of contacts. Firstly, execute the SQL script below to create a new table named contacts in your MySQL database.

CREATE TABLE `contacts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(200) NULL,
  `phone_number` VARCHAR(30) NULL,
  `address` VARCHAR(300) NULL,
  PRIMARY KEY (`id`));

Execute the SQL script below to insert data into table contacts. 

INSERT INTO `contacts` (`first_name`, `last_name`, `email`, `phone_number`, `address`) VALUES ('John', 'Doe', 'john@simplesolution.dev', '123-456-789', 'address 123, street 1, city 1');
INSERT INTO `contacts` (`first_name`, `last_name`, `email`, `phone_number`, `address`) VALUES ('Emerson', 'Wilks', 'emerson@simplesolution.dev', '123-456-788', 'address 456, street 2, city 1');
INSERT INTO `contacts` (`first_name`, `last_name`, `email`, `phone_number`, `address`) VALUES ('Wade', 'Savage', 'wade@simplesolution.dev', '123-456-787', 'address 789, street 3, city 1');
INSERT INTO `contacts` (`first_name`, `last_name`, `email`, `phone_number`, `address`) VALUES ('Star', 'Lott', 'star@simplesolution.dev', '123-456-786', 'address 234, street 4, city 1');
INSERT INTO `contacts` (`first_name`, `last_name`, `email`, `phone_number`, `address`) VALUES ('Claudia', 'James', 'claudia@simplesolution.dev', '123-456-785', 'address 678, street 5, city 1');

Select table contacts to see the data. New Spring Starter Project Finish

Configure MySQL database connection

To connect the MySQL database using Spring Data JPA we need to configure the connection information. Add the following configurations to properties file in your Spring Boot project at \src\main\resources\application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=123@abc
  • spring.datasource.url to configure the URL to your database. For example we have the database named test at localhost server which is running on default MySQL port 3306.
  • spring.datasource.username to configure the user name to access the MySQL database.
  • spring.datasource.password to configure password of your MySQL user.

Implement Entity and Repository classes

Create a new Java package named dev.simplesolution.entities and implement Contact class.

package dev.simplesolution.entities;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "contacts")
public class Contact {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;
	private String firstName;
	private String lastName;
	private String email;
	private String phoneNumber;
	private String address;
	
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getPhoneNumber() {
		return phoneNumber;
	}
	public void setPhoneNumber(String phoneNumber) {
		this.phoneNumber = phoneNumber;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
}

Create a new Java package named dev.simplesolution.repositories and define a new interface named ContactRepository.

package dev.simplesolution.repositories;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import dev.simplesolution.entities.Contact;

@Repository
public interface ContactRepository extends JpaRepository<Contact, Long> {

}

Implement Excel File service

Create a new package named dev.simplesolution.services and define new interface ExcelFileService.

package dev.simplesolution.services;

import java.io.ByteArrayInputStream;
import java.util.List;

import dev.simplesolution.entities.Contact;

public interface ExcelFileService {
	
	ByteArrayInputStream export(List<Contact> contacts);
	
}

Create a new package named dev.simplesolution.services.impl and implement a new class named ExcelFileServiceImpl.

package dev.simplesolution.services.impl;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import dev.simplesolution.entities.Contact;
import dev.simplesolution.services.ExcelFileService;

@Service
public class ExcelFileServiceImpl implements ExcelFileService {
	
	private Logger logger = LoggerFactory.getLogger(this.getClass());

	@Override
	public ByteArrayInputStream export(List<Contact> contacts) {
		try(Workbook workbook = new XSSFWorkbook()){
			Sheet sheet = workbook.createSheet("Contacts");
			
			Row row = sheet.createRow(0);
			
			// Define header cell style
	        CellStyle headerCellStyle = workbook.createCellStyle();
	        headerCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
	        headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	        
	        // Creating header cells 
	        Cell cell = row.createCell(0);
	        cell.setCellValue("First Name");
	        cell.setCellStyle(headerCellStyle);
	        
	        cell = row.createCell(1);
	        cell.setCellValue("Last Name");
	        cell.setCellStyle(headerCellStyle);
	
	        cell = row.createCell(2);
	        cell.setCellValue("Email");
	        cell.setCellStyle(headerCellStyle);
	        
	        cell = row.createCell(3);
	        cell.setCellValue("Phone Number");
	        cell.setCellStyle(headerCellStyle);
	        
	        cell = row.createCell(4);
	        cell.setCellValue("Address");
	        cell.setCellStyle(headerCellStyle);
	        
	        // Creating data rows for each contact
	        for(int i = 0; i < contacts.size(); i++) {
	        	Row dataRow = sheet.createRow(i + 1);
	        	dataRow.createCell(0).setCellValue(contacts.get(i).getFirstName());
	        	dataRow.createCell(1).setCellValue(contacts.get(i).getLastName());
	        	dataRow.createCell(2).setCellValue(contacts.get(i).getEmail());
	        	dataRow.createCell(3).setCellValue(contacts.get(i).getPhoneNumber());
	        	dataRow.createCell(4).setCellValue(contacts.get(i).getAddress());
	        }
	
	        // Making size of column auto resize to fit with data
	        sheet.autoSizeColumn(0);
	        sheet.autoSizeColumn(1);
	        sheet.autoSizeColumn(2);
	        sheet.autoSizeColumn(3);
	        sheet.autoSizeColumn(4);
	        
	        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
	        workbook.write(outputStream);
	        return new ByteArrayInputStream(outputStream.toByteArray());
		} catch (IOException ex) {
			logger.error("Error during export Excel file", ex);
			return null;
		}
	}

}

Implement Excel File download controller

Create a new package named dev.simplesolution.controller and implement a new class DownloadExcelController.

package dev.simplesolution.controller;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.util.IOUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

import dev.simplesolution.entities.Contact;
import dev.simplesolution.repositories.ContactRepository;
import dev.simplesolution.services.ExcelFileService;

@Controller
public class DownloadExcelController {
	
	@Autowired
	private ContactRepository contactRepository; 
	
	@Autowired
	private ExcelFileService excelFileService;
	
	@GetMapping("/")
    public String index() {
        return "index";
    }
	
	@GetMapping("/downloadExcelFile")
	public void downloadExcelFile(HttpServletResponse response) throws IOException {
		List<Contact> contacts = (List<Contact>)contactRepository.findAll();
        ByteArrayInputStream byteArrayInputStream = excelFileService.export(contacts);
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment; filename=contacts.xlsx");
        IOUtils.copy(byteArrayInputStream, response.getOutputStream());
	}
	
}

Implement HTML view to show download page

Creating new HTML view file at resources\templates\index.html 

<!DOCTYPE html>
<html>
<head>
    <title>Spring Boot Download Excel File Export from MySQL Database</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
</head>
<body class="container">
	<p>Click button below to download Excel file.</p>
	<a href="http://localhost:8080/downloadExcelFile" class="btn btn-primary">Download Excel File</a>
</body>
</html>

Final Application

At this step we have done the implementation of the Spring Boot project with structure as below.

Spring Boot Download Excel Complete Application

Run the Spring Boot project then open http://localhost:8080/ on browser you will got the web page as below

Spring Boot Download Excel Complete Application

Click on the Download button to download contacts.xlsx file, open the file on Excel application to see file content as following screenshot.

Excel File

Download Source Code

The source code in this article can be found at: github.com/simplesolutiondev/spring-boot-download-excel-mysql

or clone at:

git clone https://github.com/simplesolutiondev/spring-boot-download-excel-mysql.git

or download at:

Download Source Code

Happy Coding 😊

Spring Boot Web Application Download Excel File

Spring Boot Web Application Download CSV File

Spring Boot Web Application Export and Download Text File

Spring Boot Web Application Export and Download JSON File

Spring Boot Access MySQL Database with Spring Data JPA

Apache POI Create new Excel sheet

Java Create Excel File .xlsx using Apache POI

Java Read Excel File using Apache POI

Java Read Excel Workbook from File using Apache POI

Java Read Excel Workbook from InputStream using Apache POI

Java Read Password Protected Excel File using Apache POI

Java How to Iterate over Sheets Rows and Cells of Excel file using Apache POI

Java Add Rows to Existing Excel File using Apache POI

Java Add Sheet to Existing Excel File using Apache POI

Java Remove Sheet from Existing Excel File using Apache POI

Java Create Formula Excel Cells using Apache POI