Table of Contents

Download an Excel file in a Micronaut Application

Learn how to download an Excel file with the Micronaut framework and Spreadsheet Builder library.

Authors: Sergio del Amo

Micronaut Version: 4.6.3

1. Getting Started

In this guide, we will demonstrate Micronaut file transfer capabilities by creating an application which downloads an Excel file containing a list of books.

Setup IntelliJ IDEA to develop Micronaut Applications.

2. What you will need

To complete this guide, you will need the following:

3. Solution

We recommend that you follow the instructions in the next sections and create the application step by step. However, you can go right to the completed example.

4. Writing the Application

Create an application using the Micronaut Command Line Interface or with Micronaut Launch.

mn create-app example.micronaut.micronautguide --build=maven --lang=kotlin
If you don’t specify the --build argument, Gradle with the Kotlin DSL is used as the build tool.
If you don’t specify the --lang argument, Java is used as the language.
If you don’t specify the --test argument, JUnit is used for Java and Kotlin, and Spock is used for Groovy.

The previous command creates a Micronaut application with the default package example.micronaut in a directory named micronautguide.

Setup IntelliJ IDEA to develop Micronaut Applications.

5. Writing the App

5.1. Books

Create Book POJO:

src/main/kotlin/example/micronaut/Book.kt
package example.micronaut

import io.micronaut.core.annotation.Introspected
import jakarta.validation.constraints.NotBlank

@Introspected
data class Book(val isbn: @NotBlank String,
                val name: @NotBlank String)

Create an interface to encapsulate Book retrieval.

src/main/kotlin/example/micronaut/BookRepository.kt
package example.micronaut

import io.micronaut.context.annotation.DefaultImplementation

@DefaultImplementation(BookRepositoryImpl::class)
interface BookRepository {

    fun findAll(): List<Book>
}

Create a bean which implements the previous interface:

src/main/kotlin/example/micronaut/BookRepositoryImpl.kt
package example.micronaut

import jakarta.inject.Singleton

@Singleton (1)
class BookRepositoryImpl : BookRepository {

    override fun findAll() = listOf(
            Book("1491950358", "Building Microservices"),
            Book("1680502395", "Release It!"),
            Book("0321601912", "Continuous Delivery:"))
}
1 Use jakarta.inject.Singleton to designate a class as a singleton.

5.2. Spreadsheet Builder

Add a dependency to Spreadsheet builder

Spreadsheet builder provides convenient way how to read and create MS Excel OfficeOpenXML Documents (XSLX) focus not only on content side but also on easy styling.

pom.xml
<dependency>
    <groupId>builders.dsl</groupId>
    <artifactId>spreadsheet-builder-poi:2.2.1</artifactId>
    <scope>compile</scope>
</dependency>

5.3. Excel Creation

Create a interface to encapsulate Excel generation:

src/main/kotlin/example/micronaut/BookExcelService.kt
package example.micronaut

import io.micronaut.context.annotation.DefaultImplementation
import io.micronaut.http.server.types.files.SystemFile

@DefaultImplementation(BookExcelServiceImpl::class)
interface BookExcelService {

    fun excelFileFromBooks(bookList: List<Book>): SystemFile (1)

    companion object {
        const val SHEET_NAME = "Books"
        const val HEADER_ISBN = "Isbn"
        const val HEADER_NAME = "Name"
        const val HEADER_EXCEL_FILE_SUFIX = ".xlsx"
        const val HEADER_EXCEL_FILE_PREFIX = "books"
        const val HEADER_EXCEL_FILENAME = HEADER_EXCEL_FILE_PREFIX + HEADER_EXCEL_FILE_SUFIX
    }
}
1 SystemFile is specified as the return type of a route execution to indicate the given file should be downloaded by the client instead of displayed.

Externalize your styles configuration into a class implementing builders.dsl.spreadsheet.builder.api.Stylesheet interface to maximize code reuse.

src/main/kotlin/example/micronaut/BookExcelStylesheet.kt
package example.micronaut

