Importing Excel Data into MySQL Database in Spring Boot via REST API using Apache POI Library

  • Last updated Apr 25, 2024

In this tutorial, we will show you how to import data from an excel file into a MySQL database using the Apache POI library in Spring Boot via REST API.

Apache POI is an open-source Java library that allows to create and modify various file formats of Microsoft Office documents using Java programming language.

In this example, we will import data into a MySQL database from an Excel file. However, this code will work with any other database.

Let the data that we need to import to a MySQL table from an Excel file be the following:

10012 91030 87588 ABANK 50 1000.34 20.00
10232 73884 87478 XBANK
393 12000.45 25.00
32002 97485 87485 GBANK
98 1733.42 20.00
34449 36728 87482 YBANK
2 12388.00 28.00
54886 14241 87882 OBANK
8 50000.00 30.00
19239 15893 81129 ABANK
30 12005.50 22.00
63829 18937 80882 XBANK
70 100.10 2.00
95783 19893 87322 WBANK
9 500.30 2.00
19839 38944 87321 HBANK
6 28900.16 24.00
78839 39923 89299 RBANK 1 5000.00 5.00

Follow the steps below to complete this example:

  1. Add Dependencies: Dependencies used in this example are Spring Web, Spring Boot DevTools, MySQL Driver, Spring Data JPA, Lombok, and Apache POI (poi-ooxml).

  2. poi-ooxml library is one of the Apache POI components used for working with Office Open XML (OOXML) file formats, such as Microsoft Office documents (.xlsx, .pptx, .docx). It provides classes and utilities for reading, creating, and manipulating these OOXML files.

    Add the poi-ooxml library to your project:

    For Maven:


    For Gradle:

    implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '5.2.3'

  3. Add Configurations: Open the src/main/resources/ file and add the following configuration lines to the file:

  4. server.port= 8080
    #mysql database connection
    spring.datasource.url = jdbc:mysql://localhost:3306/test_buddy
    spring.datasource.username = root
    spring.datasource.password = Testing123
    spring.datasource.timeBetweenEvictionRunsMillis = 60000
    spring.datasource.maxIdle = 1
    #below properties will automatically creates and updates database schema

  5. Let's consider the following class as the entity class mapped to the database table from which we need to export the data to Excel:

  6. package com.example.transaction.entity;
    import java.util.Date;
    import jakarta.persistence.Entity;
    import jakarta.persistence.EntityListeners;
    import jakarta.persistence.GeneratedValue;
    import jakarta.persistence.GenerationType;
    import jakarta.persistence.Id;
    import jakarta.persistence.Table;
    import lombok.AllArgsConstructor;
    import lombok.Builder;
    import lombok.Builder.Default;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    @Table(name = "transaction")
    public class Transaction {
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private Long id;
      private Long initiatorId;
      private Long senderId;
      private Long receiverId;
      private double trxnAmount;
      private double feeAmount;
      private int serviceCode;
      private String bankCode;
      private boolean refunded = false;
      private int status;
      private boolean success = false;
      private boolean deleted = false;
      private Date createdDate;
      private Date modifiedDate;
      private Long createdBy;
      private Long modifiedBy;

  7. Create an interface named "TransactionRepository" that represents a repository responsible for handling data access operations related to transactions:

  8. package com.example.transaction.repository;
    import org.springframework.stereotype.Repository;
    import com.example.transaction.entity.Transaction;
    public interface TransactionRepository extends CrudRepository<Transaction, Long> {

  9. Create a service interface named "TransactionService":

  10. package com.example.transaction.service;
    import java.util.List;
    import org.springframework.web.multipart.MultipartFile;
    public interface TransactionService {
      void importExcelToDatabase(List<MultipartFile> multipartfiles);

  11. Create an implementation class named "TransactionServiceImpl" for TransactionService interface:

  12. package com.example.transaction.service.impl;
    import java.util.ArrayList;
    import java.util.List;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.web.multipart.MultipartFile;
    import com.example.transaction.entity.Transaction;
    import com.example.transaction.repository.TransactionRepository;
    import com.example.transaction.service.TransactionService;
    public class TransactionServiceImpl implements TransactionService {
      private TransactionRepository transactionRepository;
      public void importExcelToDatabase(List<MultipartFile> multipartfiles) {
        if (!multipartfiles.isEmpty()) {
          List<Transaction> transactions = new ArrayList<>();
          multipartfiles.forEach(multipartfile -> {
            try {
              XSSFWorkbook workBook = new XSSFWorkbook(multipartfile.getInputStream());
              XSSFSheet sheet = workBook.getSheetAt(0);
              // looping through each row
              for (int rowIndex = 0; rowIndex < getNumberOfNonEmptyCells(sheet, 0) - 1; rowIndex++) {
                // current row
                XSSFRow row = sheet.getRow(rowIndex);
                // skip the first row because it is a header row
                if (rowIndex == 0) {
                Long senderId = Long.parseLong(getValue(row.getCell(0)).toString());
                Long receiverId = Long.parseLong(getValue(row.getCell(1)).toString());
                Long initiatorId = Long.parseLong(getValue(row.getCell(2)).toString());
                String bankCode = String.valueOf(row.getCell(3));
                int serviceCode = Integer.parseInt(getValue(row.getCell(4)).toString());
                double transactionAmount = Double.parseDouble(row.getCell(5).toString());
                double feeAmount = Double.parseDouble(row.getCell(6).toString());
                Transaction transaction =
            } catch (IOException e) {
          if (!transactions.isEmpty()) {
            // save to database
      private Object getValue(Cell cell) {
        switch (cell.getCellType()) {
          case STRING:
            return cell.getStringCellValue();
          case NUMERIC:
            return String.valueOf((int) cell.getNumericCellValue());
          case BOOLEAN:
            return cell.getBooleanCellValue();
          case ERROR:
            return cell.getErrorCellValue();
          case FORMULA:
            return cell.getCellFormula();
          case BLANK:
            return null;
          case _NONE:
            return null;
        return null;
      public static int getNumberOfNonEmptyCells(XSSFSheet sheet, int columnIndex) {
        int numOfNonEmptyCells = 0;
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
          XSSFRow row = sheet.getRow(i);
          if (row != null) {
            XSSFCell cell = row.getCell(columnIndex);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
        return numOfNonEmptyCells;

  13. Create a controller class named TransactionController that will handle HTTP requests and interact with the TransactionService:

  14. package com.example.transaction.controller;
    import java.util.List;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.PostMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestPart;
    import org.springframework.web.bind.annotation.RestController;
    import org.springframework.web.multipart.MultipartFile;
    import com.example.transaction.service.TransactionService;
    @RequestMapping(path = "/transactions")
    public class TransactionController {
      private TransactionService transactionService;
      @PostMapping(path = "/import-excel")
      public void importExcelToDatabase(
          @RequestPart(required = true) List<MultipartFile> files) {

  15. Annotate the main class with @EnableJpaAuditing annotation. It is used to enable auditing in JPA entities. Auditing allows automatic population of specific fields such as createdDate and updatedDate in JPA entities based on the current date and time. It is commonly used to keep track of when an entity was created or last modified. The main class should look like this:

  16. package com.example;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    public class ExampleApplication {
      public static void main(String[] args) {, args);

  17. Build and run your Spring Boot application.
  18. Test your Spring Boot application by using API testing tools such as Postman to test your application's endpoint. Request example: