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:edu.ucsd.bioeng.coreplugin.tableImport.reader.ExcelAttributeSheetReader.java

License:Open Source License

/**
 * For a given Excell row, convert the cells into String.
 *
 * @param row//from w  w w . j a va2  s. co  m
 * @return
 */
private String[] createElementStringArray(Row row) {
    String[] cells = new String[mapping.getColumnCount()];
    Cell cell = null;

    for (short i = 0; i < mapping.getColumnCount(); i++) {
        cell = row.getCell(i);

        if (cell == null) {
            cells[i] = null;
        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            cells[i] = cell.getRichStringCellValue().getString();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (mapping.getAttributeTypes()[i] == CyAttributes.TYPE_INTEGER) {
                Double dblValue = cell.getNumericCellValue();
                Integer intValue = dblValue.intValue();
                cells[i] = intValue.toString();
            } else {
                cells[i] = Double.toString(cell.getNumericCellValue());
            }
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            cells[i] = Boolean.toString(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            cells[i] = null;
        } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
            cells[i] = null;
            logger.warn("Error found when reading a cell!");
        }
    }

    return cells;
}

---

From source file:edu.ucsd.bioeng.coreplugin.tableImport.reader.ExcelNetworkSheetReader.java

License:Open Source License

/**
 * For a given Excell row, convert the cells into String.
 *
 * @param row/*from w w w  . j  av  a2 s.co  m*/
 * @return
 */
private String[] createElementStringArray(final Row row) {
    String[] cells = new String[nmp.getColumnCount()];
    Cell cell = null;

    for (short i = 0; i < nmp.getColumnCount(); i++) {
        cell = row.getCell(i);

        if (cell == null) {
            cells[i] = null;
        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            cells[i] = cell.getRichStringCellValue().getString();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (nmp.getAttributeTypes()[i] == CyAttributes.TYPE_INTEGER) {
                Double dblValue = cell.getNumericCellValue();
                Integer intValue = dblValue.intValue();
                cells[i] = intValue.toString();
            } else {
                cells[i] = Double.toString(cell.getNumericCellValue());
            }
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            cells[i] = Boolean.toString(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            cells[i] = null;
        } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
            cells[i] = null;
            logger.warn("Error found when reading a cell!");
        }
    }

    return cells;
}

---

From source file:edu.ucsd.bioeng.coreplugin.tableImport.ui.PreviewTablePanel.java

License:Open Source License

private TableModel parseExcel(final URL sourceURL, int size, TableCellRenderer renderer, final Sheet sheet,
        int startLine) throws IOException {

    if (size == -1)
        size = Integer.MAX_VALUE;

    int maxCol = 0;
    final Vector<Object> data = new Vector<Object>();

    int rowCount = 0;
    Row row;//from  w ww .j a v  a2  s  .  c  om

    while (((row = sheet.getRow(rowCount)) != null) && (rowCount < size)) {
        if (rowCount >= startLine) {
            Vector<Object> rowVector = new Vector<Object>();

            if (maxCol < row.getPhysicalNumberOfCells()) {
                maxCol = row.getPhysicalNumberOfCells();
            }

            for (short j = 0; j < maxCol; j++) {
                Cell cell = row.getCell(j);

                if (cell == null) {
                    rowVector.add(null);
                } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    rowVector.add(cell.getRichStringCellValue().getString());
                } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    final Double dblValue = cell.getNumericCellValue();
                    final Integer intValue = dblValue.intValue();

                    if (intValue.doubleValue() == dblValue) {
                        rowVector.add(intValue.toString());
                    } else {
                        rowVector.add(dblValue.toString());
                    }
                } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                    rowVector.add(Boolean.toString(cell.getBooleanCellValue()));
                } else if ((cell.getCellType() == Cell.CELL_TYPE_BLANK)
                        || (cell.getCellType() == Cell.CELL_TYPE_ERROR)) {
                    rowVector.add(null);
                } else {
                    rowVector.add(null);
                }
            }

            data.add(rowVector);
        }

        rowCount++;
    }

    return new DefaultTableModel(data, this.getDefaultColumnNames(maxCol, sourceURL));
}

---

From source file:edu.vt.cs.irwin.etdscraper.retriever.excel.ExcelEtdSource.java

License:Apache License

private String getCellValue(Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue().toString();
        } else {/*from   w  w w.  j a v  a  2s  . co  m*/
            return ((Double) cell.getNumericCellValue()).toString();
        }
    case Cell.CELL_TYPE_BOOLEAN:
        return ((Boolean) cell.getBooleanCellValue()).toString();
    case Cell.CELL_TYPE_BLANK:
        return "";
    }
    throw new UnsupportedOperationException("Don't know how to work with type: " + cell.getCellType());
}

---

From source file:edu.vt.owml.saurav.raininterpolation.debug.NewMain.java

License:Open Source License

/**
 * @param args the command line arguments
 *//*w  w w. j  a  v  a 2  s .c om*/
public static void main(String[] args) {
    try {

        Workbook wb;
        wb = WorkbookFactory.create(NewMain.class.getResourceAsStream("/unit_test.xlsx"));

        // retrieve the named range
        String cellname = "stations";
        int namedCellIdx = wb.getNameIndex(cellname);
        Name aNamedCell = wb.getNameAt(namedCellIdx);
        // retrieve the cell at the named range and test its contents
        AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula());
        CellReference[] crefs = (CellReference[]) aref.getAllReferencedCells();
        int index = 0;
        int columns = 2;
        double[][] stations = new double[(int) crefs.length / columns][2];
        for (CellReference cref : crefs) {
            Sheet s = wb.getSheet(cref.getSheetName());
            Row r = s.getRow(cref.getRow());
            Cell c = r.getCell(cref.getCol());
            System.out.println(c.getNumericCellValue());
            //2 col array
            stations[(int) (index / columns)][index % columns] = c.getNumericCellValue();
            index++;
        }
        printArray(stations);

        //rain
        cellname = "gridpts";
        namedCellIdx = wb.getNameIndex(cellname);
        aNamedCell = wb.getNameAt(namedCellIdx);
        // retrieve the cell at the named range and test its contents
        aref = new AreaReference(aNamedCell.getRefersToFormula());
        crefs = (CellReference[]) aref.getAllReferencedCells();
        index = 0;
        columns = 2;
        double[][] locations = new double[(int) crefs.length / columns][2];
        for (CellReference cref : crefs) {
            Sheet s = wb.getSheet(cref.getSheetName());
            Row r = s.getRow(cref.getRow());
            Cell c = r.getCell(cref.getCol());
            System.out.println(c.getNumericCellValue());
            //2 col array
            locations[(int) (index / columns)][index % columns] = c.getNumericCellValue();
            index++;
        }
        printArray(locations);

        //rain
        cellname = "rainVal";
        namedCellIdx = wb.getNameIndex(cellname);
        aNamedCell = wb.getNameAt(namedCellIdx);
        // retrieve the cell at the named range and test its contents
        aref = new AreaReference(aNamedCell.getRefersToFormula());
        crefs = (CellReference[]) aref.getAllReferencedCells();
        index = 0;
        double[] rainValues = new double[crefs.length];
        for (CellReference cref : crefs) {
            Sheet s = wb.getSheet(cref.getSheetName());
            Row r = s.getRow(cref.getRow());
            Cell c = r.getCell(cref.getCol());
            System.out.println(c.getNumericCellValue());
            //2 col array
            rainValues[index] = c.getNumericCellValue();
            index++;
        }
        printArray(rainValues);

        //vals
        cellname = "estimates";
        namedCellIdx = wb.getNameIndex(cellname);
        aNamedCell = wb.getNameAt(namedCellIdx);
        // retrieve the cell at the named range and test its contents
        aref = new AreaReference(aNamedCell.getRefersToFormula());
        crefs = (CellReference[]) aref.getAllReferencedCells();
        index = 0;
        double[] vals = new double[crefs.length];
        for (CellReference cref : crefs) {
            Sheet s = wb.getSheet(cref.getSheetName());
            Row r = s.getRow(cref.getRow());
            Cell c = r.getCell(cref.getCol());
            System.out.println(c.getNumericCellValue());
            //2 col array
            vals[index] = c.getNumericCellValue();
            index++;
        }
        printArray(vals);

        //distances
        cellname = "distances";
        namedCellIdx = wb.getNameIndex(cellname);
        aNamedCell = wb.getNameAt(namedCellIdx);
        // retrieve the cell at the named range and test its contents
        aref = new AreaReference(aNamedCell.getRefersToFormula());
        crefs = (CellReference[]) aref.getAllReferencedCells();
        index = 0;
        columns = stations.length;
        double[] d = new double[stations.length];
        List<double[]> distances = new ArrayList();
        for (CellReference cref : crefs) {

            Sheet s = wb.getSheet(cref.getSheetName());
            Row r = s.getRow(cref.getRow());
            Cell c = r.getCell(cref.getCol());
            System.out.println(c.getNumericCellValue());
            d[index % columns] = c.getNumericCellValue();
            if (index % columns == columns - 1) {
                distances.add(d);
                d = new double[stations.length];
            }
            index++;

        }
        printArray(distances);

        IDWInterpolator idw = new IDWInterpolator();
        // printArray(idw.getDistances(stations, locations));

    } catch (FileNotFoundException ex) {
        Logger.getLogger(NewMain.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException | InvalidFormatException ex) {
        Logger.getLogger(NewMain.class.getName()).log(Level.SEVERE, null, ex);
    }
}

