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

List of usage examples for org.apache.poi.ss.usermodel CellValue getNumberValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CellValue
  6. getNumberValue

Introduction

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

Prototype

public double getNumberValue()

Source Link

Usage

From source file:org.drools.decisiontable.parser.xls.ExcelParser.java

License:Apache License

private String getCellValue(final CellValue cv) {
    switch (cv.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        return Boolean.toString(cv.getBooleanValue());
    case Cell.CELL_TYPE_NUMERIC:
        return String.valueOf(cv.getNumberValue());
    }/*from  www.  j  a va  2  s  . co  m*/
    return cv.getStringValue();
}

From source file:org.geotools.data.excel.ExcelFeatureSource.java

License:Open Source License

/**
 * create a FeatureSource with the specified Query
 * /* w  ww . j  ava2s .  c o m*/
 * @param entry
 * @param query
 *            - a query containing a filter that will be applied to the data
 */
public ExcelFeatureSource(ContentEntry entry, Query query) {
    super(entry, query);
    Date beginingOfExcelTime = HSSFDateUtil.getJavaDate(0);

    dataStore = (ExcelDataStore) entry.getDataStore();

    sheet = dataStore.getSheet();
    latCol = dataStore.getLatColumnIndex();
    lonCol = dataStore.getLonColumnIndex();
    int rows = sheet.getPhysicalNumberOfRows();
    int start = dataStore.getHeaderRowIndex() + 1;
    latCol = dataStore.getLatColumnIndex();
    lonCol = dataStore.getLonColumnIndex();
    features = new ArrayList<SimpleFeature>();
    filteredFeatures = new ArrayList<SimpleFeature>();
    evaluator = dataStore.workbook.getCreationHelper().createFormulaEvaluator();
    if (schema == null) {
        schema = getSchema();
    }
    GeometryFactory geometryFactory = dataStore.getGeometryFactory();

    SimpleFeatureBuilder builder = new SimpleFeatureBuilder(schema);
    Row header = sheet.getRow(dataStore.getHeaderRowIndex());
    for (int i = start; i < rows; i++) {
        Row data = sheet.getRow(i);
        double x = 0.0;
        double y = 0.0;
        for (int col = data.getFirstCellNum(); col < data.getLastCellNum(); col++) {
            final Cell cell = data.getCell(col);
            CellValue value = evaluator.evaluate(cell);
            if (col == latCol) {

                if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    y = value.getNumberValue();
                }
            } else if (col == lonCol) {
                if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    x = value.getNumberValue();
                }
            } else {
                // cast and handle
                final String name = header.getCell(col).getStringCellValue().trim();
                switch (value.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    AttributeType type = schema.getType(name);
                    Class<?> clazz = type.getBinding();
                    if (clazz == Double.class) {
                        builder.set(name, value.getNumberValue());
                    } else if (clazz == java.sql.Date.class) {
                        final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
                        final Calendar cal = Calendar.getInstance();
                        cal.clear();
                        cal.setTime(javaDate);
                        java.sql.Date date = new java.sql.Date(cal.getTimeInMillis());
                        builder.set(name, date);
                    } else if (clazz == java.util.Date.class) {
                        final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
                        builder.set(name, javaDate);
                    } else if (clazz == Time.class) {
                        final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
                        final Calendar cal = Calendar.getInstance();
                        cal.clear();
                        cal.setTime(javaDate);
                        cal.set(0, 0, 0);
                        Time time = new Time(cal.getTimeInMillis());
                        builder.set(name, time);
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    builder.set(name, value.getStringValue().trim());
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    builder.set(name, value.getBooleanValue());
                    break;

                default:
                    System.out.println("We don't handle " + cell.getCellType() + " type cells "
                            + cell.getStringCellValue());
                }
            }
        }
        Point p = geometryFactory.createPoint(new Coordinate(x, y));
        builder.set("the_geom", p);

        SimpleFeature feature = builder.buildFeature(null);
        features.add(feature);

    }
    filterFeatures(query);
}

From source file:org.geotools.data.excel.ExcelFeatureSource.java

License:Open Source License

