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

List of usage examples for org.apache.poi.ss.usermodel Cell getErrorCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getErrorCellValue

Introduction

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

Prototype

byte getErrorCellValue();

Source Link

Document

Get the value of the cell as an error code.

Usage

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();
        }/*from ww  w  . j av  a  2 s  .  c o  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:net.sf.excelutils.WorkbookUtils.java

License:Apache License

public static void shiftCell(Sheet sheet, Row row, Cell beginCell, int shift, int rowCount) {

    if (shift == 0)
        return;//w  w  w  . ja  v a 2  s  . co m

    // get the from & to row
    int fromRow = row.getRowNum();
    int toRow = row.getRowNum() + rowCount - 1;
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress r = sheet.getMergedRegion(i);
        if (r.getFirstRow() == row.getRowNum()) {
            if (r.getLastRow() > toRow) {
                toRow = r.getLastRow();
            }
            if (r.getFirstRow() < fromRow) {
                fromRow = r.getFirstRow();
            }
        }
    }

    for (int rownum = fromRow; rownum <= toRow; rownum++) {
        Row curRow = WorkbookUtils.getRow(rownum, sheet);
        int lastCellNum = curRow.getLastCellNum();
        for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) {
            Cell fromCell = WorkbookUtils.getCell(curRow, cellpos);
            Cell toCell = WorkbookUtils.getCell(curRow, cellpos + shift);
            toCell.setCellType(fromCell.getCellType());
            toCell.setCellStyle(fromCell.getCellStyle());
            switch (fromCell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                toCell.setCellValue(fromCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                toCell.setCellFormula(fromCell.getCellFormula());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                toCell.setCellValue(fromCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                toCell.setCellValue(fromCell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                toCell.setCellErrorValue(fromCell.getErrorCellValue());
                break;
            }
            fromCell.setCellValue("");
            fromCell.setCellType(Cell.CELL_TYPE_BLANK);
            // Workbook wb = new Workbook();
            // CellStyle style = wb.createCellStyle();
            // fromCell.setCellStyle(style);
        }

        // process merged region
        for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) {
            Cell fromCell = WorkbookUtils.getCell(curRow, cellpos);

            List shiftedRegions = new ArrayList();
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress r = sheet.getMergedRegion(i);
                if (r.getFirstRow() == curRow.getRowNum() && r.getFirstColumn() == fromCell.getColumnIndex()) {
                    r.setFirstColumn((short) (r.getFirstColumn() + shift));
                    r.setLastColumn((short) (r.getLastColumn() + shift));
                    // have to remove/add it back
                    shiftedRegions.add(r);
                    sheet.removeMergedRegion(i);
                    // we have to back up now since we removed one
                    i = i - 1;
                }
            }

            // readd so it doesn't get shifted again
            Iterator iterator = shiftedRegions.iterator();
            while (iterator.hasNext()) {
                CellRangeAddress region = (CellRangeAddress) iterator.next();
                sheet.addMergedRegion(region);
            }
        }
    }
}

From source file:org.alanwilliamson.openbd.plugin.spreadsheet.SheetUtility.java

License:Open Source License

public static void cloneCell(Cell cNew, Cell cOld) {
    cNew.setCellComment(cOld.getCellComment());
    cNew.setCellStyle(cOld.getCellStyle());
    cNew.setCellType(cOld.getCellType());

    switch (cNew.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN: {
        cNew.setCellValue(cOld.getBooleanCellValue());
        break;//  w  w  w .  j a  v  a  2s .  c  om
    }
    case Cell.CELL_TYPE_NUMERIC: {
        cNew.setCellValue(cOld.getNumericCellValue());
        break;
    }
    case Cell.CELL_TYPE_STRING: {
        cNew.setCellValue(cOld.getStringCellValue());
        break;
    }
    case Cell.CELL_TYPE_ERROR: {
        cNew.setCellValue(cOld.getErrorCellValue());
        break;
    }
    case Cell.CELL_TYPE_FORMULA: {
        cNew.setCellFormula(cOld.getCellFormula());
        break;
    }
    case Cell.CELL_TYPE_BLANK: {
        cNew.setCellValue(cOld.getNumericCellValue());
        break;
    }

    }

}

From source file:org.apache.metamodel.excel.ExcelUtils.java

License:Apache License

public static String getCellValue(Workbook wb, Cell cell) {
    if (cell == null) {
        return null;
    }//from   w ww . ja v a 2 s.  c  o  m

    final String cellCoordinate = "(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")";

    final String result;

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        result = null;
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        result = Boolean.toString(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_ERROR:
        String errorResult;
        try {
            byte errorCode = cell.getErrorCellValue();
            FormulaError formulaError = FormulaError.forInt(errorCode);
            errorResult = formulaError.getString();
        } catch (RuntimeException e) {
            logger.debug("Getting error code for {} failed!: {}", cellCoordinate, e.getMessage());
            if (cell instanceof XSSFCell) {
                // hack to get error string, which is available
                String value = ((XSSFCell) cell).getErrorCellString();
                errorResult = value;
            } else {
                logger.error("Couldn't handle unexpected error scenario in cell: " + cellCoordinate, e);
                throw e;
            }
        }
        result = errorResult;
        break;
    case Cell.CELL_TYPE_FORMULA:
        // result = cell.getCellFormula();
        result = getFormulaCellValue(wb, cell);
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            Date date = cell.getDateCellValue();
            if (date == null) {
                result = null;
            } else {
                result = DateUtils.createDateFormat().format(date);
            }
        } else {
            // TODO: Consider not formatting it, but simple using
            // Double.toString(...)
            result = _numberFormat.format(cell.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_STRING:
        result = cell.getRichStringCellValue().getString();
        break;
    default:
        throw new IllegalStateException("Unknown cell type: " + cell.getCellType());
    }

    logger.debug("cell {} resolved to value: {}", cellCoordinate, result);

    return result;
}

From source file:org.bbreak.excella.core.test.util.TestUtil.java

License:Open Source License

private static Object getCellValue(Cell cell) {
    String value = null;/*from  w  w w .ja va2 s.  com*/

    if (cell != null) {
        switch (cell.getCellTypeEnum()) {
        case BLANK:
            value = cell.getStringCellValue();
            break;
        case BOOLEAN:
            value = String.valueOf(cell.getBooleanCellValue());
            break;
        case ERROR:
            value = String.valueOf(cell.getErrorCellValue());
            break;
        case NUMERIC:
            value = String.valueOf(cell.getNumericCellValue());
            break;
        case STRING:
            value = cell.getStringCellValue();
            break;
        case FORMULA:
            value = cell.getCellFormula();
        default:
            value = "";
        }
    }
    return value;
}

