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

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

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

Introduction

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

Prototype

public HSSFCellStyle getCellStyle()

Source Link

Document

get the style for the cell.

Usage

From source file:com.pureinfo.studio.db.xls2srm.impl.XlsObjectsImpl.java

License:Open Source License

/**
 * @see com.pureinfo.dolphin.model.IObjects#next()
 *///from w  w w  . j a  va  2s.c o  m
public DolphinObject next() throws PureException {
    HSSFRow row = m_sheet.getRow(m_nCurrent++);
    if (row == null)
        return null;

    //else
    DolphinObject obj = new DolphinObject();
    Object oValue;
    HSSFCell cell;

    int nCellNum = row.getLastCellNum();
    if (nCellNum > m_heads.length) {
        nCellNum = m_heads.length;
    }
    for (int i = 0; i < nCellNum; i++) {
        cell = row.getCell((short) i);
        if (cell == null) {
            oValue = null;
        } else {
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                oValue = new Double(cell.getNumericCellValue());
                break;

            case HSSFCell.CELL_TYPE_STRING:
                oValue = cell.getStringCellValue();
                if (oValue != null)
                    oValue = ((String) oValue).trim();
                break;

            case HSSFCell.CELL_TYPE_FORMULA:
                oValue = new Double(cell.getNumericCellValue());
                break;

            case HSSFCell.CELL_TYPE_BOOLEAN:
                oValue = new Boolean(cell.getBooleanCellValue());
                break;

            case HSSFCell.CELL_TYPE_ERROR:
                throw new PureException(PureException.INVALID_VALUE, "error value in cell[" + i + "]-"
                        + m_heads[i] + ": " + String.valueOf(cell.getErrorCellValue()));
                //case HSSFCell.CELL_TYPE_BLANK:
            default:
                oValue = null;
            }//endcase

            if (oValue instanceof Number) {
                int nFormat = cell.getCellStyle().getDataFormat();
                if (nFormat >= 0xe && nFormat <= 0x16) {
                    oValue = cell.getDateCellValue();
                } else if (nFormat == 1) {
                    oValue = new Long(((Number) oValue).intValue());
                }
            }
        }

        obj.setProperty(m_heads[i], oValue);
    }
    return obj;
}

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;/* www.  j a  va 2s  . c o  m*/
    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.siva.javamultithreading.ExcelUtil.java

public static void copyCell(HSSFWorkbook newWorkbook, HSSFCell oldCell, HSSFCell newCell,
        Map<Integer, HSSFCellStyle> styleMap) {
    if (styleMap != null) {
        int stHashCode = oldCell.getCellStyle().hashCode();
        HSSFCellStyle newCellStyle = styleMap.get(stHashCode);
        if (newCellStyle == null) {
            newCellStyle = newWorkbook.createCellStyle();
            newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
            styleMap.put(stHashCode, newCellStyle);
        }/*from w  w  w. j  a  v a  2s.com*/
        newCell.setCellStyle(newCellStyle);
    }
    switch (oldCell.getCellType()) {
    case HSSFCell.CELL_TYPE_STRING:
        newCell.setCellValue(oldCell.getRichStringCellValue());
        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: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;//  ww w  . j av a2 s  .co m
    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:com.xpn.xwiki.plugin.lucene.textextraction.MSExcelTextExtractor.java

License:Open Source License

/**
 * Extracts all text from each cell of the sheet
 *///from   w w  w  .j a v  a2  s.  c  o  m
private void parseCell(HSSFCell cell, StringBuffer cleanBuffer) {
    String cellValue = null;

    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        cellValue = cell.getRichStringCellValue().getString();
    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
        HSSFCellStyle style = cell.getCellStyle();
        short formatId = style.getDataFormat();
        String formatPattern = mDataFormat.getFormat(formatId);
        formatPattern = replace(formatPattern, "\\ ", " ");

        if (isCellDateFormatted(cell)) {
            // This is a date
            formatPattern = replace(formatPattern, "mmmm", "MMMM");
            formatPattern = replace(formatPattern, "/", ".");
            SimpleDateFormat format;
            try {
                format = new SimpleDateFormat(formatPattern);
            } catch (Throwable thr) {
                if (LOG.isDebugEnabled()) {
                    LOG.debug("Creating date format failed: '" + formatPattern + "'", thr);
                }
                format = new SimpleDateFormat();
            }

            double numberValue = cell.getNumericCellValue();
            Date date = HSSFDateUtil.getJavaDate(numberValue);
            cellValue = format.format(date);
        } else {
            // This is a Number
            DecimalFormat format;
            try {
                format = new DecimalFormat(formatPattern);
            } catch (Throwable thr) {
                if (LOG.isDebugEnabled()) {
                    LOG.debug("Creating number format failed: '" + formatPattern + "'", thr);
                }
                format = new DecimalFormat();
            }

            double numberValue = cell.getNumericCellValue();
            cellValue = format.format(numberValue);
        }
    }

    if (cellValue != null) {
        cellValue = cellValue.trim();
        if (cellValue.length() != 0) {
            cleanBuffer.append(cellValue);
            cleanBuffer.append(" ");
        }
    }
}

From source file:com.xpn.xwiki.plugin.lucene.textextraction.MSExcelTextExtractor.java

License:Open Source License

/**
 * Checks cell is date formatted or not.
 * /*from w ww .j  av a 2 s  .  co  m*/
 * @return boolean
 */
private boolean isCellDateFormatted(HSSFCell cell) {
    short format = cell.getCellStyle().getDataFormat();

    if (HSSFDateUtil.isValidExcelDate(cell.getNumericCellValue())) {
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return true;
        } else {
            String fmtText = mDataFormat.getFormat(format);

            if (fmtText != null) {
                fmtText = fmtText.toLowerCase();

                if (fmtText.indexOf("d") >= 0 || fmtText.indexOf("m") >= 0 || fmtText.indexOf("y") >= 0
                        || fmtText.indexOf("h") >= 0 || fmtText.indexOf("s") >= 0) {
                    return true;
                }
            }
        }
    }

    return false;
}