@Override
protected SimpleFeatureType buildFeatureType() throws IOException {
    SimpleFeatureTypeBuilder tb = new SimpleFeatureTypeBuilder();
    tb.setName(entry.getName());/*ww w. ja v  a 2s . c  om*/
    tb.setCRS(dataStore.getProjection());
    Row header = sheet.getRow(dataStore.getHeaderRowIndex());
    Row data = sheet.getRow(dataStore.getHeaderRowIndex() + 1);
    Row nextData = sheet.getRow(dataStore.getHeaderRowIndex() + 2);
    boolean latColGood = false;
    boolean lonColGood = false;
    for (int i = header.getFirstCellNum(); i < header.getLastCellNum(); i++) {
        // go through and guess data type from cell types!
        Cell cell = data.getCell(i);
        String name = header.getCell(i).getStringCellValue().trim();
        CellValue value = evaluator.evaluate(cell);
        int type = value.getCellType();

        Class<?> clazz = null;
        if (latCol == i) {
            // check it's a number
            if (type == Cell.CELL_TYPE_NUMERIC) {
                latColGood = true;
            }
        } else if (lonCol == i) {
            // check it's a number
            if (type == Cell.CELL_TYPE_NUMERIC) {
                lonColGood = true;
            }
        } else {
            switch (type) {
            case Cell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    if (value.getNumberValue() < 1.0) {
                        clazz = Time.class;
                    } else if (Math.floor(cell.getNumericCellValue()) == Math
                            .ceil(cell.getNumericCellValue())) {
                        // midnight or just a date
                        // check the next row
                        Cell cell2 = nextData.getCell(i);
                        if (Math.floor(cell2.getNumericCellValue()) == Math.ceil(cell2.getNumericCellValue())) {
                            //probably a simple date
                            clazz = java.sql.Date.class;
                        } else {
                            // actual date/time element
                            clazz = java.util.Date.class;
                        }
                    } else {
                        // actual date/time element
                        clazz = java.util.Date.class;
                    }
                } else {
                    clazz = Double.class;
                }
                break;
            case Cell.CELL_TYPE_STRING:
                clazz = String.class;
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                clazz = Boolean.class;
                break;
            }
            System.out.println(name + ":" + clazz);
            tb.add(name, clazz);
        }

    }
    if (latColGood && lonColGood) {
        tb.add("the_geom", Point.class);
    } else {
        throw new IOException("failed to find a Lat and Lon column");
    }
    // build the type (it is immutable and cannot be modified)
    final SimpleFeatureType SCHEMA = tb.buildFeatureType();
    return SCHEMA;
}

From source file:org.is.jxlpoi.JXLPOIWorkbook.java

License:Apache License

public String getCellContentAsString(Cell c) {

    if (c == null)
        return null;

    switch (c.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        if (c.getBooleanCellValue())
            return "Y";
        else/*from  w  w w . ja  v a  2  s  .  c  o m*/
            return "N";
    case Cell.CELL_TYPE_NUMERIC:
        String result = "";
        int datatype = c.getCellStyle().getDataFormat();

        String formatString = c.getCellStyle().getDataFormatString();
        if (datatype == 174 && "yyyy/mm/dd".equals(formatString)) {
            java.util.Date date = c.getDateCellValue();
            return fmter.format(date);
        } else if (datatype == 49 || datatype == 0) {
            int d = (int) c.getNumericCellValue();
            result = Integer.toString(d);
        } else {
            result = Double.toString(c.getNumericCellValue());
        }

        //return Double.toString(c.getNumericCellValue());
        //System.out.println(" number = "+c.getNumericCellValue()+" *** value ="+twoPlaces.format(c.getNumericCellValue())+"");

        //return twoPlaces.format(c.getNumericCellValue())+"";
        return result;
    case Cell.CELL_TYPE_STRING:
        return c.getStringCellValue();
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_ERROR:
        return "#ERROR" + c.getErrorCellValue();
    case Cell.CELL_TYPE_FORMULA:

        String formulaCellValue;

        if (formulaEvaluator == null) {
            formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
            //formulaEvaluator.setIgnoreFormulaException();
            //System.out.println(formulaEvaluator);
        }

        //formulaEvaluator.evaluateFormulaCell(c);
        //formulaEvaluator.evaluateInCell(c);

        CellValue cv = formulaEvaluator.evaluate(c);

        switch (cv.getCellType()) {
        //switch (formulaEvaluator.evaluateInCell(c).getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            if (cv.getBooleanValue())
                formulaCellValue = "Y";
            else
                formulaCellValue = "F";
            break;
        case Cell.CELL_TYPE_NUMERIC:
            formulaCellValue = Double.toString(cv.getNumberValue());
            break;
        case Cell.CELL_TYPE_STRING:
            formulaCellValue = cv.getStringValue();
            break;
        case Cell.CELL_TYPE_BLANK:
            formulaCellValue = "";
            break;
        case Cell.CELL_TYPE_ERROR:
            formulaCellValue = Byte.toString(cv.getErrorValue());
            break;
        default:
            formulaCellValue = "";
            break;
        }//switch

        return formulaCellValue;
    default:
        return "";
    }//switch

}

