Java Code Examples for org.apache.poi.ss.usermodel.Cell#getNumericCellValue()

private String getStrFromCell(Cell cell) {
		String res = "";
		if (null==cell) {
			return "";
		}
//		res=cell.getRichStringCellValue().toString();
		
		switch (cell.getCellType()) {
			case Cell.CELL_TYPE_NUMERIC: // 数字/日期
				if (DateUtil.isCellDateFormatted(cell)){
					res=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue());
				}else{
					BigDecimal value =  new BigDecimal(cell.getNumericCellValue());
					String str = value.toString(); 
					if(str.contains(".0"))str = str.replace(".0", "");
					res=str;
				}
				break;
			case Cell.CELL_TYPE_STRING: // 字符串
				res = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_BOOLEAN: // 布尔
				Boolean booleanValue = cell.getBooleanCellValue();
				res = booleanValue.toString();
				break;
			case Cell.CELL_TYPE_BLANK: // 空值
				res = "";
				break;
			case Cell.CELL_TYPE_FORMULA: // 公式
				res = cell.getCellFormula();
				break;
			case Cell.CELL_TYPE_ERROR: // 故障
				res = "";
				break;
			default:
				System.out.println("未知类型");
				break;
		}
		return res;
	}
 

/**
 * Fetches the appropriate value from the cell, and uses the result, setting
 * the text and color of a label before returning the result.
 *
 * @param label The label to apply to.
 * @param c     The cell.
 *
 * @return The result, in a {@link CellFormatResult}.
 */
public CellFormatResult apply(JLabel label, Cell c) {
    switch (ultimateTypeEnum(c)) {
        case BLANK:
            return apply(label, "");
        case BOOLEAN:
            return apply(label, c.getBooleanCellValue());
        case NUMERIC:
            Double value = c.getNumericCellValue();
            if (getApplicableFormatPart(value).getCellFormatType() == CellFormatType.DATE) {
                if (DateUtil.isValidExcelDate(value)) {
                    return apply(label, c.getDateCellValue(), value);
                } else {
                    return apply(label, INVALID_VALUE_FOR_FORMAT);
                }
            } else {
                return apply(label, value);
            }
        case STRING:
            return apply(label, c.getStringCellValue());
        default:
            return apply(label, "?");
        }
}
 

@Override
public String doConvert(Cell cell) {
	int type = cell.getCellType();
	String value = null;
	if(Cell.CELL_TYPE_STRING==type){
		value = getStringValue(cell);
	}else if(Cell.CELL_TYPE_NUMERIC==type){
		Double dvalue = cell.getNumericCellValue();
		if(dvalue!=null){
			value = String.valueOf(dvalue.longValue());
		}
	}else if(Cell.CELL_TYPE_FORMULA==type){
		CellValue cv = ExcelUtils.getFormulaCellValue(cell);
		value = cv==null?defaultValue:cv.getStringValue();
	}else if(Cell.CELL_TYPE_BOOLEAN==type){
		boolean bvalue = cell.getBooleanCellValue();
		value = String.valueOf(bvalue);
	}else if(Cell.CELL_TYPE_BLANK==type){
		value = "";
	}
	return value;
}
 

private Boolean isBlank(Cell cell) {
  switch (cell.getCellType()) {
    case STRING:
      if (cell.getRichStringCellValue().length() > 0) {
        return false;
      }
      break;
    case NUMERIC:
      if (DateUtil.isCellDateFormatted(cell)
          ? cell.getDateCellValue() != null
          : cell.getNumericCellValue() != 0) {
        return false;
      }
      break;
    case BOOLEAN:
      if (cell.getBooleanCellValue()) {
        return false;
      }
      break;
    case BLANK:
      return true;
    default:
      break;
  }
  return null;
}
 

private static final Object readCell(final Cell cell) {
    switch (cell.getCellType()) {
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return DateUtil.getJavaDate(cell.getNumericCellValue());
            }
            return cell.getNumericCellValue();
        case BOOLEAN:
            return cell.getBooleanCellValue();
        default:
            return cell.getStringCellValue();
    }
}
 

