Generating Excel from Grails

Most developers do their best to avoid working in Excel, but it is often necessary to produce reports or other output in Excel format.  Often times we resort to simple CSV files, or raw data put into an Excel format.  While this handles most basic cases, it’s not always enough.   If you want to have formatting, formulas, merging etc, you are going to need to use an Excel specific library.

There are two main libraries for creating Excel documents from Java/Groovy; POI and JXL.  After some research, I came to the conclusion that JXL is more up-to-date and powerful.  It is a Java library though.  As such, it is strongly typed with a lot of extra syntax that those of us developing in Groovy like to avoid.  To get around this, I created the Grails JXL plugin.

The plugin is a wrapper for the library, but it also adds some nice builder-like syntax to create Excel documents.  For example, to create a workbook with a single worksheet and a few cells you can write:

workbook('/path/to/test.xls') {
    sheet('SheetName') {
        cell(0,0,'Column 1').bold().center()
        cell(1,0,'Column 2').bold().center()
        cell(0,1,'Value 1').left()
        cell(1,1,'Value 2').left()
    }
}

Or if you prefer, you can add the data as a map

workbook('/path/to/test.xls') {
    sheet('SheetName') {
        addData([
            ['Column 1','Column 2'],
            ['Value 1','Value 2']
       ])
    }
    (0..1).each { cell(it,0).bold().center() } 
    (0..1).each { cell(it,1).left() }
}

To get this builder syntax, you simply have to use the Mixin grails.plugin.jxl.ExcelBuilder.

    @Mixin(ExcelBuilder)
    class MyBuilder {

Notice that columns and rows are 0 indexed, and that the cell method can be used to set the cell value when one is provided, or get the current cell when no value is provided.  In either case you can use provided methods to do formatting.

The plugin provides many built in methods for formatting, such as bold(), italic(), thinBorder(), dottedBorder() etc.  It also give access to all of the JXL functionality, by allowing you to set properties of WritableFont and WritableFormat directly on the cell, such as

    cell(0,0,"foo").pointSize = 16

The plugin also allows you to create Excel formulas, as in

    cell(3,6, formula.sum(formula.range(3,0,3,5)))

which generates a cell with the formula =SUM(A4:F4). The formula object supplies a range function to create the Excel formatted range. All other functions dynamically create the Excel function with the same name; in this case sum.

The Grails JXL plugin takes advantage of a great library and Groovy’s dynamic nature to give a more convenient way to generate formatted Excel documents.  Feedback is very welcome, as this plugin is still in it’s early stages. For more detailed information, check out the wiki on github.

Posted by Shaun Jurgemeyer on Thursday, January 12th, 2012 at 10:16 am

6 Responses to “Generating Excel from Grails”

1. aeischeid says:

   January 12, 2012 at 11:49 am

   Looks pretty good!

   I wrote something to handle similar functionality using POI. never got around to releasing it as a plugin though. https://github.com/aeischeid/grails-spreadsheetJuicer-plugin

   Curious what led you to “the conclusion that JXL is more up-to-date and powerful” because I remember coming to the opposite conclusion, but don’t really remember why.
2. Shaun Jurgemeyer says:

   January 12, 2012 at 12:38 pm

   After reviewing the POI documentation I have to retract that statement. It does appear that POI is more up to date with Office 2007 formats. Chalk it up to more familiarity with JXL.
3. Leo says:

   January 16, 2012 at 5:52 am

   Thanks for sharing this!
   Currently JExcel looks to be much more outdated comparing to POI (the last release of JExcel happened back in 2009 while POI development is quite active and a new 3.8 version is about to be published).
4. An Army of Solipsists » Blog Archive » This Week in Grails (2012-02) says:

   January 17, 2012 at 10:40 am

   [...] Generating Excel from Grails [...]
5. GroovyMag - the magazine for Groovy and Grails developers about Groovy programming and Grails development says:

   January 17, 2012 at 11:49 am

   [...] Generating Excel From Grails, by Shaun Jurgemeyer at Object Partners [...]
6. » Blog Archive says:

   January 20, 2012 at 7:20 am

   [...] Generating Excel from Grails [...]