From source file:org.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * ??? ??????<br>//from w w  w  .j a  v  a  2s  .c  o  m
 * <br>
 * ?[CELL_TYPE_ERROR]??<br>
 * xls? ?HSSFErrorConstants?<br>
 * xlsx? Excel??ex.#DIV/0!?#N/A?#REF!
 * 
 * @param cell 
 * @return 
 */
public static Object getCellValue(Cell cell) {
    Object value = null;

    if (cell != null) {
        switch (cell.getCellTypeEnum()) {
        case BLANK:
            break;
        case BOOLEAN:
            value = cell.getBooleanCellValue();
            break;
        case ERROR:
            value = cell.getErrorCellValue();
            break;
        case NUMERIC:
            // ??
            if (isCellDateFormatted(cell)) {
                value = cell.getDateCellValue();
            } else {
                value = cell.getNumericCellValue();
            }
            break;
        case STRING:
            value = cell.getStringCellValue();
            break;
        case FORMULA:
            FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper()
                    .createFormulaEvaluator();
            // ?
            CellValue cellValue = evaluator.evaluate(cell);
            CellType cellType = cellValue.getCellTypeEnum();
            // ????
            switch (cellType) {
            case BLANK:
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case ERROR:
                if (cell instanceof XSSFCell) {
                    // XSSF??????
                    XSSFCell xssfCell = (XSSFCell) cell;
                    CTCell ctCell = xssfCell.getCTCell();
                    value = ctCell.getV();
                } else if (cell instanceof HSSFCell) {
                    // HSSF??????
                    value = cell.getErrorCellValue();
                }
                break;
            case NUMERIC:
                // ??
                if (isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                } else {
                    value = cell.getNumericCellValue();
                }
                break;
            case STRING:
                value = cell.getStringCellValue();
                break;
            default:
                break;
            }
        default:
            break;
        }
    }
    return value;
}

From source file:org.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * ?// ww  w .j a  v a2s  .  c  om
 * 
 * @param fromCell 
 * @param toCell 
 */
