java poi 获取单元格值时间
完整帮助类:JAVA poi 帮助类
/* * poi特殊日期格式:数字格式化成-yyyy年MM月dd日,格式 * */ private static ArrayList<String> PoiDateList = new ArrayList<String>() { { add("年"); add("月"); add("日"); } }; /// <summary> /// 获取XSSFRow的值(全部统一转成字符串) /// </summary> /// <param name="row"></param> /// <param name="index"></param> /// <returns></returns> public static String GetValue(Row row, int index) { Cell rowCell = row.getCell(index); return rowCell == null ? "" : GetValueByCellStyle(rowCell, rowCell.getCellType()); } /// <summary> /// 根据单元格的类型获取单元格的值 /// </summary> /// <param name="rowCell"></param> /// <param name="type"></param> /// <returns></returns> public static String GetValueByCellStyle(Cell rowCell, int rowCellType) { String value = ""; switch (rowCellType) { case Cell.CELL_TYPE_STRING: value = rowCell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: // 获取单元格值的格式化信息 String dataFormat = rowCell.getCellStyle().getDataFormatString(); // 判断格式化信息中是否存在:年月日 AtomicReference<Boolean> isDate = new AtomicReference<>(false); if (!StringHelper.IsNullOrWhiteSpace(dataFormat)) PoiDateList.forEach(x -> isDate.set(isDate.get() || dataFormat.contains(x))); if (DateUtil.isCellDateFormatted(rowCell)) { value = new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.getJavaDate(rowCell.getNumericCellValue())); } else if (DateUtil.isCellInternalDateFormatted(rowCell)) { value = new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.getJavaDate(rowCell.getNumericCellValue())); } //有些情况,时间搓?数字格式化显示为时间,不属于上面两种时间格式 else if (isDate.get()) { value = new SimpleDateFormat("yyyy-MM-dd").format(rowCell.getDateCellValue()); } //有些情况,时间搓?数字格式化显示为时间,不属于上面两种时间格式 else if (dataFormat == null) { value = new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.getJavaDate(rowCell.getNumericCellValue())); } else { if (StringHelper.IsNullOrWhiteSpace(dataFormat)) { value = String.valueOf(rowCell.getNumericCellValue()); } else { if (rowCell.getCellStyle().getDataFormatString().contains("$")) { value = "$" + rowCell.getNumericCellValue(); } else if (rowCell.getCellStyle().getDataFormatString().contains("¥")) { value = "¥" + rowCell.getNumericCellValue(); } else if (rowCell.getCellStyle().getDataFormatString().contains("¥")) { value = "¥" + rowCell.getNumericCellValue(); } else if (rowCell.getCellStyle().getDataFormatString().contains("€")) { value = "€" + String.valueOf(rowCell.getNumericCellValue()); } else { value = String.valueOf(rowCell.getNumericCellValue()); } } } break; case Cell.CELL_TYPE_BOOLEAN: value = String.valueOf(rowCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: value = ErrorEval.getText(rowCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: // TODO: 是否存在 嵌套 公式类型 value = GetValueByCellStyle(rowCell, rowCell.getCachedFormulaResultType()); break; default: System.out.println(rowCell); break; } return value; }