Groovy操作POI

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()

}

Here is the builder source.

The only jar you need is the Apache POI jar

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93

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