The Excel file is a spreadsheet file format created by Microsoft for use with Microsoft Excel. You can use the file to create, view, edit, analyse data, charts, budgets and more. In this tutorial, I will show you how to use Spring Boot to download Excel file from a table in MySQL Database using Apache POI.
Related Posts:
– Spring Boot: Upload/Import Excel file data into MySQL Database
– Spring Boot Multipart File upload example
– How to upload multiple files in Java Spring Boot
– Upload/Import CSV file to MySQL Database in Spring Boot
CSV file instead:
Spring Boot Download CSV file from Database example
Deployment:
– Deploy Spring Boot App on AWS – Elastic Beanstalk
– Docker Compose: Spring Boot and MySQL example
Contents
- Spring Boot Rest API: Download Excel File
- Technology
- Project Structure
- Setup Spring Boot Download Excel File project
- Configure Spring Datasource, JPA, Hibernate
- Define Data Model
- Create Data Repository for working with Database
- Implement Excel Helper Class
- Create Excel File Service
- Create Controller for Excel File Download
- Run the Spring Boot Download Excel File App
- Conclusion
- Further Reading
- Source Code
Spring Boot Rest API: Download Excel File
Assume that we have a tutorials table in MySQL database like this:
We’re gonna create a Spring Boot Application that provides APIs for downloading MySQL table data as Excel file with following content:
If you send request to /api/excel/download
, the server will return a response with an Excel file tutorials.xlsx that contains data in MySQL table:
How to do this?
You need to set the HTTP header:
"Content-disposition" : "attachment; filename=[yourFileName]"
"Content-Type" : "application/vnd.ms-excel"
We’re gonna apply the information above later in this tutorial.
You can also find how to upload Excel File to the Spring Server & store data in MySQL Database in the post:
Spring Boot: Upload/Import Excel file data into MySQL Database
Technology
- Java 8
- Spring Boot 2 (with Spring Web MVC)
- Maven 3.6.1
- Apache POI 4.1.2
Project Structure
This is the project directory that we’re gonna build:
– ExcelHelper
provides functions to write to Excel file.
– Tutorial
data model class corresponds to entity and table tutorials.
– TutorialRepository
is an interface that extends JpaRepository
for persisting data.
– ExcelService
uses ExcelHelper
and TutorialRepository
methods load data to Excel file.
– ExcelController
calls ExcelService
methods and export Rest API for downloading Excel files.
– application.properties contains configuration for Spring Data.
– pom.xml for Spring Boot, MySQL connector, Apache POI dependencies.
Setup Spring Boot Download Excel File project
Use Spring web tool or your development tool (Spring Tool Suite, Eclipse, Intellij) to create a Spring Boot project.
Then open pom.xml and add these 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>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
Configure Spring Datasource, JPA, Hibernate
Under src/main/resources folder, open application.properties and write these lines.
spring.datasource.url= jdbc:mysql://localhost:3306/testdb?useSSL=false
spring.datasource.username= root
spring.datasource.password= 123456
spring.jpa.properties.hibernate.dialect= org.hibernate.dialect.MySQL5InnoDBDialect
# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto= update
spring.datasource.username
&spring.datasource.password
properties are the same as your database installation.- Spring Boot uses Hibernate for JPA implementation, we configure
MySQL5InnoDBDialect
for MySQL database spring.jpa.hibernate.ddl-auto
is used for database initialization. We set the value toupdate
value so that a table will be created in the database automatically corresponding to defined data model. Any change to the model will also trigger an update to the table. For production, this property should bevalidate
.
Define Data Model
Our Data model is Tutorial with four fields: id, title, description, published.
In model package, we define Tutorial
class.
model/Tutorial.java
package com.bezkoder.spring.files.excel.model;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name = "tutorials")
public class Tutorial {
@Id
@Column(name = "id")
private long id;
@Column(name = "title")
private String title;
@Column(name = "description")
private String description;
@Column(name = "published")
private boolean published;
public Tutorial() {
}
public Tutorial(String title, String description, boolean published) {
this.title = title;
this.description = description;
this.published = published;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public boolean isPublished() {
return published;
}
public void setPublished(boolean isPublished) {
this.published = isPublished;
}
@Override
public String toString() {
return "Tutorial [id=" + id + ", title=" + title + ", desc=" + description + ", published=" + published + "]";
}
}
– @Entity
annotation indicates that the class is a persistent Java class.
– @Table
annotation provides the table that maps this entity.
– @Id
annotation is for the primary key.
– @Column
annotation is used to define the column in database that maps annotated field.
Create Data Repository for working with Database
Let’s create a repository to interact with Tutorials from the database.
In repository package, create TutorialRepository
interface that extends JpaRepository
.
repository/TutorialRepository.java
package com.bezkoder.spring.files.excel.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import com.bezkoder.spring.files.excel.model.Tutorial;
public interface TutorialRepository extends JpaRepository<Tutorial, Long> {
}
Now we can use JpaRepository’s methods: save()
, findOne()
, findById()
, findAll()
, count()
, delete()
, deleteById()
… without implementing these methods.
The quantity of rows in Excel file (also tutorials table) could be large, so you may want to get only several at once by modifying this Repository to work with Pagination, the instruction can be found at:
Spring Boot Pagination & Filter example | Spring JPA, Pageable
Implement Excel Helper Class
We’re gonna use Apache POI classes such as: Workbook
, Sheet
, Row
, Cell
.
Let me summarize the steps for writing to Excel file:
- create a new
Workbook
- create a new
Sheet
usingWorkbook.createSheet()
- iterate over
tutorials
list - for each
Tutorial
, create aRow
usingSheet.createRow()
- create
Cell
usingRow.createCell()
, then useCell.setCellValue()
- use
Workbook.write()
to write outByteArrayOutputStream
Workbook workbook = new XSSFWorkbook();
Sheet sheet = Workbook.createSheet(SHEET);
for (Tutorial tutorial : tutorials) {
Row row = sheet.createRow(rowIdx++);
row.createCell(0).setCellValue(tutorial.getId());
row.createCell(1).setCellValue(tutorial.getTitle());
row.createCell(2).setCellValue(tutorial.getDescription());
row.createCell(3).setCellValue(tutorial.isPublished());
}
ByteArrayOutputStream out = new ByteArrayOutputStream();
workbook.write(out);
return new ByteArrayInputStream(out.toByteArray());
Under helper package, we create ExcepHelper
class with tutorialsToExcel()
method which processes a list of Tutorials, return a ByteArrayInputStream
for Excel file later.
Here is full code of helper/ExcelHelper.java:
package com.bezkoder.spring.files.excel.helper;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.bezkoder.spring.files.excel.model.Tutorial;
public class ExcelHelper {
public static String TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
static String[] HEADERs = { "Id", "Title", "Description", "Published" };
static String SHEET = "Tutorials";
public static ByteArrayInputStream tutorialsToExcel(List<Tutorial> tutorials) {
try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream();) {
Sheet sheet = workbook.createSheet(SHEET);
// Header
Row headerRow = sheet.createRow(0);
for (int col = 0; col < HEADERs.length; col++) {
Cell cell = headerRow.createCell(col);
cell.setCellValue(HEADERs[col]);
}
int rowIdx = 1;
for (Tutorial tutorial : tutorials) {
Row row = sheet.createRow(rowIdx++);
row.createCell(0).setCellValue(tutorial.getId());
row.createCell(1).setCellValue(tutorial.getTitle());
row.createCell(2).setCellValue(tutorial.getDescription());
row.createCell(3).setCellValue(tutorial.isPublished());
}
workbook.write(out);
return new ByteArrayInputStream(out.toByteArray());
} catch (IOException e) {
throw new RuntimeException("fail to import data to Excel file: " + e.getMessage());
}
}
}
Create Excel File Service
ExcelService
class will be annotated with @Service
annotation, it uses ExcelHelper
and TutorialRepository
in load()
method which read data from database and return ByteArrayInputStream
.
service/ExcelService.java
package com.bezkoder.spring.files.excel.service;
import java.io.ByteArrayInputStream;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.bezkoder.spring.files.excel.helper.ExcelHelper;
import com.bezkoder.spring.files.excel.model.Tutorial;
import com.bezkoder.spring.files.excel.repository.TutorialRepository;
@Service
public class ExcelService {
@Autowired
TutorialRepository repository;
public ByteArrayInputStream load() {
List<Tutorial> tutorials = repository.findAll();
ByteArrayInputStream in = ExcelHelper.tutorialsToExcel(tutorials);
return in;
}
}
Create Controller for Excel File Download
In controller package, we create ExcelController
class for Rest Apis.
– @CrossOrigin
is for configuring allowed origins.
– @Controller
annotation indicates that this is a controller.
– @GetMapping
getFile() method.
– We also use @Autowired
to inject implementation of ExcelService
bean to local variable.
controller/ExcelController.java
package com.bezkoder.spring.files.excel.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.InputStreamResource;
import org.springframework.core.io.Resource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import com.bezkoder.spring.files.excel.service.ExcelService;
@CrossOrigin("http://localhost:8081")
@Controller
@RequestMapping("/api/excel")
public class ExcelController {
@Autowired
ExcelService fileService;
@GetMapping("/download")
public ResponseEntity<Resource> getFile() {
String filename = "tutorials.xlsx";
InputStreamResource file = new InputStreamResource(fileService.load());
return ResponseEntity.ok()
.header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + filename)
.contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
.body(file);
}
}
Run the Spring Boot Download Excel File App
Run Spring Boot application with command: mvn spring-boot:run
.
Conclusion
Today we’ve built a Rest API using Spring Boot to download Excel file that contains table data in MySQL database.
We also see how to use Apache POI to write data to Excel Sheet, JpaRepository
to retrieve items in database table without need of boilerplate code.
If you want to add Pagination to this Spring project, you can find the instruction at:
Spring Boot Pagination & Filter example | Spring JPA, Pageable
You can also find how to upload Excel File to the Spring Server & store data in MySQL Database in the post:
Spring Boot: Upload/Import Excel file data into MySQL Database
For CSV file instead:
Spring Boot Download CSV file from Database example
Happy learning! See you again.
Further Reading
Deployment:
– Deploy Spring Boot App on AWS – Elastic Beanstalk
– Docker Compose: Spring Boot and MySQL example
Source Code
You can find the complete source code for this tutorial on Github.
hey man, I’m getting api response in some encrypted form for the download thing in postman . could you plz let me know if any changes we need to do in postman especially in body .
Hi, you can paste the link on your browser url 🙂
To handle the Null Pointer Exception
row.createCell(0).setCellValue(tutorial.getId()==null?””:tutorial.getId());
row.createCell(1).setCellValue(tutorial.getTitle()==null?””:tutorial.getTitle());
row.createCell(2).setCellValue(tutorial.getDescription()==null?””:tutorial.getDescription());
row.createCell(3).setCellValue(tutorial.isPublished()==null?””:tutorial.isPublished());
You are a life save man. Thanks.