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

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

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

Introduction

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

Prototype

void setFont(Font font);

Source Link

Document

set the font for this style

Usage

From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java

License:Apache License

void writeField(Object v, ValueMetaInterface vMeta, ExcelWriterStepField excelField, Row xlsRow, int posX,
        Object[] row, int fieldNr, boolean isTitle) throws KettleException {
    try {/* ww  w.j  a va 2s .co  m*/
        boolean cellExisted = true;
        // get the cell
        Cell cell = xlsRow.getCell(posX);
        if (cell == null) {
            cellExisted = false;
            cell = xlsRow.createCell(posX);
        }

        // if cell existed and existing cell's styles should not be changed, don't
        if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) {

            // if the style of this field is cached, reuse it
            if (!isTitle && data.getCachedStyle(fieldNr) != null) {
                cell.setCellStyle(data.getCachedStyle(fieldNr));
            } else {
                // apply style if requested
                if (excelField != null) {

                    // determine correct cell for title or data rows
                    String styleRef = null;
                    if (!isTitle && !Utils.isEmpty(excelField.getStyleCell())) {
                        styleRef = excelField.getStyleCell();
                    } else if (isTitle && !Utils.isEmpty(excelField.getTitleStyleCell())) {
                        styleRef = excelField.getTitleStyleCell();
                    }

                    if (styleRef != null) {
                        Cell styleCell = getCellFromReference(styleRef);
                        if (styleCell != null && cell != styleCell) {
                            cell.setCellStyle(styleCell.getCellStyle());
                        }
                    }
                }

                // set cell format as specified, specific format overrides cell specification
                if (!isTitle && excelField != null && !Utils.isEmpty(excelField.getFormat())
                        && !excelField.getFormat().startsWith("Image")) {
                    setDataFormat(excelField.getFormat(), cell);
                }
                // cache it for later runs
                if (!isTitle) {
                    data.cacheStyle(fieldNr, cell.getCellStyle());
                }
            }
        }

        // create link on cell if requested
        if (!isTitle && excelField != null && data.linkfieldnrs[fieldNr] >= 0) {
            String link = data.inputRowMeta.getValueMeta(data.linkfieldnrs[fieldNr])
                    .getString(row[data.linkfieldnrs[fieldNr]]);
            if (!Utils.isEmpty(link)) {
                CreationHelper ch = data.wb.getCreationHelper();
                // set the link on the cell depending on link type
                Hyperlink hyperLink = null;
                if (link.startsWith("http:") || link.startsWith("https:") || link.startsWith("ftp:")) {
                    hyperLink = ch.createHyperlink(HyperlinkType.URL);
                    hyperLink.setLabel("URL Link");
                } else if (link.startsWith("mailto:")) {
                    hyperLink = ch.createHyperlink(HyperlinkType.EMAIL);
                    hyperLink.setLabel("Email Link");
                } else if (link.startsWith("'")) {
                    hyperLink = ch.createHyperlink(HyperlinkType.DOCUMENT);
                    hyperLink.setLabel("Link within this document");
                } else {
                    hyperLink = ch.createHyperlink(HyperlinkType.FILE);
                    hyperLink.setLabel("Link to a file");
                }

                hyperLink.setAddress(link);
                cell.setHyperlink(hyperLink);

                // if cell existed and existing cell's styles should not be changed, don't
                if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) {

                    if (data.getCachedLinkStyle(fieldNr) != null) {
                        cell.setCellStyle(data.getCachedLinkStyle(fieldNr));
                    } else {
                        // CellStyle style = cell.getCellStyle();
                        Font origFont = data.wb.getFontAt(cell.getCellStyle().getFontIndex());
                        Font hlink_font = data.wb.createFont();
                        // reporduce original font characteristics

                        hlink_font.setBold(origFont.getBold());
                        hlink_font.setCharSet(origFont.getCharSet());
                        hlink_font.setFontHeight(origFont.getFontHeight());
                        hlink_font.setFontName(origFont.getFontName());
                        hlink_font.setItalic(origFont.getItalic());
                        hlink_font.setStrikeout(origFont.getStrikeout());
                        hlink_font.setTypeOffset(origFont.getTypeOffset());
                        // make it blue and underlined
                        hlink_font.setUnderline(Font.U_SINGLE);
                        hlink_font.setColor(IndexedColors.BLUE.getIndex());
                        CellStyle style = cell.getCellStyle();
                        style.setFont(hlink_font);
                        cell.setCellStyle(style);
                        data.cacheLinkStyle(fieldNr, cell.getCellStyle());
                    }
                }
            }
        }

        // create comment on cell if requrested
        if (!isTitle && excelField != null && data.commentfieldnrs[fieldNr] >= 0
                && data.wb instanceof XSSFWorkbook) {
            String comment = data.inputRowMeta.getValueMeta(data.commentfieldnrs[fieldNr])
                    .getString(row[data.commentfieldnrs[fieldNr]]);
            if (!Utils.isEmpty(comment)) {
                String author = data.commentauthorfieldnrs[fieldNr] >= 0
                        ? data.inputRowMeta.getValueMeta(data.commentauthorfieldnrs[fieldNr]).getString(
                                row[data.commentauthorfieldnrs[fieldNr]])
                        : "Kettle PDI";
                cell.setCellComment(createCellComment(author, comment));
            }
        }
        // cell is getting a formula value or static content
        if (!isTitle && excelField != null && excelField.isFormula()) {
            // formula case
            cell.setCellFormula(vMeta.getString(v));
        } else {
            // static content case
            switch (vMeta.getType()) {
            case ValueMetaInterface.TYPE_DATE:
                if (v != null && vMeta.getDate(v) != null) {
                    cell.setCellValue(vMeta.getDate(v));
                }
                break;
            case ValueMetaInterface.TYPE_BOOLEAN:
                if (v != null) {
                    cell.setCellValue(vMeta.getBoolean(v));
                }
                break;
            case ValueMetaInterface.TYPE_STRING:
            case ValueMetaInterface.TYPE_BINARY:
                if (v != null) {
                    cell.setCellValue(vMeta.getString(v));
                }
                break;
            case ValueMetaInterface.TYPE_BIGNUMBER:
            case ValueMetaInterface.TYPE_NUMBER:
            case ValueMetaInterface.TYPE_INTEGER:
                if (v != null) {
                    cell.setCellValue(vMeta.getNumber(v));
                }
                break;
            default:
                break;
            }
        }
    } catch (Exception e) {
        logError("Error writing field (" + data.posX + "," + data.posY + ") : " + e.toString());
        logError(Const.getStackTracker(e));
        throw new KettleException(e);
    }
}

From source file:org.phenotips.export.internal.Styler.java

License:Open Source License

public void style(DataCell dataCell, Cell cell, Workbook wBook) {
    Set<StyleOption> styles = dataCell.getStyles();
    CellStyle cellStyle = wBook.createCellStyle();
    /* For \n to work properly set to true */
    cellStyle.setWrapText(true);//from w  w  w.j  a v  a  2  s.co  m
    if (this.defaultFont == null) {
        this.defaultFont = createDefaultFont(wBook);
    }
    cellStyle.setFont(this.defaultFont);
    if (styles == null) {
        if (this.styleCache.containsKey(Collections.<StyleOption>emptySet())) {
            cell.setCellStyle(this.styleCache.get(Collections.<StyleOption>emptySet()));
            return;
        }
        cell.setCellStyle(cellStyle);
        this.styleCache.put(Collections.<StyleOption>emptySet(), cellStyle);
        return;
    }

    if (this.styleCache.containsKey(styles)) {
        cell.setCellStyle(this.styleCache.get(styles));
        return;
    }

    /* Priority can be coded in by placing the if statement lower, for higher priority */
    /** Font styles */
    Font headerFont = null;
    if (styles.contains(StyleOption.HEADER)) {
        headerFont = wBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        cellStyle.setFont(headerFont);
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cell.setCellStyle(cellStyle);
    }
    if (styles.contains(StyleOption.LARGE_HEADER)) {
        if (headerFont == null) {
            headerFont = wBook.createFont();
            headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        }
        headerFont.setFontHeightInPoints((short) 12);
        cellStyle.setFont(headerFont);
        cell.setCellStyle(cellStyle);
    }
    if (styles.contains(StyleOption.YES)) {
        Font font = createDefaultFont(wBook);
        font.setColor(HSSFColor.GREEN.index);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    }
    if (styles.contains(StyleOption.NO)) {
        Font font = createDefaultFont(wBook);
        font.setColor(HSSFColor.DARK_RED.index);
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    }

    /** Border styles */
    if (styles.contains(StyleOption.HEADER_BOTTOM)) {
        cellStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
        cell.setCellStyle(cellStyle);
    }
    if (styles.contains(StyleOption.SECTION_BORDER_LEFT)) {
        cellStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
        cell.setCellStyle(cellStyle);
    }
    if (styles.contains(StyleOption.SECTION_BORDER_RIGHT)) {
        cellStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
        cell.setCellStyle(cellStyle);
    }
    if (styles.contains(StyleOption.PATIENT_BORDER)) {
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cell.setCellStyle(cellStyle);
    }
    if (styles.contains(StyleOption.FEATURE_SEPARATOR)) {
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cell.setCellStyle(cellStyle);
    }
    if (styles.contains(StyleOption.YES_NO_SEPARATOR)) {
        cellStyle.setBorderTop(CellStyle.BORDER_DASHED);
        cellStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        cell.setCellStyle(cellStyle);
    }

    /* Keep this as the last statement. */
    this.styleCache.put(styles, cellStyle);
}

