做excel导入总是遇到读数字,公式产生的数字等精确度有误差的问题。。常有小数点后出现好几位数字的情况。
还有数字长度过长会被excel转换为科学计数法,哪怕是把单元格设置为文本也没用,还是会转换为科学计数法
用百度查了好久都没发现解决办法。最后在google上搜到了解决方案。特记录下来重点是这一句:
NumberToTextConverter.toText(cell.getNumericCellValue());
public static String formatCell(Cell cell){
String ret;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
ret = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
Workbook wb = cell.getSheet().getWorkbook();
CreationHelper crateHelper = wb.getCreationHelper();
FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
ret = formatCell(evaluator.evaluateInCell(cell));
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();
ret = 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);
ret = sdf.format(date);
} else {
ret = NumberToTextConverter.toText(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BLANK:
ret = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
ret = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
ret = null;
break;
default:
ret = null;
}
return ret; //有必要自行trim
}
然后问题就解决了