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

List of usage examples for org.apache.poi.ss.usermodel CellValue getBooleanValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CellValue
  6. getBooleanValue

Introduction

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

Prototype

public boolean getBooleanValue()

Source Link

Usage

From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java

License:Open Source License

/**
 * (Each Excel sheet cell becomes an HTML table cell) Generates an HTML
 * table cell which has the same font styles, alignments, colours and
 * borders as the Excel cell.//w w w .ja v a  2  s  .c o m
 *
 * @param cell
 *            The Excel cell.
 */

private void td(final HSSFCell cell) {
    int colspan = 1;
    if (colIndex == mergeStart) {
        // First cell in the merging region - set colspan.
        colspan = mergeEnd - mergeStart + 1;
    } else if (colIndex == mergeEnd) {
        // Last cell in the merging region - no more skipped cells.
        mergeStart = -1;
        mergeEnd = -1;
        return;
    } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart && colIndex < mergeEnd) {
        // Within the merging region - skip the cell.
        return;
    }
    //KLO 05022018
    //out.append("<td ");

    out.append("<td height=\"15\" ");
    if (colspan > 1) {
        out.append("colspan='").append(colspan).append("' ");
    }
    if (cell == null) {
        out.append("/>\n");
        return;
    }

    out.append("style='");
    final HSSFCellStyle style = cell.getCellStyle();
    // Text alignment
    switch (style.getAlignment()) {
    case CellStyle.ALIGN_LEFT:
        out.append("text-align: left; ");
        break;
    case CellStyle.ALIGN_RIGHT:
        out.append("text-align: right; ");
        break;
    case CellStyle.ALIGN_CENTER:
        out.append("text-align: center; ");
        break;
    default:
        break;
    }

    // Font style, size and weight
    final HSSFFont font = style.getFont(book);
    if (font == null)
        return;
    if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) {
        out.append("font-weight: bold; ");
    }
    if (font.getItalic()) {
        out.append("font-style: italic; ");
    }
    if (font.getUnderline() != HSSFFont.U_NONE) {
        out.append("text-decoration: underline; ");
    }
    out.append("font-size: ").append(Math.floor(font.getFontHeightInPoints() * 0.8)).append("pt; ");
    // Cell background and font colours
    final short[] backRGB = style.getFillForegroundColorColor().getTriplet();
    final HSSFColor foreColor = palette.getColor(font.getColor());
    if (foreColor != null) {
        final short[] foreRGB = foreColor.getTriplet();
        if (foreRGB[0] != 0 || foreRGB[1] != 0 || foreRGB[2] != 0) {
            out.append("color: rgb(").append(foreRGB[0]).append(',').append(foreRGB[1]).append(',')
                    .append(foreRGB[2]).append(");");
        }
    }
    if (backRGB[0] != 0 || backRGB[1] != 0 || backRGB[2] != 0) {
        out.append("background-color: rgb(").append(backRGB[0]).append(',').append(backRGB[1]).append(',')
                .append(backRGB[2]).append(");");
    }
    // Border
    if (style.getBorderTop() != HSSFCellStyle.BORDER_NONE) {
        out.append("border-top-style: solid; ");
    }
    if (style.getBorderRight() != HSSFCellStyle.BORDER_NONE) {
        out.append("border-right-style: solid; ");
    }
    if (style.getBorderBottom() != HSSFCellStyle.BORDER_NONE) {
        out.append("border-bottom-style: solid; ");
    }
    if (style.getBorderLeft() != HSSFCellStyle.BORDER_NONE) {
        out.append("border-left-style: solid; ");
    }
    out.append("'>");
    String val = "";
    try {
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            val = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            // POI does not distinguish between integer and double, thus:
            final double original = cell.getNumericCellValue(), rounded = Math.round(original);
            if (Math.abs(rounded - original) < 0.00000000000000001) {
                val = String.valueOf((int) rounded);
            } else {
                val = String.valueOf(original);
            }
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            final CellValue cv = evaluator.evaluate(cell);
            if (cv == null)
                return;
            switch (cv.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                out.append(cv.getBooleanValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                out.append(cv.getNumberValue());
                break;
            case Cell.CELL_TYPE_STRING:
                out.append(cv.getStringValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_ERROR:
                break;
            default:
                break;
            }
            break;
        default:
            // Neither string or number? Could be a date.
            try {
                val = sdf.format(cell.getDateCellValue());
            } catch (final Exception e1) {
            }
        }
    } catch (final Exception e) {
        val = e.getMessage();
    }
    if ("null".equals(val)) {
        val = "";
    }
    if (pix.containsKey(rowIndex)) {
        if (pix.get(rowIndex).containsKey(colIndex)) {
            for (final HSSFPictureData pic : pix.get(rowIndex).get(colIndex)) {
                out.append("<img alt='Image in Excel sheet' src='data:");
                out.append(pic.getMimeType());
                out.append(";base64,");
                try {
                    out.append(new String(Base64.encodeBase64(pic.getData()), "US-ASCII"));
                } catch (final UnsupportedEncodingException e) {
                    throw new RuntimeException(e);
                }
                out.append("'/>");
            }
        }
    }

    if (isCode(val) && this.URL != null) {
        val = getHyperLink(val);
    }

    out.append(val);
    out.append("</td>\n");
}

