package com.gk.firework.Domain.Utils;
|
|
import org.apache.poi.ss.usermodel.*;
|
|
import java.text.DecimalFormat;
|
import java.text.Format;
|
import java.util.Date;
|
|
/**
|
* Created by Administrator on 2017/8/12.
|
*/
|
public class ExcelProperty {
|
public static Object getCellValue(Cell cell,FormulaEvaluator evaluator) {
|
Object value = "";
|
// SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd HH:mm:ss"); //日期格式化
|
// DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字
|
|
if(null == cell)
|
{
|
return "";
|
}
|
switch (cell.getCellTypeEnum()) {
|
case _NONE:
|
value = "";
|
break;
|
case BLANK:
|
value = "";
|
break;
|
case BOOLEAN:
|
value = cell.getBooleanCellValue();
|
break;
|
case ERROR:
|
value = cell.getErrorCellValue();
|
break;
|
case FORMULA:
|
CellValue cellValue = evaluator.evaluate(cell);
|
switch (cellValue.getCellTypeEnum()) {
|
case _NONE:
|
value = "";
|
break;
|
case BLANK:
|
value = "";
|
break;
|
case BOOLEAN:
|
value = cellValue.getBooleanValue();
|
break;
|
case ERROR:
|
value = cellValue.getErrorValue();
|
break;
|
case NUMERIC:
|
value = cellValue.getNumberValue();
|
break;
|
case STRING:
|
if(StringUtils.isNotBlank(cell.getStringCellValue()))
|
{
|
value=cell.getStringCellValue().trim();
|
}
|
else {
|
value ="";
|
}
|
break;
|
default:
|
break;
|
}
|
break;
|
case NUMERIC:
|
value=formatNumericCell(cell.getNumericCellValue(), cell);
|
break;
|
case STRING:
|
if(StringUtils.isNotBlank(cell.getStringCellValue()))
|
{
|
value=cell.getStringCellValue().trim();
|
}
|
else {
|
value ="";
|
}
|
break;
|
default:
|
break;
|
}
|
return value;
|
}
|
|
public static String formatNumericCell(Double value, Cell cell) {
|
if(cell.getCellTypeEnum() != CellType.NUMERIC && cell.getCellTypeEnum() != CellType.FORMULA) {
|
return null;
|
}
|
//isCellDateFormatted判断该单元格是"时间格式"或者该"单元格的公式算出来的是时间格式"
|
if(DateUtil.isCellDateFormatted(cell)) {
|
Date date = cell.getDateCellValue();
|
DataFormatter dataFormatter = new DataFormatter();
|
Format format = dataFormatter.createFormat(cell);
|
return format.format(date);
|
} else {
|
DecimalFormat df = new DecimalFormat("#.###"); //格式化number String字符
|
return df.format(value);
|
|
}
|
}
|
}
|