Apache POI Conditional Formatting - getNumConditionalFormattings is always returning 0

3
votes

I'm having trouble with reading the conditional formatting in an Excel Sheet using Apache POI 3.9. The Excel file was created using MS Excel 2010, some conditional formatting was added (I unzipped the .xlsx file, checked the sheet1.xml and it has several x14:conditionalFormatting tags) but sheet.getSheetConditionalFormatting().getNumConditionalFormattings() always returns 0. I can read other properties, including merged cells, without problems. Am I missing something?

Thanks!

2
javaexcelapache-poiconditional-formatting
posting a code sample and excel content would help here – Ashish
the code is simple: workbook.getSheetAt(0).getSheetConditionalFormatting().getNumConditionalFormattings() the excel file contains conditional formatting set from the Home ribbon, Style group, Conditional Formatting dropdown – PeterB

2 Answers

0
votes

SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

        ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.BETWEEN, "481", "499");
        PatternFormatting fill1 = rule1.createPatternFormatting();
        fill1.setFillBackgroundColor(IndexedColors.LIGHT_TURQUOISE.index);
        fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

CellRangeAddress[] regions = { CellRangeAddress.valueOf("N1:N" + rownum) };

        sheetCF.addConditionalFormatting(regions, cfRules);
-1
votes

I think I see the problem. POI does support Conditional Formatting and it reads them correctly only when the value is not a reference to another cell. So for example if I use the rule Format only cells that contain and select Specific Text and containing and a reference to a cell (=$A$1) then it won't be read by POI. If I change the reference to a static value ('test POI') then it will be handled correctly.

Is there a way to force POI to handle references correctly?