org.apache.poi.ss.usermodel.CellValue Java Examples

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

@Test
void testToValueFormulaNumericLong() throws Exception {
  CellValue cellValue = new CellValue(1.2342151234E10);

  Cell cell = mock(Cell.class);

  FormulaEvaluator formulaEvaluator = mock(FormulaEvaluator.class);
  when(formulaEvaluator.evaluate(cell)).thenReturn(cellValue);

  CreationHelper creationHelper = mock(CreationHelper.class);
  when(creationHelper.createFormulaEvaluator()).thenReturn(formulaEvaluator);

  Workbook workbook = mock(Workbook.class);
  when(workbook.getCreationHelper()).thenReturn(creationHelper);

  Sheet sheet = mock(Sheet.class);
  when(sheet.getWorkbook()).thenReturn(workbook);

  when(cell.getCellTypeEnum()).thenReturn(FORMULA);
  when(cell.getSheet()).thenReturn(sheet);
  when(cell.getNumericCellValue()).thenReturn(1.2342151234E10);
  assertEquals("12342151234", toValue(cell));
}
 

/**
 * Return the numeric value.
 *
 * @param cell the cell to extract the value from.
 * @return the numeric value from the cell.
 */
private static String getNumericValue(Cell cell, CellValue cellValue, boolean fromFormula) {
    // Date is typed as numeric
    if (HSSFDateUtil.isCellDateFormatted(cell)) { // TODO configurable??
        DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.ENGLISH);
        return sdf.format(cell.getDateCellValue());
    }
    // Numeric type (use data formatter to get number format right)
    DataFormatter formatter = new HSSFDataFormatter(Locale.ENGLISH);

    if (cellValue == null) {
        return formatter.formatCellValue(cell);
    }

    return fromFormula ? cellValue.formatAsString() : formatter.formatCellValue(cell);
}
 

/**
 *
 * @param cell the cell
 * @param cellValue the result of the evaluation of a formula, containing the cell type
 * @return internal method which switch on the formula result value type then return a String value
 */
private static String getCellValueAsString(Cell cell, CellValue cellValue) {
    if (cellValue == null) {
        return StringUtils.EMPTY;
    }
    switch (cellValue.getCellType()) {
    case BLANK:
        return "";
    case BOOLEAN:
        return cellValue.getBooleanValue() ? Boolean.TRUE.toString() : Boolean.FALSE.toString();
    case ERROR:
        return "Cell Error type";
    case NUMERIC:
        return getNumericValue(cell, cellValue, cellValue != null);
    case STRING:
        return StringUtils.trim(cell.getStringCellValue());
    default:
        return "Unknown Cell Type: " + cell.getCellType();
    }
}
 

/**
 *
 * @param cell
 * @param cellValue
 * @return internal method which switch on the formula result value type then return a String value
 */
private static String getCellValueAsString(Cell cell, CellValue cellValue) {
    if (cellValue == null) {
        return StringUtils.EMPTY;
    }
    switch (cellValue.getCellTypeEnum()) {
    case BLANK:
        return "";
    case BOOLEAN:
        return cellValue.getBooleanValue() ? Boolean.TRUE.toString() : Boolean.FALSE.toString();
    case ERROR:
        return "Cell Error type";
    case NUMERIC:
        return getNumericValue(cell, cellValue, cellValue != null);
    case STRING:
        //TODO which is better? StringUtils.trim(cell.getStringCellValue())
        return cell.getRichStringCellValue().getString();
    default:
        return "Unknown Cell Type: " + cell.getCellTypeEnum();
    }
}
 

@Override
public Date doConvert(Cell cell) {
	int type = cell.getCellType();
	Date value = null;
	if(Cell.CELL_TYPE_STRING==type){
		value = TheFunction.getInstance().parseDateTime(getStringValue(cell));
	}else if(Cell.CELL_TYPE_NUMERIC==type){
		value = cell.getDateCellValue();
	}else if(Cell.CELL_TYPE_FORMULA==type){
		CellValue cv = ExcelUtils.getFormulaCellValue(cell);
		value = cv==null?defaultValue:TheFunction.getInstance().parseDateTime(cv.getStringValue());//Types.convertValue(cv.getStringValue(), Date.class);
	}else {
		String strValue = getAsString(cell);
		if(StringUtils.isBlank(strValue))
			return defaultValue;
		value = TheFunction.getInstance().parseDateTime(strValue);
	}
	return value;
}
 

