Bug 51089

Summary: Conditional formula is constant over cells, although it shouldn't be
Product: POI Reporter: Jochen Wiedmann <jochen>
Component: HSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: normal    
Priority: P2    
Version: 3.7-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: Test class, which is used to create ConditionalFormula.xls
Java class, which is used to inspect an Excel files conditional formulas
File, which has been created by POI using the Java class
File, which has been created by editing the conditional formula manually
Biff viewer output for POI file
Biff viewer output for manually created Excel file
File, which has been created by POI using the Java class
Biff viewer output for POI file
Test class, which is used to create ConditionalFormula.xls
File, which has been created by POI using the Java class
Biff viewer output for POI file
File, which has been created by POI using the Java class
Test class, which is used to create ConditionalFormula.xls

Description Jochen Wiedmann 2011-04-20 05:44:42 UTC
Created attachment 26910 [details]
Test class, which is used to create ConditionalFormula.xls

The attached Java class creates a file called ConditionalFormula.xls. This file is basically identical with the attached file ConditionalFormula2.xls, with one important exception: The cells D3, D4, etc. have a conditional formula. When inspecting this formula in the first file, which is created by POI, then the formula is shown constant in Excel. In the second file, the formula is modified by Excel and the row number matches the cells row number.

However, on the POI API level, there is no meaningful difference between the files, as can be shown by running the "Test" class. This class is used to extract the relevant information from the excel files.
Comment 1 Jochen Wiedmann 2011-04-20 05:45:44 UTC
Created attachment 26911 [details]
Java class, which is used to inspect an Excel files conditional formulas
Comment 2 Jochen Wiedmann 2011-04-20 05:46:24 UTC
Created attachment 26912 [details]
File, which has been created by POI using the Java class
Comment 3 Jochen Wiedmann 2011-04-20 05:46:52 UTC
Created attachment 26913 [details]
File, which has been created by editing the conditional formula manually
Comment 4 Nick Burch 2011-04-20 06:08:57 UTC
Can you use BiffViewer (org.apache.poi.hssf.dev.BiffViewer) to spot what's different between POI and Excel? It's mostly the conditional formatting records that'll be of interest, are they set up differently? Are the formulas on them different?
Comment 5 Jochen Wiedmann 2011-04-20 06:35:00 UTC
Created attachment 26914 [details]
Biff viewer output for POI file
Comment 6 Jochen Wiedmann 2011-04-20 06:35:25 UTC
Created attachment 26915 [details]
Biff viewer output for manually created Excel file
Comment 7 Jochen Wiedmann 2011-04-20 06:35:51 UTC
I think the interesting part is this one:


 [CFHEADER]
        .id             = 1b0
        .numCF                  = 1
-       .needRecalc        = false
+       .needRecalc        = true
        .enclosingCellRange= org.apache.poi.ss.util.CellRangeAddress [D3:D6]
        .cfranges=[org.apache.poi.ss.util.CellRangeAddress [D3:D6]]
 [/CFHEADER]
Comment 8 Nick Burch 2011-04-20 07:14:40 UTC
You should be able to get at the CFHeaderRecord from the sheet. Can you try changing the recalc flag on that, and seeing if that fixes your file? (If so we can alter the default value)
Comment 9 Jochen Wiedmann 2011-04-21 03:26:02 UTC
Created attachment 26919 [details]
File, which has been created by POI using the Java class

Updated file, which has been created with needRecalc set to true. Still, the problem stays the same.
Comment 10 Jochen Wiedmann 2011-04-21 03:26:44 UTC
Created attachment 26920 [details]
Biff viewer output for POI file

Updated Biff viewer output for file generated by POI.
Comment 11 Jochen Wiedmann 2011-04-21 03:27:21 UTC
I have modified my program to set needRecalc=true. Still, the problem remains the same.
Comment 12 Jochen Wiedmann 2011-04-21 04:40:45 UTC
Created attachment 26921 [details]
Test class, which is used to create ConditionalFormula.xls

Updated Java class to recreate Excel file using POI, with needRecalc=true.
Comment 13 Jochen Wiedmann 2011-05-03 12:03:04 UTC
Created attachment 26950 [details]
File, which has been created by POI using the Java class

Found another difference: The presence of a RECALCID record. Doesn't help either. Attaching updated Java class, which creates the RECALCID record as well.
Comment 14 Jochen Wiedmann 2011-05-03 12:03:49 UTC
Created attachment 26951 [details]
Biff viewer output for POI file
Comment 15 Jochen Wiedmann 2011-05-03 12:04:19 UTC
Created attachment 26952 [details]
File, which has been created by POI using the Java class
Comment 16 Jochen Wiedmann 2011-05-03 12:05:24 UTC
Created attachment 26953 [details]
Test class, which is used to create ConditionalFormula.xls
Comment 17 Nick Burch 2011-05-04 01:18:27 UTC
I can't see anything obviously different in the cells or the CF stuff between the Excel and POI files

The only thing I can see that might be different is the PLS block, which we don't currently seem to handle fully. You might want to look at [MS-XLS].pdf and see if that mentions anything about Conditional Formatting and that record?