对Excle的操作的工具类

借鉴了网上的各种资料

特别是这位仁兄的：http://blog.youkuaiyun.com/thewolfish/article/details/9283525

对Excle的操作的工具类 待完善

public class ExcelUtil {

	// 处理单元格不同数据格式 注意：改方法在处理公式时返回公式不返回值
	public static String getCellValue(Cell cell) {
		String cellValue = "";
		if (cell == null) {
			return cellValue;
		}
		try {
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING: // 字符串
				cellValue = cell.getRichStringCellValue().getString().trim();
				break;
			case Cell.CELL_TYPE_NUMERIC: // 数字
				if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
					SimpleDateFormat sdf = null;
					if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
						sdf = new SimpleDateFormat("HH:mm");
					} else {// 日期
						sdf = new SimpleDateFormat("yyyy-MM-dd");
					}
					Date date = cell.getDateCellValue();
					cellValue = sdf.format(date);
				} else if (cell.getCellStyle().getDataFormat() == 58) {
					// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					double value = cell.getNumericCellValue();
					Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
					cellValue = sdf.format(date);
				} else {
					double value = cell.getNumericCellValue();
					CellStyle style = cell.getCellStyle();
					DecimalFormat format = new DecimalFormat();
					String temp = style.getDataFormatString();
					// 单元格设置成常规
					if (temp.equals("General")) {
						format.applyPattern("#");
					}
					cellValue = format.format(value);
				}
				break;
			case Cell.CELL_TYPE_BOOLEAN: // 布尔
				cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
				break;
			case Cell.CELL_TYPE_FORMULA: // 公式
				cell.getCellFormula();
				break;
			case Cell.CELL_TYPE_BLANK: // 空值
				cellValue = "";
				break;
			case Cell.CELL_TYPE_ERROR: // 故障
				cellValue = "非法字符";
				break;
			default:
				cellValue = "未知类型";
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cellValue;
	}

	// 处理单元格不同数据格式 注意：改方法在处理公式时返回值不返回公式
	public static String getCellValue(Cell cell, FormulaEvaluator evaluator) {
		String cellValue = "";
		if (cell == null) {
			return cellValue;
		}
		try {
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING: // 字符串
				cellValue = cell.getRichStringCellValue().getString().trim();
				break;
			case Cell.CELL_TYPE_NUMERIC: // 数字
				if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
					SimpleDateFormat sdf = null;
					if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
						sdf = new SimpleDateFormat("HH:mm");
					} else {// 日期
						sdf = new SimpleDateFormat("yyyy-MM-dd");
					}
					Date date = cell.getDateCellValue();
					cellValue = sdf.format(date);
				} else if (cell.getCellStyle().getDataFormat() == 58) {
					// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					double value = cell.getNumericCellValue();
					Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
					cellValue = sdf.format(date);
				} else {
					double value = cell.getNumericCellValue();
					CellStyle style = cell.getCellStyle();
					DecimalFormat format = new DecimalFormat();
					String temp = style.getDataFormatString();
					// 单元格设置成常规
					if (temp.equals("General")) {
						format.applyPattern("#");
					}
					cellValue = format.format(value);
				}
				break;
			case Cell.CELL_TYPE_BOOLEAN: // 布尔
				cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
				break;
			case Cell.CELL_TYPE_FORMULA: // 公式
				try {
					CellValue cellValueThis;
					cellValueThis = evaluator.evaluate(cell);
					switch (cellValueThis.getCellType()) { // 判断公式类型
					case Cell.CELL_TYPE_BOOLEAN:
						cellValue = String.valueOf(cellValueThis.getBooleanValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						// 处理日期
						if (DateUtil.isCellDateFormatted(cell)) {
							SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd");
							Date date = cell.getDateCellValue();
							cellValue = format.format(date);
						} else {
							cellValue = String.valueOf(cellValueThis.getNumberValue());
						}
						break;
					case Cell.CELL_TYPE_STRING:
						cellValue = cellValueThis.getStringValue();
						break;
					case Cell.CELL_TYPE_BLANK:
						cellValue = "";
						break;
					case Cell.CELL_TYPE_ERROR:
						cellValue = "";
						break;
					case Cell.CELL_TYPE_FORMULA:
						cellValue = "";
						break;
					}
				} catch (Exception e) {
					cellValue = cell.getStringCellValue().toString();
					cell.getCellFormula();
				}
				break;
			case Cell.CELL_TYPE_BLANK: // 空值
				cellValue = "";
				break;
			case Cell.CELL_TYPE_ERROR: // 故障
				cellValue = "非法字符";
				break;
			default:
				cellValue = "未知类型";
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cellValue;
	}

	public static Workbook CreatWorkbook(MultipartFile file) {
		Workbook wb = null;
		try {
			// 获取文件后缀
			String fileType = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1);
			// 创建具体工作簿
			if (fileType.equalsIgnoreCase("xlsx")) {
				wb = new XSSFWorkbook(file.getInputStream());
			} else if (fileType.equalsIgnoreCase("xls")) {
				wb = new HSSFWorkbook(file.getInputStream());
			} else {
				wb = null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (wb != null) {
				try {
					wb.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return wb;
	}

	public static Sheet CreatSheet(Workbook wb) {
		Sheet sheet = null;
		try {
			sheet = wb.createSheet();
			wb.setSelectedTab(0); // 设置活动工作表
		} catch (Exception e) {
			e.printStackTrace();
		}
		return sheet;
	}

	public static Sheet GetSheet(Workbook wb) {
		Sheet sheet = null;
		try {
			sheet = wb.getSheetAt(0);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return sheet;
	}

	public static Sheet GetSheet(Workbook wb, int index) {
		Sheet sheet = null;
		try {
			sheet = wb.getSheetAt(index);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return sheet;
	}

	public static FormulaEvaluator CreatFormulaEvaluator(MultipartFile file, Workbook wb) {
		FormulaEvaluator fe = null;
		try {
			// 获取文件后缀
			String fileType = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1);
			// 创建具体工作簿
			if (fileType.equalsIgnoreCase("xlsx")) {
				fe = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
			} else if (fileType.equalsIgnoreCase("xls")) {
				fe = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
			} else {
				fe = null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return fe;
	}

}