Example usage for org.apache.poi.hssf.usermodel HSSFCell getErrorCellValue

List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getErrorCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCell
  6. getErrorCellValue

Introduction

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

Prototype

@Override
public byte getErrorCellValue()

Source Link

Document

get the value of the cell as an error code.

Usage

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java

License:BSD License

public static Object getObject(HSSFSheet sheet, int row, short col) {
    HSSFRow hssfRow = getRow(sheet, row);

    if (hssfRow == null) {
        return null;
    }/* w w w.j a  va2 s.  c  o m*/
    HSSFCell cell = getRow(sheet, row).getCell(col);

    if (cell == null) {
        return null;
    }
    try {
        String val = cell.getStringCellValue();
        if (val != null && val.equalsIgnoreCase("(null)")) {
            return null;
        }
    } catch (Exception t) {
    }

    int type = cell.getCellType();
    switch (type) {
    case HSSFCell.CELL_TYPE_BLANK:
        return "";
    case HSSFCell.CELL_TYPE_BOOLEAN:
        return new Boolean(cell.getBooleanCellValue());
    case HSSFCell.CELL_TYPE_ERROR:
        return new Byte(cell.getErrorCellValue());
    case HSSFCell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    case HSSFCell.CELL_TYPE_NUMERIC:
        return new Double(cell.getNumericCellValue());
    case HSSFCell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    default:
        return null;
    }
}

From source file:Import.Utils.XSSFConvert.java

/**
 * @param oldCell/*  w  w  w .j a v  a 2  s . co m*/
 * @param newCell
 * @param styleMap
 */
public static void copyCell(HSSFCell oldCell, XSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) {
    if (styleMap != null) {
        int stHashCode = oldCell.getCellStyle().hashCode();
        HSSFCellStyle sourceCellStyle = styleMap.get(stHashCode);
        XSSFCellStyle destnCellStyle = newCell.getCellStyle();
        if (sourceCellStyle == null) {
            sourceCellStyle = oldCell.getSheet().getWorkbook().createCellStyle();
        }
        destnCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        styleMap.put(stHashCode, sourceCellStyle);
        newCell.setCellStyle(destnCellStyle);
    }
    switch (oldCell.getCellType()) {
    case HSSFCell.CELL_TYPE_STRING:
        newCell.setCellValue(oldCell.getStringCellValue());
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        newCell.setCellValue(oldCell.getNumericCellValue());
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        newCell.setCellValue(oldCell.getBooleanCellValue());
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        newCell.setCellErrorValue(oldCell.getErrorCellValue());
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        newCell.setCellFormula(oldCell.getCellFormula());
        break;
    default:
        break;
    }

}

From source file:org.apache.cocoon.generation.HSSFGenerator.java

License:Apache License

/**
 * Returns the cell value.//from ww  w.  ja  va  2 s  . c  om
 * @param cell   POI cell
 * @return the cell value
 */
private String getValue(HSSFCell cell) {
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:
        return "";
    case HSSFCell.CELL_TYPE_BOOLEAN:
        return BooleanUtils.toStringTrueFalse(cell.getBooleanCellValue());
    case HSSFCell.CELL_TYPE_NUMERIC:
        return Double.toString(cell.getNumericCellValue());
    case HSSFCell.CELL_TYPE_ERROR:
        return "#ERR" + cell.getErrorCellValue();
    case HSSFCell.CELL_TYPE_FORMULA:
    case HSSFCell.CELL_TYPE_STRING:
    default:
        return cell.getStringCellValue();
    }
}

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