protected String getCellValueAsString(Cell cell, int type) {
    if(cell != null) {
        switch(type) {
            case Cell.CELL_TYPE_ERROR: {
                return "ERROR"+cell.getErrorCellValue();
            }
            case Cell.CELL_TYPE_BOOLEAN: {
                return ""+cell.getBooleanCellValue();
            }
            case Cell.CELL_TYPE_NUMERIC: {
                if(DateUtil.isCellDateFormatted(cell)) {
                    return dateFormat.format(cell.getDateCellValue());
                }
                else {
                    double value = cell.getNumericCellValue();
                    String formatString = cell.getCellStyle().getDataFormatString();
                    int formatIndex = cell.getCellStyle().getDataFormat();
                    return formatter.formatRawCellContents(value, formatIndex, formatString);
                }
            }
            case Cell.CELL_TYPE_STRING: {
                return cell.getRichStringCellValue().getString();
            }
        }
    }
    return null;
}
 

private void checkFieldLength(Convert cfAnn, Cell cell, Field field, Row row) throws Exception {

        if (cfAnn.minLength() > -1) {
            if ((int) cell.getNumericCellValue() < cfAnn.minLength()) {
                throw new Exception("in row " + row.getRowNum() + ", " + field.getName() + " field too short " + "(" + (int) cell.getNumericCellValue() + ")" + " min length : " + cfAnn.minLength());
            }
        }
        if (cfAnn.maxLength() > -1) {
            if ((int) cell.getNumericCellValue() > cfAnn.maxLength()) {
                throw new Exception("in row " + row.getRowNum() + " " + field.getName() + " too long " + "(" + (int) cell.getNumericCellValue() + ")" + " max length : " + cfAnn.maxLength());
            }
        }
    }
 

public static String getCellVal(Cell cell, int i, int j) {
	if(cell == null) return "";
	
	try {
        switch(cell.getCellTypeEnum()) { // 判断cell类型
	        case NUMERIC:
	            // 判断cell是否为日期格式
	            if( org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell) ) {
	                Date dateCellVal = cell.getDateCellValue();
					return DateUtil.formatCare2Second( dateCellVal );
	            } 
	            else { // 数字,常规类型的数字会自动多出 .0（因转换后是double类型），需要格式化掉
	            	double cellVal = cell.getNumericCellValue();
	            	
	            	NumberFormat f = NumberFormat.getInstance();
	        		f.setMaximumFractionDigits(8); // 最多保留8位小数
					String val = f.format( cellVal );
					
	            	return val.replace(",", "");
	            }
	        case FORMULA:
	        	return ( (XSSFCell)cell ).getCTCell().getV();
	        case STRING:
	        	return cell.getStringCellValue();
	        default:
	        	return cell.toString();
        }
	} 
	catch( Exception e ) {
		throw new BusinessException( "Excel.getCellVal error, location = [" + i + "," + j + "], cell = " + cell, e);
	}
}
 

@Override
public Long get(Row target) throws Exception {
    final Cell cell = target.getCell(index);
    if (cell != null) {
        return (long)cell.getNumericCellValue();
    } else {
        return null;
    }
}
 

/***
 * 读取单元格的值
 *
 * @Title: getCellValue
 * @Date : 2014-9-11 上午10:52:07
 * @param cell
 * @return
 */
@SuppressWarnings("deprecation")
public String getCellValue(Cell cell) {
	Object result = "";
	if (cell != null) {
		switch (cell.getCellTypeEnum()) {
			case STRING:
				result = cell.getStringCellValue();
				break;
			case NUMERIC:
				result = cell.getNumericCellValue();
				break;
			case BOOLEAN:
				result = cell.getBooleanCellValue();
				break;
			case FORMULA:
				result = cell.getCellFormula();
				break;
			case ERROR:
				result = cell.getErrorCellValue();
				break;
			case BLANK:
				break;
			default:
				break;
		}
	}
	return result.toString();
}
 

@Override
public E get(Row target) throws Exception {
    final Cell cell = target.getCell(index);
    if (cell != null) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BLANK : return null;
            case Cell.CELL_TYPE_STRING : return Enum.valueOf(enumClass, cell.getStringCellValue());
            case Cell.CELL_TYPE_NUMERIC : return values[(int)cell.getNumericCellValue()];
            default:
                throw new UnsupportedOperationException("Cannot convert cell to enum");
        }
    } else {
        return null;
    }
}
 

/**
 * 读取每个单元格中的内容
 *
 * @param cell
 * @return
 */
private String _getCellValue(Cell cell) {
    // 如果单元格为空的，则返回空字符串
    if (cell == null) {
        return "";
    }

    // 根据单元格类型，以不同的方式读取单元格的值
    String value = "";
    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            value = cell.getRichStringCellValue().getString();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                value = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(cell.getDateCellValue());
            } else {
                value = (long) cell.getNumericCellValue() + "";
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            value = cell.getBooleanCellValue() ? "TRUE" : "FALSE";
            break;
        case Cell.CELL_TYPE_FORMULA:
            value = cell.getCellFormula();
            break;
        default:
    }
    return value;
}
 

