CODEWITHSUNDEEP
javaapache-poi
V H
V H

Reputation: 8587

apache poi conditional formatting blank cells

I am attempting to format cells that are below 5 with a yellow background and with this all the blank cells in the range also get highlighted. Attempted Equal rule with null "" and "\"\"" none of which appear to work.

Have tested by setting it GT 5 for 2nd rule and red as the colour which dos appear to work.

Blank field conditional formatting is something that can be done within excel and I haven't managed to find anything relevant so far for using apache-poi to acheive the same thing.

Wonder if any one has managed to make it work (this is on groovy so regions may look different)

SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting()
        ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "5")
        PatternFormatting fill1 = rule1.createPatternFormatting()
        fill1.setFillBackgroundColor(IndexedColors.YELLOW.index)
        fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND)
        //try and set the fields that are blank back to white cells - is not working from below unsure if it will work
        //https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/SheetConditionalFormatting.html
        ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL,"\"\"")
        PatternFormatting fill2 = rule2.createPatternFormatting()
        fill2.setFillBackgroundColor(IndexedColors.WHITE.index)
        fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND)
        CellRangeAddress[] regions = [ CellRangeAddress.valueOf("M11:T${counter}") ].toArray()

Upvotes: 1

Views: 1974

Answers (2)

V H
V H

Reputation: 8587

The above answer is very much limited to what fields you set formulae on. I was thinking about this last night and have tried something different which works with blank cells but cells must be set as "" for it work.

                def results= parseTotals(inputList)
                results.eachWithIndex{tr,ii ->
                    //11 is where it starts numeric on my report
                    ii=ii+11
                    Cell cell = row.createCell(ii)
                    if (tr) {
                        cell.setCellValue(tr as Double)
                        cell.setCellStyle(twoDecimal)
                    }else {

                        //cell.setCellValue(Cell.CELL_TYPE_BLANK)  //does not work -- below works and matches "\"\"" rule below
                        cell.setCellValue("")
                    }
                }

                SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting()
                ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "5")
                PatternFormatting fill1 = rule1.createPatternFormatting()
                fill1.setFillBackgroundColor(IndexedColors.YELLOW.index)
                fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND)
                ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL,"\"\"")

                PatternFormatting fill2 = rule2.createPatternFormatting()
                fill2.setFillBackgroundColor(IndexedColors.RED.index)
                fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND)
                CellRangeAddress[] regions = [ CellRangeAddress.valueOf("M11:T${counter}") ].toArray()
                sheetCF.addConditionalFormatting(regions,rule1,rule2)

With all that said I think I will be reverting to the pragmatic way which was through setting styles (as it currently sets the style to be a numeric field) but more really round the fact that result sizes vary per row and I don't always get the same range to put in "" as a field.

Upvotes: 2

K.Nicholas
K.Nicholas

Reputation: 11551

Well, for the blank I used a formula, with the value =ISBLANK(). The formula requires the first cell of your range, in my case A1, probably in your case M11. Also, the order of the rules seems to matter, I had to put the blank rule first when adding to the conditional formatting.

// GET CONDITIONAL FORMATTING
SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
// IS BLANK RULE
ConditionalFormattingRule ruleISBLANK = sheetCF.createConditionalFormattingRule("=ISBLANK(A1)");
PatternFormatting fill2 = ruleISBLANK.createPatternFormatting();
fill2.setFillBackgroundColor(IndexedColors.RED.index);
fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
// IS LESS THAN RULE
ConditionalFormattingRule ruleLT = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "5");
PatternFormatting fill1 = ruleLT.createPatternFormatting();
fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
// RANGE
CellRangeAddress[] regions = {CellRangeAddress.valueOf("A1:A10")};
// ADD CONDITIONAL FORMATTING
sheetCF.addConditionalFormatting(regions, ruleISBLANK);
sheetCF.addConditionalFormatting(regions, ruleLT);

Upvotes: 1

Related Questions