Example usage for org.apache.poi.ss.usermodel Cell getNumericCellValue

Introduction

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

Prototype

double getNumericCellValue();

Source Link

Document

Get the value of the cell as a number.

Usage

From source file:com.globalsight.util.ExcelUtil.java

License:Apache License

public static String getCellValue(Sheet sheet, int row, int col) {
    String value = "";
    if (sheet == null || row < 0 || col < 0)
        return "";

    Row rowData = sheet.getRow(row);/* ww  w.  j a v  a 2s  .c o m*/
    if (rowData == null)
        return "";
    Cell cell = rowData.getCell(col);
    if (cell == null)
        return "";
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        value = String.valueOf((int) cell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_STRING:
        value = cell.getStringCellValue();
        break;

    default:
        value = cell.toString();
    }

    return value;
}

---

From source file:com.glodon.tika.UpdateEmbeddedDoc.java

License:Apache License

/**
 * Called to test whether or not the embedded workbook was correctly
 * updated. This method simply recovers the first cell from the first row
 * of the first workbook and tests the value it contains.
 * <p/>//from   w  w  w  .  ja  v a 2s.c  o m
 * Note that execution will not continue up to the assertion as the
 * embedded workbook is now corrupted and causes an IllegalArgumentException
 * with the following message
 * <p/>
 * <em>java.lang.IllegalArgumentException: Your InputStream was neither an
 * OLE2 stream, nor an OOXML stream</em>
 * <p/>
 * to be thrown when the WorkbookFactory.createWorkbook(InputStream) method
 * is executed.
 *
 * @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException
 *                             Rather
 *                             than use the specific classes (HSSF/XSSF) to handle the embedded
 *                             workbook this method uses those defeined in the SS stream. As
 *                             a result, it might be the case that a SpreadsheetML file is
 *                             opened for processing, throwing this exception if that file is
 *                             invalid.
 * @throws java.io.IOException Thrown if a problem occurs in the underlying
 *                             file system.
 */
public void checkUpdatedDoc() throws OpenXML4JException, IOException {
    Workbook workbook = null;
    Sheet sheet = null;
    Row row = null;
    Cell cell = null;
    PackagePart pPart = null;
    Iterator<PackagePart> pIter = null;
    List<PackagePart> embeddedDocs = this.doc.getAllEmbedds();
    if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
        pIter = embeddedDocs.iterator();
        while (pIter.hasNext()) {
            pPart = pIter.next();
            if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION)
                    || pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) {
                workbook = WorkbookFactory.create(pPart.getInputStream());
                sheet = workbook.getSheetAt(SHEET_NUM);
                row = sheet.getRow(ROW_NUM);
                cell = row.getCell(CELL_NUM);
                assertEquals(cell.getNumericCellValue(), NEW_VALUE, 0.0001);
            }
        }
    }
}

---

From source file:com.hauldata.dbpa.file.book.XlsxSourceSheet.java

License:Apache License

private Object fromXLSX(Cell cell) {
    if (cell == null) {
        return null;
    }//from  ww  w. j  ava  2s .c om

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_NUMERIC:
        double numericValue = cell.getNumericCellValue();
        return DateUtil.isCellDateFormatted(cell) ? DateUtil.getJavaDate(numericValue) : (Double) numericValue;
    case Cell.CELL_TYPE_STRING:
    default:
        return cell.getStringCellValue();
    }
}

---

From source file:com.haulmont.mp2xls.helper.XlsHelper.java

License:Apache License

public static Object getCellValue(Cell cell) {
    if (cell == null) {
        return null;
    }/*from  w  ww .  j  a  va 2s . c  o  m*/

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_STRING:
        String formattedCellValue = cell.getStringCellValue().replace(String.valueOf(NON_BREAKING_SPACE), " ")
                .trim();
        return formattedCellValue.isEmpty() ? null : formattedCellValue;
    case Cell.CELL_TYPE_NUMERIC:
        if (isDateCell(cell)) {
            return cell.getDateCellValue();
        }

        Double numericCellValue = cell.getNumericCellValue();
        return isAlmostInt(numericCellValue) ? numericCellValue.intValue() : numericCellValue;
    case Cell.CELL_TYPE_FORMULA:
        /*
        formattedCellValue = cell.getStringCellValue();
        if (formattedCellValue != null) {
            formattedCellValue = formattedCellValue.replace(String.valueOf(NON_BREAKING_SPACE), " ").trim();
        }
        */
        return getFormulaCellValue(cell/*, formattedCellValue*/);
    default:
        throw new CellTypeIsNotSupportedException(cell);
    }
}

---

From source file:com.haulmont.mp2xls.helper.XlsHelper.java

License:Apache License

protected static Object getFormulaCellValue(Cell cell/*, String formattedCellValue*/) {
    switch (cell.getCachedFormulaResultType()) {
    case Cell.CELL_TYPE_NUMERIC:
        return cell.getNumericCellValue();
    case Cell.CELL_TYPE_STRING:
        String formattedCellValue = cell.getStringCellValue();
        if (formattedCellValue != null) {
            formattedCellValue = formattedCellValue.replace(String.valueOf(NON_BREAKING_SPACE), " ").trim();
            if (formattedCellValue.isEmpty())
                return null;
        }//from www. java2 s  .  c o  m
        return formattedCellValue;
    default:
        throw new IllegalStateException(
                String.format("Formula cell type '%s' is not supported", cell.getCachedFormulaResultType()));
    }
}

---

From source file:com.heimaide.server.common.utils.excel.ImportExcel.java

License:Open Source License

/**
 * ??/*from  w w  w. jav  a 2  s.c  o m*/
 * @param row ?
 * @param column ???
 * @return ?
 */
public Object getCellValue(Row row, int column) {
    Object val = "";
    try {
        Cell cell = row.getCell(column);
        if (cell != null) {
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                val = cell.getNumericCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                val = cell.getStringCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                try {
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        val = cell.getDateCellValue();
                    } else {
                        val = String.valueOf(cell.getNumericCellValue());
                    }
                } catch (IllegalStateException e) {
                    val = String.valueOf(cell.getRichStringCellValue());
                }
            } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                val = cell.getBooleanCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
                val = cell.getErrorCellValue();
            }
        }
    } catch (Exception e) {
        return val;
    }
    return val;
}

---

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

License:Apache License

/**
 * Return the best matching Java object underlying the passed cell.<br>
 * Note: Date values cannot be determined automatically!
 * /*  w  ww .ja va2 s.com*/
 * @param aCell
 *        The cell to be queried. May be <code>null</code>.
 * @return <code>null</code> if the cell is <code>null</code> or if it is of
 *         type blank.
 */
@Nullable
public static Object getCellValueObject(@Nullable final Cell aCell) {
    if (aCell == null)
        return null;

    final int nCellType = aCell.getCellType();
    switch (nCellType) {
    case Cell.CELL_TYPE_NUMERIC:
        return _getAsNumberObject(aCell.getNumericCellValue());
    case Cell.CELL_TYPE_STRING:
        return aCell.getStringCellValue();
    case Cell.CELL_TYPE_BOOLEAN:
        return Boolean.valueOf(aCell.getBooleanCellValue());
    case Cell.CELL_TYPE_FORMULA:
        final int nFormulaResultType = aCell.getCachedFormulaResultType();
        switch (nFormulaResultType) {
        case Cell.CELL_TYPE_NUMERIC:
            return _getAsNumberObject(aCell.getNumericCellValue());
        case Cell.CELL_TYPE_STRING:
            return aCell.getStringCellValue();
        case Cell.CELL_TYPE_BOOLEAN:
            return Boolean.valueOf(aCell.getBooleanCellValue());
        default:
            throw new IllegalArgumentException(
                    "The cell formula type " + nFormulaResultType + " is unsupported!");
        }
    case Cell.CELL_TYPE_BLANK:
        return null;
    default:
        throw new IllegalArgumentException("The cell type " + nCellType + " is unsupported!");
    }
}

---

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

License:Apache License

/**
 * Validate reference sheets//from   w ww. j  a  va 2 s .  co  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.hurence.logisland.processor.excel.ExcelExtract.java

License:Apache License

/**
 * Handle row content and transform it into a {@link Record}
 *
 * @param row the {@link Row}/*from   ww w .  java2s  .  co m*/
 * @return the transformed {@link Record}
 */
private Record handleRow(Row row, List<String> header) {
    Record ret = new StandardRecord().setTime(new Date());
    int index = 0;
    for (Cell cell : row) {
        if (configuration.getFieldNames() != null && index >= configuration.getFieldNames().size()) {
            //we've reached the end of mapping. Go to next row.
            break;
        }
        if (configuration.getColumnsToSkip().contains(cell.getColumnIndex())) {
            //skip this cell.
            continue;
        }
        String fieldName = header != null ? header.get(cell.getColumnIndex())
                : configuration.getFieldNames().get(index++);
        Field field;
        // Alternatively, get the value and format it yourself
        switch (cell.getCellTypeEnum()) {
        case STRING:
            field = new Field(fieldName, FieldType.STRING, cell.getStringCellValue());
            break;
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                field = new Field(fieldName, FieldType.LONG, cell.getDateCellValue().getTime());
            } else {
                field = new Field(fieldName, FieldType.DOUBLE, cell.getNumericCellValue());
            }
            break;
        case BOOLEAN:
            field = new Field(fieldName, FieldType.BOOLEAN, cell.getBooleanCellValue());
            break;
        case FORMULA:
            field = new Field(fieldName, FieldType.STRING, cell.getCellFormula());
            break;
        default:
            //blank or unknown
            field = new Field(fieldName, FieldType.NULL, null);
            break;
        }
        ret.setField(field);
    }
    return ret;
}

---

From source file:com.hust.zsuper.DealWithPatent.ExcelToMySQL.java

License:Open Source License

private static Object getCellValue(Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        return String.valueOf(cell.getBooleanCellValue());
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        } else {//from  w w  w.  j a  v  a  2 s .c o m
            return String.valueOf(cell.getNumericCellValue());
        }
    case Cell.CELL_TYPE_STRING:
    default:
        return cell.getStringCellValue().trim();
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »