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:

1. Add Dependencies: Dependencies used in this example are Spring Web, Spring Boot DevTools, MySQL Driver, Spring Data JPA, Lombok, and Apache POI (poi-ooxml).



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'



2. Add Configurations: Open the src/main/resources/application.properties file and add the following configuration lines to the file:



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



3. 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:



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;

}



4. Create an interface named "TransactionRepository" that represents a repository responsible for handling data access operations related to transactions:



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);

}



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

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);
  }

}



6. Next, create a utility class named CommonUtils. This class contains methods for converting dates to strings and strings to dates:



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.");
    }
  }
}



7. Create a service interface named "TransactionService":



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);

}



8. Create an implementation class named "TransactionServiceImpl" for TransactionService interface:



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

}



9. Create a controller class named TransactionController that will handle HTTP requests and interact with the TransactionService:



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));
  }

}



10. 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):



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);
      }
    };
  }

}



11. 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:



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);
  }

}



12. Build and run your Spring Boot application.
13. Test your Spring Boot application by using API testing tools such as Postman to test your application's endpoint. Request example: