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

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

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

Introduction

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

Prototype

@Override
public CellType getCachedFormulaResultType()

Source Link

Document

Only valid for formula cells

Usage

From source file:com.elbeesee.poink.transreptor.HSSFCellToXML.java

License:Open Source License

public void onTransrept(INKFRequestContext aContext) throws Exception {
    IHSSFCellRepresentation aIHSSFCellRepresentation = (IHSSFCellRepresentation) aContext
            .sourcePrimary(IHSSFCellRepresentation.class);
    HSSFCell vCell = aIHSSFCellRepresentation.getCellReadOnly();
    String vSheetName = vCell.getSheet().getSheetName();

    StringBuilder vCellXML = new StringBuilder();

    vCellXML.append("<cell columnIndex=\"");
    vCellXML.append(vCell.getColumnIndex());
    vCellXML.append("\" rowIndex=\"");
    vCellXML.append(vCell.getRow().getRowNum());
    vCellXML.append("\" sheetIndex=\"");
    vCellXML.append(vCell.getSheet().getWorkbook().getSheetIndex(vSheetName));
    vCellXML.append("\">");

    int vCellType = vCell.getCellType();
    if (vCellType == Cell.CELL_TYPE_FORMULA) {
        vCellType = vCell.getCachedFormulaResultType();
    }//from   w w w  .  j ava  2s . c  o  m

    if (vCellType == Cell.CELL_TYPE_NUMERIC) {
        if (HSSFDateUtil.isCellDateFormatted(vCell)) {
            vCellXML.append(vCell.getDateCellValue());
        } else {
            vCellXML.append(vCell.getNumericCellValue());
        }
    } else if (vCellType == Cell.CELL_TYPE_STRING) {
        vCellXML.append(XMLUtils.escape(vCell.getStringCellValue()));
    } else if (vCellType == Cell.CELL_TYPE_BOOLEAN) {
        vCellXML.append(vCell.getBooleanCellValue());
    } else if (vCellType == Cell.CELL_TYPE_BLANK) {
    } else if (vCellType == Cell.CELL_TYPE_ERROR) {
        vCellXML.append(vCell.getErrorCellValue());
    }
    //

    vCellXML.append("</cell>");

    INKFResponse vResponse = aContext.createResponseFrom(vCellXML.toString());
    vResponse.setExpiry(INKFResponse.EXPIRY_DEPENDENT);
}

From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java

private Object getCellValue(HSSFCell cell, boolean resolveFormula) {
    if (cell == null) {
        return null;
    }//from  ww w  .  j  a v a 2  s  . c o  m
    Object result = null;
    int cellType = cell.getCellType();
    switch (cellType) {
    case HSSFCell.CELL_TYPE_BLANK:
        result = "";
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        result = cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        result = "ERROR: " + cell.getErrorCellValue();
        break;
    case HSSFCell.CELL_TYPE_FORMULA:

        switch (cell.getCachedFormulaResultType()) {
        case HSSFCell.CELL_TYPE_NUMERIC:
            /*System.out.println("Last evaluated as: " + cell.getNumericCellValue());
            result = cell.getNumericCellValue();
            break;*/
            if (DateUtil.isCellDateFormatted(cell)) {
                result = cell.getDateCellValue();
            } else {
                result = cell.getNumericCellValue();
            }
            System.out.println("Numeric cell value == " + result);
            break;
        case HSSFCell.CELL_TYPE_STRING:
            System.out.println("Last evaluated as \"" + cell.getRichStringCellValue() + "\"");
            result = cell.getRichStringCellValue();
            break;
        }

        //result = cell.getCellFormula();

        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        HSSFCellStyle cellStyle = cell.getCellStyle();
        short dataFormat = cellStyle.getDataFormat();

        System.out.println("Data format for " + cell.getColumnIndex() + " = " + dataFormat);
        // assumption is made that dataFormat = 14,
        // when cellType is HSSFCell.CELL_TYPE_NUMERIC
        // is equal to a DATE format.
        //if (dataFormat == 165 || dataFormat == 164 || dataFormat == 14) {
        if (DateUtil.isCellDateFormatted(cell)) {
            result = cell.getDateCellValue();
        } else {
            result = cell.getNumericCellValue();
        }

        if (dataFormat == 0) { // alternative way of getting value : can this be replaced for the entire block
            result = new HSSFDataFormatter().formatCellValue(cell);
        }
        System.out.println("Numeric cell value == " + result);

        break;
    case HSSFCell.CELL_TYPE_STRING:
        //result = cell.getStringCellValue();
        result = cell.getRichStringCellValue();
        System.out.println("String -> " + result);
        break;
    default:
        break;
    }

    if (result instanceof Integer) {
        return String.valueOf((Integer) result);
    } else if (result instanceof Double) {
        return String.valueOf(((Double) result)); //.longValue());
    }
    if (result instanceof Date) {
        return result;
    }
    return result.toString();
}

