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
Contents
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> |
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<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()); } } } |
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)); } } |
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<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 |
Run & Check Results
Run the SpringBoot project,
-> MySQL’s records:
-> Excel Downloaded File:
