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.

Let the following be our example excel:

SenderId ReceiverId InitiatorId BankCode ServiceCode TrxnAmount FeeAmount
11112 53924 32123 AABank 4 10000.23 3.00
32355 98394 98888 HHBank 5 102.87 1.00
28999 77394 27777 LLBank 24 800.2 3
40329 76738 92788 GGBank 9 1594.00 3.00
67263 82394 86282 MSBank 20 78.00 1.00
72628 32322 98883 VKBank 9 152394.00 15.00
97388 34224 22333 RBBank 12 973992.30 10.00
54344 12221 873844 BPBank 82 672893.42 10.00
23788 67349 93748 TJBank 99 82778.10 10.00
26663 28343 55263 SDBank 112 86237.00 10.00

Follow the steps below to complete this example:

Adding Dependency

Add the following dependencies to your Spring Boot project:

For Maven

Add to the pom.xml file:

For Gradle

Add to the build.gradle file:

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

You can find the other versions of Apache POI in the Maven repository.

Adding Configurations

First, add the following credentials to your resources/ configuration file:

#port on which the application should run
server.port= 8080

#mysql database connection
spring.datasource.url = jdbc:mysql://localhost:3306/test_db
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

Creating Entity Class

Create a entity class for mapping it with transaction table of database:

package com.example.transaction.entity;

import java.time.LocalDateTime;
import javax.persistence.Entity;
import javax.persistence.EntityListeners;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.Builder.Default;

@Builder(toBuilder = true)
@Table(name = "transaction")
public class Transaction {
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;
	private Long initiatorId;
	private Long senderId;
	private Long receiverId;
	private double trxnAmount;
	private double feeAmount;
	private String serviceCode;
	private String bankCode;
	private boolean refunded = false;
	private int status;
	private boolean success = false;
	private boolean deleted = false;

	private LocalDateTime created;

	private LocalDateTime modified;

	private Long createdBy;

	private Long modifiedBy;

Creating JPA Repository Class

Create a class for performing CRUD operations on the above transaction table:

package com.example.transaction.repository;

import org.springframework.stereotype.Repository;
import com.apache.poi.example.transaction.entity.Transaction;

public interface TransactionRepository extends CrudRepository<Transaction, Long> {


Creating Service

Create a service class with a method to import data from an excel file to MySQL database:

package com.example.transaction.service;

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

public interface TransactionService {

	void importToDb(List<MultipartFile> multipartfiles);


Implementation class of the above Transaction service class:

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.apache.poi.example.transaction.entity.Transaction;
import com.apache.poi.example.transaction.repository.TransactionRepository;
import com.apache.poi.example.transaction.service.TransactionService;

public class TransactionServiceImpl implements TransactionService {

	private TransactionRepository transactionRepository;

	public void importToDb(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(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)
				} 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;

Creating Web Controller

Create a controller with a REST API endpoint that allows to import excel data into a MySQL database:

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-to-db")
	public void importTransactionsFromExcelToDb(@RequestPart(required = true) List<MultipartFile> files) {



Enabling JPA Auditing

To enable Spring JPA auditing features like @CreateDate, @CreatedBy, @LastModifiedDate, and @LastModifiedBy, the main Spring Boot class should be annotated with @EnableJpaAuditing as shown in the example below:

package com.example;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

public class ApachePoiExampleApplication {

	public static void main(String[] args) {, args);


This example is complete. You may also be interested in learning how to export MySQL data into excel.