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:at.htlpinkafeld.beans.BenutzerkontoBean.java

/**
 * Method used to load stuff from predefined Excel not currently in use
 *///ww w .  j  ava  2  s.  c o m
public void loadFromExcel(ActionEvent event) throws FileNotFoundException, IOException, ParserException {

    if (excel != null) {

        FacesContext.getCurrentInstance().addMessage(null,
                new FacesMessage("Successful", excel.getFileName() + " successfully uploaded!"));

        XSSFWorkbook workbook = new XSSFWorkbook(excel.getInputstream());

        for (int i = 1; i <= 12; i++) {
            int min = 5;
            LocalDate date = LocalDate.of(2016, i, 1);
            int max = min + date.lengthOfMonth() - 1;

            XSSFSheet sheet = workbook.getSheetAt(i);

            for (int j = min; j <= max; j++) {
                Row row = sheet.getRow(j);
                LocalDateTime start = null;
                LocalDateTime end = null;

                LocalDate day = date.withDayOfMonth((int) row.getCell(1).getNumericCellValue());

                //                DataFormatter formatter = new DataFormatter();
                //                System.out.println(formatter.formatCellValue(row.getCell(2)));
                FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

                Cell soll = row.getCell(5);
                Cell ist = row.getCell(6);

                CellValue sollValue = null;
                CellValue istValue = null;

                if (soll != null && ist != null) {
                    sollValue = evaluator.evaluate(soll);
                    istValue = evaluator.evaluate(ist);
                }

                if (sollValue != null && istValue != null) {
                    double dif = sollValue.getNumberValue() - istValue.getNumberValue();

                    if (istValue.getNumberValue() != 0.0) {

                        Cell urlaub = row.getCell(10);

                        if (urlaub != null && urlaub.getCellType() != Cell.CELL_TYPE_BLANK
                                && urlaub.getNumericCellValue() != 1.0) {

                            Cell cell = row.getCell(2);

                            //for endtime = row 2
                            if (cell != null) {
                                CellValue cellValue = evaluator.evaluate(cell);
                                if (cellValue != null) {
                                    double time = cellValue.getNumberValue() * 24;

                                    String time2;
                                    DecimalFormat df = new DecimalFormat("00.00");
                                    time2 = df.format(time);
                                    time2 = time2.replace(',', ':');
                                    LocalTime localtime = LocalTime.parse(time2);
                                    start = LocalDateTime.of(day, localtime);
                                }
                            }
                            cell = row.getCell(3);

                            //for endtime = row 3
                            if (cell != null) {
                                CellValue cellValue = evaluator.evaluate(cell);
                                if (cellValue != null) {
                                    double time = cellValue.getNumberValue() * 24;

                                    String time2;
                                    DecimalFormat df = new DecimalFormat("00.00");
                                    time2 = df.format(time);
                                    time2 = time2.replace(',', ':');
                                    LocalTime localtime = LocalTime.parse(time2);
                                    end = LocalDateTime.of(day, localtime);
                                }
                            }

                            int breaktime = 0;
                            cell = row.getCell(4);
                            if (cell != null) {
                                CellValue cellValue = evaluator.evaluate(cell);
                                if (cellValue != null) {
                                    double tempbreaktime = cellValue.getNumberValue() * 24 * 60;
                                    breaktime = (int) tempbreaktime;
                                }
                            }

                            String bemerkung = "";
                            Cell comment = row.getCell(11);
                            if (comment != null) {
                                CellValue value = evaluator.evaluate(comment);

                                if (value != null) {
                                    bemerkung = value.formatAsString();
                                    double d;
                                    try {
                                        d = Double.valueOf(bemerkung);
                                        if (BigDecimal.valueOf(d).scale() > 2) {
                                            d = d * 24 * 60;
                                            LocalTime lt = LocalTime.MIN.plusMinutes((int) (d + 0.5));
                                            bemerkung = lt.format(DateTimeFormatter.ofPattern("HH:mm"));
                                        }
                                    } catch (NumberFormatException e) {
                                        //Value is not castable to double and will be ignored -> best case scenario
                                    }
                                }
                            }

                            if (start != null && end != null) {
                                WorkTime worktime = new WorkTime(user, start, end, breaktime, bemerkung, "");
                                IstZeitService.addIstTime(worktime);

                                if (dif > 0.0) {
                                    LocalDateTime absenceend = end.plusMinutes((int) ((dif * 24 * 60) + 0.5));
                                    Absence a = new Absence(user, AbsenceTypeNew.TIME_COMPENSATION, end,
                                            absenceend, bemerkung);
                                    a.setAcknowledged(true);
                                    AbsenceService.insertAbsence(a);
                                }
                            }
                        } else if (urlaub != null && urlaub.getCellType() != Cell.CELL_TYPE_BLANK
                                && urlaub.getNumericCellValue() == 1.0) {

                            start = LocalDateTime.of(day, LocalTime.MIN);
                            end = start;

                            Absence a = new Absence(user, AbsenceTypeNew.HOLIDAY, start, end);
                            a.setAcknowledged(true);
                            AbsenceService.insertAbsence(a);
                        }
                    } else {
                        Cell cell = row.getCell(2);

                        //for endtime = row 2
                        if (cell != null) {
                            CellValue cellValue = evaluator.evaluate(cell);
                            if (cellValue != null) {
                                double time = cellValue.getNumberValue() * 24;

                                String time2;
                                DecimalFormat df = new DecimalFormat("00.00");
                                time2 = df.format(time);
                                time2 = time2.replace(',', ':');
                                LocalTime localtime = LocalTime.parse(time2);
                                start = LocalDateTime.of(day, localtime);
                            }
                        }
                        cell = row.getCell(3);

                        //for endtime = row 3
                        if (cell != null) {
                            CellValue cellValue = evaluator.evaluate(cell);
                            if (cellValue != null) {
                                double time = cellValue.getNumberValue() * 24;

                                String time2;
                                DecimalFormat df = new DecimalFormat("00.00");
                                time2 = df.format(time);
                                time2 = time2.replace(',', ':');
                                LocalTime localtime = LocalTime.parse(time2);
                                end = LocalDateTime.of(day, localtime);
                            }
                        }
                        String bemerkung = "";
                        Cell comment = row.getCell(11);
                        if (comment != null) {
                            CellValue value = evaluator.evaluate(comment);
                            if (value != null) {
                                bemerkung = value.formatAsString();
                            }
                        }
                        Absence a = new Absence(user, AbsenceTypeNew.TIME_COMPENSATION, start, end, bemerkung);
                        a.setAcknowledged(true);
                        AbsenceService.insertAbsence(a);
                    }
                }
            }
        }
    }
}

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 a  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 ww w .j  ava 2  s .  com
 * @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 ww  w. j a  va2  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 "";
    }//  w ww . j  a  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: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 v  a  2s  .  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;/*  w w w.j  a v a  2 s .co m*/
        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.helger.poi.excel.ExcelReadUtilsTest.java

License:Apache License

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

License:Apache License

/**
 * Evaluates and returns cell contained value.
 *
 * @param cell//from www  . j ava2  s .  c  o 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 w w. jav  a  2 s. c  o 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;
}