Created attachment 25828 [details] java code for creating a test file, reproducing the unexpected behaviour Using both, poi-bin-3.7-beta1-20100620 or poi-bin-3.6-20091214, when creating a formula based conditional formatting referencing another cell, the formatting is not (re-)calculated properly. The attached file contains a test case where a cell (B1) should be formatted with a yellow background if another cell (A1) is blank, what is the initial state of the created sheet. When opening the file, the format is correct (A1 is not blank, thus B1 has a yellow background). If I clear cell A1, cell B1 will not be formatted with a white background (as I just notice, it seems that just the small part hidden by the cursor will be formatted correctly). Coming from http://article.gmane.org/gmane.comp.jakarta.poi.user/13767/, I tried to find a way to set CFHeaderRecord.setNeedRecalculation(boolean b) to true (which, as long as I can see, is never used in POI's sources except in a test case), without success. Patching the constructor of CFHeaderRecord to initially calling setNeedRecalculation(true) or changing HSSFConditionalFormatting's getCFRecordsAggregate() to public, so i was able to access it, resulted in a excel file with the correct behaviour.
I've been encountering a problem very similar to that described in here. When setting conditional formatting in a sheet by means of POI, if I choose to set a rule with a formula that contain a reference to a cell that is not present in the range defined by "CellRangeAddress" object, the cells included in this region (the CellRangeAddress object) will not update to the formatting associated to the rule. Better to post some code: ------------------------------------------------------------------- SheetConditionalFormatting sheetCF = heet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("$A$15>75"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.BLUE.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = {CellRangeAddress.valueOf("A18:A20")}; sheetCF.addConditionalFormatting(regions, rule1); ------------------------------------------------------------------- If I change the formula in "$A$18>75", that is if the cell involved in the formula is included in the region defined by CellRangeAddress object, opening the file with excel the cells are formatted correctly.
I've checked out the POI project from SVN and tryed to use method "setNeedRecalculation(true)" from the object CFHeaderRecord to solve the problem about conditional formatting recalculation. Indeed it seems it's going to work but I'm not sure my changes can be adequate to patch the problem. The only change I've made is in constructor method "CFRecordsAggregate(CFHeaderRecord pHeader, CFRuleRecord[] pRules)" where I added the row: //---------------------------------- header.setNeedRecalculation(true); //---------------------------------- I'm going to test again the changes to avoid regressions and then I'll let you know.
Running the examples from https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java with -xls results in no conditional formattings displayed MultiCell, Errors, Hide Dups and Duplicates examples. I used Excel 2007 to open the xls file. Apache POI Version is 3.8.
(In reply to comment #3) > Running the examples from > https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ > ss/examples/ConditionalFormats.java with -xls results in no conditional > formattings displayed MultiCell, Errors, Hide Dups and Duplicates examples. > > I used Excel 2007 to open the xls file. Apache POI Version is 3.8. The CF Rules are present in the xls file and editing and not changing them results in the correct display of the CF.
Please try with the latest build from trunk. Many bugs have been fixed since POI-3.8 including suport for CF. A link to daily builds is on http://poi.apache.org/ Yegor
No update on this one for a long time, also bug 52122 seems to handle a similar issue. Therefore resolving this as duplicate. *** This bug has been marked as a duplicate of bug 52122 ***