Example usage for org.apache.poi.ss.usermodel CellStyle getFontIndex

List of usage examples for org.apache.poi.ss.usermodel CellStyle getFontIndex

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CellStyle
  6. getFontIndex

Introduction

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

Prototype

@Removal(version = "4.2")
short getFontIndex();

Source Link

Document

gets the index of the font for this style

Usage

From source file:com.vaadin.addon.spreadsheet.SpreadsheetStyleFactory.java

License:Open Source License

private void fontStyle(StringBuilder sb, CellStyle cellStyle) {
    try {/*from w  w w .j  a  v a2 s  .  c o  m*/
        Font font = spreadsheet.getWorkbook().getFontAt(cellStyle.getFontIndex());
        if (font.getIndex() == defaultFont.getIndex()) {
            // uses default font, no need to add styles
            return;
        }
        String fontFamily = styleFontFamily(font);
        if (!fontFamily.equals(defaultFontFamily)) {
            sb.append(fontFamily);
        }
        if (font.getBoldweight() != Font.BOLDWEIGHT_NORMAL) {
            sb.append("font-weight:");
            sb.append(font.getBoldweight());
            sb.append(";");
        }
        if (font.getItalic()) {
            sb.append("font-style:italic;");
        }
        final int fontheight = font.getFontHeightInPoints();
        if (fontheight != defaultFontHeightInPoints) {
            sb.append("font-size:");
            sb.append(fontheight);
            sb.append("pt;");
        }
        if (font.getUnderline() != Font.U_NONE) {
            sb.append("text-decoration:underline;");
        } else if (font.getStrikeout()) {
            sb.append("text-decoration:overline;");
        }
    } catch (IndexOutOfBoundsException ioobe) {
        // somehow workbook doesn't have all the fonts the cells have???
        LOGGER.log(Level.WARNING, "Font missing, " + cellStyle.getFontIndex() + " / " + cellStyle.getClass()
                + ", " + ioobe.getMessage(), ioobe);
    }
}

From source file:de.thb.ue.backend.util.EvaluationExcelFileGenerator.java

License:Apache License

private void createTextualAnswers(@NonNull List<Vote> answers, @NonNull List<String> questionTexts, Sheet sheet,
        Workbook wb) {//  w w w .j  a  va2  s. co  m
    Row row;
    Cell cell;
    row = sheet.createRow(sheet.getLastRowNum() + 2);
    cell = row.createCell(1);
    CellStyle helpStyle = wb.createCellStyle();
    helpStyle.cloneStyleFrom(headerStyle);
    helpStyle.setBorderBottom(CellStyle.BORDER_NONE);
    helpStyle.setBorderTop(CellStyle.BORDER_NONE);
    helpStyle.setBorderLeft(CellStyle.BORDER_NONE);
    helpStyle.setBorderRight(CellStyle.BORDER_NONE);
    cell.setCellValue("Kommentare");
    cell.setCellStyle(helpStyle);

    //TODO used to determine style for current line -> its stupid. Think of something better
    int styleCounter = 0;
    for (String textualQuestion : questionTexts) {
        row = sheet.createRow(sheet.getLastRowNum() + 3);
        cell = row.createCell(1);
        cell.setCellValue(textualQuestion);
        setTextQuestionStyle(cell, styleCounter, true);

        //colorize horizontal neighbour cells of headline
        for (int i = 2; i < 5; i++) {
            cell = row.createCell(i);
            setTextQuestionStyle(cell, styleCounter, false);
        }

        int rowNum = sheet.getLastRowNum();
        int counter = 1;

        for (String comment : aggregateTextAnswers(answers, textualQuestion)) {
            row = sheet.createRow(rowNum + 1);
            cell = row.createCell(1, Cell.CELL_TYPE_STRING);

            //              introduces line breaks in long comments
            ArrayList<String> commentChunks = splitComment(comment);
            StringBuilder formattedComment = new StringBuilder();
            formattedComment.append(Integer.toString(counter));
            formattedComment.append(": ");

            int chunkCounter = 0;
            for (String chunk : commentChunks) {
                formattedComment.append(chunk);

                if ((chunkCounter + 1) < commentChunks.size()) {
                    formattedComment.append(System.lineSeparator());
                }
                chunkCounter++;
            }
            cell.setCellValue(formattedComment.toString());

            CellStyle style = setTextQuestionStyle(cell, styleCounter, false);

            // increase height of row based on font size, number of lines and line spacing
            // the origin of 140 % -> http://superuser.com/questions/337181/how-many-pts-is-1-5-line-spacing-in-microsoft-word-2007
            float pointsPerLine = (wb.getFontAt(style.getFontIndex()).getFontHeightInPoints() * 140) / 100;
            row.setHeightInPoints(pointsPerLine * commentChunks.size());

            //colorize horizontal neighbour cells of comment
            for (int i = 2; i < 17; i++) {
                cell = row.createCell(i);
                setTextQuestionStyle(cell, styleCounter, false);
            }
            sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 1, 17));

            rowNum++;
            counter++;
        }
        styleCounter++;
    }
}