private ValueAndFormat getCellValue(Cell cell) {
    if (cell != null) {
        final CellType type = cell.getCellTypeEnum();
        if (type == CellType.NUMERIC || (type == CellType.FORMULA && cell.getCachedFormulaResultTypeEnum() == CellType.NUMERIC) ) {
            return new ValueAndFormat(new Double(cell.getNumericCellValue()), cell.getCellStyle().getDataFormatString());
        } else if (type == CellType.STRING || (type == CellType.FORMULA && cell.getCachedFormulaResultTypeEnum() == CellType.STRING) ) {
            return new ValueAndFormat(cell.getStringCellValue(), cell.getCellStyle().getDataFormatString());
        } else if (type == CellType.BOOLEAN || (type == CellType.FORMULA && cell.getCachedFormulaResultTypeEnum() == CellType.BOOLEAN) ) {
            return new ValueAndFormat(cell.getStringCellValue(), cell.getCellStyle().getDataFormatString());
        }
    }
    return new ValueAndFormat("", "");
}
 

/**
 * Reads the giving cell, and provides the best object possible. Is able to read
 * the value of fields that are formulas
 * 
 * @param cell
 * @return the following objects
 *         <ul>
 *         <li>a string in case the cell is text</li>
 *         <li>a date in case the cell is a date</li>
 *         <li>A double in case it is a number</li>
 *         <li>null in case it is another type</li>
 *         </ul>
 */
public Object cellToObject(Cell cell) {
	switch (cell.getCellType()) {
	case BOOLEAN:
		return null;
	case STRING:
		return cell.getStringCellValue();

	case NUMERIC:
		if (DateUtil.isCellDateFormatted(cell)) {

			return cell.getDateCellValue();
		} else {
			return cell.getNumericCellValue();
		}

	case FORMULA:
		switch (cell.getCachedFormulaResultType()) {
		case NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {

				return cell.getDateCellValue();
			} else {
				return cell.getNumericCellValue();
			}
		case STRING:
			return cell.getRichStringCellValue().getString();
		default:
			return null;
		}
	case BLANK:
		return null;
	default:
		return null;
	}

}
 

@Override
public short getShort(Row target) throws Exception {
    final Cell cell = target.getCell(index);
    if (cell != null) {
        return (short)cell.getNumericCellValue();
    } else {
        return 0;
    }
}
 

/**
 * 获取cell数据
 * @param cell
 * @return
 */
private static String getCellValue(Cell cell){
    String value = "";
    if(cell != null) {
        switch (cell.getCellTypeEnum()) {
            case FORMULA:
                value += cell.getCellFormula();
                break;

            case NUMERIC:
                double cellValue = cell.getNumericCellValue();
                if(HSSFDateUtil.isCellDateFormatted(cell)){
                    Date date = HSSFDateUtil.getJavaDate(cellValue);
                    value += DateUtil.format(date, DateUtil.DATE_TIME);
                }else{
                    value += cell.getNumericCellValue();
                }
                break;

            case STRING:
                value += cell.getStringCellValue();
                break;
            case BLANK:
                break;
            case BOOLEAN:
                value += cell.getBooleanCellValue();
                break;
            case ERROR:
                break;
            default:break;
        }
    }
    return value;
}
 

public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException {
	cfSpreadSheetData	spreadsheet = null;
	int rowNo, columnNo;
	
	/*
	 * Collect up the parameters
	 */
spreadsheet	= (cfSpreadSheetData)parameters.get(2);
rowNo				= parameters.get(1).getInt() - 1;
columnNo		= parameters.get(0).getInt() - 1;
		
if ( rowNo < 0 )
	throwException(_session, "row must be 1 or greater (" + rowNo + ")");
if ( columnNo < 0 )
	throwException(_session, "column must be 1 or greater (" + columnNo + ")");


/*
 * Find the cell in question 
 */
Sheet	sheet = spreadsheet.getActiveSheet();
Row row	= sheet.getRow( rowNo );
if ( row == null )
	row	= sheet.createRow( rowNo );

Cell cell	= row.getCell( columnNo );
if ( cell == null )
	cell = row.createCell( columnNo );

FormulaEvaluator evaluator = spreadsheet.getWorkBook().getCreationHelper().createFormulaEvaluator();

if ( cell.getCellType() == Cell.CELL_TYPE_BOOLEAN )
	return cfBooleanData.getcfBooleanData( cell.getBooleanCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_NUMERIC )
	return new cfNumberData( cell.getNumericCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_BLANK )
	return cfStringData.EMPTY_STRING;
else if ( cell.getCellType() == Cell.CELL_TYPE_STRING )
	return new cfStringData( cell.getStringCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_FORMULA ){
	CellValue cellValue = evaluator.evaluate(cell);
	
	switch (cellValue.getCellType()) {
		case Cell.CELL_TYPE_BOOLEAN:
			return cfBooleanData.getcfBooleanData(cellValue.getBooleanValue());
		case Cell.CELL_TYPE_NUMERIC:
			return new cfNumberData(cellValue.getNumberValue());
		case Cell.CELL_TYPE_STRING:
			return new cfStringData(cellValue.getStringValue());
		default:
			return cfStringData.EMPTY_STRING;
	}

}else
	return cfStringData.EMPTY_STRING;
}
 