From source file:org.obiba.onyx.core.io.support.ExcelReaderSupport.java

License:Open Source License

public static Double getNumericValue(HSSFFormulaEvaluator evaluator, Cell cell) {
    Double rvalue = null;//  w w  w. j  av  a 2  s .c o m

    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        CellValue cellValue = evaluator.evaluate(cell);

        if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            rvalue = cellValue.getNumberValue();
        }
    } else {
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            rvalue = cell.getNumericCellValue();
        }
    }

    if (rvalue == null) {
        throw new IllegalArgumentException("Unexpected cell type");
    }

    return rvalue;
}

From source file:org.openconcerto.erp.importer.DataImporter.java

License:Open Source License

public ArrayTableModel createModelFromXLS(File xlsFile, int sheetNumber) throws IOException {
    final InputStream inputStream = new FileInputStream(xlsFile);
    final POIFSFileSystem fileSystem = new POIFSFileSystem(new BufferedInputStream(inputStream));
    final HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
    final HSSFSheet sheet = workBook.getSheetAt(sheetNumber);
    Iterator<Row> rowsIterator = sheet.rowIterator();
    int columnCount = 0;
    int rowCount = 0;
    while (rowsIterator.hasNext()) {
        Row row = rowsIterator.next();/*ww w  . j  a v  a  2s.c o  m*/
        int i = row.getPhysicalNumberOfCells();
        if (i > columnCount) {
            columnCount = i;
        }
        rowCount++;
    }
    // Extract data
    rowsIterator = sheet.rowIterator();
    int start = 0;
    if (skipFirstLine) {
        start = 1;
        rowsIterator.next();
    }
    final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount - start);
    FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator();

    while (rowsIterator.hasNext()) {
        final Row row = rowsIterator.next();
        final List<Object> rowData = new ArrayList<Object>();
        for (int i = 0; i < columnCount; i++) {
            final Cell cell = row.getCell(i);

            if (cell == null) {
                rowData.add("");
            } else {
                CellValue cellValue = evaluator.evaluate(cell);
                if (cellValue == null) {
                    rowData.add("");
                } else {
                    switch (cellValue.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        rowData.add(Boolean.valueOf(cellValue.getBooleanValue()));
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        rowData.add(Double.valueOf(cellValue.getNumberValue()));
                        break;
                    case Cell.CELL_TYPE_STRING:
                        rowData.add(cellValue.getStringValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        rowData.add(cell.getCellFormula());
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        rowData.add("");
                        break;
                    default:
                        rowData.add(cellValue.getStringValue());
                        break;

                    }
                }
            }
        }

        rows.add(rowData);

    }
    inputStream.close();
    return new ArrayTableModel(rows);

}

From source file:org.openepics.conf.dl.DataStreamExcel.java

License:Open Source License