From source file:itpreneurs.itp.report.archive.CellStyleDetails.java

License:Apache License

public static void main(String[] args) throws Exception {

    //     if(args.length == 0) {
    //        throw new IllegalArgumentException("Filename must be given");
    //     }//  ww w  . j av a2 s .  c  om

    String filename = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/Workbook1.xlsx";

    Workbook wb = WorkbookFactory.create(new File(filename));
    DataFormatter formatter = new DataFormatter();

    for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) {
        Sheet sheet = wb.getSheetAt(sn);
        System.out.println("Sheet #" + sn + " : " + sheet.getSheetName());

        for (Row row : sheet) {
            System.out.println("  Row " + row.getRowNum());

            for (Cell cell : row) {
                CellReference ref = new CellReference(cell);
                System.out.print("    " + ref.formatAsString());
                System.out.print(" (" + cell.getColumnIndex() + ") ");

                CellStyle style = cell.getCellStyle();
                System.out.print("Format=" + style.getDataFormatString() + " ");
                System.out.print("FG=" + renderColor(style.getFillForegroundColorColor()) + " ");
                System.out.print("BG=" + renderColor(style.getFillBackgroundColorColor()) + " ");

                Font font = wb.getFontAt(style.getFontIndex());
                System.out.print("Font=" + font.getFontName() + " ");
                System.out.print("FontColor=");
                if (font instanceof HSSFFont) {
                    System.out.print(renderColor(((HSSFFont) font).getHSSFColor((HSSFWorkbook) wb)));
                }
                if (font instanceof XSSFFont) {
                    System.out.print(renderColor(((XSSFFont) font).getXSSFColor()));
                }

                System.out.println();
                System.out.println("        " + formatter.formatCellValue(cell));
            }
        }

        System.out.println();
    }
}

From source file:net.ceos.project.poi.annotated.core.CellStyleHandler.java

License:Apache License

/**
 * Clone a cell style passed as parameter.
 * /*from  w  w  w  .  ja v a  2s . co m*/
 * @param wb
 *            the {@link Workbook} in use
 * @param csBase
 *            the {@link CellStyle} base
 * @return the new cell style
 */
private static CellStyle cloneCellStyle(final Workbook wb, final CellStyle csBase) {
    CellStyle cs = cellStyleFactory.newInstance(wb);
    cs.setAlignment(csBase.getAlignment());
    cs.setVerticalAlignment(csBase.getVerticalAlignment());
    cs.setBorderTop(csBase.getBorderTop());
    cs.setBorderBottom(csBase.getBorderBottom());
    cs.setBorderLeft(csBase.getBorderLeft());
    cs.setBorderRight(csBase.getBorderRight());
    cs.setFillForegroundColor(csBase.getFillForegroundColor());
    cs.setFillPattern(csBase.getFillPattern());
    cs.setWrapText(csBase.getWrapText());

    cs.setFont(wb.getFontAt(csBase.getFontIndex()));
    return cs;
}

From source file:net.ceos.project.poi.annotated.core.ConditionalFormattingHandler.java

License:Apache License

/**
 * Apply the conditional formatting according the values defined at the
 * respective annotation. Is only available at the declared object, in
 * another words, at the linked {@link Sheet} with the object.
 * /* ww  w.  j  a v  a 2s. c o  m*/
 * @param configCriteria
 *            the {@link XConfigCriteria}
 * @param conditionalFomat
 *            the {@link XlsConditionalFormat}
 * @throws ConfigurationException
 */
