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 upload/import Excel file data into MySQL Database using Spring Boot & Apache POI, then export Rest API to return Excel file from database table.
Related Posts:
– 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
– Spring Boot: Download Excel file from MySQL database table
– Documentation: Spring Boot Swagger 3 example
– Caching: Spring Boot Redis Cache example
Deployment:
– Deploy Spring Boot App on AWS – Elastic Beanstalk
– Docker Compose: Spring Boot and MySQL example
Contents
- Spring Boot Rest APIs for uploading Excel Files
- Spring Boot Rest API returns Excel File
- Technology
- Project Structure
- Setup Spring Boot Excel File Upload project
- Configure Spring Datasource, JPA, Hibernate
- Define Data Model
- Create Data Repository for working with Database
- Implement Read/Write Excel Helper Class
- Create Excel File Service
- Define Response Message
- Create Controller for Upload Excel Files
- Configure Multipart File for Servlet
- Handle File Upload Exception
- Run & Check
- Conclusion
- Further Reading
- Source Code
Spring Boot Rest APIs for uploading Excel Files
Assume that we have an .xlsx file that contains Tutorial data as following:
We’re gonna create a Spring Boot Application that provides APIs for:
- uploading Excel File to the Spring Server & storing data in MySQL Database
- getting list of items from MySQL table
- downloading MySQL table data as Excel file
After the Excel file is uploaded successfully, tutorials table in MySQL database will look like this:
If we get list of Tutorials, the Spring Rest Apis will return:
Spring Boot Rest API returns Excel File
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"
You can find step by step for downloading Excel file in the tutorial:
Spring Boot: Download Excel file from MySQL database table
These are APIs to be exported:
Methods | Urls | Actions |
---|---|---|
POST | /api/excel/upload | upload an Excel File |
GET | /api/excel/tutorials | get List of items in db table |
GET | /api/excel/download | download db data as Excel file |
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 read 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 to save Excel data to MySQL, load data to Excel file, or get all Tutorials from MySQL table.
– ExcelController
calls ExcelService
methods and export Rest APIs: upload Excel file, get data from MySQL database.
– FileUploadExceptionAdvice
handles exception when the controller processes file upload.
– application.properties contains configuration for Spring Data and Servlet Multipart file.
– pom.xml for Spring Boot, MySQL connector, Apache POI dependencies.
Setup Spring Boot Excel File Upload 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
More Derived queries at:
JPA Repository query example in Spring Boot
Custom query with @Query
annotation:
Spring JPA @Query example: Custom query in Spring Boot
You also find way to write Unit Test for this JPA Repository at:
Spring Boot Unit Test for JPA Repository with @DataJpaTest
Implement Read/Write Excel Helper Class
We’re gonna use Apache POI classes such as: Workbook
, Sheet
, Row
, Cell
.
Let me summarize the steps for reading from Excel file:
- create
Workbook
fromInputStream
- create
Sheet
usingWorkbook.getSheet()
method - iterate over
Row
s byIterator
withSheet.iterator()
andIterator.hasNext()
- from each
Row
, iterate overCell
s - with each
Cell
, usegetNumericCellValue()
,getStringCellValue()
… methods to read and parse the content
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheet(SHEET);
Iterator<Row> rows = sheet.iterator();
while (rows.hasNext()) {
Row currentRow = rows.next();
Iterator<Cell> cellsInRow = currentRow.iterator();
while (cellsInRow.hasNext()) {
Cell currentCell = cellsInRow.next();
// each cell case
id = (long) currentCell.getNumericCellValue();
title = currentCell.getStringCellValue();
published = currentCell.getBooleanCellValue();
}
workbook.close();
Under helper package, we create ExcepHelper
class with 2 methods:
hasExcelFormat()
: check if a file has Excel format or notexcelToTutorials()
: readInputStream
of a file, return a list of Tutorials
Here is full code of helper/ExcelHelper.java:
package com.bezkoder.spring.files.excel.helper;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
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 org.springframework.web.multipart.MultipartFile;
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 boolean hasExcelFormat(MultipartFile file) {
if (!TYPE.equals(file.getContentType())) {
return false;
}
return true;
}
public static List<Tutorial> excelToTutorials(InputStream is) {
try {
Workbook workbook = new XSSFWorkbook(is);
Sheet sheet = workbook.getSheet(SHEET);
Iterator<Row> rows = sheet.iterator();
List<Tutorial> tutorials = new ArrayList<Tutorial>();
int rowNumber = 0;
while (rows.hasNext()) {
Row currentRow = rows.next();
// skip header
if (rowNumber == 0) {
rowNumber++;
continue;
}
Iterator<Cell> cellsInRow = currentRow.iterator();
Tutorial tutorial = new Tutorial();
int cellIdx = 0;
while (cellsInRow.hasNext()) {
Cell currentCell = cellsInRow.next();
switch (cellIdx) {
case 0:
tutorial.setId((long) currentCell.getNumericCellValue());
break;
case 1:
tutorial.setTitle(currentCell.getStringCellValue());
break;
case 2:
tutorial.setDescription(currentCell.getStringCellValue());
break;
case 3:
tutorial.setPublished(currentCell.getBooleanCellValue());
break;
default:
break;
}
cellIdx++;
}
tutorials.add(tutorial);
}
workbook.close();
return tutorials;
} catch (IOException e) {
throw new RuntimeException("fail to parse Excel file: " + e.getMessage());
}
}
}
Don’t forget to change the sheet name to Tutorials (or any name you want). It’s because we create Sheet
object from that name.
static String SHEET = "Tutorials";
...
Sheet sheet = workbook.getSheet(SHEET);
Create Excel File Service
ExcelService
class will be annotated with @Service
annotation, it uses ExcelHelper
and TutorialRepository
for 2 functions:
save(MultipartFile file)
: store Excel data to databasegetAllTutorials
(): read data from database and returnList<Tutorial>
service/ExcelService.java
package com.bezkoder.spring.files.excel.service;
import java.io.IOException;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
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 void save(MultipartFile file) {
try {
List<Tutorial> tutorials = ExcelHelper.excelToTutorials(file.getInputStream());
repository.saveAll(tutorials);
} catch (IOException e) {
throw new RuntimeException("fail to store excel data: " + e.getMessage());
}
}
public List<Tutorial> getAllTutorials() {
return repository.findAll();
}
}
Define Response Message
The ResponseMessage
is for message to client that we’re gonna use in Rest Controller and Exception Handler.
message/ResponseMessage.java
package com.bezkoder.spring.files.excel.message;
public class ResponseMessage {
private String message;
public ResponseMessage(String message) {
this.message = message;
}
public String getMessage() {
return message;
}
public void setMessage(String message) {
this.message = message;
}
}
Create Controller for Upload Excel Files
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
and @PostMapping
annotation is for mapping HTTP GET & POST requests onto specific handler methods:
- POST /upload:
uploadFile()
- GET /tutorials:
getAllTutorials()
– We use @Autowired
to inject implementation of ExcelService
bean to local variable.
controller/ExcelController.java
package com.bezkoder.spring.files.excel.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import com.bezkoder.spring.files.excel.helper.ExcelHelper;
import com.bezkoder.spring.files.excel.message.ResponseMessage;
import com.bezkoder.spring.files.excel.model.Tutorial;
import com.bezkoder.spring.files.excel.service.ExcelService;
@CrossOrigin("http://localhost:8081")
@Controller
@RequestMapping("/api/excel")
public class ExcelController {
@Autowired
ExcelService fileService;
@PostMapping("/upload")
public ResponseEntity<ResponseMessage> uploadFile(@RequestParam("file") MultipartFile file) {
String message = "";
if (ExcelHelper.hasExcelFormat(file)) {
try {
fileService.save(file);
message = "Uploaded the file successfully: " + file.getOriginalFilename();
return ResponseEntity.status(HttpStatus.OK).body(new ResponseMessage(message));
} catch (Exception e) {
message = "Could not upload the file: " + file.getOriginalFilename() + "!";
return ResponseEntity.status(HttpStatus.EXPECTATION_FAILED).body(new ResponseMessage(message));
}
}
message = "Please upload an excel file!";
return ResponseEntity.status(HttpStatus.BAD_REQUEST).body(new ResponseMessage(message));
}
@GetMapping("/tutorials")
public ResponseEntity<List<Tutorial>> getAllTutorials() {
try {
List<Tutorial> tutorials = fileService.getAllTutorials();
if (tutorials.isEmpty()) {
return new ResponseEntity<>(HttpStatus.NO_CONTENT);
}
return new ResponseEntity<>(tutorials, HttpStatus.OK);
} catch (Exception e) {
return new ResponseEntity<>(null, HttpStatus.INTERNAL_SERVER_ERROR);
}
}
}
Configure Multipart File for Servlet
Let’s define the maximum file size that can be uploaded in application.properties as following:
spring.servlet.multipart.max-file-size=2MB
spring.servlet.multipart.max-request-size=2MB
spring.servlet.multipart.max-file-size
: max file size for each request.spring.servlet.multipart.max-request-size
: max request size for a multipart/form-data.
Handle File Upload Exception
This is where we handle the case in that a request exceeds Max Upload Size. The system will throw MaxUploadSizeExceededException
and we’re gonna use @ControllerAdvice
with @ExceptionHandler
annotation for handling the exceptions.
exception/FileUploadExceptionAdvice.java
package com.bezkoder.spring.files.excel.exception;
import org.springframework.web.multipart.MaxUploadSizeExceededException;
import org.springframework.web.servlet.mvc.method.annotation.ResponseEntityExceptionHandler;
import com.bezkoder.spring.files.excel.message.ResponseMessage;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;
@ControllerAdvice
public class FileUploadExceptionAdvice extends ResponseEntityExceptionHandler {
@ExceptionHandler(MaxUploadSizeExceededException.class)
public ResponseEntity<ResponseMessage> handleMaxSizeException(MaxUploadSizeExceededException exc) {
return ResponseEntity.status(HttpStatus.EXPECTATION_FAILED).body(new ResponseMessage("File too large!"));
}
}
Run & Check
Run Spring Boot application with command: mvn spring-boot:run
.
Let’s use Postman to make some requests.
If you upload a file with size larger than max file size (2MB):
Conclusion
Today we’ve built a Rest CRUD API using Spring Boot to upload and import data from Excel file to Mysql database table.
We also see how to use Apache POI to read data from Excel Sheet, JpaRepository
to retrieve items in database table without need of boilerplate code.
More Derived queries at:
JPA Repository query example in Spring Boot
Custom query with @Query
annotation:
Spring JPA @Query example: Custom query in Spring Boot
If you want to add Pagination to this Spring project, you can find the instruction at:
Spring Boot Pagination & Filter example | Spring JPA, Pageable
For downloading Excel file:
Spring Boot: Download Excel file from MySQL database table
Upload CSV File instead:
Spring Boot: Upload CSV file Data into MySQL Database
Or upload files to database as BLOB:
Spring Boot Upload/Download File to/from Database example
Happy learning! See you again.
Further Reading
- Spring Data JPA Reference Documentation
- https://github.com/apache/poi
- Documentation: Spring Boot + Swagger 3 example (with OpenAPI 3)
- Caching: Spring Boot Redis Cache example
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.
Nice tutorial. Struggled with get request via postman. This helped:
Steps to use ‘Multipart/form-data ‘ in Postman
Hey, I used your code but it isn’t working for me.
On enabling Content-Type as “multipart/form-data; boundary=” I get a blank response with no message. On enabling the Content-Type with “application/vnd.ms-excel” I get a 500 Internal Server Error, with the message: “Current request is not a multipart request”.
The download link is working fine, it downloads a blank excel sheet with just the column headings. Also, the table structure is being created in the MySQL database. How do I make the upload function work?
Do we have to follow similar process for download api to download the db data to excel?
can you guide me how to check if the title exists in the database?
Thank you, this is very helpul guide
Required request part ‘file’ is not present]
I am getting this error
Resolved [org.springframework.web.multipart.support.MissingServletRequestPartException: Required request part ‘file’ is not present]
Hi, kindly check your HTTP client. It should be like this:
Can you extend this example to demonstrate how this feature can be used to manage many to many relationship between two entities represented as two sheets in the workbook?
Hello Sir, when I try to upload it via postman, it give me response 400 bad request : “required request part ‘file’ is not present”
Do you have any idea what wrong with it ?
Solved, I didn’t recognized that I need to type ‘file’ in the key section in postman.
But I got new error when send request “Handler dispatch failed; nested exception is java.lang.NoClassDefFoundError: org/apache/commons/io/output/UnsynchronizedByteArrayOutputStream”
Hi, Can you provide headers for upload api?
I’m not able to upload the excel.
Hi, the HTTP request Header is normal with Postman default configuration.
“message”: “Could not upload the file: new.xlsx!”
why my file is not save?
Hi, you need to change the sheet name to Tutorials (or any name you want). It’s because we create
Sheet
object from that name.I am getting an error while uploading the file
sheet.iterator() cannot be invoked because sheet is null.
Hi, you need to change the sheet name to Tutorials (or any name you want). It’s because we create
Sheet
object from that name.I created a tutorials.xlsx file with the same fields and I get the below error in postman
{
“message”: “Could not upload the file: tutorials.xlsx!”
}
Hi, you need to change the sheet name to Tutorials (or any name you want). It’s because we create
Sheet
object from that name.I can’t upload to the database I get a message:
“message”: “Could not upload the file: tutorials.xlsx!”
I use an example from gitub, get and download tutorials works properly but upload does not work.
Thank you in advance
Hi, you need to change the sheet name to Tutorials (or any name you want). It’s because we create
Sheet
object from that name.{“message”:”Could not upload the file: tutorials.xlsx!”}
error 417
the server cannot meet the operations of the exception request-header field
please help me to solve this problem
Hi, you need to change the sheet name to Tutorials (or any name you want). It’s because we create
Sheet
object from that name.I have this problem, although my xlsx has information.
Cannot invoke \”org.apache.poi.ss.usermodel.Sheet.iterator()\” because \”sheet\” is null”
Hi, you need to change the sheet name to Tutorials (or any name you want). It’s because we create
Sheet
object from that name.got null pointer exception on
Iterator rows = sheet.iterator();
Hi, you need to change the sheet name to Tutorials (or any name you want). It’s because we create
Sheet
object from that name.I’m getting NullPointerException while i try to upload the xlxs file !
Hi, you need to change the sheet name to Tutorials (or any name you want). It’s because we create
Sheet
object from that name.hello, I am following how to upload the spring boot excel file, but I have problems when trying to postman the message “message”: “Could not upload the file: tutori.xlsx!”,
even though my excel has .xlsx,
please find a solution
Hi, you need to change the sheet name to Tutorials (or any name you want). It’s because we create
Sheet
object from that name.I would like to suggest two changes here in the ExcelHelper.java file
Otherwise we get a null object of sheet and we were not able to store the data into the db table.
Thank you for your suggestion 🙂
Great omkar after changing to this code only, the file is successfully insert into database.
Thank you so much!
Could You please help me out ???? I am not able to insert it even after making the changes
Hi ,
I am getting a 417 error with message as {
“message”: “Could not Upload the file: TestCase.xlsx!”
}
While running the test my header is only Content-type =”multipart/form-data”
Can you please help me where I could have went wrong?
Hi, you need to change the sheet name to Tutorials (or any name you want). It’s because we create
Sheet
object from that name.getting org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘entityManagerFactory’ defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to open JDBC Connection for DDL execution
Hi, correct mysql credentials in the src > main > application.properties file
Check if your mysql credentials are correct and mysql server is running.
Please check your JDBC classes,
com.oracle.database.jdbc
ojdbc8
runtime
choose depending on your database in use.
Refer to this solution on SOF
https://stackoverflow.com/questions/40058001/error-creating-bean-with-name-entitymanagerfactory-defined-in-class-path-resou