Jan 12, 2012

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.

About the Author

Object Partners profile.

One thought on “Generating Excel from Grails

  1. aeischeid says:

    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:

    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:

    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. Pingback: » Blog Archive
Leave a Reply

Your email address will not be published. Required fields are marked *

Related Blog Posts
Why we started using JSON Schema in our internal Helm charts
Helm 3 supports validating the provided chart values against a JSON Schema. While it may be quicker to get started in your chart development without a schema, we found it valuable for a number of […]
Rewriting files in Google Cloud Storage
Rewriting Files in GCP Note: even though this code is in Python, this should be the same idea in JavaScript, Go, etc. I wrote the following to copy a file from one Google Cloud Storage […]
Interpreting Spatial Data in the Age of COVID-19
As 2020 has come to an end, many are eager to leave the mess of COVID-19 behind with the new year and gain a fresh start. Unfortunately, new cases are still soaring across the United […]
Building a Better Mousetrap
Recently, my daughter (age 6) was into building “mousetraps” out of shoe boxes. These were more or less comfortable cardboard mouse houses complete with beds, rooms, everything a mouse could want or need and not […]