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

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.

Contents

Technologies
Practice
SourceCode

Technologies

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

Practice

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>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-web</artifactId>

</dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-data-jpa</artifactId>

</dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-thymeleaf</artifactId>

</dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

</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 + "]";
	}
 
}

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<Customer, Long>{
}

package com.grokonez.exceldownload.repository;

import org.springframework.data.repository.CrudRepository;

import com.grokonez.exceldownload.model.Customer;

public interface CustomerRepository extends CrudRepository<Customer, Long>{

}

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

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<Customer> 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<InputStreamResource> excelCustomersReport() throws IOException {
        List<Customer> customers = (List<Customer>) 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));
    }
}

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<InputStreamResource> excelCustomersReport() throws IOException {

List<Customer> customers = (List<Customer>) 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";
	}
}

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>

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

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

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:

-> Excel Downloaded File:

SourceCode

SpringJpaExcelDownloader

By grokonez | October 18, 2018.

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

Technologies

Practice

Create SpringBoot project

Customer Model

JPA Customer Repository

Implement Excel Generator

Implement RestAPI Controller

Implement Download View

Initial Customers

Database Configuration

Run & Check Results

SourceCode

Post Tags

Got Something To Say: Cancel reply

Technologies

Practice

Create SpringBoot project

Customer Model

JPA Customer Repository

Implement Excel Generator

Implement RestAPI Controller

Implement Download View

Initial Customers

Database Configuration

Run & Check Results

SourceCode

Related Posts

Post Tags

Got Something To Say: Cancel reply