3

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

pnuts
  • 58,317
  • 11
  • 87
  • 139
Azhar
  • 398
  • 2
  • 12
  • 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 Oct 04 '13 at 10:30
  • 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 Oct 04 '13 at 12:06
  • 5
    Are you aware that Cell Styles and Conditional Formatting are two completely different things in Excel? – Gagravarr Oct 04 '13 at 14:33
  • Try setFillForegroundColor color instead of setFillBackgroundColor.http://stackoverflow.com/questions/17243529/cant-set-fill-color-apache-poi-excel-workbook/17247363#17247363 – Sankumarsingh Oct 05 '13 at 01:35
  • @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 Oct 07 '13 at 06:52
  • You'd need to do what I believe Excel does, which is fetch the rule off the Conditional Formatting, evaluate it, then fetch the appropriate colour back from the formatting based on if it matches – Gagravarr Oct 07 '13 at 08:06
  • That's crap man. It means with POI conditional formatting I will be getting only rules applied to cell and I have to take care of formatting that cell on view .. Do you have any other thoughts on this? or this is the only way? – Azhar Oct 07 '13 at 13:53

1 Answers1

2

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.

SkateScout
  • 815
  • 14
  • 24