@Override
public String getColumn(int colNum) throws Exception {
    if (currentRow == null) {
        logger.log(Level.FINE, "current row is NULL or blank");
        return "";
    }// w  w w  .  j  a  v a2 s.  c  o m
    Cell cell = currentRow.getCell(colNum);

    String columnValue = "";
    if (cell != null) {
        // cell.setCellType(Cell.CELL_TYPE_STRING);
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_FORMULA:
            CellValue cellValue = evaluator.evaluate(cell);
            if (cellValue != null) {
                columnValue = cellValue.getStringValue();
                if (columnValue == null) {
                    columnValue = Double.toString(cellValue.getNumberValue());
                }
            }
            break;
        case Cell.CELL_TYPE_NUMERIC:
            columnValue = Double.toString(cell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            columnValue = cell.getStringCellValue();
            break;
        default:
            columnValue = cell.getStringCellValue();
            break;
        }
    }
    logger.log(Level.FINER, "Cell value " + columnValue);
    return columnValue;
}

From source file:org.openepics.discs.ccdb.core.util.ExcelCell.java

License:Open Source License

/**
 * Creating a String from Excel file cell. If cell contains numeric value, this value is cast to String.
 * If there is no value for this cell, null is returned.
 *
 * @param cell the Excel {@link Cell}//from  ww w . ja  va2s  .c om
 * @param workbook the Excel {@link Workbook}
 *
 * @return the {@link String} result
 */
public static String asStringOrNull(@Nullable Cell cell, Workbook workbook) {
    final String stringValue;
    if (cell != null) {
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            final double numericCellValue = cell.getNumericCellValue();
            if (numericCellValue == (int) numericCellValue) {
                stringValue = String.valueOf((int) numericCellValue);
            } else {
                stringValue = String.valueOf(numericCellValue);
            }
        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            stringValue = cell.getStringCellValue() != null ? cell.getStringCellValue() : null;
        } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            stringValue = null;
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            stringValue = String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            final FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            final CellValue cellValue = evaluator.evaluate(cell);
            if (cellValue != null) {
                final String columnValue = cellValue.getStringValue();
                if (columnValue == null) {
                    stringValue = Double.toString(cellValue.getNumberValue());
                } else {
                    stringValue = columnValue;
                }
            } else {
                stringValue = null;
            }
        } else {
            throw new UnhandledCaseException();
        }
    } else {
        stringValue = null;
    }
    return stringValue;
}

From source file:org.paxml.table.excel.ExcelFile.java

License:Open Source License

public Object getCellValue(Cell cell) {
    CellValue cellValue = evaluator.evaluate(cell);
    switch (cellValue.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        return cellValue.getBooleanValue();
    case Cell.CELL_TYPE_NUMERIC:
        return cellValue.getNumberValue();
    case Cell.CELL_TYPE_STRING:
        return cellValue.getStringValue();
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_ERROR:
        return cellValue.getError(cell.getErrorCellValue()).getStringValue();
    // CELL_TYPE_FORMULA will never happen
    case Cell.CELL_TYPE_FORMULA:
        throw new PaxmlRuntimeException("Internal error: invalid case");
    default:/*w  ww  .ja  v  a  2s .  c om*/
        return null;
    }
}

From source file:xqt.adapters.csv.reader.RowBuilder.java

public static String[] createRowArray(Row row, FormulaEvaluator evaluator) {
    String[] cellValues = new String[row.getLastCellNum() + 1];
    for (int cellIndex = 0; cellIndex <= row.getLastCellNum(); cellIndex++) {
        CellValue cellValue = evaluator.evaluate(row.getCell(cellIndex));
        if (cellValue != null) {
            switch (cellValue.getCellType()) {
            // what about the DATE type
            case Cell.CELL_TYPE_NUMERIC:
                //System.out.print(cellValue.getNumberValue() + "\t");
                cellValues[cellIndex] = String.valueOf(cellValue.getNumberValue());
                break;
            case Cell.CELL_TYPE_STRING:
                //System.out.print(cellValue.getStringValue()  + "\t");
                cellValues[cellIndex] = cellValue.getStringValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                //System.out.println(cellValue.getBooleanValue()  + "\t");
                cellValues[cellIndex] = String.valueOf(cellValue.getBooleanValue());
                break;
            case Cell.CELL_TYPE_FORMULA: // should not happen. It is evaluated by the evaluator
            case Cell.CELL_TYPE_BLANK:
            case Cell.CELL_TYPE_ERROR:
                cellValues[cellIndex] = "";
                break;
            }/*w  ww.j a  v a2s .c  o m*/
        }
    }
    return cellValues;
}