Examples with CellStyle org.apache.poi.ss.usermodel.CellStyle used on opensource projects

Example 6 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project poi by apache.

the class SSPerformanceTest method createStyles.

static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;
    Font headerFont = wb.createFont();
    headerFont.setFontHeightInPoints((short) 14);
    headerFont.setBold(true);
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFont(headerFont);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles.put("header", style);
    Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 12);
    monthFont.setColor(IndexedColors.RED.getIndex());
    monthFont.setBold(true);
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setFont(monthFont);
    styles.put("red-bold", style);
    String[] nfmt = { "#,##0.00", "$#,##0.00", "m/d/yyyy" };
    for (String fmt : nfmt) {
        style = wb.createCellStyle();
        style.setDataFormat(wb.createDataFormat().getFormat(fmt));
        styles.put(fmt, style);
    }
    return styles;
}

Example 7 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project poi by apache.

the class ToHtml method printSheetContent.

private void printSheetContent(Sheet sheet) {
    printColumnHeads();
    out.format("<tbody>%n");
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();
        out.format("  <tr>%n");
        out.format("    <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1);
        for (int i = firstColumn; i < endColumn; i++) {
            String content = "&nbsp;";
            String attrs = "";
            CellStyle style = null;
            if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    style = cell.getCellStyle();
                    attrs = tagStyle(cell, style);
                    //Set the value that is rendered for the cell
                    //also applies the format
                    CellFormat cf = CellFormat.getInstance(style.getDataFormatString());
                    CellFormatResult result = cf.apply(cell);
                    content = result.text;
                    if (content.equals("")) {
                        content = "&nbsp;";
                    }
                }
            }
            out.format("    <td class=%s %s>%s</td>%n", styleName(style), attrs, content);
        }
        out.format("  </tr>%n");
    }
    out.format("</tbody>%n");
}

Example 8 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project poi by apache.

the class XSSFExcelExtractor method handleNonStringCell.

private void handleNonStringCell(StringBuffer text, Cell cell, DataFormatter formatter) {
    CellType type = cell.getCellTypeEnum();
    if (type == CellType.FORMULA) {
        type = cell.getCachedFormulaResultTypeEnum();
    }
    if (type == CellType.NUMERIC) {
        CellStyle cs = cell.getCellStyle();
        if (cs != null && cs.getDataFormatString() != null) {
            String contents = formatter.formatRawCellContents(cell.getNumericCellValue(), cs.getDataFormat(), cs.getDataFormatString());
            checkMaxTextSize(text, contents);
            text.append(contents);
            return;
        }
    }
    // No supported styling applies to this cell
    String contents = ((XSSFCell) cell).getRawValue();
    if (contents != null) {
        checkMaxTextSize(text, contents);
        text.append(contents);
    }
}

Example 9 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project poi by apache.

the class SheetDataWriter method writeCell.

public void writeCell(int columnIndex, Cell cell) throws IOException {
    if (cell == null) {
        return;
    }
    String ref = new CellReference(_rownum, columnIndex).formatAsString();
    _out.write("<c r=\"" + ref + "\"");
    CellStyle cellStyle = cell.getCellStyle();
    if (cellStyle.getIndex() != 0) {
        // need to convert the short to unsigned short as the indexes can be up to 64k
        // ideally we would use int for this index, but that would need changes to some more 
        // APIs
        _out.write(" s=\"" + (cellStyle.getIndex() & 0xffff) + "\"");
    }
    CellType cellType = cell.getCellTypeEnum();
    switch(cellType) {
        case BLANK:
            {
                _out.write(">");
                break;
            }
        case FORMULA:
            {
                _out.write(">");
                _out.write("<f>");
                outputQuotedString(cell.getCellFormula());
                _out.write("</f>");
                switch(cell.getCachedFormulaResultTypeEnum()) {
                    case NUMERIC:
                        double nval = cell.getNumericCellValue();
                        if (!Double.isNaN(nval)) {
                            _out.write("<v>" + nval + "</v>");
                        }
                        break;
                    default:
                        break;
                }
                break;
            }
        case STRING:
            {
                if (_sharedStringSource != null) {
                    XSSFRichTextString rt = new XSSFRichTextString(cell.getStringCellValue());
                    int sRef = _sharedStringSource.addEntry(rt.getCTRst());
                    _out.write(" t=\"" + STCellType.S + "\">");
                    _out.write("<v>");
                    _out.write(String.valueOf(sRef));
                    _out.write("</v>");
                } else {
                    _out.write(" t=\"inlineStr\">");
                    _out.write("<is><t");
                    if (hasLeadingTrailingSpaces(cell.getStringCellValue())) {
                        _out.write(" xml:space=\"preserve\"");
                    }
                    _out.write(">");
                    outputQuotedString(cell.getStringCellValue());
                    _out.write("</t></is>");
                }
                break;
            }
        case NUMERIC:
            {
                _out.write(" t=\"n\">");
                _out.write("<v>" + cell.getNumericCellValue() + "</v>");
                break;
            }
        case BOOLEAN:
            {
                _out.write(" t=\"b\">");
                _out.write("<v>" + (cell.getBooleanCellValue() ? "1" : "0") + "</v>");
                break;
            }
        case ERROR:
            {
                FormulaError error = FormulaError.forInt(cell.getErrorCellValue());
                _out.write(" t=\"e\">");
                _out.write("<v>" + error.getString() + "</v>");
                break;
            }
        default:
            {
                throw new IllegalStateException("Invalid cell type: " + cellType);
            }
    }
    _out.write("</c>");
}

Example 10 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project poi by apache.

the class ConditionalFormats method expiry.

/**
     *  Use Excel conditional formatting to highlight payments that are due in the next thirty days.
     *  In this example, Due dates are entered in cells A2:A4.
     */
static void expiry(Sheet sheet) {
    CellStyle style = sheet.getWorkbook().createCellStyle();
    style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("d-mmm"));
    sheet.createRow(0).createCell(0).setCellValue("Date");
    sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29");
    sheet.createRow(2).createCell(0).setCellFormula("A2+1");
    sheet.createRow(3).createCell(0).setCellFormula("A3+1");
    for (int rownum = 1; rownum <= 3; rownum++) {
        sheet.getRow(rownum).getCell(0).setCellStyle(style);
    }
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontStyle(false, true);
    font.setFontColorIndex(IndexedColors.BLUE.index);
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A4") };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted");
}