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:

Follow the steps below to complete this example:

Adding Dependency

Add the following dependencies to your Spring Boot project:

Add to the pom.xml file:

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

Creating Entity Class

Create a Transaction.java 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 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 lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.Builder.Default;

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

	@CreatedDate
	private LocalDateTime created;

	@LastModifiedDate
	private LocalDateTime modified;

	@CreatedBy
	private Long createdBy;

	@LastModifiedBy
	private Long modifiedBy;
}

Creating JPA Repository Class

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

package com.example.transaction.repository;

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

@Repository
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 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 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) {
							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(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;
	}
}

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;

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

	@Autowired
	private TransactionService transactionService;

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

		transactionService.importToDb(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;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;

@SpringBootApplication
@EnableJpaAuditing
public class ApachePoiExampleApplication {

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

}

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