Example usage for org.apache.poi.xssf.usermodel XSSFCell getErrorCellValue

List of usage examples for org.apache.poi.xssf.usermodel XSSFCell getErrorCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFCell
  6. getErrorCellValue

Introduction

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

Prototype

@Override
public byte getErrorCellValue() throws IllegalStateException

Source Link

Document

Get the value of the cell as an error code.

Usage

From source file:com.appdynamics.jrbronet.projectplan.ExcelManager.java

private static void copyRow(XSSFWorkbook workbook, XSSFSheet sourceWorksheet, int sourceRowNum,
        XSSFSheet destinationWorksheet, int destinationRowNum) {
    // Get the source / new row
    XSSFRow newRow = destinationWorksheet.getRow(destinationRowNum);
    XSSFRow sourceRow = sourceWorksheet.getRow(sourceRowNum);

    // If the row exist in destination, push down all rows by 1 else create a new row

    if (newRow != null) {
        destinationWorksheet.shiftRows(destinationRowNum, destinationWorksheet.getLastRowNum(), 1);
    } else {//from ww  w. j  a  v  a  2 s .  c  o  m
        newRow = destinationWorksheet.createRow(destinationRowNum);
    }

    // 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
        XSSFCell oldCell = sourceRow.getCell(i);
        XSSFCell newCell = newRow.createCell(i);

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

        // Copy style from old cell and apply to new cell
        XSSFCellStyle newCellStyle = workbook.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());

        //newCell.setCellValue(oldCell.getRawValue());
        //newCell.setCellType(oldCell.getCellType());                        

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

    }

    // If there are 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);
    }            
    }
    */
}

From source file:com.excel.javafx.frames.MainFrame.java

public static boolean compareTwoCells(XSSFCell cell1, XSSFCell cell2) {
    if ((cell1 == null) && (cell2 == null)) {
        return true;
    } else if ((cell1 == null) || (cell2 == null)) {
        return false;
    }/*from w  ww. java2  s  . c  o m*/

    boolean equalCells = false;
    int type1 = cell1.getCellType();
    int type2 = cell2.getCellType();
    if (type1 == type2) {
        if (cell1.getCellStyle().equals(cell2.getCellStyle())) {
            // Compare cells based on its type
            switch (cell1.getCellType()) {
            case HSSFCell.CELL_TYPE_FORMULA:
                if (cell1.getCellFormula().equals(cell2.getCellFormula())) {
                    equalCells = true;
                }
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                if (cell1.getNumericCellValue() == cell2.getNumericCellValue()) {
                    equalCells = true;
                }
                break;
            case HSSFCell.CELL_TYPE_STRING:
                if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                    equalCells = true;
                }
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                if (cell2.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                    equalCells = true;
                }
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                if (cell1.getBooleanCellValue() == cell2.getBooleanCellValue()) {
                    equalCells = true;
                }
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                if (cell1.getErrorCellValue() == cell2.getErrorCellValue()) {
                    equalCells = true;
                }
                break;
            default:
                if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                    equalCells = true;
                }
                break;
            }
        } else {
            return false;
        }
    } else {
        return false;
    }
    return equalCells;
}

From source file:com.jitendrasinghnz.excelreadutility.ExcelReadStringArrayXSLX.java

License:Open Source License

public String convertCellToString(XSSFCell cell) {
    int type;//from   w w  w.  j av  a  2s .c o  m
    if (cell == null) {
        type = Cell.CELL_TYPE_BLANK;
    } else {
        type = cell.getCellType();
    }
    Object result;
    switch (type) {
    case Cell.CELL_TYPE_STRING:
        result = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        result = cell.getNumericCellValue();
        break;
    case Cell.CELL_TYPE_BLANK:
        result = "";
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        result = cell.getBooleanCellValue();
        break;
    case Cell.CELL_TYPE_ERROR:
        result = cell.getErrorCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        result = cell.getCellFormula();
        break;

    default:
        throw new RuntimeException("There are no support fot the cell type");
    }
    return result.toString();
}

From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java

License:Apache License

