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.

Related Blog Posts
A security model for developers
Software security is more important than ever, but developing secure applications is more confusing than ever. TLS, mTLS, RBAC, SAML, OAUTH, OWASP, GDPR, SASL, RSA, JWT, cookie, attack vector, DDoS, firewall, VPN, security groups, exploit, […]
Building Better Data Visualization Experiences: Part 1 of 2
Through direct experience with data scientists, business analysts, lab technicians, as well as other UX professionals, I have found that we need a better understanding of the people who will be using our data visualization products in order to build them. Creating a product utilizing data with the goal of providing insight is fundamentally different from a typical user-centric web experience, although traditional UX process methods can help.
Kafka Schema Evolution With Java Spring Boot and Protobuf
In this blog I will be demonstrating Kafka schema evolution with Java, Spring Boot and Protobuf.  This app is for tutorial purposes, so there will be instances where a refactor could happen. I tried to […]
Redis Bitmaps: Storing state in small places
Redis is a popular open source in-memory data store that supports all kinds of abstract data structures. In this post and in an accompanying example Java project, I am going to explore two great use […]