Friday, March 21, 2008

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.

13 comments:

Anonymous said...

Nice set of posts on using Excel with with groovy. I've been using Apache POI to do some stuff recently but JExcelAPI looks like it might be a little less clunky from a brief peek.

One suggestion on your code; groovy has an eachWithIndex method in addition to the each method. The it works with both maps and lists and could save you a few lines of code.

Here's an example:
[foo: "bar", baz: "qux"].eachWithIndex { k, v, i -> println "$i $k $v" }

Prints:

0 foo bar
1 baz qux

Josh Reed said...

Thanks for the heads up on eachWithIndex. I'm still a bit of a n00b with Groovy and Grails.

Cheers,
Josh

Sufiyan Yasa said...

Another great tutorial.
Im planning to rss this site :)

Josh Reed said...

Thanks, Cinod. I'll try to keep the Grails content coming.

fabiant7t said...

Thanks for posting this solution! I'm using it with a List of Expandos because the export fields don't entirely match the properties of domain class objects. Works like charm!

Christian Sonne Jensen said...

Hi,

I came to almost the exact same solution as you did with downloading Excel, although I made a builder (for fun) to build Excel files.

I'll post it here to share the code for inspiration. There is plenty of room for improvements... ;-)

Pls delete it if you think it takes too much space...

/* CODE BY Christian Sonne Jensen*/
package dk.cooldev.groovy.builders;

import groovy.util.BuilderSupport;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.Map;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelBuilder extends BuilderSupport {

private WritableWorkbook workbook;
private WritableSheet currentSheet;

@Override
protected Object createNode(Object arg0) {
// TODO Auto-generated method stub
return null;
}

@Override
protected Object createNode(Object arg0, Object arg1) {
// TODO Auto-generated method stub
return null;
}

@SuppressWarnings("unchecked")
@Override
protected Object createNode(Object name, Map map) {
if (this.workbook == null) {
OutputStream out = (OutputStream) map.get("outputStream");
try {
workbook = Workbook.createWorkbook(out);
} catch (IOException e) {
e.printStackTrace();
}
return this.workbook;
}

else if (name.equals("sheet")) {
currentSheet = workbook.createSheet((String) map.get("name"), workbook.getNumberOfSheets());
return currentSheet;
}

else if (name.equals("cell")) {
Integer posColumn = (Integer) map.get("c");
Integer posRow = (Integer) map.get("r");
String type = (String) map.get("type");
try {
Object value = map.get("value");
if (value instanceof BigDecimal)
currentSheet.addCell(new Number(posColumn, posRow, ((BigDecimal) value).doubleValue()));
else
currentSheet.addCell(new Label(posColumn, posRow, (String) value));
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
return workbook;
}

@SuppressWarnings("unchecked")
@Override
protected Object createNode(Object arg0, Map arg1, Object arg2) {
// TODO Auto-generated method stub
return null;
}

@Override
protected void setParent(Object arg0, Object arg1) {
// TODO Auto-generated method stub

}

public WritableWorkbook getWorkbook() {
return workbook;
}

}


Then you can use the builder in the grails controller like this:

def export =
{
response.setHeader("Content-disposition", "attachment; filename=user.xls")
response.contentType = "application/vnd.ms-excel"
def users = chosenPPService.getUsersWithProfile(params.env, Integer.parseInt(params.id))

def builder = new ExcelBuilder()
builder.workbook (outputStream: response.outputStream){
sheet(name: "Users with profile with id=${params.id}".toString())
{
cell(c:0, r:0, value: "Username")
cell(c:0, r:1, value: "LogonId(s)")
users.eachWithIndex
{
user, index ->
cell(r:index+1, c:0, value: user.name)
cell(r:index+1, c:1, value: user.logonids.join(",").toString())
}
}
}
builder.workbook.write()
builder.workbook.close()
}

darryl west said...

Thanks for the great post. One thing, it may have been better to use a normal method or closure rather than a static for writeExcel() to enable spring loading.

Anonymous said...

Hi Josh,

Its very help ful for import and export data what the code you provided Thanks,

Is it posible to export data
to already having excel file in some location with formats and styles
without replacing that.

Thanks,

Mallu.

Matthias Bohlen said...

Thanks very much for this post. One little hint: In the code, there is a tiny bug with numbers equal to zero! If the value of the cell is zero, Groovy truth says that it is false so that the code runs into the "else" clause. So, please replace the statement "if (o[k])" by "if (o[k] != null)".

Josh Reed said...

Thanks Matthias, I updated the post with your bug fix.

Cheers,
Josh

johnrellis said...

Here's how to write an Apace POI workbook to the response in case anyone is looking :)

HSSFWorkbook workbook = //create your workbook here
def title = "My_Title.xls"
log.info "Writing Report to output Stream - ${title}"
response.setHeader("Content-disposition", "attachment; filename=${title}")
response.contentType = "application/vnd.ms-excel"
workbook.write(response.outputStream)//send binary data down the wire!
response.outputStream.flush()//no redirect needed

nils said...

thx, i made a little mod for domain objects, notice the star after the domain obj. this means, on every item in the list...


static def writeExcel(out, map) {
// 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, k.toString()))

// write out the value for each object
def r = 1
v.each() {o ->


sheet.addCell(new Label(c, r, o.toString()))


r++
}
c++
}

// close
workbook.write()
workbook.close()
}

def pdf = {

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

def flats = RealEstate.get(params.id).flats
def header = [:]
header."renter" = flats*.renter as List
header."street" = flats*.realEstate.addy.street as List
header."flat Number" = flats*.flatNumber as List


writeExcel(response.outputStream, header)

}

Anonymous said...

Excellent Article!

Thank you