protected static void applyCondition(XConfigCriteria configCriteria, XlsConditionalFormat conditionalFomat)
        throws ConfigurationException {
    // Define a Conditional Formatting rule, which triggers formatting
    // according the developer definition and applies patternFormatting
    SheetConditionalFormatting sheet = configCriteria.getSheet().getSheetConditionalFormatting();

    /* apply all rules defined */
    int i = 0;
    ConditionalFormattingRule[] rules = new ConditionalFormattingRule[conditionalFomat.rules().length];
    XlsConditionalFormatRules[] rulesAnnotated = conditionalFomat.rules();
    for (XlsConditionalFormatRules rule : rulesAnnotated) {
        ConditionalFormattingRule setRule = sheet.createConditionalFormattingRule(rule.operator(),
                rule.formula1(), StringUtils.isNotBlank(rule.formula2()) ? rule.formula2() : null);

        CellStyle decorator = null;
        try {
            decorator = configCriteria.getCellStyle(conditionalFomat.decorator());
        } catch (ElementException e) {
            throw new ConfigurationException(ExceptionMessage.CONFIGURATION_DECORATOR_MISSING.getMessage(), e);
        }
        /* add FontFormatting */
        FontFormatting fontFormat = setRule.createFontFormatting();
        Font f = configCriteria.getWorkbook().getFontAt(decorator.getFontIndex());
        fontFormat.setFontStyle(f.getItalic(), f.getBold());
        fontFormat.setFontColorIndex(f.getColor());
        fontFormat.setUnderlineType(f.getUnderline());

        /* add BorderFormatting */
        BorderFormatting borderFormat = setRule.createBorderFormatting();
        borderFormat.setBorderBottom(decorator.getBorderBottom());
        borderFormat.setBorderTop(decorator.getBorderTop());
        borderFormat.setBorderLeft(decorator.getBorderLeft());
        borderFormat.setBorderRight(decorator.getBorderRight());
        borderFormat.setBottomBorderColor(decorator.getBottomBorderColor());
        borderFormat.setTopBorderColor(decorator.getTopBorderColor());
        borderFormat.setLeftBorderColor(decorator.getLeftBorderColor());
        borderFormat.setRightBorderColor(decorator.getRightBorderColor());

        /* add PatternFormatting */
        PatternFormatting patternFormat = setRule.createPatternFormatting();
        patternFormat.setFillBackgroundColor(decorator.getFillForegroundColor());

        /* join rule */
        rules[i++] = setRule;
    }

    /* Define a region */
    CellRangeAddress[] regions = { CellRangeAddress.valueOf(CellFormulaConverter
            .calculateRangeAddressFromTemplate(configCriteria, conditionalFomat.rangeAddress())) };

    /* Apply Conditional Formatting rule defined above to the regions */
    sheet.addConditionalFormatting(regions, rules);

}

From source file:org.apache.metamodel.excel.ExcelUtils.java

License:Apache License

