POI读取Excel工具类

poi的jar包下载：http://poi.apache.org/

public class ExcelUtils {
    public static void main(String[] args) {
		ExcelUtils eu = new ExcelUtils ();
		File file = new File("G://20180105101080149.xls");
		try {
			eu.testReadData(file);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
    // 批量读取数据
	public void testReadData( File file) throws Exception {
		InputStream fileInStream = new FileInputStream(file);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInStream);
		HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
		System.out.println(">>>>>"+hssfSheet.getPhysicalNumberOfRows());
		for (int i = 0; i < hssfSheet.getPhysicalNumberOfRows(); i++) {
			HSSFRow row = hssfSheet.getRow(i);
			if(row!=null && row.getCell(1)!=null ){
				row.getCell(1).setCellType(HSSFCell.CELL_TYPE_STRING);
				String colValue= ""+ExcelUtils.readColumns(hssfWorkbook,row, i, row.getCell(1));
                ...
			}
		}
		return sb;
	}
    /**
	 * 识别模板
	 * @param hssfSheet
	 * @param row 行号
	 * @param col 列号
	 * @param yt_label 表头
	 * @return
	 */
	public static boolean identifyTemplate(HSSFSheet hssfSheet, int rowNum, int colNum, String yt_label) {
		HSSFRow row = hssfSheet.getRow(rowNum);
		if (row != null) {
			HSSFCell cell = row.getCell(colNum);
			if (cell != null) {
				String str = extractCellValue(cell);
				if (str.indexOf(yt_label) > -1) {
					return true;
				}else{
					System.out.println(str+">>>>>"+yt_label);
				}
			}
		}
		return false;
	}

	/**
	 * 提取某格的值
	 * @param cell
	 * @return
	 */
	public static String extractCellValue(HSSFCell cell) {
		String reslt = null;
		if (cell != null) {
			int cellType = cell.getCellType();
			if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {// 数字类型
				NumberFormat formatter = new DecimalFormat("#");
				reslt = formatter.format(cell.getNumericCellValue());
			} else if (cellType == HSSFCell.CELL_TYPE_STRING) {// 字符串类型
				reslt = cell.getStringCellValue();
				reslt = StringUtils.trim(reslt);
			}else if (HSSFCell.CELL_TYPE_BLANK == cellType) {// 空值
				return "";
			}
		}
		return reslt;
	}

	/**
	 * 读取一行中列的值，只返回需要的部分
	 * @param row
	 * @param rowIdx
	 * @param dataFormatter
	 * @param formulaEval
	 * @return
	 * @throws Exception 
	 */
	public static Object[] readColumns(HSSFWorkbook hssfWorkbook,HSSFRow row, int rowIdx, String colName[]) throws Exception {
		HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
		Object[] result = new Object[colName.length];
		for (int i = 0; i < colName.length; i++) {
			HSSFCell cell = row.getCell(i);
			if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC ) {
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					Date d = cell.getDateCellValue();
					DateFormat formater = new SimpleDateFormat("yyyy-MM");
					result[i] = formater.format(d);
				}else{
					cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));  
					cell.setCellStyle(cellStyle);  
					result[i] = cell.getNumericCellValue();
				}
			} else if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
				result[i] = cell.getStringCellValue();
			} else if(cell != null) {
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				result[i] = cell.getStringCellValue();
			}/* 
			if(result[i]==null || "".equals(result[i].toString().trim())){
				throw new Exception("不能为空，请检查。第" + (rowIdx + 1)+"行，第"+(i+1)+"列");
			}*/
		}
		return result;
	}

	/**
	 * 读取一行中列的值，只返回需要的部分
	 * 
	 * @param row
	 * @param rowIdx
	 * @param dataFormatter
	 * @param formulaEval
	 * @return
	 * @throws Exception 
	 */
	public static Object readColumns(HSSFWorkbook hssfWorkbook,HSSFRow row, int rowIdx,HSSFCell cell) throws Exception {
		HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
		Object result = null;	
		if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC ) {
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				Date d = cell.getDateCellValue();
				DateFormat formater = new SimpleDateFormat("yyyy-MM");
				result = formater.format(d);
			}else{
				cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));  
				cell.setCellStyle(cellStyle);  
				result = cell.getNumericCellValue();
			}
		} else if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
			result = cell.getStringCellValue();
		} else if(cell != null) {
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			result = cell.getStringCellValue();
		}
		return result;
	}
}