From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java

License:Open Source License

private void td(final HSSFCell cell, StringBuffer buf) {
    int colspan = 1;
    if (colIndex == mergeStart) {
        // First cell in the merging region - set colspan.
        colspan = mergeEnd - mergeStart + 1;
    } else if (colIndex == mergeEnd) {
        // Last cell in the merging region - no more skipped cells.
        mergeStart = -1;/*ww  w  . j a va 2 s  . c om*/
        mergeEnd = -1;
        return;
    } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart && colIndex < mergeEnd) {
        // Within the merging region - skip the cell.
        return;
    }

    //KLO 05022018
    //buf.append("<td ");

    buf.append("<td height=\"15\" ");

    if (colspan > 1) {
        buf.append("colspan='").append(colspan).append("' ");
    }
    if (cell == null) {
        buf.append("/>");
        return;
    }
    buf.append("style='");
    final HSSFCellStyle style = cell.getCellStyle();
    // Text alignment
    switch (style.getAlignment()) {
    case CellStyle.ALIGN_LEFT:
        buf.append("text-align: left; ");
        break;
    case CellStyle.ALIGN_RIGHT:
        buf.append("text-align: right; ");
        break;
    case CellStyle.ALIGN_CENTER:
        buf.append("text-align: center; ");
        break;
    default:
        break;
    }
    // Font style, size and weight
    final HSSFFont font = style.getFont(book);
    if (font == null)
        return;
    if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) {
        buf.append("font-weight: bold; ");
    }
    if (font.getItalic()) {
        buf.append("font-style: italic; ");
    }
    if (font.getUnderline() != HSSFFont.U_NONE) {
        buf.append("text-decoration: underline; ");
    }
    buf.append("font-size: ").append(Math.floor(font.getFontHeightInPoints() * 0.8)).append("pt; ");

    // Cell background and font colours
    final short[] backRGB = style.getFillForegroundColorColor().getTriplet();
    final HSSFColor foreColor = palette.getColor(font.getColor());
    if (foreColor != null) {
        final short[] foreRGB = foreColor.getTriplet();
        if (foreRGB[0] != 0 || foreRGB[1] != 0 || foreRGB[2] != 0) {
            buf.append("color: rgb(").append(foreRGB[0]).append(',').append(foreRGB[1]).append(',')
                    .append(foreRGB[2]).append(");");

        }
    }
    if (backRGB[0] != 0 || backRGB[1] != 0 || backRGB[2] != 0) {
        buf.append("background-color: rgb(").append(backRGB[0]).append(',').append(backRGB[1]).append(',')
                .append(backRGB[2]).append(");");

    }
    // Border
    if (style.getBorderTop() != HSSFCellStyle.BORDER_NONE) {
        buf.append("border-top-style: solid; ");
    }
    if (style.getBorderRight() != HSSFCellStyle.BORDER_NONE) {
        buf.append("border-right-style: solid; ");
    }
    if (style.getBorderBottom() != HSSFCellStyle.BORDER_NONE) {
        buf.append("border-bottom-style: solid; ");
    }
    if (style.getBorderLeft() != HSSFCellStyle.BORDER_NONE) {
        buf.append("border-left-style: solid; ");
    }
    buf.append("'>");
    String val = "";
    try {
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            val = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            // POI does not distinguish between integer and double, thus:
            final double original = cell.getNumericCellValue(), rounded = Math.round(original);
            if (Math.abs(rounded - original) < 0.00000000000000001) {
                val = String.valueOf((int) rounded);
            } else {
                val = String.valueOf(original);
            }
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            final CellValue cv = evaluator.evaluate(cell);
            if (cv == null)
                return;
            switch (cv.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                buf.append(cv.getBooleanValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                buf.append(cv.getNumberValue());
                break;
            case Cell.CELL_TYPE_STRING:
                buf.append(cv.getStringValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_ERROR:
                break;
            default:
                break;
            }
            break;
        default:
            // Neither string or number? Could be a date.
            try {
                val = sdf.format(cell.getDateCellValue());
            } catch (final Exception e1) {
            }
        }
    } catch (final Exception e) {
        val = e.getMessage();
    }
    if ("null".equals(val)) {
        val = "";
    }
    if (pix.containsKey(rowIndex)) {
        if (pix.get(rowIndex).containsKey(colIndex)) {
            for (final HSSFPictureData pic : pix.get(rowIndex).get(colIndex)) {
                buf.append("<img alt='Image in Excel sheet' src='data:");
                buf.append(pic.getMimeType());
                buf.append(";base64,");

                try {
                    buf.append(new String(Base64.encodeBase64(pic.getData()), "US-ASCII"));

                } catch (final UnsupportedEncodingException e) {
                    throw new RuntimeException(e);
                }
                buf.append("'/>");
            }
        }
    }

    if (isCode(val) && this.URL != null) {
        val = getHyperLink(val);
    }
    buf.append(val);
    buf.append("</td>");

}

