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 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/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.