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:
A | B | C | D | E | F | G |
SenderId | ReceiverId | InitiatorId | BankCode | ServiceCode | TrxnAmount | FeeAmount |
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:
- Add Dependencies: Dependencies used in this example are Spring Web, Spring Boot DevTools, MySQL Driver, Spring Data JPA, Lombok, and Apache POI (poi-ooxml).
- Add Configurations: Open the src/main/resources/application.properties file and add the following configuration lines to the file:
- 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:
- Create an interface named "TransactionRepository" that represents a repository responsible for handling data access operations related to transactions:
- Create a service interface named "TransactionService":
- Create an implementation class named "TransactionServiceImpl" for TransactionService interface:
- Create a controller class named TransactionController that will handle HTTP requests and interact with the TransactionService:
- 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:
- Build and run your Spring Boot application.
- Test your Spring Boot application by using API testing tools such as Postman to test your application's endpoint. Request example:
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'
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
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;
}
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> {
}
package com.example.transaction.service;
import java.util.List;
import org.springframework.web.multipart.MultipartFile;
public interface TransactionService {
void importExcelToDatabase(List<MultipartFile> multipartfiles);
}
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;
}
}
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);
}
}
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);
}
}