Bug 52122 - [PATCH] conditional formatting based on formula not (re-)calculated properly
Summary: [PATCH] conditional formatting based on formula not (re-)calculated properly
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.14-dev
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Keywords: PatchAvailable
: 49688 (view as bug list)
Depends on:
Reported: 2011-11-02 11:33 UTC by ivano.diana
Modified: 2016-07-18 10:39 UTC (History)
1 user (show)

patch.tar.gz attachement created by command ---> ant -f patch.xml (2.72 KB, application/x-gtar)
2011-11-02 12:33 UTC, ivano.diana
Test class to reproduce the problem (5.21 KB, text/plain)
2015-01-09 11:34 UTC, carlo.dellacqua
Description of the executed tests (31.29 KB, application/octet-stream)
2015-01-09 11:59 UTC, carlo.dellacqua
java test class (3.41 KB, text/plain)
2016-03-07 09:39 UTC, carlo.dellacqua
Patch which creates a sample document (4.37 KB, patch)
2016-05-05 09:42 UTC, Dominik Stadler
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description ivano.diana 2011-11-02 11:33:27 UTC
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:

Comment 1 ivano.diana 2011-11-02 12:33:54 UTC
Created attachment 27888 [details]
patch.tar.gz  attachement created by command ---> ant -f patch.xml

patch.tar.gz  contain class file:


modified in constructor method.
Comment 2 Evgeniy Berlog 2012-09-05 20:21:37 UTC

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.
Comment 3 carlo.dellacqua 2015-01-09 11:34:58 UTC
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).
Comment 4 carlo.dellacqua 2015-01-09 11:59:52 UTC
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
Comment 5 Dominik Stadler 2015-03-18 20:58:03 UTC
*** Bug 49688 has been marked as a duplicate of this bug. ***
Comment 6 Dominik Stadler 2016-01-30 16:21:41 UTC
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.
Comment 7 carlo.dellacqua 2016-03-07 09:39:33 UTC
Created attachment 33641 [details]
java test class

Java test class used to create the xls file to reproduce the problem.
Comment 8 carlo.dellacqua 2016-03-07 09:45:07 UTC
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
adding after line 74
    header = pHeader;
the call to

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);

			  Cell cell1 = row.createCell(1);

			  Cell cell2 = row.createCell(2);

			  // row 1
			  row = sheet.createRow(1);

			  cell0 = row.createCell(0);

			  cell1 = row.createCell(1);

			  cell2 = row.createCell(2);

			  // row 2
			  row = sheet.createRow(2);

			  cell0 = row.createCell(0);

			  cell1 = row.createCell(1);

			  cell2 = row.createCell(2);
			  // 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();

			  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");
		  } finally {
			  if (fos != null) {
				  try {
				  } catch (IOException x) {
Comment 9 carlo.dellacqua 2016-03-07 14:50:57 UTC
The problem occours only using HSSF (xls file extension)
Comment 10 Dominik Stadler 2016-05-05 09:42:48 UTC
Created attachment 33827 [details]
Patch which creates a sample document
Comment 11 Dominik Stadler 2016-07-17 21:48:24 UTC
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.
Comment 12 Dominik Stadler 2016-07-18 08:28:27 UTC
Still fails in Excel, so that is where the different results come from here.
Comment 13 Dominik Stadler 2016-07-18 10:39:49 UTC
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.