Groovy 读取excel文件

用Apache的POI组建读取Excel文件

相关jar包：http://poi.apache.org/download.html#POI-3.8

class UpdateResourceDate {

	def fileUrl = "E:wwwupdateRes过期时间批量更新.xls";
	
	void updateResourceDate(){
		FileInputStream is = new FileInputStream(fileUrl);
		HSSFWorkbook workbook = new HSSFWorkbook(is);
		workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);
		
		//循环sheet
		(0..<workbook.getSheets().iterator().collect {return it}.@size).each {s->
			HSSFSheet sheet = workbook.getSheetAt(s);
			int rows = sheet.physicalNumberOfRows;
			
			//忽略第一行,标题行
			(1..<rows).each{r->
				HSSFRow row = sheet.getRow(r); 	
				def cells = row.physicalNumberOfCells;
				
				(0..<cells).each{c->
					HSSFCell cell = row.getCell(c);
					if (c==4 || c==5) {
						print "  "+getDataCellVal(cell);
					}else{
						print "  "+getCellVal(cell);
					}
				}
				println "";
			}
		}	
	}
	
	//取得日期列
	def getDataCellVal(HSSFCell cell) {
		Date date = cell.getDateCellValue();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		return format.format(date);
	}
	
	def getCellVal(HSSFCell cell) {
		if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { 
			return "" 
		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { 
			return cell.getBooleanCellValue(); 
		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_ERROR) { 
			return cell.getErrorCellValue();		
		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { 
			return cell.getNumericCellValue(); 
		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { 
			return cell.getStringCellValue(); 
		} else { 
			return cell.getStringCellValue(); 
		}
	}
	
	static main(args) {
		UpdateResourceDate a = new UpdateResourceDate();
		a.updateResourceDate();
	}
}

上面的读取Excel部分，为了当我们按Alt+/的时候有函数提示（Eclipse开发），所以写得稍繁琐，

再来个简单点的：

//循环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;					
				}	
			}			
		}