Show Navigation

Download an Excel file in Grails App

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

Authors: Sergio del Amo

Grails Version: 3.3.9

1 Training

Grails Training - Developed and delivered by the folks who created and actively maintain the Grails framework!.

2 Getting Started

In this guide, we are going to demonstrate Grails file transfer capabilities by creating an app which downloads an excel file with a list of books.

2.1 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.7 or greater installed with JAVA_HOME configured appropriately

2.2 Solution

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

or

Then, cd into the complete folder which you will find in the root project of the downloaded/cloned project.

3 Writing the App

grails create-app example.grails.complete --features=events,geb2,hibernate5

we are using geb2 feature which includes Geb 2 and Gradle WebDriver Binaries plugin. Geb 2 requires JDK 1.8 or greater.

3.1 Books

Create Book POGO:

src/main/groovy/example/grails/Book.groovy
package example.grails

import groovy.transform.CompileStatic
import groovy.transform.EqualsAndHashCode
import groovy.transform.TupleConstructor

@CompileStatic
@EqualsAndHashCode
@TupleConstructor
class Book {
    String isbn
    String name
}

Create a sample service which fetches several books:

grails-app/services/example/grails/BookService.groovy
package example.grails

import groovy.transform.CompileStatic

@CompileStatic
class BookService {

    List<Book> findAll() {
        [
                new Book("1491950358", "Building Microservices"),
                new Book("1680502395", "Release It!"),
                new Book("0321601912", "Continuous Delivery:"),
        ]
    }
}

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

build.gradle
dependencies {
    ...
    ..
    .
    compile 'builders.dsl:spreadsheet-builder-poi:2.0.0.RC1'
    compile 'builders.dsl:spreadsheet-builder-groovy:2.0.0.RC1'
}

3.3 Excel Creation

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

src/main/groovy/example/grails/BookExcelStylesheet.groovy
package example.grails

import builders.dsl.spreadsheet.api.FontStyle
import builders.dsl.spreadsheet.builder.api.CanDefineStyle
import builders.dsl.spreadsheet.builder.api.Stylesheet
import groovy.transform.CompileStatic

@CompileStatic
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 service which generates the excel file.

grails-app/services/example/grails/BookExcelService.groovy
package example.grails

import builders.dsl.spreadsheet.builder.poi.PoiSpreadsheetBuilder
import groovy.transform.CompileStatic

@CompileStatic
class BookExcelService {
    public static final String SHEET_NAME = "Books"
    public static final String HEADER_ISBN = "Isbn"
    public static final String HEADER_NAME = "Name"
    public static final String EXCEL_FILE_SUFIX = ".xlsx"
    public static final String EXCEL_FILE_PREFIX = "books"
    public static final String EXCEL_FILENAME = EXCEL_FILE_PREFIX + EXCEL_FILE_SUFIX

    void exportExcelFromBooks(OutputStream outs, List<Book> bookList) {
        File file = File.createTempFile(EXCEL_FILE_PREFIX, EXCEL_FILE_SUFIX)
        PoiSpreadsheetBuilder.create(outs).build {
            apply BookExcelStylesheet
            sheet(SHEET_NAME) { s ->
                row {
                    [HEADER_ISBN, HEADER_NAME].each { header ->
                        cell {
                            value header
                            style BookExcelStylesheet.STYLE_HEADER
                        }
                    }
                }
                bookList.each { book ->
                    row {
                        cell(book.isbn)
                        cell(book.name)
                    }
                }
            }
        }
        file
    }
}

3.4 Controller

Create a controller:

grails-app/controllers/example/grails/ExcelController.groovy
package example.grails

import grails.config.Config
import grails.core.support.GrailsConfigurationAware
import groovy.transform.CompileStatic

import static org.springframework.http.HttpStatus.OK

@CompileStatic
class ExcelController implements GrailsConfigurationAware { (1)

    BookService bookService
    BookExcelService bookExcelService

    String xlsxMimeType
    String encoding

