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

Example 16 with ConditionalFormattingRule

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

the class ConditionalFormats method formatDuplicates.

/**
     * Use Excel conditional formatting to highlight duplicate entries in a column.
     */
static void formatDuplicates(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Code");
    sheet.createRow(1).createCell(0).setCellValue(4);
    sheet.createRow(2).createCell(0).setCellValue(3);
    sheet.createRow(3).createCell(0).setCellValue(6);
    sheet.createRow(4).createCell(0).setCellValue(3);
    sheet.createRow(5).createCell(0).setCellValue(5);
    sheet.createRow(6).createCell(0).setCellValue(8);
    sheet.createRow(7).createCell(0).setCellValue(0);
    sheet.createRow(8).createCell(0).setCellValue(2);
    sheet.createRow(9).createCell(0).setCellValue(8);
    sheet.createRow(10).createCell(0).setCellValue(6);
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontStyle(false, true);
    font.setFontColorIndex(IndexedColors.BLUE.index);
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A11") };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheet.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted.  " + "Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1   (Blue Font)");
}

Example 17 with ConditionalFormattingRule

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

the class TestHSSFWorkbook method setSheetOrderHSSF.

@Test
public void setSheetOrderHSSF() throws IOException {
    Workbook wb = new HSSFWorkbook();
    Sheet s1 = wb.createSheet("first sheet");
    Sheet s2 = wb.createSheet("other sheet");
    Name name1 = wb.createName();
    name1.setNameName("name1");
    name1.setRefersToFormula("'first sheet'!D1");
    Name name2 = wb.createName();
    name2.setNameName("name2");
    name2.setRefersToFormula("'other sheet'!C1");
    Row s1r1 = s1.createRow(2);
    Cell c1 = s1r1.createCell(3);
    c1.setCellValue(30);
    Cell c2 = s1r1.createCell(2);
    c2.setCellFormula("SUM('other sheet'!C1,'first sheet'!C1)");
    Row s2r1 = s2.createRow(0);
    Cell c3 = s2r1.createCell(1);
    c3.setCellFormula("'first sheet'!D3");
    Cell c4 = s2r1.createCell(2);
    c4.setCellFormula("'other sheet'!D3");
    // conditional formatting
    SheetConditionalFormatting sheetCF = s1.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.BETWEEN, "'first sheet'!D1", "'other sheet'!D1");
    ConditionalFormattingRule[] cfRules = { rule1 };
    CellRangeAddress[] regions = { // A3:A5
    new CellRangeAddress(2, 4, 0, 0) };
    sheetCF.addConditionalFormatting(regions, cfRules);
    wb.setSheetOrder("other sheet", 0);
    // names
    assertEquals("'first sheet'!D1", wb.getName("name1").getRefersToFormula());
    assertEquals("'other sheet'!C1", wb.getName("name2").getRefersToFormula());
    // cells
    assertEquals("SUM('other sheet'!C1,'first sheet'!C1)", c2.getCellFormula());
    assertEquals("'first sheet'!D3", c3.getCellFormula());
    assertEquals("'other sheet'!D3", c4.getCellFormula());
    // conditional formatting
    ConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0);
    assertEquals("'first sheet'!D1", cf.getRule(0).getFormula1());
    assertEquals("'other sheet'!D1", cf.getRule(0).getFormula2());
    wb.close();
}