Kotlin Spring Boot RestAPIs - Download Excel File - Apache POI + MySQL

In the tutorial, we show you how to create a Kotlin 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

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

Practice

Project Structure ->

Create Kotlin SpringBoot project

Use SpringToolSuite to create a Kotlin SpringBoot project with below dependencies:

<dependency>
	<groupId>org.jetbrains.kotlin</groupId>
	<artifactId>kotlin-stdlib-jdk8</artifactId>
</dependency>
<dependency>
	<groupId>org.jetbrains.kotlin</groupId>
	<artifactId>kotlin-reflect</artifactId>
</dependency>
<dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>               
      <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-data-jpa</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>
<dependency>
    <groupId>javax.xml.bind</groupId>
    <artifactId>jaxb-api</artifactId>
    <version>2.3.0</version>
</dependency>

<groupId>org.jetbrains.kotlin</groupId>

<artifactId>kotlin-stdlib-jdk8</artifactId>

</dependency>

<groupId>org.jetbrains.kotlin</groupId>

<artifactId>kotlin-reflect</artifactId>

</dependency>

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

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

</dependency>

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

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

</dependency>

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

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

</dependency>

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

<artifactId>poi-ooxml</artifactId>

</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

<groupId>javax.xml.bind</groupId>

</dependency>

Customer Model

– Customer.kt ->

package com.grokonez.kotlin.excel.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")
data class Customer(
	@Column(name = "name")
	var name: String = "",
	
	@Column(name = "address")
	var address: String = "", 		
		
	@Column(name = "age")
	var age: Int = 0, 		
	
	@Id
    @GeneratedValue(strategy = GenerationType.AUTO)
	var id: Long = -1
){}

package com.grokonez.kotlin.excel.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")

data class Customer(

@Column(name = "name")

var name: String = "",

@Column(name = "address")

var address: String = "",

@Column(name = "age")

var age: Int = 0,

@Id

@GeneratedValue(strategy = GenerationType.AUTO)

var id: Long = -1

){}

JPA Customer Repository

– CustomerRepository.kt ->

package com.grokonez.kotlin.excel.repository

import com.grokonez.kotlin.excel.model.Customer
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.stereotype.Repository

interface CustomerRepository : JpaRepository<Customer, Long>{
}

package com.grokonez.kotlin.excel.repository

import com.grokonez.kotlin.excel.model.Customer

import org.springframework.data.jpa.repository.JpaRepository

import org.springframework.stereotype.Repository

interface CustomerRepository : JpaRepository<Customer, Long>{

}

Implement Excel Generator

– ExcelGenerator.kt ->

package com.grokonez.kotlin.excel.util

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;

import org.slf4j.Logger
import org.slf4j.LoggerFactory
 
import com.grokonez.kotlin.excel.model.Customer

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;

class ExcelGenerator {
	
	companion object {
		fun customerPDFReport(customers: List<Customer> ) : ByteArrayInputStream {
			val COLUMNs = arrayOf<String>("Id", "Name", "Address", "Age")
			
			val workbook = XSSFWorkbook()
			val createHelper = workbook.getCreationHelper()
		 
			val sheet = workbook.createSheet("Customers")
		 
			val headerFont = workbook.createFont()
			headerFont.setBold(true)
			headerFont.setColor(IndexedColors.BLUE.getIndex())
		 
			val headerCellStyle = workbook.createCellStyle()
			headerCellStyle.setFont(headerFont)
		 
			// Row for Header
			val headerRow = sheet.createRow(0)
		 
			// Header
			for (col in COLUMNs.indices) {
				val cell = headerRow.createCell(col)
				cell.setCellValue(COLUMNs[col])
				cell.setCellStyle(headerCellStyle)
			}
			
			// CellStyle for Age
			val ageCellStyle = workbook.createCellStyle()
			ageCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"))
			
			var rowIdx = 1
			for (customer in customers) {
				val row = sheet.createRow(rowIdx++)
				row.createCell(0).setCellValue(customer.id.toString())
				row.createCell(1).setCellValue(customer.name)
				row.createCell(2).setCellValue(customer.address)
				val ageCell = row.createCell(3)
				ageCell.setCellValue(customer.age.toDouble())
				ageCell.setCellStyle(ageCellStyle)
			}
			
			var out = ByteArrayOutputStream()	
			workbook.write(out)
			workbook.close()
			
			return ByteArrayInputStream(out.toByteArray());
		}
	}
}

package com.grokonez.kotlin.excel.util

import java.io.ByteArrayInputStream;

import java.io.ByteArrayOutputStream;

import java.io.IOException;

import org.slf4j.Logger

import org.slf4j.LoggerFactory

import com.grokonez.kotlin.excel.model.Customer

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;

