Groovy / Gradle

Download an Excel file in a Micronaut application

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

Sergio del Amo

On this guide

In this section

Getting Started

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

Set up IntelliJ IDEA to develop Micronaut applications.

What you will need

To complete this guide, you will need the following:

Some time on your hands
A decent text editor or IDE (e.g. IntelliJ IDEA)
JDK 21 or greater installed with JAVA_HOME configured appropriately

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.

Download and unzip the source

Writing the Application

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

mn create-app example.micronaut.micronautguide --build=gradle --lang=groovy

Note	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.

Writing the App

Books

Create Book POJO:

groovy/src/main/groovy/example/micronaut/Book.groovy

package example.micronaut

import io.micronaut.core.annotation.NonNull
import groovy.transform.EqualsAndHashCode
import io.micronaut.core.annotation.Introspected
import jakarta.validation.constraints.NotBlank

@EqualsAndHashCode
@Introspected
class Book {
    @NonNull
    @NotBlank
    private final String isbn

    @NonNull
    @NotBlank
    private final String name

    Book(@NonNull @NotBlank String isbn, @NonNull @NotBlank String name) {
        this.isbn = isbn
        this.name = name
    }

    @NonNull
    String getIsbn() {
        return isbn
    }

    @NonNull
    String getName() {
        return name
    }
}

Create an interface to encapsulate Book retrieval.

groovy/src/main/groovy/example/micronaut/BookRepository.groovy

package example.micronaut

import io.micronaut.core.annotation.NonNull
import io.micronaut.context.annotation.DefaultImplementation

@DefaultImplementation(BookRepositoryImpl)
interface BookRepository {

    @NonNull
    List<Book> findAll()
}

Create a bean which implements the previous interface:

groovy/src/main/groovy/example/micronaut/BookRepositoryImpl.groovy

package example.micronaut

import io.micronaut.core.annotation.NonNull
import jakarta.inject.Singleton

@Singleton // 
class BookRepositoryImpl implements BookRepository {
    @NonNull
    @Override
    List<Book> findAll() {
        [
                new Book("1491950358", "Building Microservices"),
                new Book("1680502395", "Release It!"),
                new Book("0321601912", "Continuous Delivery:")
        ]
    }
}

1	Use `jakarta.inject.Singleton` to designate a class as a singleton.

Spreadsheet Builder

Add a dependency to Spreadsheet builder

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

dependency:spreadsheet-builder-poi:2.2.1[groupId=builders.dsl]

Excel Creation

Create an interface to encapsulate Excel generation:

groovy/src/main/groovy/example/micronaut/BookExcelService.groovy

package example.micronaut

import io.micronaut.core.annotation.NonNull
import io.micronaut.context.annotation.DefaultImplementation
import io.micronaut.http.server.types.files.SystemFile
import jakarta.validation.Valid
import jakarta.validation.constraints.NotNull

@DefaultImplementation(BookExcelServiceImpl)
interface BookExcelService {
    String SHEET_NAME = "Books"
    String HEADER_ISBN = "Isbn"
    String HEADER_NAME = "Name"
    String HEADER_EXCEL_FILE_SUFIX = ".xlsx"
    String HEADER_EXCEL_FILE_PREFIX = "books"
    String HEADER_EXCEL_FILENAME = HEADER_EXCEL_FILE_PREFIX + HEADER_EXCEL_FILE_SUFIX;

    @NonNull
    SystemFile excelFileFromBooks(@NonNull @NotNull List<@Valid Book> bookList) // 
}

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.

groovy/src/main/groovy/example/micronaut/BookExcelStylesheet.groovy

package example.micronaut

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

class BookExcelStylesheet implements Stylesheet {
    public static final String STYLE_HEADER = "header"

    @Override
    void declareStyles(CanDefineStyle stylable) {
        stylable.style(STYLE_HEADER, st -> {
            st.font(f -> f.style(FontStyle.BOLD))
        })
    }
}

Create a bean which generates the Excel file.

groovy/src/main/groovy/example/micronaut/BookExcelServiceImpl.groovy

package example.micronaut

import builders.dsl.spreadsheet.builder.poi.PoiSpreadsheetBuilder
import io.micronaut.core.annotation.NonNull
import io.micronaut.http.HttpStatus
import io.micronaut.http.exceptions.HttpStatusException
import io.micronaut.http.server.types.files.SystemFile
import org.slf4j.Logger
import org.slf4j.LoggerFactory
import jakarta.inject.Singleton
import jakarta.validation.Valid
import jakarta.validation.constraints.NotNull
import java.util.stream.Stream

