Excel File – Download from SpringBoot RestAPI + Apache POI + MySQL

spring-boot-download-excel-file-restapi-mysql-spring-jpa-apache.poi-excel-feature-image

In the tutorial, we show you how to create a SpringBoot RestAPIs application that uses Spring JPA to get data from MySQL records and uses Apache POI library to write data to a Excel file.

Related posts:
Java – How to read/write Excel file with Apache POI
How to use Spring JPA MySQL | Spring Boot
SpringBoot – Upload/Download MultipartFile to FileSystem – Bootstrap 4 + JQuery Ajax

Technologies

– Spring Boot – 2.0.6.RELEASE
– Spring JPA
– MySQL
– Apache POI

Practice

Project Structure ->

spring-boot-download-excel-file-restapi-mysql-spring-jpa-apache.poi-project-structure

Create SpringBoot project

Use SpringToolSuite to create a SpringBoot project with below dependencies:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>      
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>   
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>    
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.17</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

Customer Model

Customer.java ->


package com.grokonez.exceldownload.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "customers")
public class Customer {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;
	
	@Column(name = "name")
	private String name;
	
	@Column(name = "address")
	private String address;
	
	@Column(name = "age")
	private int age;
 
	public Customer() {
	}
 
	public Customer(Long id, String name, String address, int age) {
		this.id = id;
		this.name = name;
		this.address = address;
		this.age = age;
	}
 
	public Long getId() {
		return id;
	}
 
	public void setId(Long id) {
		this.id = id;
	}
 
	public String getName() {
		return name;
	}
 
	public void setName(String name) {
		this.name = name;
	}
 
	public String getAddress() {
		return address;
	}
 
	public void setAddress(String address) {
		this.address = address;
	}
 
	public int getAge() {
		return age;
	}
 
	public void setAge(int age) {
		this.age = age;
	}
 
	@Override
	public String toString() {
		return "Customer [id=" + id + ", name=" + name + ", address=" + address + ", age=" + age + "]";
	}
 
}

JPA Customer Repository

CustomerRepository.java ->


package com.grokonez.exceldownload.repository;

import org.springframework.data.repository.CrudRepository;

import com.grokonez.exceldownload.model.Customer;
 
public interface CustomerRepository extends CrudRepository{
}

Implement Excel Generator

ExcelGenerator.java ->


package com.grokonez.exceldownload.util;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
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.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.grokonez.exceldownload.model.Customer;

public class ExcelGenerator {
	
	public static ByteArrayInputStream customersToExcel(List customers) throws IOException {
		String[] COLUMNs = {"Id", "Name", "Address", "Age"};
		try(
				Workbook workbook = new XSSFWorkbook();
				ByteArrayOutputStream out = new ByteArrayOutputStream();
		){
			CreationHelper createHelper = workbook.getCreationHelper();
	 
			Sheet sheet = workbook.createSheet("Customers");
	 
			Font headerFont = workbook.createFont();
			headerFont.setBold(true);
			headerFont.setColor(IndexedColors.BLUE.getIndex());
	 
			CellStyle headerCellStyle = workbook.createCellStyle();
			headerCellStyle.setFont(headerFont);
	 
			// Row for Header
			Row headerRow = sheet.createRow(0);
	 
			// Header
			for (int col = 0; col < COLUMNs.length; col++) {
				Cell cell = headerRow.createCell(col);
				cell.setCellValue(COLUMNs[col]);
				cell.setCellStyle(headerCellStyle);
			}
	 
			// CellStyle for Age
			CellStyle ageCellStyle = workbook.createCellStyle();
			ageCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
	 
			int rowIdx = 1;
			for (Customer customer : customers) {
				Row row = sheet.createRow(rowIdx++);
	 
				row.createCell(0).setCellValue(customer.getId());
				row.createCell(1).setCellValue(customer.getName());
				row.createCell(2).setCellValue(customer.getAddress());
	 
				Cell ageCell = row.createCell(3);
				ageCell.setCellValue(customer.getAge());
				ageCell.setCellStyle(ageCellStyle);
			}
	 
			workbook.write(out);
			return new ByteArrayInputStream(out.toByteArray());
		}
	}
}

Implement RestAPI Controller

- CustomerExcelDownloadRestAPI.java ->


package com.grokonez.exceldownload.controller;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.InputStreamResource;
import org.springframework.http.HttpHeaders;
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.RestController;

import com.grokonez.exceldownload.model.Customer;
import com.grokonez.exceldownload.repository.CustomerRepository;
import com.grokonez.exceldownload.util.ExcelGenerator;

