Spring Boot: Upload/Import Excel file data into MySQL Database

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

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:

POST/api/excel/uploadupload an Excel File
GET/api/excel/tutorialsget List of items in db table
GET/api/excel/downloaddownload db data as Excel file


  • 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:





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 to update 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 be validate.

Define Data Model

Our Data model is Tutorial with four fields: id, title, description, published.
In model package, we define Tutorial class.


package com.bezkoder.spring.files.excel.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Table(name = "tutorials")
public class Tutorial {

  @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;

  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.


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

You also find way to write Unit Test for this JPA Repository at:
Spring Boot Unit Test for JPA Repositiory 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 from InputStream
  • create Sheet using Workbook.getSheet() method
  • iterate over Rows by Iterator with Sheet.iterator() and Iterator.hasNext()
  • from each Row, iterate over Cells
  • with each Cell, use getNumericCellValue(), 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();

Under helper package, we create ExcepHelper class with 2 methods:

  • hasExcelFormat(): check if a file has Excel format or not
  • excelToTutorials(): read InputStream 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) {

        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());

          case 1:

          case 2:

          case 3:





      return tutorials;
    } catch (IOException e) {
      throw new RuntimeException("fail to parse Excel file: " + e.getMessage());

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 database
  • getAllTutorials(): read data from database and return List<Tutorial>


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;

public class ExcelService {
  TutorialRepository repository;

  public void save(MultipartFile file) {
    try {
      List<Tutorial> tutorials = ExcelHelper.excelToTutorials(file.getInputStream());
    } 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.


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.


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;

public class ExcelController {

  ExcelService fileService;

  public ResponseEntity<ResponseMessage> uploadFile(@RequestParam("file") MultipartFile file) {
    String message = "";

    if (ExcelHelper.hasExcelFormat(file)) {
      try {

        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));

  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: 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 @ExceptionHandlerannotation for handling the exceptions.


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;

public class FileUploadExceptionAdvice extends ResponseEntityExceptionHandler {

  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):



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.

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

Or upload files to database as BLOB:
Spring Boot Upload/Download File to/from Database example


Happy learning! See you again.

Further Reading

Source Code

You can find the complete source code for this tutorial on Github.

2 thoughts to “Spring Boot: Upload/Import Excel file data into MySQL Database”

  1. 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

    1. Hi, correct mysql credentials in the src > main > application.properties file

      Check if your mysql credentials are correct and mysql server is running.

Leave a Reply

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