License:Open Source License

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

    final StringBuilder builder = new StringBuilder("");

    SimpleDateFormat dateFormat = new SimpleDateFormat(DATE_FORMAT);

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

        HSSFWorkbook wb;
        try {
            wb = new HSSFWorkbook(is);
        } catch (IOException e) {
            throw new DocumentReadException("Can't open spreadsheet.", e);
        }
        for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
            HSSFSheet sheet = wb.getSheetAt(sheetNum);
            if (sheet != null) {
                for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) {
                    HSSFRow row = sheet.getRow(rowNum);

                    if (row != null) {
                        int lastcell = row.getLastCellNum();
                        for (int k = 0; k < lastcell; k++) {
                            final HSSFCell cell = row.getCell((short) k);
                            if (cell != null) {
                                switch (cell.getCellType()) {
                                case HSSFCell.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 HSSFCell.CELL_TYPE_FORMULA:
                                    SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() {
                                        public Void run() {
                                            builder.append(cell.getCellFormula().toString()).append(" ");
                                            return null;
                                        }
                                    });
                                    break;
                                case HSSFCell.CELL_TYPE_BOOLEAN:
                                    SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() {
                                        public Void run() {
                                            builder.append(cell.getBooleanCellValue()).append(" ");
                                            return null;
                                        }
                                    });
                                    break;
                                case HSSFCell.CELL_TYPE_ERROR:
                                    SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() {
                                        public Void run() {
                                            builder.append(cell.getErrorCellValue()).append(" ");
                                            return null;
                                        }
                                    });
                                    break;
                                case HSSFCell.CELL_TYPE_STRING:
                                    SecurityHelper.doPrivilegedAction(new PrivilegedAction<Void>() {
                                        public Void run() {
                                            builder.append(cell.getStringCellValue().toString()).append(" ");
                                            return null;
                                        }
                                    });
                                    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.gageot.excel.core.StringCellMapper.java

License:Apache License

@Override
public String mapCell(HSSFCell cell, int rowNum, int columnNum) throws IOException {
    if (null == cell) {
        return ""; // TODO
    }//from   ww w  . jav a2 s  .co m

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_ERROR:
        return "Error<" + cell.getErrorCellValue() + ">";
    case Cell.CELL_TYPE_BOOLEAN:
        return booleanToString(cell);
    case Cell.CELL_TYPE_NUMERIC:
        return numericToString(cell);
    case Cell.CELL_TYPE_FORMULA:
        return formulaToString(cell);
    case Cell.CELL_TYPE_STRING:
    default:
        return richTextToString(cell);
    }
}

From source file:org.hil.children.service.impl.ChildrenManagerImpl.java

License:Open Source License

private static void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum,
        int destinationRowNum) {
    // Get the source / new row
    HSSFRow newRow = worksheet.getRow(destinationRowNum);
    HSSFRow sourceRow = worksheet.getRow(sourceRowNum);

    // If the row exist in destination, push down all rows by 1 else create a new row
    if (newRow != null) {
        worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    } else {//  ww  w  .  j a v a  2 s. com
        newRow = worksheet.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
        HSSFCell oldCell = sourceRow.getCell(i);
        HSSFCell 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
        HSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());

        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (newCell.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;
        }
    }

    // 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.getFirstRow() - cellRangeAddress.getLastRow())),
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            worksheet.addMergedRegion(newCellRangeAddress);
        }
    }
}

From source file:org.ofbiz.tools.rest.FixOfcTools.java

/**
 * ?? /*from  w w  w.ja va2  s  . co m*/
 * @param cell
 * @return
 */
public static String convertCell(HSSFCell cell) {
    String cellValue = "";
    if (cell == null) {
        return cellValue;
    }
    NumberFormat formater = NumberFormat.getInstance();
    formater.setGroupingUsed(false);
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        cellValue = formater.format(cell.getNumericCellValue());
        break;
    case HSSFCell.CELL_TYPE_STRING:
        cellValue = cell.getStringCellValue();
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        cellValue = cell.getStringCellValue();
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        cellValue = Boolean.valueOf(cell.getBooleanCellValue()).toString();
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        cellValue = String.valueOf(cell.getErrorCellValue());
        break;
    default:
        cellValue = "";
    }
    return cellValue.replaceAll("\\s", "").trim();
}

From source file:ro.nextreports.engine.exporter.util.XlsUtil.java

License:Apache License