public static void copyCell(Cell fromCell, Cell toCell) {

    if (fromCell != null) {

        // 
        CellType cellType = fromCell.getCellTypeEnum();
        switch (cellType) {
        case BLANK:
            break;
        case FORMULA:
            String cellFormula = fromCell.getCellFormula();
            toCell.setCellFormula(cellFormula);
            break;
        case BOOLEAN:
            toCell.setCellValue(fromCell.getBooleanCellValue());
            break;
        case ERROR:
            toCell.setCellErrorValue(fromCell.getErrorCellValue());
            break;
        case NUMERIC:
            toCell.setCellValue(fromCell.getNumericCellValue());
            break;
        case STRING:
            toCell.setCellValue(fromCell.getRichStringCellValue());
            break;
        default:
        }

        // 
        if (fromCell.getCellStyle() != null
                && fromCell.getSheet().getWorkbook().equals(toCell.getSheet().getWorkbook())) {
            toCell.setCellStyle(fromCell.getCellStyle());
        }

        // 
        if (fromCell.getCellComment() != null) {
            toCell.setCellComment(fromCell.getCellComment());
        }
    }
}

From source file:org.bbreak.excella.reports.ReportsTestUtil.java

License:Open Source License

/**
 * ?????// w  w  w  .j av a  2  s.  c om
 * 
 * @param cell 
 * @return ???
 */
private static String getCellValue(Cell cell) {
    String value = null;

    if (cell != null) {
        switch (cell.getCellTypeEnum()) {
        case BLANK:
            value = cell.getStringCellValue();
            break;
        case BOOLEAN:
            value = String.valueOf(cell.getBooleanCellValue());
            break;
        case ERROR:
            value = String.valueOf(cell.getErrorCellValue());
            break;
        case NUMERIC:
            value = String.valueOf(cell.getNumericCellValue());
            break;
        case STRING:
            value = cell.getStringCellValue();
            break;
        case FORMULA:
            value = cell.getCellFormula();
        default:
            value = "";
        }
    }
    return value;
}

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

License:Apache License

private String tryToReadCachedValue(Cell cell) {
    DataFormatter formatter = new DataFormatter(Locale.ENGLISH);
    String cachedValue;/*from w  w w .  j a  v a2 s  .c  o m*/
    switch (cell.getCachedFormulaResultType()) {
    case Cell.CELL_TYPE_NUMERIC:
        double num = cell.getNumericCellValue();
        if (num - Math.round(num) != 0) {
            cachedValue = String.valueOf(num);
        } else {
            cachedValue = formatter.formatCellValue(cell);
        }
        break;

    case Cell.CELL_TYPE_STRING:
        cachedValue = cell.getStringCellValue();
        break;

    case Cell.CELL_TYPE_BOOLEAN:
        cachedValue = String.valueOf(cell.getBooleanCellValue());
        break;

    case Cell.CELL_TYPE_ERROR:
        cachedValue = String.valueOf(cell.getErrorCellValue());
        break;

    default:
        throw new DecisionTableParseException(
                format("Can't read cached value for cell[row=%d, col=%d, value=%s]!", cell.getRowIndex(),
                        cell.getColumnIndex(), cell));
    }
    return cachedValue;
}

From source file:org.efaps.esjp.common.file.FileUtil_Base.java

License:Apache License

/**
 * Copy cell./*from www. j a v a  2  s. c  o  m*/
 *
 * @param _oldCell the old cell
 * @param _newCell the new cell
 * @param _styleMap the style map
 */
protected void copyCell(final Cell _oldCell, final Cell _newCell, final Map<Integer, CellStyle> _styleMap) {
    if (_styleMap != null) {
        if (_oldCell.getSheet().getWorkbook() == _newCell.getSheet().getWorkbook()) {
            _newCell.setCellStyle(_oldCell.getCellStyle());
        } else {
            final int stHashCode = _oldCell.getCellStyle().hashCode();
            CellStyle newCellStyle = _styleMap.get(stHashCode);
            if (newCellStyle == null) {
                newCellStyle = _newCell.getSheet().getWorkbook().createCellStyle();
                newCellStyle.cloneStyleFrom(_oldCell.getCellStyle());
                _styleMap.put(stHashCode, newCellStyle);
            }
            _newCell.setCellStyle(newCellStyle);
        }
    }
    switch (_oldCell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        _newCell.setCellValue(_oldCell.getStringCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        _newCell.setCellValue(_oldCell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_BLANK:
        _newCell.setCellType(Cell.CELL_TYPE_BLANK);
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        _newCell.setCellValue(_oldCell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_ERROR:
        _newCell.setCellErrorValue(_oldCell.getErrorCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        _newCell.setCellFormula(_oldCell.getCellFormula());
        break;
    default:
        break;
    }

}