From source file:org.primefaces.component.export.ExcelExporter.java

License:Open Source License

protected void applyFacetOptions(Workbook wb, ExporterOptions options, CellStyle facetStyle) {
    Font facetFont = wb.createFont();

    if (options != null) {
        String facetFontStyle = options.getFacetFontStyle();
        if (facetFontStyle != null) {
            if (facetFontStyle.equalsIgnoreCase("BOLD")) {
                facetFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            }//from  w  ww  . j av  a2 s.  c  om
            if (facetFontStyle.equalsIgnoreCase("ITALIC")) {
                facetFont.setItalic(true);
            }
        }

        HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette();
        Color color = null;

        String facetBackground = options.getFacetBgColor();
        if (facetBackground != null) {
            color = Color.decode(facetBackground);
            HSSFColor backgroundColor = palette.findSimilarColor(color.getRed(), color.getGreen(),
                    color.getBlue());
            ((HSSFCellStyle) facetStyle).setFillForegroundColor(backgroundColor.getIndex());
            facetStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        }

        String facetFontColor = options.getFacetFontColor();
        if (facetFontColor != null) {
            color = Color.decode(facetFontColor);
            HSSFColor facetColor = palette.findSimilarColor(color.getRed(), color.getGreen(), color.getBlue());
            ((HSSFFont) facetFont).setColor(facetColor.getIndex());
        }

        String facetFontSize = options.getFacetFontSize();
        if (facetFontSize != null) {
            facetFont.setFontHeightInPoints(Short.valueOf(facetFontSize));
        }
    }

    facetStyle.setFont(facetFont);
}

From source file:org.primefaces.component.export.ExcelExporter.java

License:Open Source License

protected void applyCellOptions(Workbook wb, ExporterOptions options, CellStyle cellStyle) {
    Font cellFont = wb.createFont();

    if (options != null) {
        String cellFontColor = options.getCellFontColor();
        if (cellFontColor != null) {
            HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette();
            Color color = Color.decode(cellFontColor);
            HSSFColor cellColor = palette.findSimilarColor(color.getRed(), color.getGreen(), color.getBlue());
            ((HSSFFont) cellFont).setColor(cellColor.getIndex());
        }//from w ww. ja va  2s .c  o  m

        String cellFontSize = options.getCellFontSize();
        if (cellFontSize != null) {
            cellFont.setFontHeightInPoints(Short.valueOf(cellFontSize));
        }

        String cellFontStyle = options.getCellFontStyle();
        if (cellFontStyle != null) {
            if (cellFontStyle.equalsIgnoreCase("BOLD")) {
                cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            }
            if (cellFontStyle.equalsIgnoreCase("ITALIC")) {
                cellFont.setItalic(true);
            }
        }
    }

    cellStyle.setFont(cellFont);
}

From source file:org.primefaces.component.export.ExcelXExporter.java

License:Open Source License

