Wednesday, December 29, 2010

Groovy DSL/Builders: POI Spreadsheets

It's well-known that Groovy is very rich for creating DSLs and fluent builder APIs.

I work a lot with the Apache POI library to generate Excel workbooks from data. We can use Groovy very easily to support a fluent and readable API for creating workbooks.

Here's a very simple example. Suppose we want to populate a workbook with two sheets with some data. Using the raw POI API, we could code something like this:

def workbook = new HSSFWorkbook()
def sheet1 = workbook.createSheet("Data")
def row10 = sheet1.createRow(0)
row10.createCell(0).setCellValue(new HSSFRichTextString("Invoice Number"))
row10.createCell(1).setCellValue(new HSSFRichTextString("Invoice Date"))
row10.createCell(2).setCellValue(new HSSFRichTextString("Amount"))
def row11 = sheet1.createRow(1)
row11.createCell(0).setCellValue(new HSSFRichTextString("100"))
row11.createCell(1).setCellValue(Date.parse("yyyy-MM-dd", "2010-10-18"))
def row12 = sheet1.createRow(2)
row12.createCell(0).setCellValue(new HSSFRichTextString("600"))
row12.createCell(1).setCellValue(Date.parse("yyyy-MM-dd", "2010-11-17"))
def sheet2 = workbook.createSheet("Summary")
def row20 = sheet2.createRow(0)
row20.createCell(0).setCellValue(new HSSFRichTextString("Sheet: Summary"))
def row21 = sheet2.createRow(1)
row21.createCell(0).setCellValue(new HSSFRichTextString("Total"))
row21.createCell(1).setCellValue(123.45 + 132.54)

This is not very readable. Even if we extract routines such as a common method to generate the cells in a row, the structure of our code does not follow closely the structure of what we want to create.
One of the big advantages of builders is that the structure of the code can match closely the structure of the generated result.

Here's the same workbook, created with a simple builder API:

def workbook = new HSSFWorkbookBuilder().workbook {
sheet("Data") { // sheet1
row(["Invoice Number", "Invoice Date", "Amount"])
row(["100", Date.parse("yyyy-MM-dd", "2010-10-18"), 123.45])
row(["600", Date.parse("yyyy-MM-dd", "2010-11-17"), 132.54])
sheet("Summary") { // sheet2
row(["Sheet: Summary"])
row(["Total", 123.45 + 132.54])

The HSSFWorkbookBuilder class required to do this is very straightforward:

import org.apache.poi.hssf.usermodel.HSSFRichTextString
import org.apache.poi.hssf.usermodel.HSSFWorkbook

class HSSFWorkbookBuilder {

private Workbook workbook = new HSSFWorkbook()
private Sheet sheet
private int rows

Workbook workbook(Closure closure) {
closure.delegate = this

void sheet(String name, Closure closure) {
sheet = workbook.createSheet(name)
rows = 0
closure.delegate = this

void row(values) {
Row row = sheet.createRow(rows++ as int)
values.eachWithIndex {value, col ->
Cell cell = row.createCell(col)
switch (value) {
case Date: cell.setCellValue((Date) value); break
case Double: cell.setCellValue((Double) value); break
case BigDecimal: cell.setCellValue(((BigDecimal) value).doubleValue()); break
default: cell.setCellValue(new HSSFRichTextString("" + value)); break


The magic is in the handling of the nested closures, and setting the delegate for each to the builder so that methods are resolved against the builder.

Here's another example of using such a builder. This one takes an SQL query and creates a workbook with two sheets. The first sheet contains the result of running the query, and the second sheet contains the query text.

def workbook = new HSSFWorkbookBuilder().workbook {
sheet("Data") {
{meta -> row(meta*.columnName)}, // header row with columns names from ResultSetMetaData
{rs -> row(rs.toRowResult().values())} // data row for each ResultSet row
sheet("SQL") {
sql.eachLine {line ->

Friday, February 5, 2010

Release builds with TeamCity: Selecting the branch

We've long had TeamCity doing regular "CI style" checkin builds for our Java/Ant projects. We recently added nightly builds for extra reports, and for longer-running performance tests. This was straightforward.

We finally got TeamCity doing our release builds. There were a couple of tricky points, which I thought would be worth writing up:

  • Selecting the branch
  • Manipulating the repository
  • Ensuring correct (release) versions of dependencies

Selecting the branch

This was the trickiest thing. Checkin and nightly builds always run against trunk. Well, you could set up a checkin build for a long-running development branch too, but that's not difficult. The release build should be done from the release branch, and that can be different for each release. Or it can be the same, if you have to do a fix release on an existing one!

We found a pretty good way to do this with TeamCity, using Build Configuration Templates and Configuration Parameters.

The idea is that you set up a template that contains all of the settings for the release build, except for the branch name. The branch name is specified by a configuration parameter. Then, the template is instantiated for each branch as desired. Each time the template is instantiated, the branch name configuration parameter is given for that instance.

Here are some details, using Subversion VCS and a hypothetical project named "xxx":

Create the Release Build Template

  1. Edit project's existing checkin build config.
  2. Click "Extract Template" to create a new template.
  3. Enter "release-build-template" for Name.
  4. Back in the checkin build config, click "Detach from Template".
  5. Click OK.

We've created a template, with no configurations attached.

Set up the Release Build Template

Edit the template. Change settings as given in the following sections.

Version Control Settings

  1. Create a new VCS root named xxx-branches.
  2. Specify Subversion as the Type of VCS.
  3. Enter Svn repo URL + "/xxx/branches" as the URL.
  4. Test the Connection.
  5. Save the VCS root.
  6. Attach the template to the xxx-branches VCS root.
  7. Detach the template from the xxx-trunk VCS root.
  8. Add checkout rule for VCS root: "%release.branch%=>.". This tells TeamCity to checkout the specific release branch into the working directory.
  9. Save Version Control Settings.

Runner Settings

  1. Change Target to "release-build", or whatever you want to call your release build target.
  2. Save Runner Settings.

You must have a target in your build script called "release-build", or whatever you want to call your release build target. This target must build the release and publish it somewhere. For example, it could copy it to a staging area on a server. Or, it could publish it to your enterprise repository.

The Ant target might look something like this:

Build Triggering Settings

  1. Delete/disable all triggering (VCS and Dependencies).
  2. Save Build Triggering Settings.
We've modified the template so that it will checkout the source from a release branch, with the specific branch given by a configuration parameter ("release.branch"). It will then build and publish the release.

Create a Release Branch Build Config for Release xx.yy

This procedure creates a release build config for a particular branch.

  1. Edit the release-build template's build configuration.
  2. Click Create Build Configuration From Template.
  3. Enter "release-xx.yy" for Name, where "xx.yy" is the name of your release branch.
  4. Enter the name of the branch for the release.branch parameter. For example, "RB-01.05".
We've created a configuration for running a release build on the branch.

To create a release:

  1. Ensure all changes for the release are checked into trunk.
  2. Create the branch. For example:
    svn copy $SVN/xxx/trunk $SVN/xxx/branches/RB-xx.yy

  3. Click Run on the release build configuration in TeamCity.

You can keep the release build configuration around for a particular branch as long as you like. If you are finished with a branch, you can delete the build configuration. If you need it again, it's easy to recreate it from the template.

That's it. I hope to write up some notes on the other points (manipulating the repository and ensuring the correct release versions of dependencies) soon.