private void copyRow(XSSFRow sourceRow, XSSFRow targetRow, XSSFCreationHelper factory, XSSFDrawing patriarch) {
    for (int j = 0; j < sourceRow.getPhysicalNumberOfCells(); j++) {
        XSSFCell cell = sourceRow.getCell(j);
        if (cell != null) {
            XSSFCell newCell = targetRow.createCell(j);
            int cellType = cell.getCellType();
            newCell.setCellType(cellType);
            switch (cellType) {
            case XSSFCell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(cell.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_ERROR:
                newCell.setCellErrorValue(cell.getErrorCellValue());
                break;
            case XSSFCell.CELL_TYPE_FORMULA:
                newCell.setCellFormula(cell.getCellFormula());
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(cell.getNumericCellValue());
                break;
            case XSSFCell.CELL_TYPE_STRING:
                newCell.setCellValue(cell.getRichStringCellValue());
                break;
            default:
                newCell.setCellValue(formatter.formatCellValue(cell));
            }/*from www .jav a  2s . c  o m*/
            if (cell.getCellComment() != null) {
                XSSFClientAnchor anchor = factory.createClientAnchor();
                anchor.setDx1(100);
                anchor.setDx2(100);
                anchor.setDy1(100);
                anchor.setDy2(100);
                anchor.setCol1(newCell.getColumnIndex());
                anchor.setCol2(newCell.getColumnIndex() + 4);
                anchor.setRow1(newCell.getRowIndex());
                anchor.setRow2(newCell.getRowIndex() + 4);
                anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);

                XSSFComment comment = patriarch.createCellComment(anchor);
                comment.setString(cell.getCellComment().getString());
                newCell.setCellComment(comment);
            }
            newCell.setCellStyle(cell.getCellStyle());
            newCell.getSheet().setColumnWidth(newCell.getColumnIndex(),
                    cell.getSheet().getColumnWidth(cell.getColumnIndex()));
        }
    }
}

From source file:org.exoplatform.services.document.impl.MSXExcelDocumentReader.java

License:Open Source License

/**
 * Returns only a text from .xlsx file content.
 * /*from w  w  w .j  a  v a 2s .c  o m*/
 * @param is an input stream with .xls file content.
 * @return The string only with text from file content.
 */
public String getContentAsText(final InputStream is) throws IOException, DocumentReadException {
    if (is == null) {
        throw new IllegalArgumentException("InputStream is null.");
    }

    StringBuilder builder = new StringBuilder("");
    SimpleDateFormat dateFormat = new SimpleDateFormat(DATE_FORMAT);

    try {
        if (is.available() == 0) {
            return "";
        }

        XSSFWorkbook wb;
        try {
            wb = SecurityHelper.doPrivilegedIOExceptionAction(new PrivilegedExceptionAction<XSSFWorkbook>() {
                public XSSFWorkbook run() throws Exception {
                    return new XSSFWorkbook(is);
                }
            });
        } catch (IOException e) {
            throw new DocumentReadException("Can't open spreadsheet.", e);
        } catch (OpenXML4JRuntimeException e) {
            return builder.toString();
        }
        for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
            XSSFSheet sheet = wb.getSheetAt(sheetNum);
            if (sheet != null) {
                for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) {
                    XSSFRow row = sheet.getRow(rowNum);

                    if (row != null) {
                        int lastcell = row.getLastCellNum();
                        for (int k = 0; k < lastcell; k++) {
                            XSSFCell cell = row.getCell(k);
                            if (cell != null) {
                                switch (cell.getCellType()) {
                                case XSSFCell.CELL_TYPE_NUMERIC: {
                                    double d = cell.getNumericCellValue();
                                    if (isCellDateFormatted(cell)) {
                                        Date date = HSSFDateUtil.getJavaDate(d);
                                        String cellText = dateFormat.format(date);
                                        builder.append(cellText).append(" ");
                                    } else {
                                        builder.append(d).append(" ");
                                    }
                                    break;
                                }
                                case XSSFCell.CELL_TYPE_FORMULA:
                                    builder.append(cell.getCellFormula().toString()).append(" ");
                                    break;
                                case XSSFCell.CELL_TYPE_BOOLEAN:
                                    builder.append(cell.getBooleanCellValue()).append(" ");
                                    break;
                                case XSSFCell.CELL_TYPE_ERROR:
                                    builder.append(cell.getErrorCellValue()).append(" ");
                                    break;
                                case XSSFCell.CELL_TYPE_STRING:
                                    builder.append(cell.getStringCellValue().toString()).append(" ");
                                    break;
                                default:
                                    break;
                                }
                            }
                        }
                    }
                }
            }
        }
    } finally {
        if (is != null) {
            try {
                is.close();
            } catch (IOException e) {
                if (LOG.isTraceEnabled()) {
                    LOG.trace("An exception occurred: " + e.getMessage());
                }
            }
        }
    }
    return builder.toString();
}

From source file:org.kuali.test.runner.output.PoiHelper.java

License:Educational Community License

private void copyCell(XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) {
    if (styleMap != null) {
        int stHashCode = oldCell.getCellStyle().hashCode();
        XSSFCellStyle newCellStyle = styleMap.get(stHashCode);
        if (newCellStyle == null) {
            newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
            newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
            newCellStyle.setFont(oldCell.getCellStyle().getFont());
            styleMap.put(stHashCode, newCellStyle);
        }//from   ww w  .ja v a2s .c  om

        newCell.setCellStyle(newCellStyle);
    }

    switch (oldCell.getCellType()) {
    case XSSFCell.CELL_TYPE_STRING:
        newCell.setCellValue(oldCell.getStringCellValue());
        break;
    case XSSFCell.CELL_TYPE_NUMERIC:
        newCell.setCellValue(oldCell.getNumericCellValue());
        break;
    case XSSFCell.CELL_TYPE_BLANK:
        newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
        break;
    case XSSFCell.CELL_TYPE_BOOLEAN:
        newCell.setCellValue(oldCell.getBooleanCellValue());
        break;
    case XSSFCell.CELL_TYPE_ERROR:
        newCell.setCellErrorValue(oldCell.getErrorCellValue());
        break;
    case XSSFCell.CELL_TYPE_FORMULA:
        newCell.setCellFormula(oldCell.getCellFormula());
        break;
    default:
        break;
    }
}