import builders.dsl.spreadsheet.api.FontStyle
import builders.dsl.spreadsheet.builder.api.CanDefineStyle
import builders.dsl.spreadsheet.builder.api.CellStyleDefinition
import builders.dsl.spreadsheet.builder.api.FontDefinition
import builders.dsl.spreadsheet.builder.api.Stylesheet

class BookExcelStylesheet : Stylesheet {

    override fun declareStyles(stylable: CanDefineStyle) {
        stylable.style(STYLE_HEADER) { st: CellStyleDefinition ->
            st.font { f: FontDefinition -> f.style(FontStyle.BOLD) }
        }
    }

    companion object {
        const val STYLE_HEADER = "header"
    }
}

Create a bean which generates the Excel file.

src/main/kotlin/example/micronaut/BookExcelServiceImpl.kt
package example.micronaut

import builders.dsl.spreadsheet.builder.api.CellDefinition
import builders.dsl.spreadsheet.builder.api.RowDefinition
import builders.dsl.spreadsheet.builder.api.SheetDefinition
import builders.dsl.spreadsheet.builder.api.WorkbookDefinition
import builders.dsl.spreadsheet.builder.poi.PoiSpreadsheetBuilder
import example.micronaut.BookExcelService.Companion.HEADER_EXCEL_FILE_PREFIX
import example.micronaut.BookExcelService.Companion.HEADER_EXCEL_FILE_SUFIX
import example.micronaut.BookExcelService.Companion.HEADER_ISBN
import example.micronaut.BookExcelService.Companion.HEADER_NAME
import example.micronaut.BookExcelService.Companion.SHEET_NAME
import example.micronaut.BookExcelServiceImpl
import io.micronaut.http.HttpStatus
import io.micronaut.http.exceptions.HttpStatusException
import io.micronaut.http.server.types.files.SystemFile
import jakarta.inject.Singleton
import org.slf4j.LoggerFactory
import java.io.File
import java.io.IOException
import java.util.stream.Stream
import jakarta.validation.Valid

@Singleton (1)
open class BookExcelServiceImpl : BookExcelService {

    override fun excelFileFromBooks(bookList: List<@Valid Book>): SystemFile {
        try {
            val file = File.createTempFile(HEADER_EXCEL_FILE_PREFIX, HEADER_EXCEL_FILE_SUFIX)
            PoiSpreadsheetBuilder.create(file).build { w: WorkbookDefinition ->
                w.apply(BookExcelStylesheet::class.java)
                w.sheet(SHEET_NAME) { s: SheetDefinition ->
                    s.row { r: RowDefinition ->
                        Stream.of(HEADER_ISBN, HEADER_NAME)
                                .forEach { header: String? ->
                                    r.cell { cd: CellDefinition ->
                                        cd.value(header)
                                        cd.style(BookExcelStylesheet.STYLE_HEADER)
                                    }
                                }
                    }
                    for ((isbn, name) in bookList) {
                        s.row { r: RowDefinition ->
                            r.cell(isbn)
                            r.cell(name)
                        }
                    }
                }
            }
            return SystemFile(file).attach(BookExcelService.HEADER_EXCEL_FILENAME)
        } catch (e: IOException) {
            LOG.error("File not found exception raised when generating excel file")
        }
        throw HttpStatusException(HttpStatus.SERVICE_UNAVAILABLE, "error generating excel file")
    }

    companion object {
        private val LOG = LoggerFactory.getLogger(BookExcelServiceImpl::class.java)
    }
}
1 Use jakarta.inject.Singleton to designate a class as a singleton.

5.4. Controller

6. Views

To use the Thymeleaf Java template engine to render views in a Micronaut application, add the following dependency on your classpath.

pom.xml
<dependency>
    <groupId>io.micronaut.views</groupId>
    <artifactId>micronaut-views-thymeleaf</artifactId>
    <scope>compile</scope>
</dependency>

Create a controller:

src/main/kotlin/example/micronaut/HomeController.kt
package example.micronaut

import io.micronaut.http.annotation.Controller
import io.micronaut.http.annotation.Get
import io.micronaut.http.annotation.Produces
import io.micronaut.http.server.types.files.SystemFile
import io.micronaut.views.View