From source file:corner.orm.tapestry.service.excel.ExcelService.java

License:Apache License

/**
 * exceltitle//from  w ww .j ava 2 s  .com
 * 
 * @param wb
 * @param row
 * @param index
 * @param cellValue
 */
protected void createTitleCell(HSSFWorkbook wb, HSSFRow row, int index, String cellValue) {
    HSSFCell titlecell = row.createCell((short) index);
    if (titlecell.getCellStyle() == null) //??????
        titlecell.setCellStyle(this.getTitleStyle(wb));
    titlecell.setCellValue(new HSSFRichTextString(cellValue));
}

From source file:corner.orm.tapestry.service.excel.ExcelService.java

License:Apache License

/**
 * excelcontent/*from  w w  w.jav a2  s. com*/
 * 
 * @param wb
 * @param row
 * @param index
 * @param cellValue
 */
protected void createContentCell(HSSFWorkbook wb, HSSFRow row, int index, String cellValue) {
    HSSFCell contentcell = row.createCell((short) index);
    if (contentcell.getCellStyle() == null) //??????
        contentcell.setCellStyle(this.getContentCellStyle(wb));
    contentcell.setCellValue(new HSSFRichTextString(cellValue));
}

From source file:de.viaboxx.nlstools.formats.MBExcelPersistencer.java

License:Apache License

private boolean readSheet(MBBundle bundle) {
    if (sheet.getLastRowNum() == 0)
        return false;

    HSSFRow row = sheet.getRow(0);/*from w  w w.ja  v a 2 s. co m*/
    if (row.getLastCellNum() < 1 || row.getCell(1) == null)
        return false;
    bundle.setBaseName(getStringValue(row.getCell(1)));

    row = sheet.getRow(1);
    if (row != null) {
        if (row.getCell(1) != null) {
            bundle.setInterfaceName(getStringValue(row.getCell(1)));
        }

        if (row.getCell(3) != null) {
            bundle.setSqldomain(getStringValue(row.getCell(3)));
        }
    }
    int firstCol = 2;

    rowNum = 3;
    row = sheet.getRow(rowNum++); // read locales

    String aliasOrDescriptionHeader = getStringValue(row.getCell(1)); // backward compatibility
    boolean aliasColumnAvailable = false;
    if (aliasOrDescriptionHeader != null && "Aliases".equals(aliasOrDescriptionHeader.trim())) {
        firstCol++;
        aliasColumnAvailable = true;
    }
    int colNum = firstCol;

    List<String> locales = new ArrayList<String>();

    HSSFCell cell = row.getCell(colNum++);
    while (colNum <= row.getLastCellNum()) {
        if (cell != null) {
            locales.add(getStringValue(cell));
        }
        if (row.getLastCellNum() >= colNum) {
            cell = row.getCell(colNum++);
        } else {
            cell = null;
        }
    }

    row = sheet.getRow(rowNum++);
    while (row != null) {
        if (row.getCell(0) != null) {
            MBEntry entry = new MBEntry();
            bundle.getEntries().add(entry);
            entry.setKey(getStringValue(row.getCell(0)));
            if (aliasColumnAvailable) { // backward compatibility
                String aliasesCommaSeparated = getStringValue(row.getCell(1));
                if (aliasesCommaSeparated != null) {
                    StringTokenizer tokens = new StringTokenizer(aliasesCommaSeparated, ", ");
                    List<String> aliases = new ArrayList<String>();
                    while (tokens.hasMoreTokens()) {
                        aliases.add(tokens.nextToken());
                    }
                    entry.setAliases(aliases);
                }
            }
            if (row.getCell(firstCol - 1) != null) {
                entry.setDescription(getStringValue(row.getCell(firstCol - 1)));
            }
            colNum = firstCol;
            for (String each : locales) {
                cell = row.getCell(colNum++);
                if (cell != null) {
                    final String svalue = getStringValue(cell);
                    if (StringUtils.isNotEmpty(svalue) ||
                    // detect STYLE_MISSING
                            cell.getCellStyle()
                                    .getFillBackgroundColor() == HSSFColor.HSSFColorPredefined.BLUE_GREY
                                            .getIndex()
                            || cell.getCellStyle()
                                    .getFillForegroundColor() == HSSFColor.HSSFColorPredefined.BLUE_GREY
                                            .getIndex()
                            || cell.getCellStyle().getFont(wb).getColor() == Font.COLOR_RED) {
                        MBText text = new MBText();
                        text.setLocale(each);
                        text.setValue(svalue);
                        text.setReview(cell.getCellStyle().getFont(wb).getColor() == Font.COLOR_RED);
                        entry.getTexts().add(text);
                    }
                }
            }
        }
        row = sheet.getRow(rowNum++);
    }
    return true;
}

From source file:fitlibrary.runner.SpreadsheetRunner.java

License:Open Source License

private boolean leftBordered(HSSFCell cell) {
    if (cell == null)
        return false;
    return cell.getCellStyle().getBorderLeft() > 0;
}