Examples with HSSFCellStyle org.apache.poi.hssf.usermodel.HSSFCellStyle used on opensource projects

Example 31 with HSSFCellStyle

use of org.apache.poi.hssf.usermodel.HSSFCellStyle in project poi by apache.

the class ExcelExtractor method getText.

@Override
public String getText() {
    StringBuffer text = new StringBuffer();
    // We don't care about the difference between
    //  null (missing) and blank cells
    _wb.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
    // Process each sheet in turn
    for (int i = 0; i < _wb.getNumberOfSheets(); i++) {
        HSSFSheet sheet = _wb.getSheetAt(i);
        if (sheet == null) {
            continue;
        }
        if (_includeSheetNames) {
            String name = _wb.getSheetName(i);
            if (name != null) {
                text.append(name);
                text.append("\n");
            }
        }
        // Header text, if there is any
        if (_includeHeadersFooters) {
            text.append(_extractHeaderFooter(sheet.getHeader()));
        }
        int firstRow = sheet.getFirstRowNum();
        int lastRow = sheet.getLastRowNum();
        for (int j = firstRow; j <= lastRow; j++) {
            HSSFRow row = sheet.getRow(j);
            if (row == null) {
                continue;
            }
            // Check each cell in turn
            int firstCell = row.getFirstCellNum();
            int lastCell = row.getLastCellNum();
            if (_includeBlankCells) {
                firstCell = 0;
            }
            for (int k = firstCell; k < lastCell; k++) {
                HSSFCell cell = row.getCell(k);
                boolean outputContents = true;
                if (cell == null) {
                    // Only output if requested
                    outputContents = _includeBlankCells;
                } else {
                    switch(cell.getCellTypeEnum()) {
                        case STRING:
                            text.append(cell.getRichStringCellValue().getString());
                            break;
                        case NUMERIC:
                            text.append(_formatter.formatCellValue(cell));
                            break;
                        case BOOLEAN:
                            text.append(cell.getBooleanCellValue());
                            break;
                        case ERROR:
                            text.append(ErrorEval.getText(cell.getErrorCellValue()));
                            break;
                        case FORMULA:
                            if (!_shouldEvaluateFormulas) {
                                text.append(cell.getCellFormula());
                            } else {
                                switch(cell.getCachedFormulaResultTypeEnum()) {
                                    case STRING:
                                        HSSFRichTextString str = cell.getRichStringCellValue();
                                        if (str != null && str.length() > 0) {
                                            text.append(str);
                                        }
                                        break;
                                    case NUMERIC:
                                        HSSFCellStyle style = cell.getCellStyle();
                                        double nVal = cell.getNumericCellValue();
                                        short df = style.getDataFormat();
                                        String dfs = style.getDataFormatString();
                                        text.append(_formatter.formatRawCellContents(nVal, df, dfs));
                                        break;
                                    case BOOLEAN:
                                        text.append(cell.getBooleanCellValue());
                                        break;
                                    case ERROR:
                                        text.append(ErrorEval.getText(cell.getErrorCellValue()));
                                        break;
                                    default:
                                        throw new IllegalStateException("Unexpected cell cached formula result type: " + cell.getCachedFormulaResultTypeEnum());
                                }
                            }
                            break;
                        default:
                            throw new RuntimeException("Unexpected cell type (" + cell.getCellTypeEnum() + ")");
                    }
                    // Output the comment, if requested and exists
                    HSSFComment comment = cell.getCellComment();
                    if (_includeCellComments && comment != null) {
                        // Replace any newlines with spaces, otherwise it
                        //  breaks the output
                        String commentText = comment.getString().getString().replace('\n', ' ');
                        text.append(" Comment by " + comment.getAuthor() + ": " + commentText);
                    }
                }
                // Output a tab if we're not on the last cell
                if (outputContents && k < (lastCell - 1)) {
                    text.append("\t");
                }
            }
            // Finish off the row
            text.append("\n");
        }
        // Finally Footer text, if there is any
        if (_includeHeadersFooters) {
            text.append(_extractHeaderFooter(sheet.getFooter()));
        }
    }
    return text.toString();
}

Example 32 with HSSFCellStyle

use of org.apache.poi.hssf.usermodel.HSSFCellStyle in project poi by apache.

the class AbstractExcelConverter method isTextEmpty.

protected boolean isTextEmpty(HSSFCell cell) {
    final String value;
    switch(cell.getCellTypeEnum()) {
        case STRING:
            // XXX: enrich
            value = cell.getRichStringCellValue().getString();
            break;
        case FORMULA:
            switch(cell.getCachedFormulaResultTypeEnum()) {
                case STRING:
                    HSSFRichTextString str = cell.getRichStringCellValue();
                    if (str == null || str.length() <= 0)
                        return false;
                    value = str.toString();
                    break;
                case NUMERIC:
                    HSSFCellStyle style = cell.getCellStyle();
                    double nval = cell.getNumericCellValue();
                    short df = style.getDataFormat();
                    String dfs = style.getDataFormatString();
                    value = _formatter.formatRawCellContents(nval, df, dfs);
                    break;
                case BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case ERROR:
                    value = ErrorEval.getText(cell.getErrorCellValue());
                    break;
                default:
                    value = ExcelToHtmlUtils.EMPTY;
                    break;
            }
            break;
        case BLANK:
            value = ExcelToHtmlUtils.EMPTY;
            break;
        case NUMERIC:
            value = _formatter.formatCellValue(cell);
            break;
        case BOOLEAN:
            value = String.valueOf(cell.getBooleanCellValue());
            break;
        case ERROR:
            value = ErrorEval.getText(cell.getErrorCellValue());
            break;
        default:
            return true;
    }
    return ExcelToHtmlUtils.isEmpty(value);
}

