POI-根据Cell获取对应的String类型值 

 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     }

 

posted @ 2017-03-27 11:22  舒山  阅读(11571)  评论(0)  编辑  收藏  举报