@Singleton // 
class BookExcelServiceImpl implements BookExcelService {

    private static final Logger LOG = LoggerFactory.getLogger(BookExcelServiceImpl)

    @NonNull
    SystemFile excelFileFromBooks(@NonNull @NotNull List<@Valid Book> bookList) {
        try {
            File file = File.createTempFile(HEADER_EXCEL_FILE_PREFIX, HEADER_EXCEL_FILE_SUFIX);
            PoiSpreadsheetBuilder.create(file).build(w -> {
                w.apply(BookExcelStylesheet)
                w.sheet(SHEET_NAME, s -> {
                    s.row(r -> Stream.of(HEADER_ISBN, HEADER_NAME)
                            .forEach(header -> r.cell(cd -> {
                                    cd.value(header)
                                    cd.style(BookExcelStylesheet.STYLE_HEADER)
                                })
                            ))
                    bookList.stream()
                            .forEach( book -> s.row(r -> {
                                r.cell(book.isbn)
                                r.cell(book.name)
                            }));
                })
            })
            return new SystemFile(file).attach(HEADER_EXCEL_FILENAME)
        } catch (IOException e) {
            LOG.error("File not found exception raised when generating excel file");
        }
        throw new HttpStatusException(HttpStatus.SERVICE_UNAVAILABLE, "error generating excel file")
    }
}

1	Use `jakarta.inject.Singleton` to designate a class as a singleton.

Controller

Views

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

build.gradle

implementation("io.micronaut.views:micronaut-views-thymeleaf")

Create a controller:

groovy/src/main/groovy/example/micronaut/HomeController.groovy

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 // 
class HomeController {

    private final BookRepository bookRepository
    private final BookExcelService bookExcelService

    HomeController(BookRepository bookRepository,  // 
                   BookExcelService bookExcelService) {
        this.bookRepository = bookRepository
        this.bookExcelService = bookExcelService
    }

    @View("index") // 
    @Get
    Map<String, String> index() {
        [:]
    }

    @Produces(value = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    @Get("/excel") // 
    SystemFile excel() { // 
        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>

Tests

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

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

groovy/src/test/groovy/example/micronaut/DownloadExcelSpec.groovy

package example.micronaut

import builders.dsl.spreadsheet.query.api.SpreadsheetCriteria
import builders.dsl.spreadsheet.query.api.SpreadsheetCriteriaResult
import builders.dsl.spreadsheet.query.poi.PoiSpreadsheetCriteria
import io.micronaut.http.HttpRequest
import io.micronaut.http.HttpStatus
import spock.lang.Specification
import io.micronaut.test.extensions.spock.annotation.MicronautTest
import io.micronaut.http.client.HttpClient
import io.micronaut.http.client.annotation.Client
import jakarta.inject.Inject
import io.micronaut.http.HttpResponse

@MicronautTest // 
class DownloadExcelSpec extends Specification {

    @Inject
    @Client("/")
    HttpClient client // 

    def "books can be downloaded as an excel file"() {
        when:
        HttpResponse<byte[]> response = client.toBlocking().exchange(HttpRequest.GET('/excel')  // 
                .accept("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"), byte[])

        then:
        noExceptionThrown()
        response.status == HttpStatus.OK

        when:
        InputStream inputStream = new ByteArrayInputStream(response.body())  // 
        SpreadsheetCriteria query = PoiSpreadsheetCriteria.FACTORY.forStream(inputStream)
        SpreadsheetCriteriaResult result = query.query( { workbookCriterion ->
            workbookCriterion.sheet(BookExcelService.SHEET_NAME, { sheetCriterion ->
                sheetCriterion.row({ rowCriterion ->
                    rowCriterion.cell({ cellCriterion ->
                        cellCriterion.value('Building Microservices')
                    })
                })
            })
        })

        then: 'a row is found'
        result.cells.size() == 1
    }
}

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.

Testing the Application

To run the tests:

./gradlew test

Then open build/reports/tests/test/index.html in a browser to see the results.

Running the Application

To run the application, use the ./gradlew run command, which starts the application on port 8080.

Next Steps

Read more about Micronaut File Transfers support.

Help with the Micronaut Framework

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

License

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