From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java

License:Open Source License

private String getCellData(final HSSFCell cell) {
    if (cell == null)
        return null;
    int colspan = 1;
    if (colIndex == mergeStart) {
        // First cell in the merging region - set colspan.
        colspan = mergeEnd - mergeStart + 1;
    } else if (colIndex == mergeEnd) {
        // Last cell in the merging region - no more skipped cells.
        mergeStart = -1;/*from   www.j av  a2  s .  co m*/
        mergeEnd = -1;
        return null;
    } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart && colIndex < mergeEnd) {
        // Within the merging region - skip the cell.
        return null;
    }
    //StringBuffer buf = new StringBuffer();
    String val = "";
    try {
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            val = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            // POI does not distinguish between integer and double, thus:
            final double original = cell.getNumericCellValue(), rounded = Math.round(original);
            if (Math.abs(rounded - original) < 0.00000000000000001) {
                val = String.valueOf((int) rounded);
            } else {
                val = String.valueOf(original);
            }
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            final CellValue cv = evaluator.evaluate(cell);
            if (cv == null)
                return null;
            switch (cv.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                out.append(cv.getBooleanValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                out.append(cv.getNumberValue());
                break;
            case Cell.CELL_TYPE_STRING:
                out.append(cv.getStringValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_ERROR:
                break;
            default:
                break;
            }
            break;
        default:
            // Neither string or number? Could be a date.
            try {
                val = sdf.format(cell.getDateCellValue());
            } catch (final Exception e1) {
            }
        }
    } catch (final Exception e) {
        val = e.getMessage();
    }
    if ("null".equals(val)) {
        val = "";
    }
    return val;
}

From source file:hu.webhejj.commons.io.table.excel.ExcelRowValueConverter.java

License:Apache License

