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

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

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

Introduction

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

Prototype

void setCellErrorValue(byte value);

Source Link

Document

Set a error value for the cell

Usage

From source file:com.vaadin.addon.spreadsheet.command.CellValueCommand.java

License:Open Source License

/**
 * Sets the given value to the cell at the given coordinates.
 * // w w w.j a  va 2 s  .co  m
 * @param row
 *            Row index, 0-based
 * @param col
 *            Column index, 0-based
 * @param value
 *            Value to set to the cell
 * @param cellsToUpdate
 *            List of cells that need updating at the end. If the cell value
 *            is modified, the cell is added to this list.
 * @return Previous value of the cell or null if not available
 */
protected Object updateCellValue(int row, int col, Object value, List<Cell> cellsToUpdate) {
    Cell cell = getCell(row, col);
    Object oldValue = getCellValue(cell);
    if (value == null && cell == null) {
        return null; // nothing to do
    }

    if (cell == null && value != null) {
        // create cell
        Row row2 = getSheet().getRow(row);
        if (row2 == null) {
            row2 = getSheet().createRow(row);
        }
        cell = row2.createCell(col);
    }

    if (value == null) { // delete
        if (cell == null || cell.getCellStyle().getIndex() == 0) {
            getSheet().getRow(row).removeCell(cell);
            if (!spreadsheet.isRerenderPending()) {
                spreadsheet.markCellAsDeleted(cell, false);
            }
        } else {
            cell.setCellValue((String) null);
            if (!spreadsheet.isRerenderPending()) {
                cellsToUpdate.add(cell);
            }
        }
    } else {
        if (value instanceof String) {
            if (((String) value).startsWith("=")) {
                try {
                    cell.setCellFormula(((String) value).substring(1));
                } catch (FormulaParseException fpe) {
                    cell.setCellValue((String) value);
                }
            } else {
                cell.setCellValue((String) value);
            }
        } else if (value instanceof Byte) {
            cell.setCellErrorValue((Byte) value);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        }
        if (!spreadsheet.isRerenderPending()) {
            cellsToUpdate.add(cell);
        }
    }
    return oldValue;
}

From source file:jp.ryoyamamoto.poiutils.Cells.java

License:Apache License

private static void copyCellValue(Cell source, Cell target) {
    switch (source.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        target.setCellValue(source.getNumericCellValue());
        break;//from   ww w. j a v a2 s  .c o m
    case Cell.CELL_TYPE_STRING:
        target.setCellValue(source.getRichStringCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        target.setCellFormula(source.getCellFormula());
        break;
    case Cell.CELL_TYPE_BLANK:
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        target.setCellValue(source.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_ERROR:
        target.setCellErrorValue(source.getErrorCellValue());
        break;
    }
}

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;//from w w  w.j  a  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.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * ?/*  ww  w.ja  v a  2  s . c  o m*/
 * 
 * @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.efaps.esjp.common.file.FileUtil_Base.java

License:Apache License

/**
 * Copy cell./*from www  . j a  va  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;
    }

}

From source file:org.joeffice.spreadsheet.cell.CellUtils.java

License:Apache License

public static void copyCell(Cell oldCell, Cell newCell) {
    newCell.setCellStyle(oldCell.getCellStyle());

    if (newCell.getCellComment() != null) {
        newCell.setCellComment(oldCell.getCellComment());
    }/*from ww w  . j  ava2s. com*/

    if (oldCell.getHyperlink() != null) {
        newCell.setHyperlink(oldCell.getHyperlink());
    }

    newCell.setCellType(oldCell.getCellType());

    // Set the cell data value
    switch (oldCell.getCellType()) {
    case 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;
    case Cell.CELL_TYPE_NUMERIC:
        newCell.setCellValue(oldCell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_STRING:
        newCell.setCellValue(oldCell.getRichStringCellValue());
        break;
    }
}

From source file:org.projectforge.excel.ExportSheet.java

License:Open Source License

private static Row copyRow(Sheet worksheet, int rowNum) {
    Row sourceRow = worksheet.getRow(rowNum);

    //Save the text of any formula before they are altered by row shifting
    String[] formulasArray = new String[sourceRow.getLastCellNum()];
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        if (sourceRow.getCell(i) != null && sourceRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA)
            formulasArray[i] = sourceRow.getCell(i).getCellFormula();
    }/*from  w w w  .  j  ava  2s.c  o  m*/

    worksheet.shiftRows(rowNum, worksheet.getLastRowNum(), 1);
    Row newRow = sourceRow; //Now sourceRow is the empty line, so let's rename it
    sourceRow = worksheet.getRow(rowNum + 1); //Now the source row is at rowNum+1

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell;

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            continue;
        } else {
            newCell = newRow.createCell(i);
        }

        // Copy style from old cell and apply to new cell
        CellStyle newCellStyle = worksheet.getWorkbook().createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case 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(formulasArray[i]);
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        default:
            break;
        }
    }

    // If there are any merged regions in the source row, copy to new row
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            worksheet.addMergedRegion(newCellRangeAddress);
        }
    }
    return newRow;
}

From source file:org.seasar.fisshplate.core.element.GenericCell.java

License:Apache License

protected void mergeImpl(FPContext context, Cell out) {
    Object cellValue = getCellValue();

    int cellType = cell.getCellType();
    if (cellType == Cell.CELL_TYPE_FORMULA) {
        out.setCellFormula((String) cellValue);
    } else if (cellType == Cell.CELL_TYPE_ERROR) {
        out.setCellErrorValue(((Byte) cellValue).byteValue());
    } else if (cellValue instanceof Date) {
        out.setCellValue(((Date) cellValue));
        out.setCellType(Cell.CELL_TYPE_NUMERIC);
    } else if (cellValue instanceof String) {
        out.setCellValue((String) cellValue);
        out.setCellType(Cell.CELL_TYPE_STRING);
    } else if (cellValue instanceof Boolean) {
        out.setCellValue(((Boolean) cellValue).booleanValue());
        out.setCellType(Cell.CELL_TYPE_BOOLEAN);
    } else if (isNumber(cellValue)) {
        out.setCellValue(Double.valueOf(cellValue.toString()).doubleValue());
        out.setCellType(Cell.CELL_TYPE_NUMERIC);
    }/*w  ww .  j av  a2  s .  co  m*/
}