Example usage for org.apache.poi.ss.usermodel CellValue getCellType

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel CellValue getCellType.

Prototype

public CellType getCellType() 

Source Link

Document

Return the cell type.

Usage

From source file:au.com.onegeek.lambda.parser.XslxUtil.java

License:Apache License

public static Object evaluateCellFormula(final Workbook workbook, final Cell cell) {
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    CellValue cellValue = evaluator.evaluate(cell);
    Object result = null;/* w  ww  .  ja  v a 2 s  .  c  o m*/

    if (cellValue.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
        result = cellValue.getBooleanValue();
    } else if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        result = cellValue.getNumberValue();
    } else if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) {
        result = cellValue.getStringValue();
    }

    return result;
}

---

From source file:blueprint.sdk.experimental.util.XlsReader.java

License:Open Source License

/**
 * returns numeric/formula/date/time value
 *
 * @param cell target cell//from w ww  . ja  v  a  2s  .  c om
 * @return numeric value
 * @throws IOException cell type error
 */
protected String getNumericValue(final HSSFCell cell) throws IOException {
    String result;

    try {
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_NUMERIC:
            result = Double.toString(cell.getNumericCellValue());
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            CellValue cellValue = evaluator.evaluate(cell);
            switch (cellValue.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                result = Long.toString((long) cellValue.getNumberValue());
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                result = "";
                break;
            default:
                throw new IOException(createTypeErrMsg(cell));
            }
            break;
        default:
            throw new IOException(createTypeErrMsg(cell));
        }
    } catch (IllegalStateException e) {
        e.printStackTrace();
        throw new IOException(createTypeErrMsg(cell));
    }

    return result;
}

---

From source file:blueprint.sdk.experimental.util.XlsReader.java

License:Open Source License

/**
 * returns numeric/string value/*from  w w w . ja  v  a  2  s .c o  m*/
 *
 * @param cell target cell
 * @return text value
 * @throws IOException cell type error
 */
protected String getTextValue(final HSSFCell cell) throws IOException {
    String result;

    try {
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_NUMERIC:
            result = Long.toString((long) cell.getNumericCellValue());
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            CellValue cellValue = evaluator.evaluate(cell);
            switch (cellValue.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                result = Long.toString((long) cellValue.getNumberValue());
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                result = "";
                break;
            default:
                throw new IOException(createTypeErrMsg(cell));
            }
            break;
        case HSSFCell.CELL_TYPE_STRING:
            result = cell.getRichStringCellValue().getString();
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            result = "";
            break;
        default:
            throw new IOException(createTypeErrMsg(cell));
        }
    } catch (IllegalStateException e) {
        e.printStackTrace();
        throw new IOException(createTypeErrMsg(cell));
    }

    return result;
}

---

From source file:br.com.tecsinapse.dataio.importer.ImporterUtils.java

License:LGPL

public static Object getValueOrEmptyAsObject(FormulaEvaluator evaluator, Cell cell, boolean expectedDate) {
    final CellValue cellValue = safeEvaluteFormula(evaluator, cell);
    if (cellValue == null) {
        return "";
    }/*from ww w . ja v a 2s.c o m*/
    switch (cellValue.getCellType()) {
    case BOOLEAN:
        return cellValue.getBooleanValue();
    case NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)
                || (expectedDate && DateUtil.isValidExcelDate(cellValue.getNumberValue()))) {
            return cell.getDateCellValue();
        }
        BigDecimal bd = BigDecimal.valueOf(cell.getNumericCellValue()).setScale(DECIMAL_PRECISION,
                BigDecimal.ROUND_HALF_UP);
        return bd.stripTrailingZeros();
    case STRING:
        return cellValue.getStringValue();
    case ERROR:
        return "ERRO";
    default:
        return "";
    }
}

---

From source file:br.com.tecsinapse.exporter.importer.ImporterUtils.java

License:LGPL

public static Object getValueOrEmptyAsObject(FormulaEvaluator evaluator, Cell cell) {
    final CellValue cellValue = evaluator.evaluate(cell);
    if (cellValue == null) {
        return "";
    }//w ww  . ja v a2s . c o m
    switch (cellValue.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        return Boolean.valueOf(cellValue.getBooleanValue());
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            Date date = cell.getDateCellValue();
            return date;
        }
        BigDecimal bd = BigDecimal.valueOf(cell.getNumericCellValue()).setScale(DECIMAL_PRECISION,
                BigDecimal.ROUND_HALF_UP);
        return bd.stripTrailingZeros();
    case Cell.CELL_TYPE_STRING:
        return cellValue.getStringValue();
    case Cell.CELL_TYPE_ERROR:
        return "ERRO";
    default:
        return "";
    }
}