@Override
public Double doConvert(Cell cell) {
	int type = cell.getCellType();
	Double value = null;
	if(Cell.CELL_TYPE_STRING==type){
		value = Double.parseDouble(getStringValue(cell));
	}else if(Cell.CELL_TYPE_NUMERIC==type){
		value = cell.getNumericCellValue();
	}else if(Cell.CELL_TYPE_FORMULA==type){
		CellValue cv = ExcelUtils.getFormulaCellValue(cell);
		value = cv==null?defaultValue:cv.getNumberValue();
	}else{
		String strValue = getAsString(cell);
		if(StringUtils.isBlank(strValue))
			return defaultValue;
		value = Double.parseDouble(strValue);
	}
	return value;
}
 

@Override
public Long doConvert(Cell cell) {
	int type = cell.getCellType();
	Long value = null;
	if(Cell.CELL_TYPE_STRING==type){
		value = ((Number)Double.parseDouble(getStringValue(cell))).longValue();
	}else if(Cell.CELL_TYPE_NUMERIC==type){
		Double dvalue = cell.getNumericCellValue();
		value = dvalue.longValue();
	}else if(Cell.CELL_TYPE_FORMULA==type){
		CellValue cv = ExcelUtils.getFormulaCellValue(cell);
		value = cv==null?defaultValue:(long)cv.getNumberValue();
	}else{
		String strValue = getAsString(cell);
		if(StringUtils.isBlank(strValue))
			return defaultValue;
		value = Long.parseLong(strValue);
	}
	return value;
}
 

protected void setCellValue(Cell cell, CellValue cv) {
    CellType cellType = cv.getCellTypeEnum();
    switch (cellType) {
        case BOOLEAN:
            cell.setCellValue(cv.getBooleanValue());
            break;
        case ERROR:
            cell.setCellErrorValue(cv.getErrorValue());
            break;
        case NUMERIC:
            cell.setCellValue(cv.getNumberValue());
            break;
        case STRING:
            cell.setCellValue(createRichTextString(cv.getStringValue()));
            break;
        case BLANK:
            // never happens - blanks eventually get translated to zero
        case FORMULA:
            // this will never happen, we have already evaluated the formula
        default:
            throw new IllegalStateException("Unexpected cell value type (" + cellType + ")");
    }
}
 

protected static void setCellType(Cell cell, CellValue cv) {
    CellType cellType = cv.getCellTypeEnum();
    switch (cellType) {
        case BOOLEAN:
        case ERROR:
        case NUMERIC:
        case STRING:
            cell.setCellType(cellType);
            return;
        case BLANK:
            // never happens - blanks eventually get translated to zero
            throw new IllegalArgumentException("This should never happen. Blanks eventually get translated to zero.");
        case FORMULA:
            // this will never happen, we have already evaluated the formula
            throw new IllegalArgumentException("This should never happen. Formulas should have already been evaluated.");
        default:
            throw new IllegalStateException("Unexpected cell value type (" + cellType + ")");
    }
}
 

/**
 * If cell contains a formula, the formula is evaluated and returned,
 * else the CellValue simply copies the appropriate cell value from
 * the cell and also its cell type. This method should be preferred over
 * evaluateInCell() when the call should not modify the contents of the
 * original cell.
 *
 * @param cell may be <code>null</code> signifying that the cell is not present (or blank)
 * @return <code>null</code> if the supplied cell is <code>null</code> or blank
 */
@Override
public CellValue evaluate(Cell cell) {
    if (cell == null) {
        return null;
    }

    switch (cell.getCellTypeEnum()) {
        case BOOLEAN:
            return CellValue.valueOf(cell.getBooleanCellValue());
        case ERROR:
            return CellValue.getError(cell.getErrorCellValue());
        case FORMULA:
            return evaluateFormulaCellValue(cell);
        case NUMERIC:
            return new CellValue(cell.getNumericCellValue());
        case STRING:
            return new CellValue(cell.getRichStringCellValue().getString());
        case BLANK:
            return null;
        default:
            throw new IllegalStateException("Bad cell type (" + cell.getCellTypeEnum() + ")");
    }
}
 

public static CellValue getFormulaCellValue(Cell cell){
		try {
			return cell==null?null:cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator().evaluate(cell);
		} catch (Exception e) {
//			throw new BaseException("get formual cell value["+cell.getRowIndex()+", "+ cell.getColumnIndex()+"] error : " + e.getMessage(), e);
			if(ExcelGenerators.isDevModel()){
				logger.warn("get formual cell value["+cell.getRowIndex()+", "+ cell.getColumnIndex()+"] error : " + e.getMessage());
			}
			return null;
		}
	}
 

/**
 * Return the numeric value.
 *
 * @param cell the cell to extract the value from.
 * @return the numeric value from the cell.
 */