@Override
protected void applyFacetOptions(Workbook wb, ExporterOptions options, CellStyle facetStyle) {
    Font facetFont = wb.createFont();
    facetFont.setFontName("Arial");

    if (options != null) {
        String facetFontStyle = options.getFacetFontStyle();
        if (facetFontStyle != null) {
            if (facetFontStyle.equalsIgnoreCase("BOLD")) {
                facetFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            }//from   w w w. ja  v  a  2s  .  c o m
            if (facetFontStyle.equalsIgnoreCase("ITALIC")) {
                facetFont.setItalic(true);
            }
        }

        String facetBackground = options.getFacetBgColor();
        if (facetBackground != null) {
            XSSFColor backgroundColor = new XSSFColor(Color.decode(facetBackground));
            ((XSSFCellStyle) facetStyle).setFillForegroundColor(backgroundColor);
            facetStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        }

        String facetFontColor = options.getFacetFontColor();
        if (facetFontColor != null) {
            XSSFColor facetColor = new XSSFColor(Color.decode(facetFontColor));
            ((XSSFFont) facetFont).setColor(facetColor);
        }

        String facetFontSize = options.getFacetFontSize();
        if (facetFontSize != null) {
            facetFont.setFontHeightInPoints(Short.valueOf(facetFontSize));
        }
    }

    facetStyle.setFont(facetFont);
}

From source file:org.primefaces.component.export.ExcelXExporter.java

License:Open Source License

@Override
protected void applyCellOptions(Workbook wb, ExporterOptions options, CellStyle cellStyle) {
    Font cellFont = wb.createFont();
    cellFont.setFontName("Arial");

    if (options != null) {
        String cellFontColor = options.getCellFontColor();
        if (cellFontColor != null) {
            XSSFColor cellColor = new XSSFColor(Color.decode(cellFontColor));
            ((XSSFFont) cellFont).setColor(cellColor);
        }/* w ww.  j a  va  2 s . com*/

        String cellFontSize = options.getCellFontSize();
        if (cellFontSize != null) {
            cellFont.setFontHeightInPoints(Short.valueOf(cellFontSize));
        }

        String cellFontStyle = options.getCellFontStyle();
        if (cellFontStyle != null) {
            if (cellFontStyle.equalsIgnoreCase("BOLD")) {
                cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            }
            if (cellFontStyle.equalsIgnoreCase("ITALIC")) {
                cellFont.setItalic(true);
            }
        }
    }

    cellStyle.setFont(cellFont);
}

From source file:org.projectforge.excel.CellFormat.java

License:Open Source License

/**
 * Please note: the data format should be set by the ContentProvider (for re-usage).
 * @param cellStyle/* w  w  w .  j  a  va  2  s .co  m*/
 */
public void copyToCellStyle(final CellStyle cellStyle) {
    if (alignment != null) {
        cellStyle.setAlignment(alignment);
    }
    if (font != null) {
        cellStyle.setFont(font);
    }
    if (fillForegroundColor != null) {
        cellStyle.setFillForegroundColor(fillForegroundColor);
    }
    if (wrapText != null) {
        cellStyle.setWrapText(wrapText);
    }
}

From source file:org.rapidpm.data.table.export.Table2XLSX.java

License:Apache License

private CellStyle createHyperLinkStyle(final XSSFWorkbook workbook) {
    final CellStyle hlink_style = workbook.createCellStyle();
    final Font hlink_font = workbook.createFont();
    hlink_font.setUnderline(Font.U_SINGLE);
    hlink_font.setColor(IndexedColors.BLUE.getIndex());
    hlink_style.setFont(hlink_font);
    return hlink_style;
}

From source file:org.riflemansd.businessprofit.excel.ExcelExampleFont.java

License:Open Source License

