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.

Grails Excel File Upload

Just like most things in Grails, parsing data from an uploaded Excel file is relatively easy. The first thing I did was go out and grab JExcelAPI. JExcelAPI will let you read and write Excel spreadsheets from Java. It doesn't support all of the advanced features (like charts) of Excel, but for my needs it works well. You can also look at Apache POI for reading and writing more MS Office formats, and there's numerous other options if you're on Windows or want to plop down some money for a commercial library.

After dropping JExcelAPI into my lib directory, I went ahead and created a view to upload the spreadsheet. The form can be as simple or as fancy as you want, just as long as you have a file input field:

<g:form action="upload" method="post" enctype="multipart/form-data">
<label for="file">File:</label>
<input type="file" name="file" id="file"/>
<input class="save" type="submit" value="Upload"/>
</g:form>

Now we need to create the upload method in our controller to parse the Excel file:

def upload = {
 // get our multipart
 MultipartHttpServletRequest mpr = (MultipartHttpServletRequest)request;
 CommonsMultipartFile file = (CommonsMultipartFile) mpr.getFile("file");
  
 // create our workbook
 Workbook workbook = Workbook.getWorkbook(file.inputStream)
 Sheet sheet = workbook.getSheet(0)

 def added = 0;
 def skipped = [];
 for (int r = 3; r < sheet.rows; r++) {
  // get our fields
  def top = sheet.getCell(0, r).contents
 
  def bottom = sheet.getCell(1, r).contents
  if (bottom == "") bottom = null
 
  def number = sheet.getCell(2, r).contents
  if (number == "") number = 1

  def spacing = sheet.getCell(3, r).contents
  if (spacing == "") spacing = 0.0
 
  def type = sheet.getCell(4, r).contents
  def notes = sheet.getCell(5, r).contents
 
  // check that we got a top and a type
  if (top == null || top == "") {
   // do nothing
  } else if ((new SampleRequest(
    "investigator":user,
    "hole":hole,
    "sampleGroup":group,
    "top":top,
    "bottom":bottom,
    "sampleType":type,
    "samplesRequested":number,
    "sampleSpacing":spacing,
    "notes":notes)).save()) {
   added++
  } else {
   skipped += (r + 1)
  }
 }
 workbook.close()

 // generate our flash message
 flash.message = "${added} sample request(s) added."
 if (skipped.size() > 0) {
  flash.message += "  Rows ${skipped.join(', ')} were skipped because they were incomplete or malformed"
 }
 redirect(controller:"home", action:"index")
}

My spreadsheet has a fairly simple format with fixed columns, some being optional and some required, so I hard coded the column indices.

In a future blog post, I'll show how to output an Excel spreadsheet with Grails and JExcelAPI.

Edit: Blogger seems to have eaten some of the code when I updated tags so I just updated it.