CODEWITHSUNDEEP
javaapache-poi
Azhar
Azhar

Reputation: 398

Conditional formatting with POI libraries

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

Upvotes: 3

Views: 1973

Answers (1)

SkateScout
SkateScout

Reputation: 870

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.

Upvotes: 2

Related Questions