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

kotlin-spring-boot-restapi-download-excel-file-spring-jpa-mysql-feature-image

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.

Related posts:
– Kotlin – 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

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

Practice

Project Structure ->

kotlin-spring-boot-restapi-download-excel-file-spring-jpa-mysql-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>

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
){}

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

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 ) : ByteArrayInputStream {
			val COLUMNs = arrayOf("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 {
        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"
	}
}

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

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

Run & Check Results

Run the SpringBoot project,

-> MySQL’s records:

kotlin-spring-boot-restapi-download-excel-file-spring-jpa-mysql-mysql-records-schema

-> Excel Downloaded File:

kotlin-spring-boot-restapi-download-excel-file-spring-jpa-mysql-download-view

kotlin-spring-boot-restapi-download-excel-file-spring-jpa-mysql-apache.poi-xlsx-file-results

SourceCode

KotlinSpringJpaExcelDownload



By grokonez | October 25, 2018.

Last updated on April 18, 2021.


Related Posts

Post Tags


Got Something To Say:

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

*