From source file:com.report.excel.ExcelToHtmlConverter.java

License:Apache License

protected boolean processCell(HSSFCell cell, Element tableCellElement, int normalWidthPx, int maxSpannedWidthPx,
        float normalHeightPt) {
    final HSSFCellStyle cellStyle = cell.getCellStyle();

    String value;/* w w w .  j a  v  a 2 s  .com*/
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_STRING:
        // XXX: enrich
        value = cell.getRichStringCellValue().getString();
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        /*switch (evaluator.evaluateFormulaCell(cell)) {
            case Cell.CELL_TYPE_BOOLEAN:
          value = cell.getBooleanCellValue();
           break;
            case Cell.CELL_TYPE_NUMERIC:
          value = cell.getNumericCellValue();
           break;
            case Cell.CELL_TYPE_STRING:
           System.out.println(cell.getStringCellValue());
           break;
            case Cell.CELL_TYPE_BLANK:
           break;
            case Cell.CELL_TYPE_ERROR:
           System.out.println(cell.getErrorCellValue());
           break;
            case Cell.CELL_TYPE_FORMULA: 
           break;
        }*/

        switch (cell.getCachedFormulaResultType()) {
        case HSSFCell.CELL_TYPE_STRING:
            HSSFRichTextString str = cell.getRichStringCellValue();
            if (str != null && str.length() > 0) {
                value = (str.toString());
            } else {
                value = ExcelToHtmlUtils.EMPTY;
            }
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            HSSFCellStyle style = cellStyle;
            if (style == null) {
                value = String.valueOf(cell.getNumericCellValue());
            } else {
                value = (_formatter.formatRawCellContents(cell.getNumericCellValue(), style.getDataFormat(),
                        style.getDataFormatString()));
            }
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            value = String.valueOf(cell.getBooleanCellValue());
            break;
        case HSSFCell.CELL_TYPE_ERROR:
            value = ErrorEval.getText(cell.getErrorCellValue());
            break;
        default:
            logger.log(POILogger.WARN,
                    "Unexpected cell cachedFormulaResultType (" + cell.getCachedFormulaResultType() + ")");
            value = ExcelToHtmlUtils.EMPTY;
            break;
        }
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        value = ExcelToHtmlUtils.EMPTY;
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        value = _formatter.formatCellValue(cell);
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        value = String.valueOf(cell.getBooleanCellValue());
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        value = ErrorEval.getText(cell.getErrorCellValue());
        break;
    default:
        logger.log(POILogger.WARN, "Unexpected cell type (" + cell.getCellType() + ")");
        return true;
    }

    final boolean noText = ExcelToHtmlUtils.isEmpty(value);
    final boolean wrapInDivs = !noText && isUseDivsToSpan() && !cellStyle.getWrapText();

    final short cellStyleIndex = cellStyle.getIndex();
    if (cellStyleIndex != 0) {
        HSSFWorkbook workbook = cell.getRow().getSheet().getWorkbook();
        String mainCssClass = getStyleClassName(workbook, cellStyle);
        if (wrapInDivs) {
            tableCellElement.setAttribute("class", mainCssClass + " " + cssClassContainerCell);
        } else {
            tableCellElement.setAttribute("class", mainCssClass);
        }

        if (noText) {
            /*
             * if cell style is defined (like borders, etc.) but cell text
             * is empty, add "&nbsp;" to output, so browser won't collapse
             * and ignore cell
             */
            value = "\u00A0";
        }
    }

    if (isOutputLeadingSpacesAsNonBreaking() && value.startsWith(" ")) {
        StringBuilder builder = new StringBuilder();
        for (int c = 0; c < value.length(); c++) {
            if (value.charAt(c) != ' ')
                break;
            builder.append('\u00a0');
        }

        if (value.length() != builder.length())
            builder.append(value.substring(builder.length()));

        value = builder.toString();
    }

    Text text = htmlDocumentFacade.createText(value);

    if (wrapInDivs) {
        Element outerDiv = htmlDocumentFacade.createBlock();
        outerDiv.setAttribute("class", this.cssClassContainerDiv);

        Element innerDiv = htmlDocumentFacade.createBlock();
        StringBuilder innerDivStyle = new StringBuilder();
        innerDivStyle.append("position:absolute;min-width:");
        innerDivStyle.append(normalWidthPx);
        innerDivStyle.append("px;");
        if (maxSpannedWidthPx != Integer.MAX_VALUE) {
            innerDivStyle.append("max-width:");
            innerDivStyle.append(maxSpannedWidthPx);
            innerDivStyle.append("px;");
        }
        innerDivStyle.append("overflow:hidden;max-height:");
        innerDivStyle.append(normalHeightPt);
        innerDivStyle.append("pt;white-space:nowrap;");
        ExcelToHtmlUtils.appendAlign(innerDivStyle, cellStyle.getAlignment());
        htmlDocumentFacade.addStyleClass(outerDiv, cssClassPrefixDiv, innerDivStyle.toString());

        innerDiv.appendChild(text);
        outerDiv.appendChild(innerDiv);
        tableCellElement.appendChild(outerDiv);
    } else {
        tableCellElement.appendChild(text);
    }

    return ExcelToHtmlUtils.isEmpty(value) && cellStyleIndex == 0;
}

