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

The following examples show how to use org.apache.poi.ss.usermodel.Cell#getCachedFormulaResultType() . 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: Excel2007ResultSet.java    From rapidminer-studio with GNU Affero General Public License v3.0 11 votes vote down vote up
@Override
public ValueType getNativeValueType(int columnIndex) throws ParseException {
	Cell cell = getCurrentCell(columnIndex);
	final int type = cell.getCellType();
	if (type == Cell.CELL_TYPE_BLANK) {
		return ValueType.EMPTY;
	} else if (type == Cell.CELL_TYPE_STRING) {
		return ValueType.STRING;
	} else if (type == Cell.CELL_TYPE_NUMERIC) {
		if (DateUtil.isCellDateFormatted(cell)) {
			return ValueType.DATE;
		} else {
			return ValueType.NUMBER;
		}
	} else if (type == Cell.CELL_TYPE_FORMULA) {
		if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC) {
			return ValueType.NUMBER;
		} else {
			return ValueType.STRING;
		}
	} else {
		return ValueType.STRING;
	}
}
 
Example 2
Source File: Excel2007ResultSet.java    From rapidminer-studio with GNU Affero General Public License v3.0 6 votes vote down vote up
@Override
public String getString(int columnIndex) {
	final Cell cell = getCurrentCell(columnIndex);
	if (cell == null) {
		return "";
	}
	if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
		return String.valueOf(cell.getNumericCellValue());
	} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
		String value;
		if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC) {
			value = String.valueOf(cell.getNumericCellValue());
		} else {
			value = cell.getStringCellValue();
		}
		return value;
	} else {
		try {
			return cell.getStringCellValue();
		} catch (IllegalStateException e) {
			return "";
		}
	}
}
 
Example 3
Source File: DataImporterController.java    From curly with Apache License 2.0 6 votes vote down vote up
private String getStringValueFromCell(Cell cell) {
    if (cell == null) {
        return null;
    }
    int cellType = cell.getCellType();
    if (cellType == Cell.CELL_TYPE_FORMULA) {
        cellType = cell.getCachedFormulaResultType();
    }
    switch (cellType) {
        case Cell.CELL_TYPE_BOOLEAN:
            return Boolean.toString(cell.getBooleanCellValue());
        case Cell.CELL_TYPE_BLANK:
            return null;
        case Cell.CELL_TYPE_NUMERIC:
            double num = cell.getNumericCellValue();
            if (num == Math.floor(num)) {
                return Integer.toString((int) num);
            } else {
                return Double.toString(cell.getNumericCellValue());
            }
        case Cell.CELL_TYPE_STRING:
            return cell.getStringCellValue();
        default:
            return "???";
    }
}
 
Example 4
Source File: XssfWCellImpl.java    From xlsbeans with Apache License 2.0 6 votes vote down vote up
private String getCellFormulaContents(Cell cell) {
  String contents = null;
  switch (cell.getCachedFormulaResultType()) {
    case Cell.CELL_TYPE_NUMERIC:
      contents = getNumericContents(cell);
      break;
    case Cell.CELL_TYPE_STRING:
      contents = cell.getStringCellValue();
      break;
    case Cell.CELL_TYPE_BOOLEAN:
      contents = String.valueOf(cell.getBooleanCellValue());
      break;
    case Cell.CELL_TYPE_ERROR:
      contents = String.valueOf(cell.getCellFormula());
      break;
    default:
      contents = "";
  }
  return contents;
}
 
Example 5
Source File: XssfWCellImpl.java    From xlsbeans with Apache License 2.0 6 votes vote down vote up
private String getCellFormulaContents(Cell cell) {
  String contents = null;
  switch (cell.getCachedFormulaResultType()) {
    case Cell.CELL_TYPE_NUMERIC:
      contents = getNumericContents(cell);
      break;
    case Cell.CELL_TYPE_STRING:
      contents = cell.getStringCellValue();
      break;
    case Cell.CELL_TYPE_BOOLEAN:
      contents = String.valueOf(cell.getBooleanCellValue());
      break;
    case Cell.CELL_TYPE_ERROR:
      contents = String.valueOf(cell.getCellFormula());
      break;
    default:
      contents = "";
  }
  return contents;
}
 
Example 6
Source File: ExcelReader.java    From Open-Lowcode with Eclipse Public License 2.0 5 votes vote down vote up
/**
 * 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 7
Source File: ReadExcelFormula.java    From journaldev with MIT License 5 votes vote down vote up
public static void readExcelFormula(String fileName) throws IOException{
	
	FileInputStream fis = new FileInputStream(fileName);
	
	//assuming xlsx file
	Workbook workbook = new XSSFWorkbook(fis);
	Sheet sheet = workbook.getSheetAt(0);
	Iterator<Row> rowIterator = sheet.iterator();
	while (rowIterator.hasNext()) 
       {
		Row row = rowIterator.next();
		Iterator<Cell> cellIterator = row.cellIterator();
           
           while (cellIterator.hasNext()) 
           {
           	Cell cell = cellIterator.next();
           	switch(cell.getCellType()){
           	case Cell.CELL_TYPE_NUMERIC:
           		System.out.println(cell.getNumericCellValue());
           		break;
           	case Cell.CELL_TYPE_FORMULA:
           		System.out.println("Cell Formula="+cell.getCellFormula());
           		System.out.println("Cell Formula Result Type="+cell.getCachedFormulaResultType());
           		if(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC){
           			System.out.println("Formula Value="+cell.getNumericCellValue());
           		}
           	}
           }
       }
}
 
Example 8
Source File: CellValueAndNotFormulaHelper.java    From tutorials with MIT License 5 votes vote down vote up
public Object getCellValueByFetchingLastCachedValue(String fileLocation, String cellLocation) throws IOException {
    Object cellValue = new Object();

    FileInputStream inputStream = new FileInputStream(new File(fileLocation));
    Workbook workbook = new XSSFWorkbook(inputStream);

    Sheet sheet = workbook.getSheetAt(0);

    CellAddress cellAddress = new CellAddress(cellLocation);
    Row row = sheet.getRow(cellAddress.getRow());
    Cell cell = row.getCell(cellAddress.getColumn());

    if (cell.getCellType() == CellType.FORMULA) {
        switch (cell.getCachedFormulaResultType()) {
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                cellValue = cell.getNumericCellValue();
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            default:
                cellValue = null;
        }
    }

    workbook.close();
    return cellValue;
}
 
Example 9
Source File: SheetUtil.java    From lams with GNU General Public License v2.0 4 votes vote down vote up
@Override
public int evaluateFormulaCell(Cell cell) {
    //noinspection deprecation
    return cell.getCachedFormulaResultType();
}
 
Example 10
Source File: XLSXDataParser.java    From CloverETL-Engine with GNU Lesser General Public License v2.1 4 votes vote down vote up
@Override
public int evaluateFormulaCell(Cell cell) {
	return cell.getCachedFormulaResultType();
}