From source file:org.talend.dataprep.qa.util.ExcelComparator.java

License:Open Source License

public static boolean compareTwoCells(XSSFCell cell1, XSSFCell cell2) {
    if ((cell1 == null) && (cell2 == null)) {
        return true;
    } else if ((cell1 == null) || (cell2 == null)) {
        return false;
    }/*from w w w. j  a  v  a  2  s  .  co m*/

    boolean equalCells = false;
    int type1 = cell1.getCellTypeEnum().getCode();
    int type2 = cell2.getCellTypeEnum().getCode();
    if (type1 == type2) {
        if (cell1.getCellStyle().equals(cell2.getCellStyle())) {
            // Compare cells based on its type
            switch (cell1.getCellTypeEnum().getCode()) {
            case HSSFCell.CELL_TYPE_FORMULA:
                if (cell1.getCellFormula().equals(cell2.getCellFormula())) {
                    equalCells = true;
                }
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                if (cell1.getNumericCellValue() == cell2.getNumericCellValue()) {
                    equalCells = true;
                }
                break;
            case HSSFCell.CELL_TYPE_STRING:
                if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                    equalCells = true;
                }
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                if (cell2.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                    equalCells = true;
                }
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                if (cell1.getBooleanCellValue() == cell2.getBooleanCellValue()) {
                    equalCells = true;
                }
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                if (cell1.getErrorCellValue() == cell2.getErrorCellValue()) {
                    equalCells = true;
                }
                break;
            default:
                if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                    equalCells = true;
                }
                break;
            }
        } else {
            return false;
        }
    } else {
        return false;
    }
    return equalCells;
}

From source file:sv.com.mined.sieni.controller.GestionNotasController.java

public static void copyRow(XSSFSheet worksheetSource, XSSFSheet worksheetDestination, int sourceRowNum,
        int destinationRowNum) {
    // Get the source / new row
    XSSFRow origen = worksheetSource.getRow(sourceRowNum);
    XSSFRow destino = worksheetDestination.createRow(destinationRowNum);

    // Loop through source columns to add to new row
    for (int i = 0; i < origen.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        XSSFCell oldCell = origen.getCell(i);
        XSSFCell newCell = destino.createCell(i);
        // If the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;// ww w.java 2  s .co m
            continue;
        }

        //Ajustar tamaos columnas
        worksheetDestination.setColumnWidth(i, worksheetSource.getColumnWidth(i));

        // Copy style from old cell and apply to new cell
        XSSFCellStyle newCellStyle = newCell.getSheet().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:
            newCell.setCellValue(oldCell.getStringCellValue());
            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:tan.jam.jsf.Shifting.java

public static void InsertRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum,
        int destinationRowNum) {

    worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    XSSFRow newRow = worksheet.getRow(destinationRowNum);
    XSSFRow sourceRow = worksheet.getRow(sourceRowNum);

    if (newRow != null) {
        worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    } else {/*from   w  w  w  .  ja  v a 2 s.c o  m*/
        newRow = worksheet.createRow(destinationRowNum);
    }

    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        XSSFCell oldCell = sourceRow.getCell(i);
        XSSFCell newCell = newRow.createCell(i);

        if (oldCell == null) {
            newCell = null;
            continue;
        }

        XSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        ;
        newCell.setCellStyle(newCellStyle);

        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

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

        newCell.setCellType(oldCell.getCellType());

        switch (oldCell.getCellType()) {
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula("+" + "F" + destinationRowNum + "*G" + destinationRowNum);
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
            //newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
            newCell.setCellValue("");
            break;
        }
    }

    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);
        }
    }
    int inc = destinationRowNum + 1;
    worksheet.getRow(destinationRowNum).getCell(7).setCellFormula("+F" + inc + "*G" + inc);
}

From source file:tan.jam.jsf.Shifting.java

private static void CopyData(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum,
        int destinationRowNum, int Mov) {
    XSSFRow newRow = worksheet.getRow(destinationRowNum);
    XSSFRow sourceRow = worksheet.getRow(sourceRowNum);

    for (int i = sourceRow.getLastCellNum(); i > 8 + Mov; i--) {

        int d = i - 1;
        XSSFCell oldCell = sourceRow.getCell(d);
        XSSFCell newCell = newRow.createCell(i);

        if (oldCell == null) {
            newCell = null;/*www .jav a 2s .c o m*/
            continue;
        }

        XSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        ;
        newCell.setCellStyle(newCellStyle);

        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

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

        newCell.setCellType(oldCell.getCellType());

        switch (oldCell.getCellType()) {
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }
    }
}