1 /** 2 * 根据不同情况获取Java类型值 3 * <ul><li>空白类型<ul><li>返回空字符串</li></ul></li></ul><ul><li>布尔类型</li><ul><li>返回Boulean类型值</li></ul></ul><ul><li>错误类型</li><ul><li>返回String类型值:Bad value</li></ul></ul><ul><li>数字类型</li><ul><li>日期类型</li><ul><li>返回格式化后的String类型,e.g.2017-03-15 22:22:22</li></ul><li>数字类型</li><ul><li>返回经过处理的java中的数字字符串,e.g.1.23E3==>1230</li></ul></ul> </ul><ul><li>公式类型</li><ul><li>公式正常</li><ul><li>返回计算后的String类型结果</li></ul></ul><ul><li>公式异常</li><ul><li>返回错误码,e.g.#DIV/0!;#NAME?;#VALUE!</li></ul></ul> </ul><ul><li>字符串类型</li><ul><li>返回String类型值</li></ul></ul> 4 * 5 * @param cell 6 * XSSFCell类型单元格 7 * @return 返回Object类型值 8 * @since 2017-03-26 00:05:36{@link #getValueOfNumericCell()} 9 */ 10 public static Object getJavaValue(XSSFCell cell) { 11 Object o = null; 12 int cellType = cell.getCellType(); 13 switch (cellType) { 14 case XSSFCell.CELL_TYPE_BLANK: 15 o = ""; 16 break; 17 case XSSFCell.CELL_TYPE_BOOLEAN: 18 o = cell.getBooleanCellValue(); 19 break; 20 case XSSFCell.CELL_TYPE_ERROR: 21 o = "Bad value!"; 22 break; 23 case XSSFCell.CELL_TYPE_NUMERIC: 24 o = getValueOfNumericCell(cell); 25 break; 26 case XSSFCell.CELL_TYPE_FORMULA: 27 try { 28 o = getValueOfNumericCell(cell); 29 } catch (IllegalStateException e) { 30 try { 31 o = cell.getRichStringCellValue().toString(); 32 } catch (IllegalStateException e2) { 33 o = cell.getErrorCellString(); 34 } 35 } catch (Exception e) { 36 e.printStackTrace(); 37 } 38 break; 39 default: 40 o = cell.getRichStringCellValue().getString(); 41 } 42 return o; 43 } 44 45 46 // 获取数字类型的cell值 47 private static Object getValueOfNumericCell(XSSFCell cell) { 48 Boolean isDate = DateUtil.isCellDateFormatted(cell); 49 Double d = cell.getNumericCellValue(); 50 Object o = null; 51 if (isDate) { 52 o = DateFormat.getDateTimeInstance() 53 .format(cell.getDateCellValue()); 54 } else { 55 o = getRealStringValueOfDouble(d); 56 } 57 return o; 58 } 59 60 // 处理科学计数法与普通计数法的字符串显示,尽最大努力保持精度 61 private static String getRealStringValueOfDouble(Double d) { 62 String doubleStr = d.toString(); 63 boolean b = doubleStr.contains("E"); 64 int indexOfPoint = doubleStr.indexOf('.'); 65 if (b) { 66 int indexOfE = doubleStr.indexOf('E'); 67 // 小数部分 68 BigInteger xs = new BigInteger(doubleStr.substring(indexOfPoint 69 + BigInteger.ONE.intValue(), indexOfE)); 70 // 指数 71 int pow = Integer.valueOf(doubleStr.substring(indexOfE 72 + BigInteger.ONE.intValue())); 73 int xsLen = xs.toByteArray().length; 74 int scale = xsLen - pow > 0 ? xsLen - pow : 0; 75 doubleStr = String.format("%." + scale + "f", d); 76 } else { 77 java.util.regex.Pattern p = Pattern.compile(".0$"); 78 java.util.regex.Matcher m = p.matcher(doubleStr); 79 if (m.find()) { 80 doubleStr = doubleStr.replace(".0", ""); 81 } 82 } 83 return doubleStr; 84 }