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

springboot-mvc-restapi-export-download-excel-file-mongodb-apache-poi---feature-image

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

springboot-mvc-restapi-export-download-excel-file-mongodb-apache-poi---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 ->


	
		SpringBoot Excel
	
	
		

Download Excel Customers File

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:

springboot-mvc-restapi-export-download-excel-file-mongodb-apache-poi---find-all-mongodb-documents

-> Excel Downloaded File:

springboot-mvc-restapi-export-download-excel-file-mongodb-apache-poi---web-view

Network Logs:

springboot-mvc-restapi-export-download-excel-file-mongodb-apache-poi---network-logs

-> File Content:

springboot-mvc-restapi-export-download-excel-file-mongodb-apache-poi---download-files

SourceCode

- SpringJpaExcelDownloader

0 0 votes
Article Rating
Subscribe
Notify of
guest
4.2K Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments