Technipelago Blog Stuff that we learned...
Groovy loves POI and POI loves Groovy
Publicerad den 19 Feb 2010
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
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)
}
}
}
« Back
