Exporting Data from MySQL Database to Excel in Spring Boot via REST API using Apache POI Library
In this tutorial, we will show you how to export data from a MySQL database to an excel file in Spring Boot via REST API using the Apache POI Library.
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 export data from a MySQL database. However, this code will work with any other database.
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 classes to handle custom exceptions. Custom exceptions allow you to create specific exception types for your application that can be thrown when certain exceptional situations occur.
- Next, create a utility class named CommonUtils. This class contains methods for converting dates to strings and strings to dates:
- 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:
- It is recommended that we should explicitly configure the TaskExecutor if the file to be downloaded is large and will take more than a minute to download. Here is the complete code for configuring the TaskExecutor with a request timeout of 3600000 milliseconds (60 minutes):
- 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 java.util.Date;
import java.util.List;
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> {
List<Transaction> findByCreatedDateBetween(Date startDate, Date endDate);
}Let's start by creating a Java class named Error with three private fields: message, status, and timestamp. The use of Lombok @Data annotation will generate the getter and setter methods. This class represents data container that holds information related to an error:
package com.example.exception.model;
import lombok.Data;
@Data
public class Error {
private String message;
private int status;
private Long timestamp;
}
Create a custom exception class named InvalidRequestException, which extends the RuntimeException class:
package com.example.exception;
public class InvalidRequestException extends RuntimeException {
private static final long serialVersionUID = 1L;
public InvalidRequestException(String message) {
super(message);
}
}
Create a Global Exception Handler class named GlobalExceptionHandlerController. The purpose of this class is to handle specific exceptions globally, providing consistent and customized error responses to clients when certain exceptions occur during the application's execution:
package com.example.exception.controller;
import java.util.Date;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;
import com.example.exception.InvalidRequestException;
import jakarta.servlet.http.HttpServletRequest;
import com.example.exception.model.Error;
@ControllerAdvice
public class GlobalExceptionHandlerController {
@ExceptionHandler(InvalidRequestException.class)
public ResponseEntity<Object> invalidRequest(InvalidRequestException ex,
HttpServletRequest request) {
Error error = new Error();
error.setMessage(ex.getMessage());
error.setTimestamp(new Date().getTime());
error.setStatus(HttpStatus.BAD_REQUEST.value());
return new ResponseEntity<>(error, null, HttpStatus.BAD_REQUEST);
}
}
package com.example.util;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import com.example.exception.InvalidRequestException;
public class CommonUtils {
public static String convertDateToString(Date date) {
if (date == null) {
return "";
}
String pattern = "yyyy-MM-dd";
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
return sdf.format(date);
}
public static Date convertStringToDate(String date) {
if (date == null || date.isEmpty()) {
return null;
}
String pattern = "yyyy-MM-dd";
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
sdf.setLenient(false); // Disallow lenient parsing to ensure strict matching
try {
// Attempt to parse the date with the specified pattern
return sdf.parse(date);
} catch (ParseException e) {
// If parsing fails, it means the date does not match the pattern,
throw new InvalidRequestException("date must be in yyyy-mm-dd format.");
}
}
}
package com.example.transaction.service;
import org.springframework.web.servlet.mvc.method.annotation.StreamingResponseBody;
import jakarta.servlet.http.HttpServletResponse;
public interface TransactionService {
StreamingResponseBody exportToExcel(String startDate, String endDate, HttpServletResponse response);
}
package com.example.transaction.service.impl;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.servlet.mvc.method.annotation.StreamingResponseBody;
import com.example.exception.InvalidRequestException;
import com.example.transaction.entity.Transaction;
import com.example.transaction.repository.TransactionRepository;
import com.example.transaction.service.TransactionService;
import com.example.util.CommonUtils;
import jakarta.servlet.http.HttpServletResponse;
@Service
public class TransactionServiceImpl implements TransactionService {
@Autowired
private TransactionRepository transactionRepository;
@Override
public StreamingResponseBody exportToExcel(String startDate, String endDate,
HttpServletResponse response) {
Date startDateTime = CommonUtils.convertStringToDate(startDate);
Date endDateTime = CommonUtils.convertStringToDate(endDate);
if (startDate == null || endDate == null) {
throw new InvalidRequestException("Both startDate and endDate is required.");
}
// Set the time part of the date to 00:00:00
Calendar calendar = Calendar.getInstance();
calendar.setTime(startDateTime);
calendar.set(Calendar.HOUR_OF_DAY, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.SECOND, 0);
startDateTime = calendar.getTime();
// Set the time part of the date to 23:59:59
calendar.setTime(endDateTime);
calendar.set(Calendar.HOUR_OF_DAY, 23);
calendar.set(Calendar.MINUTE, 59);
calendar.set(Calendar.SECOND, 59);
endDateTime = calendar.getTime();
List<Transaction> transactions =
transactionRepository.findByCreatedDateBetween(startDateTime, endDateTime);
if (transactions.isEmpty()) {
throw new InvalidRequestException("No data found in database");
}
return outputStream -> {
try (ByteArrayOutputStream out = new ByteArrayOutputStream();
SXSSFWorkbook workbook = new SXSSFWorkbook(SXSSFWorkbook.DEFAULT_WINDOW_SIZE)) {
// Creating excel sheet
String sheetName = "Transactions";
Sheet sheet = workbook.createSheet(sheetName);
// Creating font style for excel sheet
Font headerFont = workbook.createFont();
headerFont.setColor(IndexedColors.BLACK.getIndex());
CellStyle headerColumnStyle = workbook.createCellStyle();
headerColumnStyle.setFont(headerFont);
// Row for header at 0 index
Row headerRow = sheet.createRow(0);
// Name of the columns to be added in the sheet
String[] columns = new String[] {"id", "sender_id", "receiver_id", "initiator_id",
"bank_code", "service_code", "transaction_amount", "fee_amount", "status", "success",
"refunded", "created_date", "created_by", "modified_date", "modified_by"};
// Creating header column at the first row
for (int i = 0; i < columns.length; i++) {
Cell headerColumn = headerRow.createCell(i);
headerColumn.setCellValue(columns[i]);
headerColumn.setCellStyle(headerColumnStyle);
}
// Date formatting
CellStyle dataColumnDateFormatStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
dataColumnDateFormatStyle
.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy h:mm;@"));
// Adding data to sheet from the second row
int rowIndex = 1;
for (Transaction transaction : transactions) {
// Creating row for writing data
Row dataRow = sheet.createRow(rowIndex);
Cell columnId = dataRow.createCell(0);
columnId.setCellValue(transaction.getId() != null ? transaction.getId() : -1);
Cell columnSenderId = dataRow.createCell(1);
columnSenderId
.setCellValue(transaction.getSenderId() != null ? transaction.getSenderId() : -1);
Cell columnReceiverId = dataRow.createCell(2);
columnReceiverId
.setCellValue(transaction.getReceiverId() != null ? transaction.getReceiverId() : -1);
Cell columnInitiatorId = dataRow.createCell(3);
columnInitiatorId.setCellValue(
transaction.getInitiatorId() != null ? transaction.getInitiatorId() : -1);
Cell columnBankCode = dataRow.createCell(4);
columnBankCode
.setCellValue(transaction.getBankCode() != null ? transaction.getBankCode() : "");
Cell columnServiceCode = dataRow.createCell(5);
columnServiceCode.setCellValue(transaction.getServiceCode());
Cell columnTrxnAmount = dataRow.createCell(6);
columnTrxnAmount.setCellValue(transaction.getTrxnAmount());
Cell columnFeeAmount = dataRow.createCell(7);
columnFeeAmount.setCellValue(transaction.getFeeAmount());
Cell columnStatus = dataRow.createCell(8);
columnStatus.setCellValue(transaction.getStatus());
Cell columnIsSuccess = dataRow.createCell(9);
columnIsSuccess.setCellValue(transaction.isSuccess());
Cell columnIsRefunded = dataRow.createCell(10);
columnIsRefunded.setCellValue(transaction.isRefunded());
Cell columnCreated = dataRow.createCell(11);
columnCreated.setCellStyle(dataColumnDateFormatStyle);
columnCreated.setCellValue(
transaction.getCreatedDate() != null ? transaction.getCreatedDate() : null);
Cell columnCreatedBy = dataRow.createCell(12);
columnCreatedBy
.setCellValue(transaction.getCreatedBy() != null ? transaction.getCreatedBy() : -1);
Cell columnModified = dataRow.createCell(13);
columnModified.setCellStyle(dataColumnDateFormatStyle);
columnModified.setCellValue(
transaction.getModifiedDate() != null ? transaction.getModifiedDate() : null);
Cell columnModifiedBy = dataRow.createCell(14);
columnModifiedBy
.setCellValue(transaction.getModifiedBy() != null ? transaction.getModifiedBy() : -1);
// Incrementing rowIndex by 1
rowIndex++;
}
workbook.write(out);
workbook.dispose();
String filename = "Transactions-" + startDate + "-" + endDate + ".xlsx";
response.setHeader("Content-Disposition", "attachment; filename=" + filename);
response.setContentLength((int) out.size());
InputStream inputStream = new ByteArrayInputStream(out.toByteArray());
int BUFFER_SIZE = 1024;
int bytesRead;
byte[] buffer = new byte[BUFFER_SIZE];
// Writing to output stream
while ((bytesRead = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, bytesRead);
}
if (inputStream != null) {
inputStream.close();
}
}
};
}
}package com.example.transaction.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.mvc.method.annotation.StreamingResponseBody;
import com.example.transaction.service.TransactionService;
import jakarta.servlet.http.HttpServletResponse;
@RestController
@RequestMapping(path = "/transactions")
public class TransactionController {
@Autowired
private TransactionService transactionService;
@GetMapping(path = "/export-to-excel")
public ResponseEntity<StreamingResponseBody> downloadTransactions(
@RequestParam(name = "startDate", required = true) String startDate,
@RequestParam(name = "endDate", required = true) String endDate,
HttpServletResponse response) {
return ResponseEntity.ok(transactionService.exportToExcel(startDate, endDate, response));
}
}package com.example.config;
import java.util.concurrent.Callable;
import org.springframework.aop.interceptor.AsyncUncaughtExceptionHandler;
import org.springframework.aop.interceptor.SimpleAsyncUncaughtExceptionHandler;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.task.AsyncTaskExecutor;
import org.springframework.scheduling.annotation.AsyncConfigurer;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.web.context.request.NativeWebRequest;
import org.springframework.web.context.request.async.CallableProcessingInterceptor;
import org.springframework.web.context.request.async.TimeoutCallableProcessingInterceptor;
import org.springframework.web.servlet.config.annotation.AsyncSupportConfigurer;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;
@Configuration
@EnableAsync
@EnableScheduling
public class AsyncConfig implements AsyncConfigurer {
@Override
@Bean(name = "taskExecutor")
public AsyncTaskExecutor getAsyncExecutor() {
ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
executor.setCorePoolSize(10);
executor.setMaxPoolSize(15);
executor.setQueueCapacity(50);
return executor;
}
@Override
public AsyncUncaughtExceptionHandler getAsyncUncaughtExceptionHandler() {
return new SimpleAsyncUncaughtExceptionHandler();
}
@Bean
public WebMvcConfigurer webMvcConfigurerConfigurer(AsyncTaskExecutor taskExecutor,
CallableProcessingInterceptor callableProcessingInterceptor) {
return new WebMvcConfigurer() {
@Override
public void configureAsyncSupport(AsyncSupportConfigurer configurer) {
configurer.setDefaultTimeout(3600000).setTaskExecutor(taskExecutor);
configurer.registerCallableInterceptors(callableProcessingInterceptor);
WebMvcConfigurer.super.configureAsyncSupport(configurer);
}
};
}
@Bean
public CallableProcessingInterceptor callableProcessingInterceptor() {
return new TimeoutCallableProcessingInterceptor() {
@Override
public <T> Object handleTimeout(NativeWebRequest request, Callable<T> task) throws Exception {
return super.handleTimeout(request, task);
}
};
}
}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);
}
}