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 use MySQL database. However, the code to export data to excel will work with any database.
Follow the steps below to complete this example:
Adding Dependency
Add the following dependencies to your Spring Boot project:
For MavenAdd 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 java.time.LocalDateTime;
import java.util.List;
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> {
List<Transaction> findByCreatedBetween(LocalDateTime startDate, LocalDateTime endDate);
}
Creating Service
Create a service class with a method to export data from database to excel file:
package com.example.transaction.service;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.servlet.mvc.method.annotation.StreamingResponseBody;
public interface TransactionService {
StreamingResponseBody exportToExcel(Long startDate, Long endDate, HttpServletResponse response);
}
Implementation class of the above Transaction service class:
package com.example.transaction.service.impl;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneId;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
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.apache.poi.example.exception.InvalidRequestException;
import com.apache.poi.example.transaction.entity.Transaction;
import com.apache.poi.example.transaction.repository.TransactionRepository;
import com.apache.poi.example.transaction.service.TransactionService;
@Service
public class TransactionServiceImpl implements TransactionService {
@Autowired
private TransactionRepository transactionRepository;
@Override
public StreamingResponseBody exportToExcel(Long startDate, Long endDate, HttpServletResponse response) {
LocalTime startTime = LocalTime.of(0, 0);
LocalTime endTime = LocalTime.of(23, 59);
LocalDateTime startDateTime = null;
LocalDateTime endDateTime = null;
if (startDate == null || endDate == null) {
throw new InvalidRequestException("both startDate and endDate request parameter is mandatory");
}
startDateTime = LocalDateTime.of(Instant.ofEpochMilli(startDate).atZone(ZoneId.systemDefault()).toLocalDate(),
startTime);
endDateTime = LocalDateTime.of(Instant.ofEpochMilli(endDate).atZone(ZoneId.systemDefault()).toLocalDate(),
endTime);
List<Transaction> transactions = transactionRepository.findByCreatedBetween(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.getCreated() != null ? transaction.getCreated() : 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.getModified() != null ? transaction.getModified() : 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();
}
}
};
}
}
Creating Web Controller
Create a controller with a REST API endpoint that allows to export and download data from database to excel file:
package com.example.transaction.controller;
import javax.servlet.http.HttpServletResponse;
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.apache.poi.example.transaction.service.TransactionService;
@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) Long startDate,
@RequestParam(name = "endDate", required = true) Long endDate, HttpServletResponse response) {
return ResponseEntity.ok(transactionService.exportToExcel(startDate, endDate, response));
}
}
Creating Config class
Create AsyncConfig.java class and configure the TaskExecutor. It is recommended that you 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):
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);
}
};
}
}
Creating Custom Exception Handling classes
Create a custom InvalidRequestException.java exception class:
package com.example.exception;
public class InvalidRequestException extends RuntimeException {
private static final long serialVersionUID = 1L;
public InvalidRequestException(String message) {
super(message);
}
public InvalidRequestException(String message, Throwable cause) {
super(message, cause);
}
}
Create ErrorDto.java class:
package com.example.exception.dto;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Builder.Default;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder(toBuilder = true)
public class ErrorDto {
@Default
private boolean error = true;
private String message;
}
Create GlobalExceptionHandler.java exception handler class:
package com.example.exception.handler;
import org.springframework.core.Ordered;
import org.springframework.core.annotation.Order;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.ResponseStatus;
import org.springframework.web.bind.annotation.RestController;
import com.apache.poi.example.exception.InvalidRequestException;
import com.apache.poi.example.exception.dto.ErrorDto;
@ControllerAdvice
@RestController
@Order(Ordered.HIGHEST_PRECEDENCE)
public class GlobalExceptionHandler {
@ResponseStatus(HttpStatus.BAD_REQUEST)
@ExceptionHandler(InvalidRequestException.class)
public ErrorDto invalidRequestException(InvalidRequestException ex) {
ErrorDto error = new ErrorDto();
error.setMessage(ex.getMessage());
return error;
}
}
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);
}
}
You can run your application and test the API by making a GET request as shown in the example below:
NOTE: The value of startDate and endDate query param is in milliseconds.
This example is complete. You may also be interested in learning how to import excel data into a MySQL database.
