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') {
            ['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.

    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
Android Development for iOS Developers
Android development has greatly improved since the early days. Maybe you tried it out when Android development was done in Eclipse, emulators were slow and buggy, and Java was the required language. Things have changed […]
Add a custom object to your Liquibase diff
Adding a custom object to your liquibase diff is a pretty simple two step process. Create an implementation of DatabaseObject Create an implementation of SnapshotGenerator In my case I wanted to add tracking of Stored […]
Keeping Secrets Out of Terraform State
There are many instances where you will want to create resources via Terraform with secrets that you just don’t want anyone to see. These could be IAM credentials, certificates, RDS DB credentials, etc. One problem […]
Validating Terraform Plans using Open Policy Agent
When developing infrastructure as code using terraform, it can be difficult to test and validate changes without executing the code against a real environment. The feedback loop between writing a line of code and understanding […]