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"))
row11.createCell(2).setCellValue(123.45)
def row12 = sheet1.createRow(2)
row12.createCell(0).setCellValue(new HSSFRichTextString("600"))
row12.createCell(1).setCellValue(Date.parse("yyyy-MM-dd", "2010-11-17"))
row12.createCell(2).setCellValue(132.54)
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
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Workbook

class HSSFWorkbookBuilder {

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

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

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

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") {
db.eachRow(
sql,
{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 ->
row([line])
}
}
}

3 comments:

Justin N. said...
This comment has been removed by the author.
Justin N. said...

Thanks for sharing. This worked well for me.

concrain said...

Will this work on Unix or Linux machines?