From source file:com.wangzhu.poi.ExcelToHtmlConverter.java

License:Apache License

protected boolean processCell(HSSFCell cell, Element tableCellElement, int normalWidthPx, int maxSpannedWidthPx,
        float normalHeightPt) {
    final HSSFCellStyle cellStyle = cell.getCellStyle();

    String value;/*from   w w w . j a  va  2  s .c  om*/
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        // XXX: enrich
        value = cell.getRichStringCellValue().getString();
        break;
    case Cell.CELL_TYPE_FORMULA:
        switch (cell.getCachedFormulaResultType()) {
        case Cell.CELL_TYPE_STRING:
            HSSFRichTextString str = cell.getRichStringCellValue();
            if ((str != null) && (str.length() > 0)) {
                value = (str.toString());
            } else {
                value = ExcelToHtmlUtils.EMPTY;
            }
            break;
        case Cell.CELL_TYPE_NUMERIC:
            HSSFCellStyle style = cellStyle;
            if (style == null) {
                value = String.valueOf(cell.getNumericCellValue());
            } else {
                value = (this._formatter.formatRawCellContents(cell.getNumericCellValue(),
                        style.getDataFormat(), style.getDataFormatString()));
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            value = String.valueOf(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            value = ErrorEval.getText(cell.getErrorCellValue());
            break;
        default:
            ExcelToHtmlConverter.logger.log(POILogger.WARN,
                    "Unexpected cell cachedFormulaResultType (" + cell.getCachedFormulaResultType() + ")");
            value = ExcelToHtmlUtils.EMPTY;
            break;
        }
        break;
    case Cell.CELL_TYPE_BLANK:
        value = ExcelToHtmlUtils.EMPTY;
        break;
    case Cell.CELL_TYPE_NUMERIC:
        value = this._formatter.formatCellValue(cell);
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        value = String.valueOf(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_ERROR:
        value = ErrorEval.getText(cell.getErrorCellValue());
        break;
    default:
        ExcelToHtmlConverter.logger.log(POILogger.WARN, "Unexpected cell type (" + cell.getCellType() + ")");
        return true;
    }

    final boolean noText = ExcelToHtmlUtils.isEmpty(value);
    final boolean wrapInDivs = !noText && this.isUseDivsToSpan() && !cellStyle.getWrapText();

    final short cellStyleIndex = cellStyle.getIndex();
    if (cellStyleIndex != 0) {
        HSSFWorkbook workbook = cell.getRow().getSheet().getWorkbook();
        String mainCssClass = this.getStyleClassName(workbook, cellStyle);
        if (wrapInDivs) {
            tableCellElement.setAttribute("class", mainCssClass + " " + this.cssClassContainerCell);
        } else {
            tableCellElement.setAttribute("class", mainCssClass);
        }

        if (noText) {
            /*
             * if cell style is defined (like borders, etc.) but cell text
             * is empty, add "&nbsp;" to output, so browser won't collapse
             * and ignore cell
             */
            value = "\u00A0";
        }
    }

    if (this.isOutputLeadingSpacesAsNonBreaking() && value.startsWith(" ")) {
        StringBuffer builder = new StringBuffer();
        for (int c = 0; c < value.length(); c++) {
            if (value.charAt(c) != ' ') {
                break;
            }
            builder.append('\u00a0');
        }

        if (value.length() != builder.length()) {
            builder.append(value.substring(builder.length()));
        }

        value = builder.toString();
    }

    Text text = this.htmlDocumentFacade.createText(value);

    if (wrapInDivs) {
        Element outerDiv = this.htmlDocumentFacade.createBlock();
        outerDiv.setAttribute("class", this.cssClassContainerDiv);

        Element innerDiv = this.htmlDocumentFacade.createBlock();
        StringBuffer innerDivStyle = new StringBuffer();
        innerDivStyle.append("position:absolute;min-width:");
        innerDivStyle.append(normalWidthPx);
        innerDivStyle.append("px;");
        if (maxSpannedWidthPx != Integer.MAX_VALUE) {
            innerDivStyle.append("max-width:");
            innerDivStyle.append(maxSpannedWidthPx);
            innerDivStyle.append("px;");
        }
        innerDivStyle.append("overflow:hidden;max-height:");
        innerDivStyle.append(normalHeightPt);
        innerDivStyle.append("pt;white-space:nowrap;");
        ExcelToHtmlUtils.appendAlign(innerDivStyle, cellStyle.getAlignment());
        this.htmlDocumentFacade.addStyleClass(outerDiv, this.cssClassPrefixDiv, innerDivStyle.toString());

        innerDiv.appendChild(text);
        outerDiv.appendChild(innerDiv);
        tableCellElement.appendChild(outerDiv);
    } else {
        tableCellElement.appendChild(text);
    }

    return ExcelToHtmlUtils.isEmpty(value) && (cellStyleIndex == 0);
}

From source file:edu.ku.brc.specify.tasks.subpane.wb.XLSImport.java

License:Open Source License

public DataImportIFace.Status getData(final Workbench workbench) {
    if (config.getStatus() == ConfigureExternalDataIFace.Status.Valid) {
        DateWrapper scrDateFormat = AppPrefsCache.getDateWrapper("ui", "formatting", "scrdateformat");
        try {//from   w  w w  .  j ava 2  s.c  o  m
            InputStream input = new FileInputStream(config.getFile());
            POIFSFileSystem fs = new POIFSFileSystem(input);
            HSSFWorkbook workBook = new HSSFWorkbook(fs);
            HSSFSheet sheet = workBook.getSheetAt(0);
            int numRows = 0;

            // Calculate the number of rows and columns

            Set<WorkbenchTemplateMappingItem> wbtmiSet = workbench.getWorkbenchTemplate()
                    .getWorkbenchTemplateMappingItems();
            Vector<WorkbenchTemplateMappingItem> wbtmiList = new Vector<WorkbenchTemplateMappingItem>();
            NumberFormat nf = NumberFormat.getInstance();
            nf.setMinimumFractionDigits(0);
            nf.setMaximumFractionDigits(20);
            nf.setGroupingUsed(false); //gets rid of commas
            NumberFormat nfGeoCoord = NumberFormat.getInstance();
            nfGeoCoord.setMinimumFractionDigits(0);
            nfGeoCoord.setMaximumFractionDigits(
                    LatLonConverter.DECIMAL_SIZES[LatLonConverter.FORMAT.DDDDDD.ordinal()]);
            nfGeoCoord.setGroupingUsed(false); //gets rid of commas
            char decSep = new DecimalFormatSymbols().getDecimalSeparator();
            wbtmiList.addAll(wbtmiSet);

            Collections.sort(wbtmiList);

            this.truncations.clear();
            Vector<HSSFHyperlink> activeHyperlinks = new Vector<HSSFHyperlink>();

            // Iterate over each row in the sheet
            Iterator<?> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();
                if (numRows == 0 && config.getFirstRowHasHeaders()) {
                    numRows++;
                    getSystemCols(row);
                    continue;
                }

                WorkbenchRow wbRow = workbench.addRow();

                for (WorkbenchTemplateMappingItem wbtmi : wbtmiList) {
                    int cellNum = wbtmi.getOrigImportColumnIndex().intValue();
                    if (cellNum == -1) {
                        if (wbtmi.getViewOrder() != null) {
                            cellNum = wbtmi.getViewOrder().intValue();
                            if (cellNum == -1) {
                                continue;
                            }
                        }
                    }
                    HSSFCell cell = row.getCell(cellNum);
                    if (cell == null) {
                        continue;
                    }
                    int type = cell.getCellType();
                    if (type == HSSFCell.CELL_TYPE_FORMULA) {
                        type = cell.getCachedFormulaResultType();
                    }
                    String value = "";
                    boolean skip = false;

                    switch (type) {
                    case HSSFCell.CELL_TYPE_NUMERIC: {
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            //even if WorkbenchTask.getDataType(wbtmi) is not Calendar or Date. Hmmmm.
                            value = scrDateFormat.getSimpleDateFormat().format(cell.getDateCellValue());
                        } else {
                            Class<?> classObj = WorkbenchTask.getDataType(wbtmi);
                            if (classObj.equals(Integer.class)) {
                                double numeric = cell.getNumericCellValue();
                                value = Integer.toString((int) numeric);

                            } else if (classObj.equals(Calendar.class) || classObj.equals(Date.class)) {
                                Date d = cell.getDateCellValue();
                                if (d != null) {
                                    value = scrDateFormat.getSimpleDateFormat().format(cell.getDateCellValue());
                                } else {
                                    value = null;
                                }
                            } else {
                                double numeric = cell.getNumericCellValue();
                                value = nf.format(numeric);
                                if (isGeoCoordinate(wbtmi)) {
                                    int sepInx = value.indexOf(decSep);
                                    if (sepInx > -1 && value.substring(sepInx).length() > nfGeoCoord
                                            .getMaximumFractionDigits()) {
                                        String value2 = nfGeoCoord.format(numeric);
                                        int maxlen = wbtmi.getFieldName().startsWith("latitude")
                                                ? nfGeoCoord.getMaximumFractionDigits() + 3
                                                : nfGeoCoord.getMaximumFractionDigits() + 4;
                                        if (numeric < 0) {
                                            maxlen++;
                                        }
                                        //System.out.println(value + " " + trackTrunc(value, numRows, wbtmi.getViewOrder(), wbtmi.getCaption(), 
                                        //      maxlen) + " " + value2);
                                        value = value2;
                                    }
                                }
                            }
                        }
                        break;
                    }

                    case HSSFCell.CELL_TYPE_STRING:
                        HSSFHyperlink hl = checkHyperlinks(cell, activeHyperlinks);
                        if (hl == null /*|| (hl != null && hl.getType() == HSSFHyperlink.LINK_EMAIL)*/) {
                            value = cell.getRichStringCellValue().getString();
                        } else {
                            //value = hl.getAddress();
                            value = hl.getLabel();
                        }
                        break;

                    case HSSFCell.CELL_TYPE_BLANK:
                        value = "";
                        type = HSSFCell.CELL_TYPE_STRING;
                        break;

                    case HSSFCell.CELL_TYPE_BOOLEAN:
                        boolean bool = cell.getBooleanCellValue();
                        value = Boolean.toString(bool);
                        break;

                    default:
                        skip = true;
                        break;
                    }

                    if (!skip && value != null && !value.trim().equals("")) {
                        wbRow.setData(truncateIfNecessary(value, numRows, wbtmi), wbtmi.getViewOrder(), true);
                    }
                }
                addImageInfo(row, wbRow);
                addGeoInfo(row, wbRow);
                numRows++;
            }
            if (activeHyperlinks.size() > 0) {
                log.warn("Hyperlinks vector not empty after import. Overlapping hyperlink ranges?");
            }
            return status = this.truncations.size() == 0 && this.messages.size() == 0
                    ? DataImportIFace.Status.Valid
                    : DataImportIFace.Status.Modified;
        } catch (Exception ex) {
            edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount();
            edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(XLSImport.class, ex);
            log.error(ex);
        }
    }
    return status = DataImportIFace.Status.Error;
}