导入Excel数据(语言:groovy)

import java.io.FileInputStream
import org.apache.poi.ss.usermodel.WorkbookFactory
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.Row


/**
* @author cdh
*
* 基于apache POI项目的实现
*/
public class Excels{
static getDateFromExcel(){
def workBook = getExcelWorkbook(new FileInputStream('D:/SaleMobile.xlsx'))
getSheetListByWorkbook(workBook).each {sheet ->
def rowList = Excels.getRowListBySheet(sheet)
def colCount = Excels.getFirstRowCellCount(sheet)//列数
rowList.remove(0.intValue())//去除第一行（文字行）
rowList.each {row->
def oVal = []
def cellList = Excels.getCellListByRow(row, colCount)
cellList.each {cell->
oVal << Excels.getValueByCell(cell)
}
}
}
}
/**
* inp FileInputStream
*/
static def getExcelWorkbook(inp) {
    def wb = WorkbookFactory.create(inp);
    wb.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK)
    return wb
}

static def getSheetListByWorkbook(workbook) {
def sheetList = []
def i = 0
while (true) {
try {
def sheet = workbook.getSheetAt(i++)
sheetList << sheet
} catch (Exception e) {
return sheetList
}
}
}

static def getRowListBySheet(sheet) {
return sheet.rowIterator().collect{return it}
}

static def getFirstRowCellCount(sheet) {
return sheet.getRow(0).cellIterator().collect{return it}.@size
}

static def getCellListByRow(row, cellNum) {
--cellNum
def cellList = []
(0..cellNum).each {index->
cellList << row.getCell(index)
}
return cellList
}

static def getValueByCell(cell) {
if (cell.cellType == Cell.CELL_TYPE_BLANK) {
return ""
} else if (cell.cellType == Cell.CELL_TYPE_BOOLEAN) {
return cell.booleanCellValue
} else if (cell.cellType == Cell.CELL_TYPE_ERROR) {
return cell.errorCellValue
} else if (cell.cellType == Cell.CELL_TYPE_FORMULA) {
return cell.cellFormula
} else if (cell.cellType == Cell.CELL_TYPE_NUMERIC) {
return cell.numericCellValue
} else if (cell.cellType == Cell.CELL_TYPE_STRING) {
return cell.stringCellValue
} else {
return cell.stringCellValue
}
}
}

评论

1 楼 sam.ds.chen 2011-11-23  

虽然groovy是java-friendly的，但太像java的groovy不算groovy
这样写可能会更groovy一点：

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