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

Introduction

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

Prototype

public byte getErrorValue() 

Source Link

Usage

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;//ww  w  .j a  va  2s. c  o  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.jmc.jfxxlsdiff.util.POIXlsUtil.java

private static Object getFormulaValue(Cell cell) {
    Object cv = null;//w  w  w .  j  a  va 2  s .c om

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

---

From source file:com.miraisolutions.xlconnect.data.DefaultColumnBuilder.java

License:Open Source License

protected void handleCell(Cell c, CellValue cv) {
    String msg;//  ww w  .  j  a va  2  s . co  m
    // Determine (evaluated) cell data type
    switch (cv.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        addMissing();
        return;
    case Cell.CELL_TYPE_BOOLEAN:
        addValue(c, cv, DataType.Boolean);
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(c))
            addValue(c, cv, DataType.DateTime);
        else {
            boolean missing = false;
            for (int i = 0; i < missingValueNumbers.length; i++) {
                if (cv.getNumberValue() == missingValueNumbers[i]) {
                    missing = true;
                    break;
                }
            }
            if (missing)
                addMissing();
            else
                addValue(c, cv, DataType.Numeric);
        }
        break;
    case Cell.CELL_TYPE_STRING:
        boolean missing = false;
        for (int i = 0; i < missingValueStrings.length; i++) {
            if (cv.getStringValue() == null || cv.getStringValue().equals(missingValueStrings[i])) {
                missing = true;
                break;
            }
        }
        if (missing)
            addMissing();
        else
            addValue(c, cv, DataType.String);
        break;
    case Cell.CELL_TYPE_FORMULA:
        msg = "Formula detected in already evaluated cell " + CellUtils.formatAsString(c) + "!";
        cellError(msg);
        break;
    case Cell.CELL_TYPE_ERROR:
        msg = "Error detected in cell " + CellUtils.formatAsString(c) + " - "
                + CellUtils.getErrorMessage(cv.getErrorValue());
        cellError(msg);
        break;
    default:
        msg = "Unexpected cell type detected for cell " + CellUtils.formatAsString(c) + "!";
        cellError(msg);
    }
}

---

From source file:com.miraisolutions.xlconnect.data.FastColumnBuilder.java

License:Open Source License

protected void handleCell(Cell c, CellValue cv) {
    String msg;/*from w ww.j  a v a2  s  . c o m*/
    // Determine (evaluated) cell data type
    switch (cv.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        addMissing();
        return;
    case Cell.CELL_TYPE_BOOLEAN:
        addValue(c, cv, DataType.Boolean);
        break;
    case Cell.CELL_TYPE_NUMERIC:
        addValue(c, cv, DataType.Numeric);
        break;
    case Cell.CELL_TYPE_STRING:
        addValue(c, cv, DataType.String);
        break;
    case Cell.CELL_TYPE_FORMULA:
        msg = "Formula detected in already evaluated cell " + CellUtils.formatAsString(c) + "!";
        cellError(msg);
        break;
    case Cell.CELL_TYPE_ERROR:
        msg = "Error detected in cell " + CellUtils.formatAsString(c) + " - "
                + CellUtils.getErrorMessage(cv.getErrorValue());
        cellError(msg);
        break;
    default:
        msg = "Unexpected cell type detected for cell " + CellUtils.formatAsString(c) + "!";
        cellError(msg);
    }
}

---

From source file:csv.impl.ExcelReader.java

License:Open Source License

/**
 * Returns the evaluated cell content.//  ww  w .  j  a  v  a 2  s. c o  m
 * This assumes the cell contains a formula.
 * @param cell cell to evaluate
 * @return cell value
 */
public Object evaluateCellValue(Cell cell) {
    FormulaEvaluator evaluator = getFormulaEvaluator();
    CellValue value = evaluator.evaluate(cell);
    switch (value.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return value.getStringValue();
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return DateUtil.getJavaDate(value.getNumberValue());
        } else {
            return value.getNumberValue();
        }
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return value.getBooleanValue();
    case Cell.CELL_TYPE_ERROR:
        return value.getErrorValue();
    default:
        System.out.println("type=" + cell.getCellType());
    }
    return cell.getCellFormula();
}

---

From source file:mw.sqlitetool.ExcelHelper.java

public Object getCellValue(Cell cell) {
    if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) {
        return "";
    } else if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
        return cell.getBooleanCellValue();
    } else if (cell.getCellType() == cell.CELL_TYPE_ERROR) {
        return cell.getErrorCellValue();
    } else if (cell.getCellType() == cell.CELL_TYPE_FORMULA) {
        FormulaEvaluator evaluator = _workbook.getCreationHelper().createFormulaEvaluator();
        CellValue val = evaluator.evaluate(cell);
        if (val.getCellType() == cell.CELL_TYPE_BOOLEAN) {
            return val.getBooleanValue();
        } else if (val.getCellType() == cell.CELL_TYPE_NUMERIC) {
            return val.getNumberValue();
        } else if (val.getCellType() == cell.CELL_TYPE_STRING) {
            return val.getStringValue();
        } else if (val.getCellType() == cell.CELL_TYPE_ERROR) {
            return val.getErrorValue();
        }/* w w  w . j  ava2  s.  co m*/
    } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        } else {
            return cell.getNumericCellValue();
        }
    } else if (cell.getCellType() == cell.CELL_TYPE_STRING) {
        return cell.getStringCellValue();
    }
    return "";
}

---

From source file:opn.greenwebs.FXMLDocumentController.java

private Object getCellData(File fSheet, int row, int coll) {
    try {//from  ww w. j av  a2 s  .co m
        XSSFWorkbook book = new XSSFWorkbook(fSheet);
        FormulaEvaluator eval = book.getCreationHelper().createFormulaEvaluator();
        XSSFSheet xSheet = book.getSheet("Digital Version");
        Cell celled = xSheet.getRow(row).getCell(coll);
        if (celled != null) {
            CellValue cellval = eval.evaluate(celled);
            if (cellval == null) {
                //System.out.println("cellval is null at line 918 " + fSheet.getAbsolutePath() + " " + row + " " + coll);
                return "";
            } else {
                switch (cellval.getCellType()) {
                case Cell.CELL_TYPE_BLANK:
                    logger.info("got a blank");
                    return "";
                case Cell.CELL_TYPE_BOOLEAN:
                    logger.info("got a boolean");
                    return cellval.getBooleanValue();
                case Cell.CELL_TYPE_ERROR:
                    return cellval.getErrorValue();
                case Cell.CELL_TYPE_FORMULA:
                    return cellval.getStringValue();
                case Cell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(celled)) {
                        return HSSFDateUtil.getJavaDate(cellval.getNumberValue());
                    } else {
                        return cellval.getNumberValue();
                    }
                case Cell.CELL_TYPE_STRING:
                    return cellval.getStringValue();
                default:
                    return "";
                }
            }
        }
    } catch (IOException | InvalidFormatException ex) {
        Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex);
    }
    return "";
}

---

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/*  w w  w  . j a  v  a2 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

}

---