---

From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java

License:Apache License

/**
 * [Flow #-7]    :  ?  ?? //from  w w  w. j  a va  2s .  c om
 */
@Test
public void testUseTemplate1() throws Exception {

    StringBuffer sb = new StringBuffer();
    StringBuffer sbResult = new StringBuffer();

    sb.append(fileLocation).append("/template/").append("template.xls");
    sbResult.append(fileLocation).append("/").append("testUseTemplate1.xls");

    Object[][] sample_data = { { "Yegor Kozlov", "YK", 5.0, 8.0, 10.0, 5.0, 5.0, 7.0, 6.0 },
            { "Gisella Bronzetti", "GB", 4.0, 3.0, 1.0, 3.5, null, null, 4.0 }, };

    try {

        Workbook wb = excelService.loadExcelTemplate(sb.toString());
        Sheet sheet = wb.getSheetAt(0);

        // set data
        for (int i = 0; i < sample_data.length; i++) {
            Row row = sheet.getRow(2 + i);
            for (int j = 0; j < sample_data[i].length; j++) {
                if (sample_data[i][j] == null)
                    continue;

                Cell cell = row.getCell(j);

                if (sample_data[i][j] instanceof String) {
                    cell.setCellValue(new HSSFRichTextString((String) sample_data[i][j]));
                } else {
                    cell.setCellValue((Double) sample_data[i][j]);
                }
            }
        }

        // ? 
        sheet.setForceFormulaRecalculation(true);

        excelService.createWorkbook(wb, sbResult.toString());

        //////////////////////////////////////////////////////////////////////////
        // ?
        Workbook wbT = excelService.loadWorkbook(sbResult.toString());
        Sheet sheetT = wbT.getSheetAt(0);

        for (int i = 0; i < sample_data.length; i++) {
            Row row = sheetT.getRow(2 + i);
            for (int j = 0; j < sample_data[i].length; j++) {
                Cell cell = row.getCell(j);

                LOGGER.debug("sample_data[i][j] : {}", sample_data[i][j]);

                if (sample_data[i][j] == null) {
                    assertEquals(cell.getCellType(), Cell.CELL_TYPE_BLANK);
                } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    assertEquals((Double) sample_data[i][j], Double.valueOf(cell.getNumericCellValue()));
                } else {
                    assertEquals((String) sample_data[i][j], cell.getRichStringCellValue().getString());
                }
            }
        }

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testUseTemplate end....");
    }
}

---

From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java

License:Apache License

/**
 * [Flow #-7-1]    :  ?  ?? /* w  ww .  j av a2  s.  co m*/
 * jXLS 
 */
