CODEWITHSUNDEEP
excelgrailsgroovyexportexport-to-excel
randomizertech
randomizertech

Reputation: 2329

Grails: How do I export a grails list to Microsoft Excel?

I have a list with information and I want to export it to Excel. How do I do it?

Is the "Export Plugin" any good? I think I saw one a while ago to export files to Excel but I can't find it anymore.

Upvotes: 3

Views: 12109

Answers (3)

mathifonseca
mathifonseca

Reputation: 1483

I've been using the JXL plugin for Grails for a while and it works perfectly.

It even has an option to write the Excel file to the response, so that the user can directly download the file using my REST service.

The link is: http://grails.org/plugin/jxl

Here is an example of how simple it is to create workbooks:

new ExcelBuilder().workbook('/path/to/test.xls') {
    sheet('SheetName') {
        cell(0,0,'DEF')
    }
}

You can find more information here.

Upvotes: 0

EricWerk
EricWerk

Reputation: 167

The Grails Export Plugin is excellent at what it does: export a list of domain objects (or query results) onto a single sheet (or page), in a number of formats -- including Excel. The ability to name columns, indicate width, transform your data on-the-fly is great -- I've used it in several projects.

if( params.format && params.format != "html"){
    response.contentType = grailsApplication.config.grails.mime.types[params.format]
    response.setHeader("Content-disposition", "attachment; filename=Docs_${new Date().format('yyyy-MM-dd')}.${params.extension}")

    List fields = ["documentNo", "modifiedBy", "modifiedDate"]
    Map labels = ["documentNo": 'Document No', "modifiedBy":'Modified by', "modifiedDate":'Last modified']

    def fullDocId = { domain, value ->
        return domain.fullDocId()
    }
    def formatDate = { domain, value ->
        return value.format('yyyy-MM-dd HH-mm')
    }

    Map formatters = [documentNo:fullDocId, modifiedDate:formatDate]
    Map parameters = [title: "${query}", "column.widths": [20, 15, 15]]

    exportService.export(params.format, response.outputStream, Document.list(), fields, labels, formatters, parameters)
}

However, I've also used JExcel as suggested by Rob, when I wanted something other than a simple list. The right tool for the job, and all that. :-)

Upvotes: 1

Rob Hruska
Rob Hruska

Reputation: 120456

If you want actual Excel documents (rather than just CSV files), I've used the JExcel library with some success. Here's a quickly-written example that could probably be Groovy-fied a little bit.

Edit: Updated my example to do this in a controller. Architecturally it would make more sense to split this up a bit, but this is just for example's sake.

import jxl.*
import jxl.write.*

class SomeController {

    def report = {
        def file = createReport(MyDomain.list())

        response.setHeader('Content-disposition', 'attachment;filename=Report.xls')
        response.setHeader('Content-length', "${file.size()}")

        OutputStream out = new BufferedOutputStream(response.outputStream)

        try {
            out.write(file.bytes)

        } finally {
            out.close()
            return false
        }
    }

    private File createReport(def list) {
        WorkbookSettings workbookSettings = new WorkbookSettings()
        workbookSettings.locale = Locale.default

        def file = File.createTempFile('myExcelDocument', '.xls')
        file.deleteOnExit()

        WritableWorkbook workbook = Workbook.createWorkbook(file, workbookSettings)

        WritableFont font = new WritableFont(WritableFont.ARIAL, 12)
        WritableCellFormat format = new WritableCellFormat(font)

        def row = 0
        WritableSheet sheet = workbook.createSheet('MySheet', 0)

        list.each {
            // if list contains objects with 'foo' and 'bar' properties, this will
            // output one row per list item, with column A containing foo and column
            // B containing bar
            sheet.addCell(new Label(0, row, it.foo, format))
            sheet.addCell(new Label(1, row++, it.bar, format))
        }
    }
}

Using this library lets you do things like formatting, using multiple worksheets, etc.

Upvotes: 6

Related Questions