public static void main(String[] args) {
    // create a new file
    FileOutputStream out = null;/*from  w  ww.j  a  v  a2s . c o m*/
    try {
        out = new FileOutputStream("workbook.xls");
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex);
    }
    // create a new workbook
    Workbook wb = new HSSFWorkbook();
    // create a new sheet
    Sheet s = wb.createSheet();
    // declare a row object reference
    Row r = null;
    // declare a cell object reference
    Cell c = null;
    // create 3 cell styles
    CellStyle cs = wb.createCellStyle();
    CellStyle cs2 = wb.createCellStyle();
    CellStyle cs3 = wb.createCellStyle();
    DataFormat df = wb.createDataFormat();
    // create 2 fonts objects
    Font f = wb.createFont();
    Font f2 = wb.createFont();

    //set font 1 to 12 point type
    f.setFontHeightInPoints((short) 12);
    //make it blue
    f.setColor((short) 0xc);
    // make it bold
    //arial is the default font
    f.setBoldweight(Font.BOLDWEIGHT_BOLD);

    //set font 2 to 10 point type
    f2.setFontHeightInPoints((short) 10);
    //make it red
    f2.setColor((short) Font.COLOR_RED);
    //make it bold
    f2.setBoldweight(Font.BOLDWEIGHT_BOLD);

    f2.setStrikeout(true);

    //set cell stlye
    cs.setFont(f);
    //set the cell format 
    cs.setDataFormat(df.getFormat("#,##0.0"));

    //set a thin border
    cs2.setBorderBottom(cs2.BORDER_THIN);
    //fill w fg fill color
    cs2.setFillPattern((short) CellStyle.SOLID_FOREGROUND);
    //set the cell format to text see DataFormat for a full list
    cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));

    // set the font
    cs2.setFont(f2);

    // set the sheet name in Unicode
    wb.setSheetName(0, "\u0422\u0435\u0441\u0442\u043E\u0432\u0430\u044F "
            + "\u0421\u0442\u0440\u0430\u043D\u0438\u0447\u043A\u0430");
    // in case of plain ascii
    // wb.setSheetName(0, "HSSF Test");
    // create a sheet with 30 rows (0-29)
    int rownum;
    for (rownum = (short) 0; rownum < 30; rownum++) {
        // create a row
        r = s.createRow(rownum);
        // on every other row
        if ((rownum % 2) == 0) {
            // make the row height bigger  (in twips - 1/20 of a point)
            r.setHeight((short) 0x249);
        }

        //r.setRowNum(( short ) rownum);
        // create 10 cells (0-9) (the += 2 becomes apparent later
        for (short cellnum = (short) 0; cellnum < 10; cellnum += 2) {
            // create a numeric cell
            c = r.createCell(cellnum);
            // do some goofy math to demonstrate decimals
            c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));

            String cellValue;

            // create a string cell (see why += 2 in the
            c = r.createCell((short) (cellnum + 1));

            // on every other row
            if ((rownum % 2) == 0) {
                // set this cell to the first cell style we defined
                c.setCellStyle(cs);
                // set the cell's string value to "Test"
                c.setCellValue("Test");
            } else {
                c.setCellStyle(cs2);
                // set the cell's string value to "\u0422\u0435\u0441\u0442"
                c.setCellValue("\u0422\u0435\u0441\u0442");
            }

            // make this column a bit wider
            s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20)));
        }
    }

    //draw a thick black border on the row at the bottom using BLANKS
    // advance 2 rows
    rownum++;
    rownum++;

    r = s.createRow(rownum);

    // define the third style to be the default
    // except with a thick black border at the bottom
    cs3.setBorderBottom(cs3.BORDER_THICK);

    //create 50 cells
    for (short cellnum = (short) 0; cellnum < 50; cellnum++) {
        //create a blank type cell (no value)
        c = r.createCell(cellnum);
        // set it to the thick black border style
        c.setCellStyle(cs3);
    }

    //end draw thick black border

    // demonstrate adding/naming and deleting a sheet
    // create a sheet, set its title then delete it
    s = wb.createSheet();
    wb.setSheetName(1, "DeletedSheet");
    wb.removeSheetAt(1);
    //end deleted sheet
    try {
        // write the workbook to the output stream
        // close our file (don't blow out our file handles
        wb.write(out);
    } catch (IOException ex) {
        Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex);
    }
    try {
        out.close();
    } catch (IOException ex) {
        Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:org.riflemansd.businessprofit.excel.MyExcelDocument.java

License:Open Source License

public void setHeader(int nsheet, int nrow, int ncolumn, String value) {
    org.apache.poi.ss.usermodel.Cell cell = getCell(nsheet, nrow, ncolumn);
    CellStyle style = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 12);
    style.setFont(font);
    cell.setCellValue(value);/*from  w  w  w . j  ava2s .c  om*/
    cell.setCellStyle(style);
}