private static String getNumericValue(Cell cell, CellValue cellValue, boolean fromFormula) {
    if (DateUtil.isCellDateFormatted(cell)) {
        return sdf.format(cell.getDateCellValue());
    }
    
    if (cellValue == null) {
        return formatter.formatCellValue(cell);
    }

    return fromFormula ? cellValue.formatAsString() : formatter.formatCellValue(cell);
}
 

@Override
public CellValue evaluate(Cell cell) {return null;  }
 

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

@Override
public CellValue evaluate(Cell cell) {
	throw new UnsupportedOperationException();
}
 

private static void processFormulaColumn(final Workbook excelDoc,
                                         final Sheet worksheet,
                                         final int firstRow,
                                         final int columnIndex) {

    final FormulaEvaluator evaluator = excelDoc.getCreationHelper().createFormulaEvaluator();
    for (int i = firstRow; i <= worksheet.getLastRowNum(); i++) {
        final Row row = worksheet.getRow(i);
        if (row != null) {
            final Cell cell = row.getCell(columnIndex);
            if (cell != null && (cell.getCellType() != CellType.BLANK)) {
                //formula type
                if (cell.getCellType() == CellType.FORMULA) {
                    CellValue cellValue = evaluator.evaluate(cell);

                    switch (cellValue.getCellType()) {
                        case BOOLEAN:
                            cell.setCellType(CellType.STRING);
                            break;
                        case NUMERIC:
                            cell.setCellType(CellType.NUMERIC);
                            break;
                        case STRING:
                            if (StringUtils.isBlank(cell.getStringCellValue())) {
                                cell.setCellType(CellType.BLANK);
                            } else {
                                cell.setCellType(CellType.STRING);
                            }
                            break;
                        case BLANK:

                            break;
                        case ERROR:
                            break;

                        // CELL_TYPE_FORMULA will never happen
                        case FORMULA:
                            break;
                    }
                }
            }
        }
    }
}
 

/**
 * 计算公式结果
 *
 * @param cell 单元格类型为公式的单元格
 * @return 返回单元格计算后的值 格式化成String
 * @author QingMings
 * Email:[email protected]
 * date 2018-01-13
 */
public static String calculationFormula(Cell cell) {

    CellValue cellValue = cell.getSheet().getWorkbook().getCreationHelper()
            .createFormulaEvaluator().evaluate(cell);
    return cellValue.formatAsString();
}
 

/**
 * If cell contains formula, it evaluates the formula,
 *  and saves the result of the formula. The cell
 *  remains as a formula cell.
 * Else if cell does not contain formula, this method leaves
 *  the cell unchanged.
 * Note that the type of the formula result is returned,
 *  so you know what kind of value is also stored with
 *  the formula.
 * <pre>
 * CellType evaluatedCellType = evaluator.evaluateFormulaCellEnum(cell);
 * </pre>
 * Be aware that your cell will hold both the formula,
 *  and the result. If you want the cell replaced with
 *  the result of the formula, use {@link #evaluate(org.apache.poi.ss.usermodel.Cell)} }
 * @param cell The cell to evaluate
 * @return The type of the formula result (the cell's type remains as CellType.FORMULA however)
 *         If cell is not a formula cell, returns {@link CellType#_NONE} rather than throwing an exception.
 * @since POI 3.15 beta 3
 */
@Override
public CellType evaluateFormulaCellEnum(Cell cell) {
    if (cell == null || cell.getCellTypeEnum() != CellType.FORMULA) {
        return CellType._NONE;
    }
    CellValue cv = evaluateFormulaCellValue(cell);
    // cell remains a formula cell, but the cached value is changed
    setCellValue(cell, cv);
    return cv.getCellTypeEnum();
}
 

/**
 * If cell contains formula, it evaluates the formula, and
 *  puts the formula result back into the cell, in place
 *  of the old formula.
 * Else if cell does not contain formula, this method leaves
 *  the cell unchanged.
 * Note that the same instance of HSSFCell is returned to
 * allow chained calls like:
 * <pre>
 * int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType();
 * </pre>
 * Be aware that your cell value will be changed to hold the
 *  result of the formula. If you simply want the formula
 *  value computed for you, use {@link #evaluateFormulaCellEnum(Cell)}}
 * @param cell
 * @return the {@code cell} that was passed in, allowing for chained calls
 */
@Override
public Cell evaluateInCell(Cell cell) {
    if (cell == null) {
        return null;
    }
    Cell result = cell;
    if (cell.getCellTypeEnum() == CellType.FORMULA) {
        CellValue cv = evaluateFormulaCellValue(cell);
        setCellValue(cell, cv);
        setCellType(cell, cv); // cell will no longer be a formula cell
    }
    return result;
}
 

protected abstract CellValue evaluateFormulaCellValue(Cell cell);