SpringBoot MVC RestAPIs – MongoDB – Export/Download Excel File using Apache POI example

In the tutorial, we show you how to create a SpringBoot RestAPIs application that uses Spring Data MongoDB to get data from MongoDB collection 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 SpringData MongoRepository to interact with MongoDB
– SpringBoot – Upload/Download MultipartFile to FileSystem – Bootstrap 4 + JQuery Ajax

Technologies

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

Practice

Project Structure ->

Create SpringBoot project

Use SpringToolSuite to create a SpringBoot project with below dependencies:

    org.springframework.boot
    spring-boot-starter-web
      

	org.springframework.boot
	spring-boot-starter-data-mongodb
    

	org.springframework.boot
	spring-boot-starter-thymeleaf
    

	org.apache.poi
	poi-ooxml
	3.17

Customer Model

– Customer.java ->

package com.ozenero.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.ozenero.exceldownload.repository;

import org.springframework.data.repository.CrudRepository;

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

Implement Excel Generator

– ExcelGenerator.java ->

package com.ozenero.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.ozenero.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.ozenero.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.ozenero.exceldownload.model.Customer;
import com.ozenero.exceldownload.repository.CustomerRepository;
import com.ozenero.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.ozenero.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 ->

	
		
	
	
		
Download Excel Customers File
		

			Customers .XLSX
		
	

Initial Customers

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

package com.ozenero.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.ozenero.exceldownload.model.Customer;
import com.ozenero.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.data.mongodb.database=gkzdb
spring.data.mongodb.port=27017

Run & Check Results

Run the SpringBoot project,

-> MongoDB’s documents:

-> Excel Downloaded File:

Network Logs:

-> File Content:

SourceCode

- SpringJpaExcelDownloader