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
Google Professional Machine Learning Engineer Exam 2021
Exam Description A Professional Machine Learning Engineer designs, builds, and productionizes ML models to solve business challenges using Google Cloud technologies and knowledge of proven ML models and techniques. The ML Engineer is proficient in all aspects […]
Designing Kubernetes Controllers
There has been some excellent online discussion lately around Kubernetes controllers, highlighted by an excellent Speakerdeck presentation assembled by Tim Hockin. What I’d like to do in this post is explore some of the implications […]
React Server Components
The React Team recently announced new work they are doing on React Server Components, a new way of rendering React components. The goal is to create smaller bundle sizes, speed up render time, and prevent […]
Jolt custom java transform
Jolt is a JSON to JSON transformation library where the transform is defined in JSON. It’s really good at reorganizing the json data and massaging it into the output JSON you need. Sometimes, you just […]