@RestController
@RequestMapping("/api/customers")
public class CustomerExcelDownloadRestAPI {
    @Autowired
    CustomerRepository customerRepository;
 
    @GetMapping(value = "/download/customers.xlsx")
    public ResponseEntity excelCustomersReport() throws IOException {
        List customers = (List) customerRepository.findAll();
		
		ByteArrayInputStream in = ExcelGenerator.customersToExcel(customers);
		// return IOUtils.toByteArray(in);
		
		HttpHeaders headers = new HttpHeaders();
        headers.add("Content-Disposition", "attachment; filename=customers.xlsx");
		
		 return ResponseEntity
	                .ok()
	                .headers(headers)
	                .body(new InputStreamResource(in));
    }
}

Implement Download View

– Create ViewController.java ->


package com.grokonez.exceldownload.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
public class ViewController {

	@RequestMapping("/")
	public String home() {
		return "home";
	}
}

– Create .html view home.html ->

<html>
	<head>
		<title>SpringBoot Excel</title>
	</head>
	<body>
		<h3>Download Excel Customers File</h3>
		<div>
			<a href="http://localhost:8080/api/customers/download/customers.xlsx">Customers .XLSX</a>
		</div>
	</body>
</html>

Initial Customers

– In main class, we use CommandLineRunner to init Customer’s records:


package com.grokonez.exceldownload;

import java.util.Arrays;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import com.grokonez.exceldownload.model.Customer;
import com.grokonez.exceldownload.repository.CustomerRepository;

@SpringBootApplication
public class SpringJpaExcelDownloaderApplication implements CommandLineRunner {

	@Autowired
	CustomerRepository repository;
	
	public static void main(String[] args) {
		SpringApplication.run(SpringJpaExcelDownloaderApplication.class, args);
	}
	
    @Override
    public void run(String... args) throws Exception {
    	
    	List customers = Arrays.asList(
    			new Customer(Long.valueOf(1), "Jack Smith", "Massachusetts", 23),
    			new Customer(Long.valueOf(2), "Adam Johnson", "New York", 27),
    			new Customer(Long.valueOf(3), "Katherin Carter", "Washington DC", 26),
    			new Customer(Long.valueOf(4), "Jack London", "Nevada", 33), 
    			new Customer(Long.valueOf(5), "Jason Bourne", "California", 36));
    	
		// save a list of Customers
		repository.saveAll(customers);
    }	
}

Database Configuration

application.properties ->


spring.datasource.url=jdbc:mysql://localhost:3306/testdb
spring.datasource.username=root
spring.datasource.password=12345
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create-drop

Run & Check Results

Run the SpringBoot project,

-> MySQL’s records:

spring-boot-download-excel-file-restapi-mysql-spring-jpa-apache.poi-mysql-tables

-> Excel Downloaded File:

spring-boot-download-excel-file-restapi-mysql-spring-jpa-apache.poi-view

spring-boot-download-excel-file-restapi-mysql-spring-jpa-apache.poi-network-logs

spring-boot-download-excel-file-restapi-mysql-spring-jpa-apache.poi-excel-downloaded-file

SourceCode

SpringJpaExcelDownloader



By grokonez | October 18, 2018.

Last updated on April 21, 2021.



Related Posts


9 thoughts on “Excel File – Download from SpringBoot RestAPI + Apache POI + MySQL”

  1. You should set Content-Type in order to have a valid mime Type.
    For example
    headers.add(“Content-Type”, “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”);

  2. I tried this, but getting error. Actually I have configured plsql db in application.properties seems profile is not activated.
    I want to pull the record from plsql and download as excel. Please help me.

    INFO: No Active profile set, falling back to default profiles: default
    INFO: Refreshing org.springframework.boot.context.embedded.annotationConfigEmbeddedWebapplicationcontect@66fd8833:startupdate ; root of context hierarchy
    WARN: Exception encountered during context initialization – cancelling refresh attempt: org.springframework.beans.factory.BeanDefinitionStoreException:Failed to parse configuration class [com.dataextract.SpringJpaExcelDownloaderApplication]; nested exception is java.io.FileNotFoundException: Class path resource[application.properties] cannot be opened because it does not exist.
    ERROR: Destroy method on bean withname ‘org.springframework.boot.autoconfigure.internalCachingMetadataReaderFactory’ threw an exception.

    I am new to spring boot. i want to finish this for my organization. Please help me.

  3. With little modification in code , able to create xls file . able to hit end point url from postman directly.Thanks a lot.

Got Something To Say:

Your email address will not be published. Required fields are marked *

*