@Test
public void testUseTemplate2() throws Exception {
    StringBuffer sb = new StringBuffer();
    StringBuffer sbResult = new StringBuffer();

    sb.append(fileLocation).append("/template/").append("template2.xls");
    sbResult.append(fileLocation).append("/").append("testUseTemplate2.xls");

    try {
        //  ? .
        List<PersonHourVO> persons = new ArrayList<PersonHourVO>();
        PersonHourVO person = new PersonHourVO();
        person.setName("Yegor Kozlov");
        person.setId("YK");
        person.setMon(5.0);
        person.setTue(8.0);
        person.setWed(10.0);
        person.setThu(5.0);
        person.setFri(5.0);
        person.setSat(7.0);
        person.setSun(6.0);

        persons.add(person);

        PersonHourVO person1 = new PersonHourVO();
        person1.setName("Gisella Bronzetti");
        person1.setId("GB");
        person1.setMon(4.0);
        person1.setTue(3.0);
        person1.setWed(1.0);
        person1.setThu(3.5);
        person1.setSun(4.0);

        persons.add(person1);

        Map<String, Object> beans = new HashMap<String, Object>();
        beans.put("persons", persons);
        XLSTransformer transformer = new XLSTransformer();

        transformer.transformXLS(sb.toString(), beans, sbResult.toString());

        //////////////////////////////////////////////////////////////////////////
        // ?
        Workbook wb = excelService.loadWorkbook(sbResult.toString());
        Sheet sheet = wb.getSheetAt(0);

        Double[][] value = { { 5.0, 8.0, 10.0, 5.0, 5.0, 7.0, 6.0 }, { 4.0, 3.0, 1.0, 3.5, null, null, 4.0 } };

        for (int i = 0; i < 2; i++) {
            Row row2 = sheet.getRow(i + 2);

            for (int j = 0; j < 7; j++) {
                Cell cellValue = row2.getCell((j + 2));
                if (cellValue.getCellType() == Cell.CELL_TYPE_BLANK)
                    continue;
                LOGGER.debug("cellTot.getNumericCellValue() : {}", cellValue.getNumericCellValue());
                assertEquals(value[i][j], Double.valueOf(cellValue.getNumericCellValue()));
            }
        }

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testUseTemplate end....");
    }
}

---

From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-7]    :  ?  ?? /*from  w ww  .  ja va2 s . c  om*/
 */
@Test
public void testUseTemplate1() throws Exception {

    StringBuffer sb = new StringBuffer();
    StringBuffer sbResult = new StringBuffer();

    sb.append(fileLocation).append("/template/").append("template.xlsx");
    sbResult.append(fileLocation).append("/").append("testUseTemplate1.xlsx");

    Object[][] sample_data = { { "Yegor Kozlov", "YK", 5.0, 8.0, 10.0, 5.0, 5.0, 7.0, 6.0 },
            { "Gisella Bronzetti", "GB", 4.0, 3.0, 1.0, 3.5, null, null, 4.0 }, };

    try {

        XSSFWorkbook wb = null;
        wb = excelService.loadExcelTemplate(sb.toString(), wb);
        Sheet sheet = wb.getSheetAt(0);

        // set data
        for (int i = 0; i < sample_data.length; i++) {
            Row row = sheet.getRow(2 + i);
            for (int j = 0; j < sample_data[i].length; j++) {
                if (sample_data[i][j] == null)
                    continue;

                Cell cell = row.getCell(j);

                if (sample_data[i][j] instanceof String) {
                    cell.setCellValue(new XSSFRichTextString((String) sample_data[i][j]));
                } else {
                    cell.setCellValue((Double) sample_data[i][j]);
                }
            }
        }

        // ? 
        sheet.setForceFormulaRecalculation(true);

        excelService.createWorkbook(wb, sbResult.toString());

        //////////////////////////////////////////////////////////////////////////
        // ?
        Workbook wbT = excelService.loadWorkbook(sbResult.toString(), new XSSFWorkbook());
        Sheet sheetT = wbT.getSheetAt(0);

        for (int i = 0; i < sample_data.length; i++) {
            Row row = sheetT.getRow(2 + i);
            for (int j = 0; j < sample_data[i].length; j++) {
                Cell cell = row.getCell(j);

                LOGGER.debug("sample_data[i][j] : {}", sample_data[i][j]);

                if (sample_data[i][j] == null) {
                    assertEquals(cell.getCellType(), XSSFCell.CELL_TYPE_BLANK);
                } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                    assertEquals((Double) sample_data[i][j], Double.valueOf(cell.getNumericCellValue()));
                } else {
                    assertEquals((String) sample_data[i][j], cell.getRichStringCellValue().getString());
                }
            }
        }

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testUseTemplate end....");
    }
}

---

From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-7-1]    :  ?  ?? //from   w  w  w  .ja v  a  2  s  . com
 * jXLS 
 */