---

From source file:cn.edu.zjnu.acm.judge.util.excel.ExcelUtil.java

License:Apache License

private static JsonElement parseAsJsonElement(Cell cell, FormulaEvaluator evaluator) {
    switch (cell.getCellType()) {
    case NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return new JsonPrimitive(DateFormatterHolder.FORMATTER.format(cell.getDateCellValue().toInstant()));
        } else {//w w  w.  j  a va 2 s .com
            return new JsonPrimitive(cell.getNumericCellValue());
        }
    case STRING:
        return new JsonPrimitive(cell.getStringCellValue());
    case FORMULA:
        CellValue cellValue = evaluator.evaluate(cell);
        switch (cellValue.getCellType()) {
        case NUMERIC:
            return new JsonPrimitive(cellValue.getNumberValue());
        case STRING:
            return new JsonPrimitive(cellValue.getStringValue());
        case BLANK:
            return new JsonPrimitive("");
        case BOOLEAN:
            return new JsonPrimitive(cellValue.getBooleanValue());
        case ERROR:
        default:
            return null;
        }
    case BLANK:
        return new JsonPrimitive("");
    case BOOLEAN:
        return new JsonPrimitive(cell.getBooleanCellValue());
    case ERROR:
    default:
        return null;
    }
}

---

From source file:com.helger.poi.excel.ExcelReadUtilsTest.java

License:Apache License

/**
 * Validate reference sheets/*from  ww  w .  j av a  2s .  c  o  m*/
 * 
 * @param aWB
 *        Workbook to use
 */
private void _validateWorkbook(@Nonnull final Workbook aWB) {
    final Sheet aSheet1 = aWB.getSheet("Sheet1");
    assertNotNull(aSheet1);
    assertNotNull(aWB.getSheet("Sheet2"));
    final Sheet aSheet3 = aWB.getSheet("Sheet3");
    assertNotNull(aSheet3);
    assertNull(aWB.getSheet("Sheet4"));

    Cell aCell = aSheet1.getRow(0).getCell(0);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aCell.getCellType());
    assertEquals("A1", aCell.getStringCellValue());

    aCell = aSheet1.getRow(1).getCell(1);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aCell.getCellType());
    assertEquals("B2", aCell.getStringCellValue());

    aCell = aSheet1.getRow(2).getCell(2);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aCell.getCellType());
    assertEquals("C\n3", aCell.getStringCellValue());

    aCell = aSheet1.getRow(3).getCell(3);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_NUMERIC, aCell.getCellType());
    assertEquals(0.00001, 4.4, aCell.getNumericCellValue());

    for (int i = 0; i < 6; ++i) {
        aCell = aSheet3.getRow(i).getCell(i);
        assertNotNull(aCell);
        assertEquals(Cell.CELL_TYPE_NUMERIC, aCell.getCellType());
        assertEquals(0.00001, i + 1, aCell.getNumericCellValue());
    }

    // ="abc"
    aCell = aSheet1.getRow(4).getCell(0);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("\"abc\"", aCell.getCellFormula());
    assertEquals("abc", aCell.getStringCellValue());
    CellValue aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE)
            .evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aEvaluated.getCellType());
    assertEquals("abc", aEvaluated.getStringValue());

    // =4711
    aCell = aSheet1.getRow(5).getCell(1);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("4711", aCell.getCellFormula());
    assertEquals(0.00001, 4711, aCell.getNumericCellValue());
    aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE).evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_NUMERIC, aEvaluated.getCellType());
    assertEquals(0.00001, 4711, aEvaluated.getNumberValue());

    // =TRUE
    aCell = aSheet1.getRow(6).getCell(2);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("TRUE", aCell.getCellFormula());
    assertTrue(aCell.getBooleanCellValue());
    aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE).evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_BOOLEAN, aEvaluated.getCellType());
    assertTrue(aEvaluated.getBooleanValue());

    // Refers to cell at 6/2
    aCell = aSheet1.getRow(7).getCell(3);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("C7", aCell.getCellFormula());
    assertTrue(aCell.getBooleanCellValue());
    aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE).evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_BOOLEAN, aEvaluated.getCellType());
    assertTrue(aEvaluated.getBooleanValue());
}