    @Override
    void setConfiguration(Config co) {  (1)
        xlsxMimeType = co.getProperty('grails.mime.types.xlsxMimeType',
                String,
                'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        encoding = co.getProperty('grails.converters.encoding', String, 'UTF-8')
    }

    def index() {
        response.status = OK.value() (2)
        response.setHeader "Content-disposition", "attachment; filename=${BookExcelService.EXCEL_FILENAME}" (3)
        response.contentType = "${xlsxMimeType};charset=${encoding}" (4)
        OutputStream outs = response.outputStream
        bookExcelService.exportExcelFromBooks(outs, bookService.findAll()) (5)
        outs.flush()
        outs.close()
    }
}
1 Implement grails.core.support.GrailsConfigurationAware to configure mime types and encoding configuration.
2 A controller method can access the response object which is an instace of Servlet API’s HttpServletResponse class
3 Set Content-Disposition to indicate the file should be downloaded.
4 Set the download Content-Type.
5 Write excel file to output stream, flush and close it.

By default, a Grails application created from scratch contains a link to every controller registered in the application. We will test that clicking that links downloads an Excel file.

home

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

We use also, Geb; a browser automation solution.

We told Grails CLI to use geb2 feature when we created the app in Writing the App section.

Grails geb2 feature includes the necessary Geb dependencies:

build.gradle
    testCompile "org.grails.plugins:geb"
    testCompile "org.seleniumhq.selenium:selenium-remote-driver:3.6.0"
    testCompile "org.seleniumhq.selenium:selenium-api:3.6.0"
    testCompile "org.seleniumhq.selenium:selenium-support:3.6.0"
    testRuntime "org.seleniumhq.selenium:selenium-chrome-driver:3.6.0"
    testRuntime "org.seleniumhq.selenium:selenium-firefox-driver:3.6.0"

Grails geb2 feature generates a src/integration-test/resources/GebConfig.groovy file to configure different environments for Geb. Modify it to configure some chrome options to control the download path.

src/integration-test/resources/GebConfig.groovy
import org.openqa.selenium.chrome.ChromeDriver
import org.openqa.selenium.chrome.ChromeOptions
import org.openqa.selenium.firefox.FirefoxDriver

ChromeOptions options = new ChromeOptions()
if ( System.getProperty('download.folder') ) {
    options.setExperimentalOption("prefs", [
            "profile.default_content_settings.popups":  0, (1)
            "download.default_directory": System.getProperty('download.folder') (2)
    ])
}

environments {

    // run via “./gradlew -Dgeb.env=chrome iT”
    chrome {
        driver = { new ChromeDriver(options) }
    }

    // run via “./gradlew -Dgeb.env=chromeHeadless iT”
    chromeHeadless {
        driver = {
            options.addArguments('headless')
            new ChromeDriver(options)
        }
    }

    // run via “./gradlew -Dgeb.env=firefox iT”
    firefox {
        driver = { new FirefoxDriver() }
    }
}
1 Disable confirmation popups
2 Configure the download folder

Geb uses the Page concept pattern - The Page Object Pattern gives us a common sense way to model content in a reusable and maintainable way. Create a Geb Page to encapsulate the Excel link:

src/integration-test/groovy/example/grails/HomePage.groovy
package example.grails

import geb.Page

class HomePage extends Page {

    static at = { title == 'Welcome to Grails' }

    static url = '/'

    static content = {
        excelLink { $('a', text: contains('Excel'), 0) }
    }

    void downloadExcel() {
        excelLink.click()
    }
}

geb2 feature installs also webdriver-binaries Gradle plugin; a plugin that downloads and caches WebDriver binaries specific to the OS the build runs on.

build.gradle
buildscript {
    repositories {
...
..
    }
    dependencies {
        classpath "com.bertramlabs.plugins:asset-pipeline-gradle:2.15.1"
    }
}

apply plugin:"com.energizedwork.webdriver-binaries"

dependencies {
...
..
.
}

webdriverBinaries {
    chromedriver '2.45'
    geckodriver '0.23.0'
}

tasks.withType(Test) {
    systemProperty "geb.env", System.getProperty('geb.env') (1)
    systemProperty "download.folder", System.getProperty('download.folder') (2)
    systemProperty "geb.build.reportsDir", reporting.file("geb/integrationTest")
}
1 Pass system property geb.env to the tests.
2 Pass system property download.folder to the tests.

Create a test which verifies the Excel file is downloaded and the content matches our expectations.

src/integration-test/groovy/example/grails/DownloadExcelSpec.groovy
package example.grails

import builders.dsl.spreadsheet.query.api.SpreadsheetCriteria
import builders.dsl.spreadsheet.query.api.SpreadsheetCriteriaResult
import builders.dsl.spreadsheet.query.poi.PoiSpreadsheetCriteria
import geb.spock.GebSpec
import grails.testing.mixin.integration.Integration
import spock.lang.IgnoreIf
import spock.util.concurrent.PollingConditions

@Integration
class DownloadExcelSpec extends GebSpec {

    @IgnoreIf({ !sys['download.folder'] || sys['geb.env'] != 'chrome' })
    def "books can be downloaded as an excel file"() {
        given:
        PollingConditions conditions = new PollingConditions(timeout: 5)

        when:
        browser.to HomePage

        then:
        browser.at HomePage

        when: 'clicking excel button'
        String expectedPath = System.getProperty('download.folder') + "/" + BookExcelService.EXCEL_FILENAME
        File outputFile = new File(expectedPath)
        browser.page(HomePage).downloadExcel()

        then: 'an excel file is downloaded'
        conditions.eventually { outputFile.exists() }

        when: 'if we search for a row with a particular value (Building Microservices)'
        SpreadsheetCriteria query = PoiSpreadsheetCriteria.FACTORY.forFile(outputFile)
        SpreadsheetCriteriaResult result = query.query {
            sheet(BookExcelService.SHEET_NAME) {
                row {
                    cell {
                        value 'Building Microservices'
                    }
                }
            }
        }

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

        cleanup:
        outputFile?.delete()
    }
}

To run the tests:

$ ./gradlew -Dgeb.env=chrome -Ddownload.folder=/Users/sdelamo/Downloads integrationTest
$ open build/reports/tests/test/index.html

4 Running the app

To run the application use the ./gradlew bootRun command which will start the application on port 8080.

5 Help with Grails

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

OCI is Home to Grails

Meet the Team