public <T> T getValue(Row row, int column, Class<T> valueType) {

    if (row == null) {
        return null;
    }// w w w  . j ava  2s  .c  o  m

    Cell cell = row.getCell(column);
    if (cell == null) {
        return null;
    }

    CellValue cellValue = getCellValue(row, cell, column);
    if (cellValue == null) {
        return null;
    }

    switch (cellValue.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return null;

    case Cell.CELL_TYPE_BOOLEAN:
        if (String.class.isAssignableFrom(valueType)) {
            return (T) Boolean.toString(cellValue.getBooleanValue());

        } else if (Integer.class.isAssignableFrom(valueType)) {
            return (T) Integer.valueOf(cellValue.getBooleanValue() ? 1 : 0);

        } else if (Long.class.isAssignableFrom(valueType)) {
            return (T) Long.valueOf(cellValue.getBooleanValue() ? 1L : 0L);

        } else {
            throw new ClassCastException(
                    "Can't convert " + cellValue.getBooleanValue() + " to " + valueType.getName());
        }

    case Cell.CELL_TYPE_STRING:
        String stringValue = cellValue.getStringValue();
        if (CompareUtils.isEmpty(stringValue)) {
            return null;
        }
        if ("null".equals(stringValue)) {
            return null;
        }
        if (String.class.isAssignableFrom(valueType)) {
            return (T) stringValue;

        } else if (Integer.class.isAssignableFrom(valueType)) {
            return (T) Integer.valueOf(stringValue);

        } else if (Long.class.isAssignableFrom(valueType)) {
            return (T) Long.valueOf(stringValue);

        } else if (valueType.isEnum()) {
            return (T) Enum.valueOf((Class<? extends Enum>) valueType, stringValue);

        } else if (BigDecimal.class.isAssignableFrom(valueType)) {
            return (T) (CompareUtils.isEmpty(stringValue) ? null : new BigDecimal(stringValue));

        } else if (Boolean.class.isAssignableFrom(valueType)) {
            return (T) Boolean.valueOf("true".equalsIgnoreCase(stringValue)
                    || (!CompareUtils.isEmpty(stringValue) && !"0".equals(stringValue)));

        } else {
            throw new ClassCastException("Can't convert " + stringValue + " to " + valueType.getName());
        }

    case Cell.CELL_TYPE_NUMERIC:
        if (String.class.isAssignableFrom(valueType)) {
            Format format = formatter.createFormat(cell);
            if (format == null) {
                // TODO: do this without creating a BigDecimal each time
                return (T) new BigDecimal(cellValue.getNumberValue()).toString();
            } else {
                return (T) format.format(cellValue.getNumberValue());
            }

        } else if (Integer.class.isAssignableFrom(valueType)) {
            return (T) Integer.valueOf((int) cellValue.getNumberValue());

        } else if (Long.class.isAssignableFrom(valueType)) {
            return (T) Long.valueOf((int) cellValue.getNumberValue());

        } else {
            throw new ClassCastException(
                    "Can't convert " + cellValue.getNumberValue() + " to " + valueType.getName());
        }
    case Cell.CELL_TYPE_ERROR:
        FormulaError error = FormulaError.forInt(cell.getErrorCellValue());
        if (FormulaError.NA.equals(error)) {
            return null;
        } else {
            // System.err.format("  Cell[%d,%d] error code %s\n", r.getRowNum(), column, error);
            return null;
            // throw new RuntimeException(String.format("Cell[%d,%d] error code %s", r.getRowNum(), column, error));
        }
    }
    throw new IllegalArgumentException("Don't know how to convert cell of type " + cellValue.getCellType());
}

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();
        }//  ww w  .j  a  v a2 s.c om
    } 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 {// w  ww.j a va  2s .  c o 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:opn.greenwebs.FXMLDocumentController.java

private List<ItemDB> createListFromTable(File fStock) {
    List<ItemDB> lstMain = new ArrayList<>();
    logger.log(Level.INFO, "into the breach {0}", fStock.getAbsolutePath());
    try (FileInputStream fis = new FileInputStream(fStock); XSSFWorkbook book = new XSSFWorkbook(fis)) {
        XSSFSheet sheet = book.getSheet("Digital Version");
        logger.log(Level.INFO, "we have a sheet {0}", sheet.getSheetName());
        FormulaEvaluator evaluator2 = book.getCreationHelper().createFormulaEvaluator();
        List lstItem;//w w  w .j  a v a 2s . co m
        List<XSSFTable> lstTables = sheet.getTables();
        logger.log(Level.SEVERE, "we have a table? {0}", lstTables.size());
        if (!lstTables.isEmpty()) {
            logger.log(Level.INFO, "the table is not empty");
            for (int j = lstTables.get(0).getStartCellReference().getRow(); j <= lstTables.get(0)
                    .getEndCellReference().getRow(); j++) {
                ItemDB itmLine = new ItemDB();
                lstItem = new ArrayList();
                logger.log(Level.INFO, "we have some rows");
                if (j > lstTables.get(0).getStartCellReference().getRow()) {
                    Row row = sheet.getRow(j);
                    if (row != null) {
                        Iterator<Cell> cellIt = row.cellIterator();
                        logger.log(Level.INFO, "we have some cells {0}", cellIt.hasNext());
                        while (cellIt.hasNext()) {
                            Cell cell = cellIt.next();
                            CellValue cellvalue = evaluator2.evaluate(cell);
                            if (cellvalue == null) {
                                lstItem.add("");
                            } else {
                                switch (cellvalue.getCellType()) {
                                case Cell.CELL_TYPE_STRING:
                                    lstItem.add(cellvalue.getStringValue());
                                    break;
                                case Cell.CELL_TYPE_NUMERIC:
                                    lstItem.add(cellvalue.getNumberValue());
                                    break;
                                case Cell.CELL_TYPE_BOOLEAN:
                                    lstItem.add(cellvalue.getBooleanValue());
                                case Cell.CELL_TYPE_BLANK:
                                    lstItem.add("");
                                    break;
                                case Cell.CELL_TYPE_FORMULA:
                                    lstItem.add("You found a formula");
                                    break;
                                default:
                                }
                            }
                        }
                        logger.log(Level.ALL, "lstItem is {0} and {1}",
                                new Object[] { lstItem.size(), lstItem.toString() });
                        itmLine.setDblQty(Double.parseDouble(lstItem.get(0).toString()));
                        logger.log(Level.INFO, "setting the quantity to {0}", lstItem.get(0).toString());
                        itmLine.setStrMfr(lstItem.get(1).toString());
                        itmLine.setStrSKU(lstItem.get(2).toString());
                        itmLine.setStrDescrip(lstItem.get(3).toString());
                        itmLine.setStrSupplier(lstItem.get(4).toString());
                        itmLine.setStrSupPart(lstItem.get(5).toString());
                        logger.log(Level.INFO, "setting the suppart to {0}", lstItem.get(5));
                        if (lstItem.size() > 6) {
                            if (!lstItem.get(6).toString().isEmpty()) {
                                itmLine.setDblSalePrice(Double.parseDouble(lstItem.get(6).toString()));
                            }
                        }
                        //System.out.println("Added item " + itmLine.getStrDescrip());
                        lstMain.add(itmLine);
                    }

                }
            }
        }
    } catch (IOException ex) {
        logger.info(ex.getLocalizedMessage());

    }
    logger.log(Level.INFO, "returning {0}", lstMain.size());
    return lstMain;
}

From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetGetCellValue.java

License:Open Source License

public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException {
    cfSpreadSheetData spreadsheet = null;
    int rowNo, columnNo;

    /*//www .j  a  va 2  s .com
     * Collect up the parameters
     */
    spreadsheet = (cfSpreadSheetData) parameters.get(2);
    rowNo = parameters.get(1).getInt() - 1;
    columnNo = parameters.get(0).getInt() - 1;

    if (rowNo < 0)
        throwException(_session, "row must be 1 or greater (" + rowNo + ")");
    if (columnNo < 0)
        throwException(_session, "column must be 1 or greater (" + columnNo + ")");

    /*
     * Find the cell in question 
     */
    Sheet sheet = spreadsheet.getActiveSheet();
    Row row = sheet.getRow(rowNo);
    if (row == null)
        row = sheet.createRow(rowNo);

    Cell cell = row.getCell(columnNo);
    if (cell == null)
        cell = row.createCell(columnNo);

    FormulaEvaluator evaluator = spreadsheet.getWorkBook().getCreationHelper().createFormulaEvaluator();

    if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
        return cfBooleanData.getcfBooleanData(cell.getBooleanCellValue());
    else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
        return new cfNumberData(cell.getNumericCellValue());
    else if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
        return cfStringData.EMPTY_STRING;
    else if (cell.getCellType() == Cell.CELL_TYPE_STRING)
        return new cfStringData(cell.getStringCellValue());
    else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        CellValue cellValue = evaluator.evaluate(cell);

        switch (cellValue.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            return cfBooleanData.getcfBooleanData(cellValue.getBooleanValue());
        case Cell.CELL_TYPE_NUMERIC:
            return new cfNumberData(cellValue.getNumberValue());
        case Cell.CELL_TYPE_STRING:
            return new cfStringData(cellValue.getStringValue());
        default:
            return cfStringData.EMPTY_STRING;
        }

    } else
        return cfStringData.EMPTY_STRING;
}

