Download an Excel file in Micronaut App

Learn how to download an excel file with Micronaut and Spreadsheet Builder library.

Authors: Sergio del Amo

Micronaut Version: 2.5.9

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.

If you use Java or Kotlin and IntelliJ IDEA, make sure to enable annotation processing.

annotationprocessorsintellij

2. What you will need

To complete this guide, you will need the following:

  • Some time on your hands

  • A decent text editor or IDE

  • JDK 1.8 or greater installed with JAVA_HOME configured appropriately

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=java
If you don’t specify the --build argument, Gradle is used as the build tool.
If you don’t specify the --lang argument, Java is used as the language.

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

If you use Java or Kotlin and IntelliJ IDEA, make sure to enable annotation processing.

annotationprocessorsintellij

5. Writing the App

5.1. Books

Create Book POJO:

src/main/java/example/micronaut/Book.java
package example.micronaut;

import io.micronaut.core.annotation.NonNull;
import io.micronaut.core.annotation.Introspected;

import javax.validation.constraints.NotBlank;

@Introspected
public class Book {
    @NonNull
    @NotBlank
    private final String isbn;

    @NonNull
    @NotBlank
    private final String name;

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

    @NonNull
    public String getIsbn() {
        return isbn;
    }

    @NonNull
    public String getName() {
        return name;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        Book book = (Book) o;

        if (!isbn.equals(book.isbn)) return false;
        return name.equals(book.name);
    }

    @Override
    public int hashCode() {
        int result = isbn.hashCode();
        result = 31 * result + name.hashCode();
        return result;
    }
}

Create an interface to encapsulate Book retrieval.

src/main/java/example/micronaut/BookRepository.java
package example.micronaut;

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

import java.util.List;

@DefaultImplementation(BookRepositoryImpl.class)
public interface BookRepository {

    @NonNull
    List<Book> findAll();
}

Create a bean which implements the previous interface:

src/main/java/example/micronaut/BookRepositoryImpl.java
package example.micronaut;

import javax.inject.Singleton;
import io.micronaut.core.annotation.NonNull;
import java.util.List;
import java.util.Arrays;

@Singleton (1)
public class BookRepositoryImpl implements BookRepository {
    @NonNull
    @Override
    public List<Book> findAll() {
        Book buildingMicroservices = new Book("1491950358", "Building Microservices");
        Book releaseIt = new Book("1680502395", "Release It!");
        Book cidelivery = new Book("0321601912", "Continuous Delivery:");
        return Arrays.asList(buildingMicroservices, releaseIt, cidelivery);
    }
}
1 To register a Singleton in Micronaut’s application context, annotate your class with javax.inject.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/java/example/micronaut/BookExcelService.java
package example.micronaut;

import java.util.List;

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

import javax.validation.Valid;
import javax.validation.constraints.NotNull;

@DefaultImplementation(BookExcelServiceImpl.class)
public 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)
}
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/java/example/micronaut/BookExcelStylesheet.java
package example.micronaut;

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

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

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

Create a bean which generates the Excel file.

src/main/java/example/micronaut/BookExcelServiceImpl.java
package example.micronaut;

import builders.dsl.spreadsheet.builder.poi.PoiSpreadsheetBuilder;
import io.micronaut.core.annotation.NonNull;
import io.micronaut.core.annotation.Nullable;
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 javax.inject.Singleton;
import javax.validation.Valid;
import javax.validation.constraints.NotNull;
import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.stream.Stream;

@Singleton (1)
public class BookExcelServiceImpl implements BookExcelService {

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

    @NonNull
    public 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.class);
                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.getIsbn());
                                r.cell(book.getName());
                            }));
                });
            });
            return new SystemFile(file).attach(HEADER_EXCEL_FILENAME);
        } catch (IOException e) {
            if (LOG.isErrorEnabled()) {
                LOG.error("File not found exception raised when generating excel file");
            }
        }
        throw new HttpStatusException(HttpStatus.SERVICE_UNAVAILABLE, "error generating excel file");
    }
}
1 To register a Singleton in Micronaut’s application context, annotate your class with javax.inject.Singleton

5.4. Controller

Add Server Side View Rendering and Thymeleaf dependencies:

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

Create a controller:

src/main/java/example/micronaut/HomeController.java
package example.micronaut;

import io.micronaut.http.MediaType;
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;

import java.util.HashMap;
import java.util.Map;

@Controller (1)
public class HomeController {

    protected final BookRepository bookRepository;
    protected final BookExcelService bookExcelService;

    public HomeController(BookRepository bookRepository,  (2)
                          BookExcelService bookExcelService) {
        this.bookRepository = bookRepository;
        this.bookExcelService = bookExcelService;
    }

    @View("index") (3)
    @Get
    public Map<String, String> index() {
        return new HashMap<>();
    }

    @Produces(value = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    @Get("/excel") (4)
    public SystemFile excel() { (5)
        return 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 would you like to render the response against.
4 You can specify the HTTP verb for which a controller’s 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>

5.5. 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/java/example/micronaut/DownloadExcelTest.java
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.HttpResponse;
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 org.junit.jupiter.api.Test;
import javax.inject.Inject;
import java.io.ByteArrayInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import static org.junit.jupiter.api.Assertions.assertEquals;

@MicronautTest (1)
class DownloadExcelTest {

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

    @Test
    public void booksCanBeDownloadedAsAnExcelFile() throws FileNotFoundException {
        HttpRequest<?> request = HttpRequest.GET("/excel")
                .accept("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); (3)
        HttpResponse<byte[]> response = client.toBlocking().exchange(request, byte[].class);

        assertEquals(HttpStatus.OK, response.getStatus());

        InputStream inputStream = new ByteArrayInputStream(response.body()); (4)
        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"))));
        });
        assertEquals(result.getCells().size(), 1);
    }
}
1 Annotate the class with @MicronautTest to let Micronaut starts the embedded server and inject the beans. More info: https://micronaut-projects.github.io/micronaut-test/latest/guide/index.html.
2 Inject the HttpClient bean in the application context.
3 Creating HTTP Requests is easy thanks to Micronaut’s fluid API.
4 Use .body() to retrieve the excel bytes.

6. Testing the Application

To run the tests:

$ ./mvnw test

7. Running the Application

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

8. Next Steps

Read more about File Transfers support inside Micronaut.

9. Help with Micronaut

Object Computing, Inc. (OCI) sponsored the creation of this Guide. A variety of consulting and support services are available.