@Test
public void testUseTemplate2() throws Exception {
    StringBuffer sb = new StringBuffer();
    StringBuffer sbResult = new StringBuffer();

    sb.append(fileLocation).append("/template/").append("template2.xlsx");
    sbResult.append(fileLocation).append("/").append("testUseTemplate2.xlsx");

    try {
        //  ? .
        List<PersonHourVO> persons = new ArrayList<PersonHourVO>();
        PersonHourVO person = new PersonHourVO();
        person.setName("Yegor Kozlov");
        person.setId("YK");
        person.setMon(5.0);
        person.setTue(8.0);
        person.setWed(10.0);
        person.setThu(5.0);
        person.setFri(5.0);
        person.setSat(7.0);
        person.setSun(6.0);

        persons.add(person);

        PersonHourVO person1 = new PersonHourVO();
        person1.setName("Gisella Bronzetti");
        person1.setId("GB");
        person1.setMon(4.0);
        person1.setTue(3.0);
        person1.setWed(1.0);
        person1.setThu(3.5);
        person1.setSun(4.0);

        persons.add(person1);

        Map<String, Object> beans = new HashMap<String, Object>();
        beans.put("persons", persons);
        XLSTransformer transformer = new XLSTransformer();

        transformer.transformXLS(sb.toString(), beans, sbResult.toString());

        //////////////////////////////////////////////////////////////////////////
        // ?
        Workbook wb = excelService.loadWorkbook(sbResult.toString(), new XSSFWorkbook());
        Sheet sheet = wb.getSheetAt(0);

        Double[][] value = { { 5.0, 8.0, 10.0, 5.0, 5.0, 7.0, 6.0 }, { 4.0, 3.0, 1.0, 3.5, null, null, 4.0 } };

        for (int i = 0; i < 2; i++) {
            Row row2 = sheet.getRow(i + 2);

            for (int j = 0; j < 7; j++) {
                Cell cellValue = row2.getCell((j + 2));
                if (cellValue.getCellType() == Cell.CELL_TYPE_BLANK)
                    continue;
                LOGGER.debug("cellTot.getNumericCellValue() : {}", cellValue.getNumericCellValue());
                assertEquals(value[i][j], Double.valueOf(cellValue.getNumericCellValue()));
            }
        }

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testUseTemplate end....");
    }
}

---

From source file:egovframework.rte.fdl.excel.util.EgovExcelUtil.java

License:Apache License

/**
 * ? ? String   ./*from  w  w  w . jav  a  2  s . c o  m*/
 * 
 * @param cell <code>Cell</code>
 * @return  
 */
public static String getValue(Cell cell) {

    String result = "";

    if (null == cell || cell.equals(null)) {
        return "";
    }

    if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
        LOGGER.debug("### Cell.CELL_TYPE_BOOLEAN : {}", Cell.CELL_TYPE_BOOLEAN);
        result = String.valueOf(cell.getBooleanCellValue());

    } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
        LOGGER.debug("### Cell.CELL_TYPE_ERROR : {}", Cell.CELL_TYPE_ERROR);
        // byte errorValue =
        // cell.getErrorCellValue();

    } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        LOGGER.debug("### Cell.CELL_TYPE_FORMULA : {}", Cell.CELL_TYPE_FORMULA);

        String stringValue = null;
        String longValue = null;

        try {
            stringValue = cell.getRichStringCellValue().getString();
            longValue = doubleToString(cell.getNumericCellValue());
        } catch (Exception e) {
            LOGGER.debug("{}", e);
        }

        if (stringValue != null) {
            result = stringValue;
        } else if (longValue != null) {
            result = longValue;
        } else {
            result = cell.getCellFormula();
        }

    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        LOGGER.debug("### Cell.CELL_TYPE_NUMERIC : {}", Cell.CELL_TYPE_NUMERIC);

        result = DateUtil.isCellDateFormatted(cell)
                ? EgovDateUtil.toString(cell.getDateCellValue(), "yyyy/MM/dd", null)
                : doubleToString(cell.getNumericCellValue());

    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        LOGGER.debug("### Cell.CELL_TYPE_STRING : {}", Cell.CELL_TYPE_STRING);
        result = cell.getRichStringCellValue().getString();

    } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        LOGGER.debug("### Cell.CELL_TYPE_BLANK : {}", Cell.CELL_TYPE_BLANK);
    }

    return result;
}

---

• «
• 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
• »