@Controller (1)
class HomeController(private val bookRepository: BookRepository,  (2)
                     private val bookExcelService: BookExcelService) {

    @View("index") (3)
    @Get
    fun index(): Map<String, String> = HashMap()

    @Produces(value = ["application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"])
    @Get("/excel") (4)
    fun excel(): SystemFile = (5)
            bookExcelService.excelFileFromBooks(bookRepository.findAll())
}
1 The class is defined as a controller with the @Controller annotation mapped to the path /.
2 Constructor injection
3 Use @View annotation to specify which template to use to render the response.
4 You can specify the HTTP verb that a controller action responds to. To respond to a GET request, use io.micronaut.http.annotation.Get
5 SystemFile is specified as the return type of a route execution to indicate the given file should be downloaded by the client instead of displayed.

The previous controller index method renders a simple view with a link to download the Excel file:

src/main/resources/views/index.html
<!DOCTYPE html>
<html>
<head>
    <title>Micronaut</title>
</head>
<body>
<p><a href="/excel">Excel</a></p>
</body>
</html>

6.1. Tests

Often, file transfers remain untested in many applications. In this section, you will see how easy is to test that the file downloads but also that the downloaded file contents match our expectations.

Create a test to verify the Excel file is downloaded and the content matches our expectations.

src/test/kotlin/example/micronaut/DownloadExcelTest.kt
package example.micronaut

import builders.dsl.spreadsheet.query.api.CellCriterion
import builders.dsl.spreadsheet.query.api.RowCriterion
import builders.dsl.spreadsheet.query.api.SheetCriterion
import builders.dsl.spreadsheet.query.api.WorkbookCriterion
import builders.dsl.spreadsheet.query.poi.PoiSpreadsheetCriteria
import io.micronaut.http.HttpRequest
import io.micronaut.http.HttpStatus
import io.micronaut.http.client.HttpClient
import io.micronaut.http.client.annotation.Client
import io.micronaut.test.extensions.junit5.annotation.MicronautTest
import jakarta.inject.Inject
import org.junit.jupiter.api.Assertions.assertEquals
import org.junit.jupiter.api.Test
import java.io.ByteArrayInputStream
import java.io.FileNotFoundException
import java.io.InputStream

@MicronautTest (1)
class DownloadExcelTest(@Client("/") val client: HttpClient) { (2)

    @Test
    @Throws(FileNotFoundException::class)
    fun booksCanBeDownloadedAsAnExcelFile() {
        val request: HttpRequest<*> = HttpRequest.GET<Any>("/excel")
                .accept("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") (3)
        val response = client.toBlocking().exchange(request, ByteArray::class.java)

        assertEquals(HttpStatus.OK, response.status)

        val inputStream: InputStream = ByteArrayInputStream(response.body()) (4)
        val query = PoiSpreadsheetCriteria.FACTORY.forStream(inputStream)
        val result = query.query { workbookCriterion: WorkbookCriterion ->
            workbookCriterion.sheet(BookExcelService.SHEET_NAME) { sheetCriterion: SheetCriterion ->
                sheetCriterion.row { rowCriterion: RowCriterion ->
                    rowCriterion.cell { cellCriterion: CellCriterion -> cellCriterion.value("Building Microservices") }
                }
            }
        }

        assertEquals(1, result.cells.size)
    }
}
1 Annotate the class with @MicronautTest so the Micronaut framework will initialize the application context and the embedded server. More info.
2 Inject the HttpClient bean and point it to the embedded server.
3 Creating HTTP Requests is easy thanks to the Micronaut framework fluid API.
4 Use .body() to retrieve the excel bytes.

7. Testing the Application

To run the tests:

./mvnw test

8. Running the Application

To run the application, use the ./mvnw mn:run command, which starts the application on port 8080.

9. Next Steps

Read more about Micronaut File Transfers support.

10. Help with the Micronaut Framework

The Micronaut Foundation sponsored the creation of this Guide. A variety of consulting and support services are available.

11. License

All guides are released with an Apache license 2.0 license for the code and a Creative Commons Attribution 4.0 license for the writing and media (images…​).