Example 33 with HSSFCellStyle

use of org.apache.poi.hssf.usermodel.HSSFCellStyle in project poi by apache.

the class ExcelToHtmlConverter method processCell.

protected boolean processCell(HSSFCell cell, Element tableCellElement, int normalWidthPx, int maxSpannedWidthPx, float normalHeightPt) {
    final HSSFCellStyle cellStyle = cell.getCellStyle();
    String value;
    switch(cell.getCellTypeEnum()) {
        case STRING:
            // XXX: enrich
            value = cell.getRichStringCellValue().getString();
            break;
        case FORMULA:
            switch(cell.getCachedFormulaResultTypeEnum()) {
                case STRING:
                    HSSFRichTextString str = cell.getRichStringCellValue();
                    if (str != null && str.length() > 0) {
                        value = (str.toString());
                    } else {
                        value = ExcelToHtmlUtils.EMPTY;
                    }
                    break;
                case NUMERIC:
                    double nValue = cell.getNumericCellValue();
                    short df = cellStyle.getDataFormat();
                    String dfs = cellStyle.getDataFormatString();
                    value = _formatter.formatRawCellContents(nValue, df, dfs);
                    break;
                case BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case ERROR:
                    value = ErrorEval.getText(cell.getErrorCellValue());
                    break;
                default:
                    logger.log(POILogger.WARN, "Unexpected cell cachedFormulaResultType (" + cell.getCachedFormulaResultTypeEnum() + ")");
                    value = ExcelToHtmlUtils.EMPTY;
                    break;
            }
            break;
        case BLANK:
            value = ExcelToHtmlUtils.EMPTY;
            break;
        case NUMERIC:
            value = _formatter.formatCellValue(cell);
            break;
        case BOOLEAN:
            value = String.valueOf(cell.getBooleanCellValue());
            break;
        case ERROR:
            value = ErrorEval.getText(cell.getErrorCellValue());
            break;
        default:
            logger.log(POILogger.WARN, "Unexpected cell type (" + cell.getCellTypeEnum() + ")");
            return true;
    }
    final boolean noText = ExcelToHtmlUtils.isEmpty(value);
    final boolean wrapInDivs = !noText && isUseDivsToSpan() && !cellStyle.getWrapText();
    if (cellStyle.getIndex() != 0) {
        @SuppressWarnings("resource") 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 " " to output, so browser won't collapse
                 * and ignore cell
                 */
            value = " ";
        }
    }
    if (isOutputLeadingSpacesAsNonBreaking() && value.startsWith(" ")) {
        StringBuilder builder = new StringBuilder();
        for (int c = 0; c < value.length(); c++) {
            if (value.charAt(c) != ' ')
                break;
            builder.append(' ');
        }
        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) && (cellStyle.getIndex() == 0);
}

Example 34 with HSSFCellStyle

use of org.apache.poi.hssf.usermodel.HSSFCellStyle in project poi by apache.

the class NumberComparingSpreadsheetGenerator method writeHeaderRow.

static void writeHeaderRow(HSSFWorkbook wb, HSSFSheet sheet) {
    sheet.setColumnWidth(0, 6000);
    sheet.setColumnWidth(1, 6000);
    sheet.setColumnWidth(2, 3600);
    sheet.setColumnWidth(3, 3600);
    sheet.setColumnWidth(4, 2400);
    sheet.setColumnWidth(5, 2400);
    sheet.setColumnWidth(6, 2400);
    sheet.setColumnWidth(7, 2400);
    sheet.setColumnWidth(8, 2400);
    HSSFRow row = sheet.createRow(0);
    HSSFCellStyle style = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    style.setFont(font);
    writeHeaderCell(row, 0, "Raw Long Bits A", style);
    writeHeaderCell(row, 1, "Raw Long Bits B", style);
    writeHeaderCell(row, 2, "Value A", style);
    writeHeaderCell(row, 3, "Value B", style);
    writeHeaderCell(row, 4, "Exp Cmp", style);
    writeHeaderCell(row, 5, "LT", style);
    writeHeaderCell(row, 6, "EQ", style);
    writeHeaderCell(row, 7, "GT", style);
    writeHeaderCell(row, 8, "Check", style);
}

Example 35 with HSSFCellStyle

use of org.apache.poi.hssf.usermodel.HSSFCellStyle in project poi by apache.

the class NumberRenderingSpreadsheetGenerator method writeHeaderRow.

static void writeHeaderRow(HSSFWorkbook wb, HSSFSheet sheet) {
    sheet.setColumnWidth(0, 3000);
    sheet.setColumnWidth(1, 6000);
    sheet.setColumnWidth(2, 6000);
    sheet.setColumnWidth(3, 6000);
    sheet.setColumnWidth(4, 6000);
    sheet.setColumnWidth(5, 1600);
    sheet.setColumnWidth(6, 20000);
    HSSFRow row = sheet.createRow(0);
    HSSFCellStyle style = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    style.setFont(font);
    writeHeaderCell(row, 0, "Value", style);
    writeHeaderCell(row, 1, "Raw Long Bits", style);
    writeHeaderCell(row, 2, "JDK Double Rendering", style);
    writeHeaderCell(row, 3, "Actual Rendering", style);
    writeHeaderCell(row, 4, "Expected Rendering", style);
    writeHeaderCell(row, 5, "Match", style);
    writeHeaderCell(row, 6, "Java Metadata", style);
}