From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetQueryRead.java

License:Open Source License

public cfData execute(cfSession _session, cfArgStructData argStruct) throws cfmRunTimeException {
    cfSpreadSheetData spreadsheet = (cfSpreadSheetData) getNamedParam(argStruct, "spreadsheet", null);
    if (spreadsheet == null)
        throwException(_session, "spreadsheet object not given");

    //How many columns are we using
    int[] columnsToUse;
    String columns = getNamedStringParam(argStruct, "columns", null);
    if (columns != null) {

        int x = 0;
        List<Integer> numbers = tagUtils.getNumberListSorted(columns);
        columnsToUse = new int[numbers.size()];
        Iterator<Integer> numbersIT = numbers.iterator();
        while (numbersIT.hasNext())
            columnsToUse[x++] = (numbersIT.next() - 1);

    } else {/*from  w ww  . j ava2  s.co m*/
        int maxColumns = SheetUtility.getMaxColumn(spreadsheet.getActiveSheet());
        columnsToUse = new int[maxColumns];
        for (int x = 0; x < maxColumns; x++)
            columnsToUse[x] = x;
    }

    //Figure out the columns
    String columnLabels[] = null;
    int startRow = 0;
    String columnnames = getNamedStringParam(argStruct, "columnnames", null);
    int headerRow = getNamedIntParam(argStruct, "headerrow", -1);

    FormulaEvaluator evaluator = spreadsheet.getWorkBook().getCreationHelper().createFormulaEvaluator();

    if (columnnames != null) {
        columnLabels = (columnnames.split("\\s*,\\s*"));
        if (columnLabels.length != columnsToUse.length)
            throwException(_session, "The COLUMNNAMES does not match the number of columns");
    } else if (headerRow > -1) {

        headerRow = headerRow - 1;
        Row row = spreadsheet.getActiveSheet().getRow(headerRow);
        if (row == null)
            throwException(_session, "The HEADERROW does not exist");

        columnLabels = new String[columnsToUse.length];

        for (int c = 0; c < columnsToUse.length; c++) {
            Cell cell = row.getCell(columnsToUse[c]);

            if (cell == null)
                columnLabels[c] = "";
            else
                columnLabels[c] = cell.toString();
        }

        startRow = headerRow + 1;

    } else {

        columnLabels = new String[columnsToUse.length];
        for (int x = 0; x < columnLabels.length; x++)
            columnLabels[x] = "Column " + (columnsToUse[x] + 1);

    }

    //Create the query
    cfQueryResultData queryData = new cfQueryResultData(columnLabels, "SpreadSheet");
    List<Map<String, cfData>> vResults = new ArrayList<Map<String, cfData>>();

    int sheetNo = getNamedIntParam(argStruct, "sheet", -1);
    if (sheetNo != -1)
        spreadsheet.setActiveSheet(sheetNo);

    Sheet sheet = spreadsheet.getActiveSheet();
    Row row;
    Cell cell;
    cfData cfdata;

    String rowsSt = getNamedStringParam(argStruct, "rows", null);
    if (rowsSt != null) {

        List<Integer> rows = tagUtils.getNumberListSorted(rowsSt);
        Iterator<Integer> rowsIT = rows.iterator();
        while (rowsIT.hasNext()) {
            int r = rowsIT.next() - 1;

            Map<String, cfData> hm = new FastMap<String, cfData>();

            if ((row = sheet.getRow(r)) == null)
                continue;

            for (int c = 0; c < columnsToUse.length; c++) {
                cell = row.getCell(columnsToUse[c]);
                if (cell == null)
                    cfdata = new cfStringData("");
                else {
                    if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
                        cfdata = cfBooleanData.getcfBooleanData(cell.getBooleanCellValue());
                    else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                        cfdata = new cfNumberData(cell.getNumericCellValue());
                    else if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
                        cfdata = new cfStringData("");
                    else if (cell.getCellType() == Cell.CELL_TYPE_STRING)
                        cfdata = new cfStringData(cell.getStringCellValue());
                    else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                        CellValue cellValue = evaluator.evaluate(cell);

                        switch (cellValue.getCellType()) {
                        case Cell.CELL_TYPE_BOOLEAN:
                            cfdata = cfBooleanData.getcfBooleanData(cellValue.getBooleanValue());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            cfdata = new cfNumberData(cellValue.getNumberValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            cfdata = new cfStringData(cellValue.getStringValue());
                            break;

                        default:
                            cfdata = cfStringData.EMPTY_STRING;
                            break;
                        }

                    } else
                        cfdata = new cfStringData("");
                }

                hm.put(columnLabels[c], cfdata);
            }

            vResults.add(hm);
        }

    } else {

        /*
         * Read __ALL__ the rows associated with this spreadsheet
         */
        for (int r = startRow; r < sheet.getLastRowNum() + 1; r++) {
            Map<String, cfData> hm = new FastMap<String, cfData>();

            if ((row = sheet.getRow(r)) == null)
                continue;

            for (int c = 0; c < columnsToUse.length; c++) {
                cell = row.getCell(columnsToUse[c]);
                if (cell == null)
                    cfdata = new cfStringData("");
                else {
                    if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
                        cfdata = cfBooleanData.getcfBooleanData(cell.getBooleanCellValue());
                    else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                        cfdata = new cfNumberData(cell.getNumericCellValue());
                    else if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
                        cfdata = new cfStringData("");
                    else if (cell.getCellType() == Cell.CELL_TYPE_STRING)
                        cfdata = new cfStringData(cell.getStringCellValue());
                    else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                        CellValue cellValue = evaluator.evaluate(cell);

                        switch (cellValue.getCellType()) {
                        case Cell.CELL_TYPE_BOOLEAN:
                            cfdata = cfBooleanData.getcfBooleanData(cellValue.getBooleanValue());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            cfdata = new cfNumberData(cellValue.getNumberValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            cfdata = new cfStringData(cellValue.getStringValue());
                            break;

                        default:
                            cfdata = cfStringData.EMPTY_STRING;
                            break;
                        }

                    } else
                        cfdata = new cfStringData("");
                }

                hm.put(columnLabels[c], cfdata);
            }

            vResults.add(hm);
        }

    }

    queryData.populateQuery(vResults);
    return queryData;
}