/**
 * Copy a cell to another cell/*  w  w w  .java2s.  co  m*/
 * 
 * @param oldCell cell to be copied
 * @param newCell cell to be created
 * @param styleMap style map
 */
public static void copyCell(HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) {
    if (styleMap != null) {
        if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
            newCell.setCellStyle(oldCell.getCellStyle());
        } else {
            int stHashCode = oldCell.getCellStyle().hashCode();
            HSSFCellStyle 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 HSSFCell.CELL_TYPE_STRING:
        newCell.setCellValue(oldCell.getStringCellValue());
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        newCell.setCellValue(oldCell.getNumericCellValue());
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        newCell.setCellValue(oldCell.getBooleanCellValue());
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        newCell.setCellErrorValue(oldCell.getErrorCellValue());
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        newCell.setCellFormula(oldCell.getCellFormula());
        break;
    default:
        break;
    }

}

From source file:shouQiSystem.user.ReadDriverEXL.java

private String getValue(HSSFCell hssfCell) {
    DecimalFormat df = new DecimalFormat("#");
    switch (hssfCell.getCellType()) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            System.out.println(sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue())).toString());
            return sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue())).toString();
        }/*from   w  w  w.  ja va  2 s. com*/
        System.out.println(df.format(hssfCell.getNumericCellValue()).toString());
        return df.format(hssfCell.getNumericCellValue()).toString();

    case HSSFCell.CELL_TYPE_STRING:
        System.out.println(hssfCell.getStringCellValue());
        return hssfCell.getStringCellValue();
    case HSSFCell.CELL_TYPE_FORMULA:
        return hssfCell.getCellFormula();
    case HSSFCell.CELL_TYPE_BLANK:
        return "";
    case HSSFCell.CELL_TYPE_BOOLEAN:
        return hssfCell.getBooleanCellValue() + "";
    case HSSFCell.CELL_TYPE_ERROR:
        return hssfCell.getErrorCellValue() + "";
    }
    return "";
}

From source file:test.ExcelUtil.java

License:Apache License

/**
 * , /*w w w  .j  a  v  a2s  .  co  m*/
 * 
 * @param cell
 * @return
 */
public static String cell2string(HSSFCell cell, HSSFFormulaEvaluator evaluator) {
    if (cell == null) {
        return null;
    }
    String str = null;
    final int cellType = cell.getCellType();

    switch (cellType) {
    case HSSFCell.CELL_TYPE_STRING:
        str = "" + cell.getRichStringCellValue().getString().trim();
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            str = "" + dateFormat.format(cell.getDateCellValue());
        } else {
            str = String.valueOf(cell.getNumericCellValue());

            //modify by cyyan 2008-09-23 19:17:28 
            //excelE, ;
            //E, 15, (15, )
            // 15, 0, 0
            //            str = "" + new BigDecimal(numberStr).setScale(15, BigDecimal.ROUND_HALF_UP);
            //            
            //            //modify yanchangyou 2008-09-26 18:01:43 
            //            // .0000* , 0, 
            //            if (str.indexOf('.') != -1) {
            //               str = str.replaceAll("(\\.)?0*$", "");
            //            }            

            /*
             * , 
             */
            //            if (str.indexOf('.') != -1) { //
            //               int index = str.length();
            //               for (int i = str.length()-1; i > -1; i--) {
            //                  if (str.charAt(i) == '0') {
            //                     index = i;
            //                  } else if (str.charAt(i) == '.'){
            //                     index = i;
            //                     break;
            //                  } else {
            //                     break;
            //                  }
            //               }
            //               str = str.substring(0, index);
            //            }
        }
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        str = "";
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        str = "" + cell.getBooleanCellValue();
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        str = "" + cell.getErrorCellValue();
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        if (evaluator == null) {
            str = "" + cell.getRichStringCellValue().getString();
        } else {
            str = "" + evaluator.evaluate(cell).getNumberValue();
        }

        break;
    }

    return (str == null || str.trim().equals("")) ? null : str.trim();
}