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

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:

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



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:

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




For Gradle:

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



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



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
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=update



3. 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:



package com.example.transaction.entity;

import java.util.Date;
import org.springframework.data.annotation.CreatedBy;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedBy;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
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;

@Builder
@Data
@NoArgsConstructor
@AllArgsConstructor
@EntityListeners(AuditingEntityListener.class)
@Entity
@Table(name = "transaction")
public class Transaction {
  @Id
  @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;
  @Default
  private boolean refunded = false;
  private int status;
  @Default
  private boolean success = false;
  @Default
  private boolean deleted = false;

  @CreatedDate
  private Date createdDate;

  @LastModifiedDate
  private Date modifiedDate;

  @CreatedBy
  private Long createdBy;

  @LastModifiedBy
  private Long modifiedBy;

}



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



package com.example.transaction.repository;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import com.example.transaction.entity.Transaction;

@Repository
public interface TransactionRepository extends CrudRepository<Transaction, Long> {
  
}



5. Create a service interface named "TransactionService":



package com.example.transaction.service;

import java.util.List;
import org.springframework.web.multipart.MultipartFile;

public interface TransactionService {

  void importExcelToDatabase(List<MultipartFile> multipartfiles);
  
}



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



package com.example.transaction.service.impl;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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;

@Service
public class TransactionServiceImpl implements TransactionService {

  @Autowired
  private TransactionRepository transactionRepository;

  @Override
  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) {
              continue;
            }
            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 =
                Transaction.builder().senderId(senderId).receiverId(receiverId)
                    .initiatorId(initiatorId).bankCode(bankCode).serviceCode(serviceCode)
                    .trxnAmount(transactionAmount).feeAmount(feeAmount).build();
            transactions.add(transaction);
          }
        } catch (IOException e) {
          e.printStackTrace();
        }
      });

      if (!transactions.isEmpty()) {
        // save to database
        transactionRepository.saveAll(transactions);
      }
    }
  }

  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;
      default:
        break;
    }
    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) {
          numOfNonEmptyCells++;
        }
      }
    }
    return numOfNonEmptyCells;
  }

}



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



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;

@RestController
@RequestMapping(path = "/transactions")
public class TransactionController {

  @Autowired
  private TransactionService transactionService;

  @PostMapping(path = "/import-excel")
  public void importExcelToDatabase(
      @RequestPart(required = true) List<MultipartFile> files) {

    transactionService.importExcelToDatabase(files);

  }
}



8. 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:



package com.example;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;

@EnableJpaAuditing
@SpringBootApplication
public class ExampleApplication {

  public static void main(String[] args) {
    SpringApplication.run(ExampleApplication.class, args);
  }

}



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