class ExcelGenerator {

companion object {

fun customerPDFReport(customers: List<Customer> ) : ByteArrayInputStream {

val COLUMNs = arrayOf<String>("Id", "Name", "Address", "Age")

val workbook = XSSFWorkbook()

val createHelper = workbook.getCreationHelper()

val sheet = workbook.createSheet("Customers")

val headerFont = workbook.createFont()

headerFont.setBold(true)

headerFont.setColor(IndexedColors.BLUE.getIndex())

val headerCellStyle = workbook.createCellStyle()

headerCellStyle.setFont(headerFont)

// Row for Header

val headerRow = sheet.createRow(0)

// Header

for (col in COLUMNs.indices) {

val cell = headerRow.createCell(col)

cell.setCellValue(COLUMNs[col])

cell.setCellStyle(headerCellStyle)

}

// CellStyle for Age

val ageCellStyle = workbook.createCellStyle()

ageCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"))

var rowIdx = 1

for (customer in customers) {

val row = sheet.createRow(rowIdx++)

row.createCell(0).setCellValue(customer.id.toString())

row.createCell(1).setCellValue(customer.name)

row.createCell(2).setCellValue(customer.address)

val ageCell = row.createCell(3)

ageCell.setCellValue(customer.age.toDouble())

ageCell.setCellStyle(ageCellStyle)

}

var out = ByteArrayOutputStream()

workbook.write(out)

workbook.close()

return ByteArrayInputStream(out.toByteArray());

}

Implement RestAPI Controller

– CustomerRestAPIs.kt ->

package com.grokonez.kotlin.excel.controller

import java.io.ByteArrayInputStream
import java.io.IOException

import org.springframework.beans.factory.annotation.Autowired
import org.springframework.core.io.InputStreamResource
import org.springframework.http.HttpHeaders
import org.springframework.http.MediaType
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.kotlin.excel.model.Customer
import com.grokonez.kotlin.excel.repository.CustomerRepository
import com.grokonez.kotlin.excel.util.ExcelGenerator

@RestController
@RequestMapping("/api/excel")
class CustomerRestAPIs {
	
    @Autowired
    lateinit var customerRepository: CustomerRepository;
	
    @GetMapping("/customers.xlsx")
    fun customerReport(): ResponseEntity<InputStreamResource> {
        val customers = customerRepository.findAll();

        val bis = ExcelGenerator.customerPDFReport(customers);

        val headers = HttpHeaders();
        headers.add("Content-Disposition", "attachment; filename=customers.xlsx");

        return ResponseEntity
                .ok()
                .headers(headers)
                .body(InputStreamResource(bis));
    }
}

package com.grokonez.kotlin.excel.controller

import java.io.ByteArrayInputStream

import java.io.IOException

import org.springframework.beans.factory.annotation.Autowired

import org.springframework.core.io.InputStreamResource

import org.springframework.http.HttpHeaders

import org.springframework.http.MediaType

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.kotlin.excel.model.Customer

import com.grokonez.kotlin.excel.repository.CustomerRepository

import com.grokonez.kotlin.excel.util.ExcelGenerator

@RestController

@RequestMapping("/api/excel")

class CustomerRestAPIs {

@Autowired

lateinit var customerRepository: CustomerRepository;

@GetMapping("/customers.xlsx")

fun customerReport(): ResponseEntity<InputStreamResource> {

val customers = customerRepository.findAll();

val bis = ExcelGenerator.customerPDFReport(customers);

val headers = HttpHeaders();

headers.add("Content-Disposition", "attachment; filename=customers.xlsx");

return ResponseEntity

.ok()

.headers(headers)

.body(InputStreamResource(bis));

}

Implement Download View

– Create ViewController.kt ->

package com.grokonez.kotlin.excel.controller

import org.springframework.stereotype.Controller
import org.springframework.web.bind.annotation.GetMapping

@Controller
public class ViewController {
 
	@GetMapping("/")
	fun home(): String {
		return "home"
	}
}

package com.grokonez.kotlin.excel.controller

import org.springframework.stereotype.Controller

import org.springframework.web.bind.annotation.GetMapping

@Controller

public class ViewController {

@GetMapping("/")

fun home(): String {

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/excel/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/excel/customers.xlsx">Customers .XLSX</a>

</div>

</body>

</html>

Initial Customers

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

package com.grokonez.kotlin.excel

import com.grokonez.kotlin.excel.model.Customer

import org.springframework.beans.factory.annotation.Autowired
import org.springframework.context.annotation.Bean
import org.springframework.boot.CommandLineRunner
import com.grokonez.kotlin.excel.repository.CustomerRepository

import org.springframework.boot.SpringApplication
import org.springframework.boot.autoconfigure.SpringBootApplication
import org.springframework.boot.runApplication

@SpringBootApplication
class KotlinSpringJpaExcelDownloadApplication {
	@Bean
	fun initial(repository: CustomerRepository) = CommandLineRunner {
		repository.saveAll(listOf(Customer("Jack Smith", "Massachusetts", 23), 
									Customer("Adam Johnson", "New York", 27), 
									Customer("Katherin Carter", "Washington DC", 26),
									Customer("Jack London", "Nevada", 33), 
									Customer("Jason Bourne", "California", 36)));
	}
}

fun main(args: Array<String>) {
    SpringApplication.run(KotlinSpringJpaExcelDownloadApplication::class.java, *args)
}

package com.grokonez.kotlin.excel

import com.grokonez.kotlin.excel.model.Customer

import org.springframework.beans.factory.annotation.Autowired

import org.springframework.context.annotation.Bean

import org.springframework.boot.CommandLineRunner

import com.grokonez.kotlin.excel.repository.CustomerRepository

import org.springframework.boot.SpringApplication

import org.springframework.boot.autoconfigure.SpringBootApplication

import org.springframework.boot.runApplication

@SpringBootApplication

class KotlinSpringJpaExcelDownloadApplication {

@Bean

fun initial(repository: CustomerRepository) = CommandLineRunner {

repository.saveAll(listOf(Customer("Jack Smith", "Massachusetts", 23),

Customer("Adam Johnson", "New York", 27),

Customer("Katherin Carter", "Washington DC", 26),

Customer("Jack London", "Nevada", 33),

Customer("Jason Bourne", "California", 36)));

}

fun main(args: Array<String>) {

SpringApplication.run(KotlinSpringJpaExcelDownloadApplication::class.java, *args)

}

Database Configuration

– application.properties ->

spring.datasource.url=jdbc:mysql://localhost:3306/testdb?useSSL=false
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?useSSL=false

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

KotlinSpringJpaExcelDownload

By grokonez | October 25, 2018.

Kotlin Spring Boot RestAPIs – Download Excel File – Apache POI + MySQL

Technologies

Practice

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