Conditional formatting with POI libraries

Question

I am facing some issues with POI conditional formatting. I am not exactly getting what POI is doing here. I am setting background colour formatting rule for cell value which has value more than 70.I would like to get that CellStyle (applied through conditional formatting rule) in my application but POI won't return the updated cell style rather it returns the default one. here is my code

            XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet();

    sheetConditionalFormatting sheetCF = sheet
            .getSheetConditionalFormatting();



    // Condition 1: Cell Value Is greater than 70 (Blue Fill)
    ConditionalFormattingRule rule1 = sheetCF
            .createConditionalFormattingRule(ComparisonOperator.GT, "70");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:C10") };

    int index = sheetCF.addConditionalFormatting(regions, rule1);

    sheet.createRow(0).createCell(0).setCellValue(84);
    sheet.createRow(1).createCell(0).setCellValue(60);
    sheet.createRow(2).createCell(0).setCellValue(50);
    sheet.createRow(3).createCell(0).setCellValue(51);
    sheet.createRow(4).createCell(0).setCellValue(49);
    sheet.createRow(5).createCell(0).setCellValue(41);

    Cell cell = sheet.getRow(0).getCell(0);
    CellStyle style = cell.getCellStyle();
    System.out.println("style index  : "+style.getIndex()+" value:"+cell.getNumericCellValue());

with above code, style.getIndex() always returns 0 (i.e default formatting). I feel it should return me the updated formatting style with background color. When I write the above workbook in actual xlsx file and open with MSExcel, I could see the background color for first cell. Again when I read it from xlsx file to POI workbook it won't returns cell style with background color.

Has anyone tried/faced same kind of problem?

Regards, Azhar

Probably "style index" refers to a static style, and isn't expected to change by conditionality anyway? "Style" is what is defined, I wouldn't expect it to change as the value changes. Formatting changes. Whether you can pick that up in POI depends on how far they chased the quirks of MS's implementation.. I wouldn't rely on it. — Thomas W
actually, when I change the Cell style by using ' cellStyle.setFillBackgroundColor() and cellStyle.setFillForegroundColor' the POI creates new cellStyle and respective cell returns newly created style, but this won't happen when cell is formatted through conditional formatting. — Azhar
Are you aware that Cell Styles and Conditional Formatting are two completely different things in Excel? — Gagravarr
Try setFillForegroundColor color instead of setFillBackgroundColor.stackoverflow.com/questions/17243529/… — Sankumarsingh
@Gagravarr: What I think is irrespective of how you change the cell formatting (be it through conditional formatting/ direct cell style property), Cell Styles should get changed. If its not then it will be hard to get the styles which has been applied through conditional formatting. I am not sure about how POI/Excel does this? if its different then how could I get the cell styles formatted through conditional formatting rule. I am stuck with this.any pointers will be appreciated. — Azhar

SkateScout SkateScout · Accepted Answer · 2019-08-08T23:00:02

getCellStyle return the Format Style that is associated with the cell. It does not return the evaluated Format Style after applying the conditional Styles. For this you need the ConditionalFormattingEvaluator.

Conditional formatting with POI libraries

1 Answers