public static Style getCellStyle(Workbook workbook, Cell cell) {
    if (cell == null) {
        return Style.NO_STYLE;
    }/* w w w  .  j  a v  a 2 s. com*/
    final CellStyle cellStyle = cell.getCellStyle();

    final short fontIndex = cellStyle.getFontIndex();
    final Font font = workbook.getFontAt(fontIndex);
    final StyleBuilder styleBuilder = new StyleBuilder();

    // Font bold, italic, underline
    if (font.getBoldweight() >= Font.BOLDWEIGHT_BOLD) {
        styleBuilder.bold();
    }
    if (font.getItalic()) {
        styleBuilder.italic();
    }
    if (font.getUnderline() != FontUnderline.NONE.getByteValue()) {
        styleBuilder.underline();
    }

    // Font size
    final Font stdFont = workbook.getFontAt((short) 0);
    final short fontSize = font.getFontHeightInPoints();
    if (stdFont.getFontHeightInPoints() != fontSize) {
        styleBuilder.fontSize(fontSize, SizeUnit.PT);
    }

    // Font color
    final short colorIndex = font.getColor();
    if (font instanceof HSSFFont) {
        if (colorIndex != HSSFFont.COLOR_NORMAL) {
            final HSSFWorkbook wb = (HSSFWorkbook) workbook;
            HSSFColor color = wb.getCustomPalette().getColor(colorIndex);
            if (color != null) {
                short[] triplet = color.getTriplet();
                styleBuilder.foreground(triplet);
            }
        }
    } else if (font instanceof XSSFFont) {
        XSSFFont xssfFont = (XSSFFont) font;

        XSSFColor color = xssfFont.getXSSFColor();
        if (color != null) {
            String argbHex = color.getARGBHex();
            if (argbHex != null) {
                styleBuilder.foreground(argbHex.substring(2));
            }
        }
    } else {
        throw new IllegalStateException(
                "Unexpected font type: " + (font == null ? "null" : font.getClass()) + ")");
    }

    // Background color
    if (cellStyle.getFillPattern() == 1) {
        Color color = cellStyle.getFillForegroundColorColor();
        if (color instanceof HSSFColor) {
            short[] triplet = ((HSSFColor) color).getTriplet();
            if (triplet != null) {
                styleBuilder.background(triplet);
            }
        } else if (color instanceof XSSFColor) {
            String argb = ((XSSFColor) color).getARGBHex();
            if (argb != null) {
                styleBuilder.background(argb.substring(2));
            }
        } else {
            throw new IllegalStateException(
                    "Unexpected color type: " + (color == null ? "null" : color.getClass()) + ")");
        }
    }

    // alignment
    switch (cellStyle.getAlignment()) {
    case CellStyle.ALIGN_LEFT:
        styleBuilder.leftAligned();
        break;
    case CellStyle.ALIGN_RIGHT:
        styleBuilder.rightAligned();
        break;
    case CellStyle.ALIGN_CENTER:
        styleBuilder.centerAligned();
        break;
    case CellStyle.ALIGN_JUSTIFY:
        styleBuilder.justifyAligned();
        break;
    }

    return styleBuilder.create();
}

From source file:org.drugepi.table.CellStyleLookup.java

License:Mozilla Public License

public static String styleToString(CellStyle style) {
    StringBuffer sb = new StringBuffer();
    sb.append("getDataFormatString=" + style.getDataFormatString() + "\n");
    sb.append("getFontIndex=" + style.getFontIndex() + "\n");
    sb.append("getHidden=" + style.getHidden() + "\n");
    sb.append("getAlignment=" + style.getAlignment() + "\n");
    sb.append("getWrapText=" + style.getWrapText() + "\n");
    sb.append("getVerticalAlignment=" + style.getVerticalAlignment() + "\n");
    sb.append("getRotation=" + style.getRotation() + "\n");
    sb.append("getIndention=" + style.getIndention() + "\n");
    sb.append("getBorderLeft=" + style.getBorderLeft() + "\n");
    sb.append("getBorderRight=" + style.getBorderRight() + "\n");
    sb.append("getBorderTop=" + style.getBorderTop() + "\n");
    sb.append("getBorderBottom=" + style.getBorderBottom() + "\n");
    sb.append("getLeftBorderColor=" + style.getLeftBorderColor() + "\n");
    sb.append("getRightBorderColor=" + style.getRightBorderColor() + "\n");
    sb.append("getTopBorderColor=" + style.getTopBorderColor() + "\n");
    sb.append("getBottomBorderColor=" + style.getBottomBorderColor() + "\n");
    sb.append("getFillPattern=" + style.getFillPattern() + "\n");
    sb.append("getFillBackgroundColor=" + style.getFillBackgroundColor() + "\n");
    sb.append("getFillForegroundColor=" + style.getFillForegroundColor() + "\n");

    return sb.toString();
}

From source file:org.joeffice.spreadsheet.cell.CellRenderer.java

License:Apache License