From source file:org.databene.formats.xls.XLSUtil.java

License:Open Source License

public static Object resolveCellValue(Cell cell, String emptyMarker, String nullMarker,
        Converter<String, ?> stringPreprocessor) {
    if (cell == null)
        return null;
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return convertString(cell, emptyMarker, nullMarker, stringPreprocessor);
    case Cell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(cell))
            return cell.getDateCellValue();
        else/*from  w  w w  .  ja  v  a 2  s. co m*/
            return mapNumberType(cell.getNumericCellValue());
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_BLANK:
    case Cell.CELL_TYPE_ERROR:
        return cell.getRichStringCellValue().getString();
    case Cell.CELL_TYPE_FORMULA:
        FormulaEvaluator evaluator = createFormulaEvaluator(cell);
        CellValue cellValue = evaluator.evaluate(cell);
        switch (cellValue.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            return convertString(cellValue, emptyMarker, stringPreprocessor);
        case HSSFCell.CELL_TYPE_NUMERIC:
            if (HSSFDateUtil.isCellDateFormatted(cell))
                return HSSFDateUtil.getJavaDate(cellValue.getNumberValue());
            else
                return mapNumberType(cellValue.getNumberValue());
        case Cell.CELL_TYPE_BOOLEAN:
            return cellValue.getBooleanValue();
        case HSSFCell.CELL_TYPE_BLANK:
        case HSSFCell.CELL_TYPE_ERROR:
            return null;
        default:
            throw new IllegalStateException("Unexpected cell type: " + cellValue.getCellType());
            // CELL_TYPE_FORMULA is not supposed to be encountered here
        }
    default:
        throw new ConfigurationError("Not a supported cell type: " + cell.getCellType());
    }
}