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
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) } } }
« Tillbaka