Technipelago AB

Technipelago Blog Stuff that we learned...


Groovy loves POI and POI loves Groovy

This little Groovy builder makes reading Microsoft Excel documents a breeze. With it you can write the following code to insert customers into your Grails database

Publicerad den 19 Feb 2010

new ExcelBuilder("customers.xls").eachLine([labels:true]) {
  new Person(name:"$firstname $lastname",
    address:address, telephone:phone).save()
}

If the spreadsheet has no labels on the first row, you can use numeric index to access cells.


new ExcelBuilder("customers.xls").eachLine {
  println "First column on row ${it.rowNum} = ${cell(0)}"
}

Here is the builder source.

The only jar you need is the Apache POI jar


package extract.excel

import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.hssf.usermodel.HSSFSheet
import org.apache.poi.hssf.usermodel.HSSFRow
import org.apache.poi.hssf.usermodel.HSSFCell
import org.apache.poi.hssf.usermodel.HSSFDateUtil

/**
 * Groovy Builder that extracts data from
 * Microsoft Excel spreadsheets.
 * @author Goran Ehrsson
 */
class ExcelBuilder {

    def workbook
    def labels
    def row

    ExcelBuilder(String fileName) {
        HSSFRow.metaClass.getAt = {int idx ->
            def cell = delegate.getCell(idx)
            if(! cell) {
                return null
            }
            def value
            switch(cell.cellType) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                if(HSSFDateUtil.isCellDateFormatted(cell)) {
                    value = cell.dateCellValue
                } else {
                    value = cell.numericCellValue
                }
                break
                case HSSFCell.CELL_TYPE_BOOLEAN:
                value = cell.booleanCellValue
                break
                default:
                value = cell.stringCellValue
                break
            }
            return value
        }

        new File(fileName).withInputStream{is->
            workbook = new HSSFWorkbook(is)
        }
    }

    def getSheet(idx) {
        def sheet
        if(! idx) idx = 0
        if(idx instanceof Number) {
            sheet = workbook.getSheetAt(idx)
        } else if(idx ==~ /^\d+$/) {
            sheet = workbook.getSheetAt(Integer.valueOf(idx))
        } else {
            sheet = workbook.getSheet(idx)
        }
        return sheet
    }

    def cell(idx) {
        if(labels && (idx instanceof String)) {
            idx = labels.indexOf(idx.toLowerCase())
        }
        return row[idx]
    }

    def propertyMissing(String name) {
        cell(name)
    }

    def eachLine(Map params = [:], Closure closure) {
        def offset = params.offset ?: 0
        def max = params.max ?: 9999999
        def sheet = getSheet(params.sheet)
        def rowIterator = sheet.rowIterator()
        def linesRead = 0

        if(params.labels) {
            labels = rowIterator.next().collect{it.toString().toLowerCase()}
        }
        offset.times{ rowIterator.next() }

        closure.setDelegate(this)

        while(rowIterator.hasNext() && linesRead++ < max) {
            row = rowIterator.next()
            closure.call(row)
        }
    }
}

Tags: groovy poi excel


« Back