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

The following examples show how to use org.apache.poi.ss.usermodel.Cell#getNumericCellValue() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.

Example 1

Source File: ExcelUtil.java From Leo with Apache License 2.0

6 votes

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;
	}

Example 2

Source File: CellFormat.java From lams with GNU General Public License v2.0

6 votes

/**
 * 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, "?");
        }
}

Example 3

Source File: WorkbookReaderFactory.java From onetwo with Apache License 2.0

6 votes

@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;
}

Example 4

Source File: XlsxReader.java From tablesaw with Apache License 2.0

6 votes

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;
}

Example 5

Source File: Serialization.java From joinery with GNU General Public License v3.0

5 votes

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();
    }
}

Example 6

Source File: AbstractExcelExtractor.java From wandora with GNU General Public License v3.0

5 votes

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;
}

Example 7

Source File: ExcelImporter.java From robe with GNU Lesser General Public License v3.0

5 votes

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());
            }
        }
    }

Example 8

Source File: ExcelPOI.java From boubei-tss with Apache License 2.0

5 votes

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);
	}
}

Example 9

Source File: PoiLongGetter.java From SimpleFlatMapper with MIT License

5 votes

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

Example 10

Source File: ExcelUtil.java From danyuan-application with Apache License 2.0

5 votes

/***
 * 读取单元格的值
 *
 * @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();
}

Example 11

Source File: PoiEnumGetter.java From SimpleFlatMapper with MIT License

5 votes

@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;
    }
}

Example 12

Source File: ExcelHelper.java From Excel2Entity with MIT License

5 votes

/**
 * 读取每个单元格中的内容
 *
 * @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;
}

Example 13

Source File: EvaluationConditionalFormatRule.java From lams with GNU General Public License v2.0

5 votes

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("", "");
}

Example 14

Source File: ExcelReader.java From Open-Lowcode with Eclipse Public License 2.0

5 votes

/**
 * 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;
	}

}

Example 15

Source File: PoiShortGetter.java From SimpleFlatMapper with MIT License

5 votes

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

Example 16

Source File: ExcelUtil.java From javautils with Apache License 2.0

5 votes

/**
 * 获取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;
}

Example 17

Source File: SpreadsheetGetCellValue.java From openbd-core with GNU General Public License v3.0

4 votes

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;
}

Example 18

Source File: ConvertUtils.java From java-master with Apache License 2.0

4 votes

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);
    }
}

Example 19

Source File: ExcelUtil.java From ruoyiplus with MIT License

4 votes

/**
 * 获取单元格值
 * 
 * @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;
}

Example 20

Source File: ExcelUtil.java From RuoYi-Vue with MIT License

4 votes

/**
 * 获取单元格值
 * 
 * @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;
}