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

Example 1 with FontFormatting

use of org.apache.poi.ss.usermodel.FontFormatting 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");
}

Example 2 with FontFormatting

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

the class ConditionalFormats method errors.

/**
     *  Use Excel conditional formatting to check for errors,
     *  and change the font colour to match the cell colour.
     *  In this example, if formula result is  #DIV/0! then it will have white font colour.
     */
static void errors(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue(84);
    sheet.createRow(1).createCell(0).setCellValue(0);
    sheet.createRow(2).createCell(0).setCellFormula("ROUND(A1/A2,0)");
    sheet.createRow(3).createCell(0).setCellValue(0);
    sheet.createRow(4).createCell(0).setCellFormula("ROUND(A6/A4,0)");
    sheet.createRow(5).createCell(0).setCellValue(41);
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    // Condition 1: Formula Is   =ISERROR(C2)   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("ISERROR(A1)");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontColorIndex(IndexedColors.WHITE.index);
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A6") };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheet.getRow(2).createCell(1).setCellValue("<== The error in this cell is hidden. Condition: Formula Is   =ISERROR(C2)   (White Font)");
    sheet.getRow(4).createCell(1).setCellValue("<== The error in this cell is hidden. Condition: Formula Is   =ISERROR(C2)   (White Font)");
}

Example 3 with FontFormatting

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

the class ConditionalFormats method evaluateRules.

/**
     * Print out a summary of the conditional formatting rules applied to cells on the given sheet.
     * Only cells with a matching rule are printed, and for those, all matching rules are sumarized.
     * @param wb
     * @param sheetName
     */
static void evaluateRules(Workbook wb, String sheetName) {
    final WorkbookEvaluatorProvider wbEvalProv = (WorkbookEvaluatorProvider) wb.getCreationHelper().createFormulaEvaluator();
    final ConditionalFormattingEvaluator cfEval = new ConditionalFormattingEvaluator(wb, wbEvalProv);
    // if cell values have changed, clear cached format results
    cfEval.clearAllCachedValues();
    final Sheet sheet = wb.getSheet(sheetName);
    for (Row r : sheet) {
        for (Cell c : r) {
            final List<EvaluationConditionalFormatRule> rules = cfEval.getConditionalFormattingForCell(c);
            // check rules list for null, although current implementation will return an empty list, not null, then do what you want with results
            if (rules == null || rules.isEmpty())
                continue;
            final CellReference ref = ConditionalFormattingEvaluator.getRef(c);
            if (rules.isEmpty())
                continue;
            System.out.println("\n" + ref.formatAsString() + " has conditional formatting.");
            for (EvaluationConditionalFormatRule rule : rules) {
                ConditionalFormattingRule cf = rule.getRule();
                StringBuilder b = new StringBuilder();
                b.append("\tRule ").append(rule.getFormattingIndex()).append(": ");
                // check for color scale
                if (cf.getColorScaleFormatting() != null) {
                    b.append("\n\t\tcolor scale (caller must calculate bucket)");
                }
                // check for data bar
                if (cf.getDataBarFormatting() != null) {
                    b.append("\n\t\tdata bar (caller must calculate bucket)");
                }
                // check for icon set
                if (cf.getMultiStateFormatting() != null) {
                    b.append("\n\t\ticon set (caller must calculate icon bucket)");
                }
                // check for fill
                if (cf.getPatternFormatting() != null) {
                    final PatternFormatting fill = cf.getPatternFormatting();
                    b.append("\n\t\tfill pattern ").append(fill.getFillPattern()).append(" color index ").append(fill.getFillBackgroundColor());
                }
                // font stuff
                if (cf.getFontFormatting() != null) {
                    final FontFormatting ff = cf.getFontFormatting();
                    b.append("\n\t\tfont format ").append("color index ").append(ff.getFontColorIndex());
                    if (ff.isBold())
                        b.append(" bold");
                    if (ff.isItalic())
                        b.append(" italic");
                    if (ff.isStruckout())
                        b.append(" strikeout");
                    b.append(" underline index ").append(ff.getUnderlineType());
                }
                System.out.println(b);
            }
        }
    }
}

Example 4 with FontFormatting

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

the class ConditionalFormats method hideDupplicates.

/**
     * Use Excel conditional formatting to hide the duplicate values,
     * and make the list easier to read. In this example, when the table is sorted by Region,
     * the second (and subsequent) occurences of each region name will have white font colour.
     */
static void hideDupplicates(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("City");
    sheet.createRow(1).createCell(0).setCellValue("Boston");
    sheet.createRow(2).createCell(0).setCellValue("Boston");
    sheet.createRow(3).createCell(0).setCellValue("Chicago");
    sheet.createRow(4).createCell(0).setCellValue("Chicago");
    sheet.createRow(5).createCell(0).setCellValue("New York");
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("A2=A1");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontColorIndex(IndexedColors.WHITE.index);
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A6") };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheet.getRow(1).createCell(1).setCellValue("<== the second (and subsequent) " + "occurences of each region name will have white font colour.  " + "Condition: Formula Is   =A2=A1   (White Font)");
}

Example 5 with FontFormatting

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

the class ConditionalFormats method overlapping.

/**
     * Multiple conditional formatting rules can apply to
     *  one cell, some combining, some beating others.
     * Done in order of the rules added to the 
     *  SheetConditionalFormatting object
     */
static void overlapping(Sheet sheet) {
    for (int i = 0; i < 40; i++) {
        int rn = i + 1;
        Row r = sheet.createRow(i);
        r.createCell(0).setCellValue("This is row " + rn + " (" + i + ")");
        String str = "";
        if (rn % 2 == 0) {
            str = str + "even ";
        }
        if (rn % 3 == 0) {
            str = str + "x3 ";
        }
        if (rn % 5 == 0) {
            str = str + "x5 ";
        }
        if (rn % 10 == 0) {
            str = str + "x10 ";
        }
        if (str.length() == 0) {
            str = "nothing special...";
        }
        r.createCell(1).setCellValue("It is " + str);
    }
    sheet.autoSizeColumn(0);
    sheet.autoSizeColumn(1);
    sheet.getRow(1).createCell(3).setCellValue("Even rows are blue");
    sheet.getRow(2).createCell(3).setCellValue("Multiples of 3 have a grey background");
    sheet.getRow(4).createCell(3).setCellValue("Multiples of 5 are bold");
    sheet.getRow(9).createCell(3).setCellValue("Multiples of 10 are red (beats even)");
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    // Condition 1: Row divides by 10, red (will beat #1)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),10)=0");
    FontFormatting font1 = rule1.createFontFormatting();
    font1.setFontColorIndex(IndexedColors.RED.index);
    // Condition 2: Row is even, blue
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)=0");
    FontFormatting font2 = rule2.createFontFormatting();
    font2.setFontColorIndex(IndexedColors.BLUE.index);
    // Condition 3: Row divides by 5, bold
    ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("MOD(ROW(),5)=0");
    FontFormatting font3 = rule3.createFontFormatting();
    font3.setFontStyle(false, true);
    // Condition 4: Row divides by 3, grey background
    ConditionalFormattingRule rule4 = sheetCF.createConditionalFormattingRule("MOD(ROW(),3)=0");
    PatternFormatting fill4 = rule4.createPatternFormatting();
    fill4.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.index);
    fill4.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
    // Apply
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:F41") };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheetCF.addConditionalFormatting(regions, rule2);
    sheetCF.addConditionalFormatting(regions, rule3);
    sheetCF.addConditionalFormatting(regions, rule4);
}