Bug 52122

Summary: [PATCH] conditional formatting based on formula not (re-)calculated properly
Product: POI Reporter: ivano.diana
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal CC: berger
Priority: P2 Keywords: PatchAvailable
Version: 3.14-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: patch.tar.gz attachement created by command ---> ant -f patch.xml
Test class to reproduce the problem
Description of the executed tests
java test class
Patch which creates a sample document

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:

//----------------------------------
header.setNeedRecalculation(true);        
//----------------------------------
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:

src\java\org\apache\poi\hssf\record\aggregates\CFRecordsAggregate.java 

modified in constructor method.
Comment 2 Evgeniy Berlog 2012-09-05 20:21:37 UTC
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.
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
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.");
	  }
}
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.