Grails Excel File Download

This is the companion to my previous "Grails Excel File Upload" post. In this post, I'll show how to create and send an Excel spreadsheet from your Grails webapp.

The first thing you'll need to do is go out and grab JExcelAPI if haven't already. JExcelAPI will let you read and write Excel spreadsheets from Java. Drop the jxl.jar in your webapp's lib directory.

In my app, I'm providing Excel download functionality from a couple different controllers, each with a different list of domain objects. To enable the highest degree of re-use, I wrote a generic utility method for serializing an object to a row in an Excel spreadsheet:

 import jxl.*
 import jxl.write.*

...

 static def writeExcel(out, map, objects) {
     // create our workbook and sheet
     def workbook = Workbook.createWorkbook(out)
     def sheet = workbook.createSheet("Requests", 0)
 
     // walk through our map and write out the headers
     def c = 0
     map.each() { k, v ->
         // write out our header
         sheet.addCell(new Label(c, 0, v.toString()))
     
         // write out the value for each object
         def r = 1
         objects.each() { o ->
             if (o[k] != null) {
                 if (o[k] instanceof java.lang.Number) {
                     sheet.addCell(new Number(c, r, o[k]))
                 } else {
                     sheet.addCell(new Label(c, r, o[k].toString()))
                 }
             }
             r++
         }
         c++
     }
 
     // close
     workbook.write()
     workbook.close()
 }

Edit: See Ted's comment on this post about using eachWithIndex() instead of keeping track of the r and c vars yourself.

This method takes an OutputStream, like the ServletOutputStream you can get from response.outputStream; a map of property names and "nice" header titles; and a list of objects to write to the spreadsheet.

Then I call it from my controller with appropriate values:

     def header = [:]
     header.id = "Id"
     header.investigator = "Investigator"
     header.hole = "Hole"
     header.top = "Interval Top (mbsf)"
     header.bottom = "Interval Bottom (mbsf)"
     header.samplesRequested = "Samples Requested"
     header.sampleSpacing = "Sample Spacing (m)"
     header.sampleType = "Volume/Type"
     header.sampleGroup = "Group/Discipline"
     header.notes = "Notes"
     header.status = "Status"
     header.priority = "Priority"
 
     ExcelUtils.writeExcel(response.outputStream, header, SampleRequest.findAllByHole(hole))

The final piece of the puzzle is to make sure you set your content type and content disposition headers before you call the writeExcel() method:

    // set our header and content type
    response.setHeader("Content-disposition", "attachment; filename=${hole}-requests.xls")
    response.contentType = "application/vnd.ms-excel"

The nice part about the writeExcel() method is that it works with just about any list of objects and can easily be customized. For example, in another place in my app, I let the user download all of the sample request domain objects associated with them. To do that, I use nearly the same code:

     // set our header and content type
     response.setHeader("Content-disposition", "attachment; filename=${user}-requests.xls")
     response.contentType = "application/vnd.ms-excel"
 
     // define our header map
     def header = [:]
     header.id = "Id"
     header.hole = "Hole"
     header.top = "Interval Top (mbsf)"
     header.bottom = "Interval Bottom (mbsf)"
     header.samplesRequested = "Samples Requested"
     header.sampleSpacing = "Sample Spacing (m)"
     header.sampleType = "Volume/Type"
     header.sampleGroup = "Group/Discipline"
     header.notes = "Notes"
 
     ExcelUtils.writeExcel(response.outputStream, header, SampleRequest.findAllByUser(user))

I removed a few redundant/unimportant properties from my header map and call the writeExcel() method with the list of SampleRequests associated only with that user.