CODEWITHSUNDEEP
apache-poiconditional-formatting
milesmiles55
milesmiles55

Reputation: 677

Apache POI 3.9 conditional formatting (string value)

I have the following code set up in my servlet to format a column based on a string value but, I get an error when trying to compile (org.apache.poi.ss.formula.FormulaParseException: Specified named range 'green' does not exist in the current workbook.). How should I test for a string value?

SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Cell Value is equal to green (Green Fill)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "green");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.GREEN.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    // Condition 2: Cell Value Is  equal to yellow   (Yellow Fill)
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "yellow");
    PatternFormatting fill2 = rule2.createPatternFormatting();
    fill2.setFillBackgroundColor(IndexedColors.YELLOW.index);
    fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);


    CellRangeAddress[] regions = {
            CellRangeAddress.valueOf("B1:B44")
    };

    sheetCF.addConditionalFormatting(regions, rule1, rule2);

Upvotes: 2

Views: 3814

Answers (2)

Andrei Pop
Andrei Pop

Reputation: 51

I had this problem and solved it by adding double quotes on the strings so that "green" becomes "\"green\"".

ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "\"green\"");

Hope it works.

Upvotes: 5

Sankumarsingh
Sankumarsingh

Reputation: 10079

Use updated one... Its working when you apply both the rules individually 

 SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

// Condition 1: Cell Value is equal to green (Green Fill)
ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "green");
PatternFormatting fill1 = rule1.createPatternFormatting();
fill1.setFillBackgroundColor(IndexedColors.GREEN.index);
fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
CellRangeAddress[] regions = {CellRangeAddress.valueOf("B1:B44")};
sheetCF.addConditionalFormatting(regions, rule1);

// Condition 2: Cell Value Is  equal to yellow   (Yellow Fill)
ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "yellow");
PatternFormatting fill2 = rule2.createPatternFormatting();
fill2.setFillBackgroundColor(IndexedColors.YELLOW.index);
fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
sheetCF.addConditionalFormatting(regions, rule2);

Upvotes: 2

Related Questions