Bug 56140 - ConditionalFormat with formula returns wrong target
Summary: ConditionalFormat with formula returns wrong target
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.9-FINAL
Hardware: All All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2014-02-15 09:51 UTC by picard09
Modified: 2014-09-01 22:33 UTC (History)
0 users

Example file from website (with other package) (14.95 KB, text/plain)
2014-02-15 09:51 UTC, picard09
created xls (10.00 KB, application/octet-stream)
2014-02-15 09:53 UTC, picard09

Note You need to log in before you can comment on or make changes to this bug.
Description picard09 2014-02-15 09:51:45 UTC
Created attachment 31314 [details]
Example file from website (with other package)

1. Take your example from the website (see file attached). 
2. Use -xls as parameter. 
3. Change to sheet duplicates
4. go to cell A2
5. look at the conditional formula: it says: =ZÄHLENWENN($A$2:$A$11;A3)>1

What did i expect?
Look at the code: it says

  ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1");

So, I expected the formula to refer to cell A2 instead of A3!

This occurs with various formulas some how randomly (e.g. I tried E3 and got I5, or O3 and I got AC5)

Please check and confirm.
Comment 1 picard09 2014-02-15 09:53:00 UTC
Created attachment 31315 [details]
created xls
Comment 2 Dominik Stadler 2014-09-01 22:33:47 UTC
I tried to debug this, as far as I see we create the correct structures for the PtgRef object according to the MS-XLS spec, however also OpenOffice loads the conditional format incorrectly for this case.

I also saw that OpenOffice creates a PtgRefN, which has offsets instead of relative/absolute coordinates, something which POI seems to totally ignore and handle equal to PtgRef for now! However this does not explain the issue we are seeing here, at least as far as I see.