public static void decorateComponent(Cell cell, JComponent renderingComponent, JComponent defaultRenderer) {
    CellStyle style = cell.getCellStyle();

    // Background neither the index or the color works for XSSF cells
    Color backgroundColor = CellUtils.poiToAwtColor(style.getFillBackgroundColorColor());
    if (backgroundColor != null) {
        renderingComponent.setBackground(backgroundColor);
    } else {/* w  w w. java2 s  .  c  o  m*/
        renderingComponent.setBackground(defaultRenderer.getBackground());
    }

    // Font and forground
    short fontIndex = style.getFontIndex();
    if (fontIndex > 0) {
        Font xlsFont = cell.getSheet().getWorkbook().getFontAt(fontIndex);
        java.awt.Font font = java.awt.Font.decode(xlsFont.getFontName());
        font = font.deriveFont((float) xlsFont.getFontHeightInPoints());
        font = font.deriveFont(java.awt.Font.PLAIN);
        if (xlsFont.getItalic()) {
            font = font.deriveFont(java.awt.Font.ITALIC);
        }
        if (xlsFont.getBoldweight() == Font.BOLDWEIGHT_BOLD) {
            font = font.deriveFont(java.awt.Font.BOLD);
        }
        if (xlsFont.getUnderline() > Font.U_NONE) {
            // no underline in fonts
        }
        short fontColorIndex = xlsFont.getColor();
        Color fontColor = CellUtils.shortToColor(fontColorIndex);
        if (fontColor != null) {
            renderingComponent.setForeground(fontColor);
        } else {
            renderingComponent.setForeground(defaultRenderer.getForeground());
        }
        renderingComponent.setFont(font);
    } else {
        renderingComponent.setForeground(defaultRenderer.getForeground());
        renderingComponent.setFont(defaultRenderer.getFont());
    }

    // Borders
    // At the moment done in renderer but should be done with a JLayer to paint over the grid
    renderingComponent.setBorder(new CellBorder(cell));

    if (cell.getCellComment() != null) {
        renderingComponent.setToolTipText(cell.getCellComment().getString().getString());
    }
}

From source file:org.joeffice.spreadsheet.TableStyleable.java

License:Apache License

private Font copyFont(Cell cell) {
    CellStyle style = cell.getCellStyle();
    Workbook workbook = cell.getSheet().getWorkbook();
    short fontIndex = style.getFontIndex();
    Font xlsFont = cell.getSheet().getWorkbook().getFontAt(fontIndex);
    Font newFont = workbook.createFont();
    newFont.setFontName(xlsFont.getFontName());
    newFont.setFontHeight((short) xlsFont.getFontHeight());
    newFont.setBoldweight(xlsFont.getBoldweight());
    newFont.setItalic(xlsFont.getItalic());
    newFont.setUnderline(xlsFont.getUnderline());
    newFont.setColor(xlsFont.getColor());
    return newFont;
}

From source file:org.netxilia.impexp.impl.ExcelImportService.java

License:Open Source License

private ICellCommand copyCell(Cell poiCell, CellReference cellReference, HSSFPalette palette,
        NetxiliaStyleResolver styleResolver) throws FormulaParsingException {

    CellStyle poiStyle = poiCell.getCellStyle();
    Styles styles = PoiUtils.poiStyle2Netxilia(poiStyle,
            poiCell.getSheet().getWorkbook().getFontAt(poiStyle.getFontIndex()), palette, styleResolver);

    IGenericValue value = null;//from  w  w  w. ja v a 2 s  .  c om
    Formula formula = null;

    // log.info("CELL TYPE:" + cellReference + " type:" + poiCell.getCellType() + " "
    // + (poiCell.getCellType() == Cell.CELL_TYPE_FORMULA ? poiCell.getCellFormula() : "no"));
    switch (poiCell.getCellType()) {
    // TODO translate errors

    case Cell.CELL_TYPE_STRING:
        value = new StringValue(poiCell.getStringCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(poiCell)) {
            DateTime dt = new DateTime(poiCell.getDateCellValue());
            // TODO decide whether is date or time
            if (dt.isBefore(EXCEL_START)) {
                value = new DateValue(dt.toLocalTime());
            } else if (dt.getMillisOfDay() == 0) {
                value = new DateValue(dt.toLocalDate());
            } else {
                value = new DateValue(dt.toLocalDateTime());
            }
        } else {
            value = new NumberValue(poiCell.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        value = new BooleanValue(poiCell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        if (poiCell.getCellFormula() != null) {
            formula = formulaParser.parseFormula(new Formula("=" + poiCell.getCellFormula()));
        }
        break;
    }

    if ((styles == null || styles.getItems().isEmpty()) && formula == null
            && (value == null || value.equals(GenericValueUtils.EMTPY_STRING))) {
        return null;
    }
    return CellCommands.cell(new AreaReference(cellReference), value, formula, styles);
}