When creating a formula based conditional formatting referencing another cell, the formatting is not (re-)calculated properly. The only change I've made to patch the problem is in constructor method "CFRecordsAggregate(CFHeaderRecord pHeader, CFRuleRecord[] pRules)" in class "CFRecordsAggregate" where I added the row: //---------------------------------- header.setNeedRecalculation(true); //----------------------------------
Created attachment 27888 [details] patch.tar.gz attachement created by command ---> ant -f patch.xml patch.tar.gz contain class file: src\java\org\apache\poi\hssf\record\aggregates\CFRecordsAggregate.java modified in constructor method.
Hi, Can you please attach a simple unit test to show the problem you fixed in your patch? Regards, Evgeniy (In reply to comment #1) > Created attachment 27888 [details] > patch.tar.gz attachement created by command ---> ant -f patch.xml > > patch.tar.gz contain class file: > > src\java\org\apache\poi\hssf\record\aggregates\CFRecordsAggregate.java > > modified in constructor method.
Created attachment 32359 [details] Test class to reproduce the problem I've compiled and run the attached class; as a result see the next attached file where I try to explain the problem. P.S.: I've executed the tests using POI versions 3.8 beta4, 3.9, 3.11 and the results is the same; when I've executed the test with POI library patched as described by Mr. Diana, the behavior is as expected (see next attached file).
Created attachment 32360 [details] Description of the executed tests In test_description.docx there is the description of the esecuted test; in conditional-sheet_poi-3.8.xls and conditional-sheet_poi-3.8_mod.xls there are the execel files which I've opened with Excel 2013
*** Bug 49688 has been marked as a duplicate of this bug. ***
I tried the sample application with the latest version of POI and it did already work there, so it seems some work that we did on conditional formatting also fixed this case. Please retry with a recent nightly or POI 3.14-beta1 and reopen with additional information if it still does not work for you.
Created attachment 33641 [details] java test class Java test class used to create the xls file to reproduce the problem.
I've downloaded the last version of POI (poi-bin-3.14, poi-src-3.14) and the problem is still present; to solve the problem I've changed the source file CFRecordsAggregate.java adding after line 74 header = pHeader; the call to header.setNeedRecalculation(true); The following is the test class I use to create the xls file package test; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.ConditionalFormattingRule; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.PatternFormatting; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.SheetConditionalFormatting; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; public class TestExcelConditionalFormattingByFormula_2 { public static void main(String[] args) throws IOException { FileOutputStream fos = null; try { Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("Conditional Formatting Test"); sheet.setColumnWidth(0, 256 * 10); sheet.setColumnWidth(1, 256 * 10); sheet.setColumnWidth(2, 256 * 10); // Create some content. // row 0 Row row = sheet.createRow(0); Cell cell0 = row.createCell(0); cell0.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); cell0.setCellValue(100); Cell cell1 = row.createCell(1); cell1.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); cell1.setCellValue(120); Cell cell2 = row.createCell(2); cell2.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); cell2.setCellValue(130); // row 1 row = sheet.createRow(1); cell0 = row.createCell(0); cell0.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); cell0.setCellValue(200); cell1 = row.createCell(1); cell1.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); cell1.setCellValue(220); cell2 = row.createCell(2); cell2.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); cell2.setCellValue(230); // row 2 row = sheet.createRow(2); cell0 = row.createCell(0); cell0.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); cell0.setCellValue(300); cell1 = row.createCell(1); cell1.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); cell1.setCellValue(320); cell2 = row.createCell(2); cell2.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); cell2.setCellValue(330); // Create conditional formatting, CELL1 should be yellow if CELL0 is not blank. SheetConditionalFormatting formatting = sheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule = formatting.createConditionalFormattingRule("$A$1>75"); PatternFormatting pattern = rule.createPatternFormatting(); pattern.setFillBackgroundColor(IndexedColors.BLUE.index); pattern.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] range = {CellRangeAddress.valueOf("B2:C2")}; CellRangeAddress[] range2 = {CellRangeAddress.valueOf("B1:C1")}; formatting.addConditionalFormatting(range, rule); formatting.addConditionalFormatting(range2, rule); // Write file. fos = new FileOutputStream("conditional-sheet.xls"); workbook.write(fos); } finally { if (fos != null) { try { fos.close(); } catch (IOException x) { } } } System.out.println("ready."); } }
The problem occours only using HSSF (xls file extension)
Created attachment 33827 [details] Patch which creates a sample document
I just re-tested on the latest version of POI and it seems to work: If you use my attached patch and open the file in LibreOffice (only had a Linux box here, need to try on Windows as well), then the behavior is as expected, i.e. if I change "100" to below 75, all the blue cells become white. If I change them back I get all blue again. So we need to confirm on Windows if it actually does not work in Excel itself.
Still fails in Excel, so that is where the different results come from here.
Finally applied this for Excel via v1753199, it seems LibreOffice does handle such Excel files correctly out of the box, but Excel itself does not handle the conditional formatting correctly unless the recalculate is forced, which we do now in CFRecordsAggregate.