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

Introduction

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

Prototype

public String getStringValue() 

Source Link

Usage

From source file:android_connector.ExcelReader.java

/**
 * Gibt eine vernnftige Darstellung einer Zelle als String zurck.
 * @param cell die Zelle/*from   ww w  . j av  a  2s.  com*/
 * @return z.B. bei Zelle, die eine Gleichung enthlt, deren Ergebnis
 */
private String differCellType(Cell cell) {
    String returnValue = "";
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        returnValue = String.valueOf(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        returnValue = String.valueOf(cell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_STRING:
        returnValue = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        FormulaEvaluator evaluator = this.wb.getCreationHelper().createFormulaEvaluator();
        CellValue cellValue = evaluator.evaluate(cell);
        returnValue = cellValue.getStringValue();
        break;
    case Cell.CELL_TYPE_ERROR:
        returnValue = String.valueOf(cell.getErrorCellValue());
        break;
    case Cell.CELL_TYPE_BLANK:
        returnValue = "";
        break;
    default:
        returnValue = "default value at (" + cell.getRowIndex() + ";" + cell.getColumnIndex() + ") !";
        break;
    }
    return returnValue;
}

---

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;/*from   www.j ava  2 s  . com*/

    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: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  .  j  av a2  s  .  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  w  w .j  a v  a2  s . c om
    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 {//from ww  w.  ja va2s  .c  o  m
            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:cn.trymore.core.util.excel.PoiExcelParser.java

License:Open Source License

public Object getCellContent(Object cell) {
    if (cell != null) {
        HSSFCell cel = (HSSFCell) cell;//from   w w w  .  ja v  a  2 s . com
        HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(this.book);
        CellValue cellValue = evaluator.evaluate(cel);
        switch (cel.getCellType()) {
        case HSSFCell.CELL_TYPE_NUMERIC:
            if (HSSFDateUtil.isCellDateFormatted(cel)) {
                return UtilDate.parseTime(cel.getDateCellValue(), "yyyy-MM-dd HH:mm:ss");
            } else {
                return Double.valueOf(cellValue.getNumberValue());
            }
        case HSSFCell.CELL_TYPE_STRING:
            return cellValue.getStringValue();
        case HSSFCell.CELL_TYPE_BOOLEAN:
            return Boolean.valueOf(cellValue.getBooleanValue());
        case HSSFCell.CELL_TYPE_ERROR:
            return Byte.valueOf(cellValue.getErrorValue());
        case HSSFCell.CELL_TYPE_BLANK:
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            return cellValue.formatAsString();
        default:
            return null;
        }
    }
    return null;
}

---

From source file:com.b2international.snowowl.datastore.server.importer.ExcelUtilities.java

License:Apache License

private static String extractAsString(final Cell cell, final boolean formatNumber) {
    String value = "";

    if (cell == null) {
        return value;
    }//from w ww  .j a  va  2s  .com

    FormulaEvaluator formulaEvaluator = cell.getSheet().getWorkbook().getCreationHelper()
            .createFormulaEvaluator();

    int type = cell.getCellType();

    switch (type) {
    case Cell.CELL_TYPE_NUMERIC:
        if (formatNumber) {
            value = convertToString(cell.getNumericCellValue());
        } else {
            value = convertToStringWithoutFormat(cell.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_STRING:
        value = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        CellValue cellValue = formulaEvaluator.evaluate(cell);
        value = cellValue.getStringValue(); //type should be checked
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        value = Boolean.toString(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_BLANK:
        //do nothing, sctId is null
        break;
    default:
        LOGGER.log(Level.SEVERE, "Unsupported cell type:" + type + " for cell: " + cell);
        break;
    }
    return null == value ? "" : value;
}

---

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

License:Apache License

/**
 * Validate reference sheets//from  w  ww . j  ava2 s  . com
 * 
 * @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.jkoolcloud.tnt4j.streams.parsers.AbstractExcelParser.java

License:Apache License

/**
 * Evaluates and returns cell contained value.
 *
 * @param cell//from w  ww.  j  a  v a 2  s.co  m
 *            cell instance to evaluate value
 * @return evaluated cell value
 */
protected Object getCellValue(Cell cell) {
    CellValue cellValue;
    synchronized (EVALUATOR_LOCK) {
        if (evaluator == null) {
            Workbook workbook = cell.getSheet().getWorkbook();
            evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        }

        cellValue = evaluator.evaluate(cell);
    }

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

    switch (cellValue.getCellTypeEnum()) {
    case BOOLEAN:
        return cellValue.getBooleanValue();
    case NUMERIC:
        return cellValue.getNumberValue();
    case STRING:
        return cellValue.getStringValue();
    default:
        return cellValue.formatAsString();
    }
}

---

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

private static Object getFormulaValue(Cell cell) {
    Object cv = null;//from  w ww. j  a  v a 2 s.co m

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

---

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