public static Number numberType(Class<?> fieldTypeClz, Cell cell) {
    Number value = null;
    try {
        value = cell.getNumericCellValue();
    } catch (Exception e) {
        // 非Number类型的列
    }
    if (value != null) {
        if (fieldTypeClz == BigDecimal.class) {
            return BigDecimal.valueOf(value.doubleValue());
        } else if (fieldTypeClz == BigInteger.class) {
            return BigInteger.valueOf(value.longValue());
        } else if (fieldTypeClz == Long.class) {
            return value.longValue();
        } else if (fieldTypeClz == Double.class) {
            return value.doubleValue();
        } else if (fieldTypeClz == Float.class) {
            return value.floatValue();
        } else {
            return value.intValue();
        }
    }
    // 取值失败,则把cell值当成String类型来取
    String numberStr = cell.getStringCellValue();
    if (StringUtils.isBlank(numberStr)) {
        return null;
    }
    if (!StringUtils.isNumeric(numberStr)) {
        return null;
    }
    if (fieldTypeClz == BigDecimal.class) {
        return new BigDecimal(numberStr);
    } else if (fieldTypeClz == BigInteger.class) {
        return new BigInteger(numberStr);
    } else if (fieldTypeClz == Long.class) {
        return Long.valueOf(numberStr);
    } else if (fieldTypeClz == Double.class) {
        return Double.valueOf(numberStr);
    } else if (fieldTypeClz == Float.class) {
        return Float.valueOf(numberStr);
    } else {
        return Integer.valueOf(numberStr);
    }
}
 

/**
 * 获取单元格值
 * 
 * @param row 获取的行
 * @param column 获取单元格列号
 * @return 单元格值
 */
public Object getCellValue(Row row, int column)
{
    if (row == null)
    {
        return row;
    }
    Object val = "";
    try
    {
        Cell cell = row.getCell(column);
        if (cell != null)
        {
            if (cell.getCellTypeEnum() == CellType.NUMERIC)
            {
                val = cell.getNumericCellValue();
                if (HSSFDateUtil.isCellDateFormatted(cell))
                {
                    val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                }
                else
                {
                    if ((Double) val % 1 > 0)
                    {
                        val = new DecimalFormat("0.00").format(val);
                    }
                    else
                    {
                        val = new DecimalFormat("0").format(val);
                    }
                }
            }
            else if (cell.getCellTypeEnum() == CellType.STRING)
            {
                val = cell.getStringCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
            {
                val = cell.getBooleanCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.ERROR)
            {
                val = cell.getErrorCellValue();
            }

        }
    }
    catch (Exception e)
    {
        return val;
    }
    return val;
}
 

/**
 * 获取单元格值
 * 
 * @param row 获取的行
 * @param column 获取单元格列号
 * @return 单元格值
 */
public Object getCellValue(Row row, int column)
{
    if (row == null)
    {
        return row;
    }
    Object val = "";
    try
    {
        Cell cell = row.getCell(column);
        if (StringUtils.isNotNull(cell))
        {
            if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA)
            {
                val = cell.getNumericCellValue();
                if (HSSFDateUtil.isCellDateFormatted(cell))
                {
                    val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                }
                else
                {
                    if ((Double) val % 1 > 0)
                    {
                        val = new DecimalFormat("0.00").format(val);
                    }
                    else
                    {
                        val = new DecimalFormat("0").format(val);
                    }
                }
            }
            else if (cell.getCellTypeEnum() == CellType.STRING)
            {
                val = cell.getStringCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
            {
                val = cell.getBooleanCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.ERROR)
            {
                val = cell.getErrorCellValue();
            }

        }
    }
    catch (Exception e)
    {
        return val;
    }
    return val;
}