Groovy Excel. Groovy读写Excel文件

ExcelBuilder.groovy

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

ReadExcel.groovy

//循环sheet
workbook.getSheets().each {sheet->
    //循环行
    sheet.eachWithIndex {row, index->
        if (index>0) {//忽略第一行，标题行
            def cells = row.physicalNumberOfCells;//取得列数
            
            String resId = "";
            def date = "";
            String name = "";
            
            resId = getCellVal(row.getCell(0)).toString();
            
            name = getCellVal(row.getCell(1)).toString();
            date = getDateCellVal(row.getCell(5)).toString();
            
            println resId + "," + name + "," + date;                    
        }    
    }            
}        

//

import org.apache.poi.hssf.usermodel.*

HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream('c:/tmp/sample_data.xls'))
(0..<wb.numberOfSheets).each{
    HSSFSheet sheet = wb.getSheetAt(it)
    int rows = sheet.physicalNumberOfRows
    println "Sheet " + it + " \"" + wb.getSheetName(it) + "\" has " + rows + " row(s)."
    (1..<rows).each{r->
        HSSFRow row = sheet.getRow(r)
        if (row != null) {
            int cells = row.physicalNumberOfCells
            println "\nROW " + row.rowNum + " has " + cells + " cell(s)."
            (0..<cells).each{c->
                HSSFCell cell = row.getCell(c)
                println "CELL col=" + cell.columnIndex + " VALUE=" + cell.stringCellValue
            }
        }
    }  
}

TestExcelBuilder.groovy

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