---

From source file:com.jmc.jfxxlsdiff.util.POIXlsUtil.java

private static Object getFormulaValue(Cell cell) {
    Object cv = null;// w  ww .j  ava  2 s  .  c om

    FormulaEvaluator fe = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
    CellValue v = fe.evaluate(cell);

    switch (v.getCellType()) {
    case Cell.CELL_TYPE_BLANK: {
        break;
    }
    case Cell.CELL_TYPE_BOOLEAN: {
        cv = v.getBooleanValue();
        break;
    }
    case Cell.CELL_TYPE_ERROR: {
        cv = v.getErrorValue();
        break;
    }
    //case Cell.CELL_TYPE_FORMULA: {
    //   cv = cell.getCellFormula();
    //   break;
    //}
    case Cell.CELL_TYPE_NUMERIC: {
        double d = v.getNumberValue();

        if (DateUtil.isCellDateFormatted(cell)) {
            Calendar cal = Calendar.getInstance();
            cal.setTime(DateUtil.getJavaDate(d));
            cv = cal.getTime();
        } else {
            cv = d;
        }
        break;
    }
    case Cell.CELL_TYPE_STRING: {
        cv = v.getStringValue();
        break;
    }
    default: {
        logger.log(Level.WARNING, "Unexpected formula cell type = {0}", v.getCellType());
        break;
    }
    }

    return cv;
}

---

From source file:com.miraisolutions.xlconnect.data.DefaultColumnBuilder.java

License:Open Source License

protected void handleCell(Cell c, CellValue cv) {
    String msg;//  w w  w .java 2 s  . c  o m
    // Determine (evaluated) cell data type
    switch (cv.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        addMissing();
        return;
    case Cell.CELL_TYPE_BOOLEAN:
        addValue(c, cv, DataType.Boolean);
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(c))
            addValue(c, cv, DataType.DateTime);
        else {
            boolean missing = false;
            for (int i = 0; i < missingValueNumbers.length; i++) {
                if (cv.getNumberValue() == missingValueNumbers[i]) {
                    missing = true;
                    break;
                }
            }
            if (missing)
                addMissing();
            else
                addValue(c, cv, DataType.Numeric);
        }
        break;
    case Cell.CELL_TYPE_STRING:
        boolean missing = false;
        for (int i = 0; i < missingValueStrings.length; i++) {
            if (cv.getStringValue() == null || cv.getStringValue().equals(missingValueStrings[i])) {
                missing = true;
                break;
            }
        }
        if (missing)
            addMissing();
        else
            addValue(c, cv, DataType.String);
        break;
    case Cell.CELL_TYPE_FORMULA:
        msg = "Formula detected in already evaluated cell " + CellUtils.formatAsString(c) + "!";
        cellError(msg);
        break;
    case Cell.CELL_TYPE_ERROR:
        msg = "Error detected in cell " + CellUtils.formatAsString(c) + " - "
                + CellUtils.getErrorMessage(cv.getErrorValue());
        cellError(msg);
        break;
    default:
        msg = "Unexpected cell type detected for cell " + CellUtils.formatAsString(c) + "!";
        cellError(msg);
    }
}

---

From source file:com.miraisolutions.xlconnect.data.FastColumnBuilder.java

License:Open Source License

protected void handleCell(Cell c, CellValue cv) {
    String msg;/*from   w w w  .j a  v  a2s  .  c  o m*/
    // Determine (evaluated) cell data type
    switch (cv.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        addMissing();
        return;
    case Cell.CELL_TYPE_BOOLEAN:
        addValue(c, cv, DataType.Boolean);
        break;
    case Cell.CELL_TYPE_NUMERIC:
        addValue(c, cv, DataType.Numeric);
        break;
    case Cell.CELL_TYPE_STRING:
        addValue(c, cv, DataType.String);
        break;
    case Cell.CELL_TYPE_FORMULA:
        msg = "Formula detected in already evaluated cell " + CellUtils.formatAsString(c) + "!";
        cellError(msg);
        break;
    case Cell.CELL_TYPE_ERROR:
        msg = "Error detected in cell " + CellUtils.formatAsString(c) + " - "
                + CellUtils.getErrorMessage(cv.getErrorValue());
        cellError(msg);
        break;
    default:
        msg = "Unexpected cell type detected for cell " + CellUtils.formatAsString(c) + "!";
        cellError(msg);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• »