Bug 59361

Summary: Opening existing workbook and writing to new file changes the formulae inside <f> tags
Product: POI Reporter: mate.marjai
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED WONTFIX    
Severity: normal CC: mate.marjai
Priority: P2    
Version: 3.14-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description mate.marjai 2016-04-20 15:00:27 UTC
Reproduction steps:

1. Have an existing XLSX file (call it a template), that has data validation rules defined on certain cells
2. Open the file and parse it with XSSF 
3. Select the worksheet that has data validation and change at least one cell's value
4. Output the updated workbook into a new file.

Observed behaviour:

The XML file for the template stores the data validation rules with HTML codes when describing "greater than" or "less than" rules. The result file has these HTML codes replaced with the character possibly causing issues with the parsing & validation application. Example:

Template file <worksheet1.xml>
-------------
<f t="shared" ref="CD5:CD36" si="33">
IF(ISERROR(OR(N5&lt;&gt;INT(N5),NOT(ISNUMBER(N5)))),1,IF(OR(N5&lt;&gt;INT(N5),NOT(ISNUMBER(N5))),1,0))</f>

...

<f t="shared" ref="CF5:CF36" si="35">
IF(AND(AP5&gt;0,ISBLANK(R5),ISBLANK($R$4)),1,0)</f>
-------------

Results file <worksheet1.xml>
-------------
<f t="shared" ref="CD5:CD36" si="33">
IF(ISERROR(OR(N5&lt;>
INT(N5),NOT(ISNUMBER(N5)))),1,IF(OR(N5&lt;>
INT(N5),NOT(ISNUMBER(N5))),1,0))</f>

...

<f t="shared" ref="CF5:CF36" si="35">
IF(AND(AP5>
0,ISBLANK(R5),ISBLANK($R$4)),1,0)</f>
-------------

Symptom:

When opening the result file with Excel, the validation seems to be broken, i.e. misfiring and marking fields that pass failed, and vice versa.
Comment 1 Dominik Stadler 2016-05-19 19:10:49 UTC
Can you provide your sample file to make it easier to reproduce this?
Comment 2 Dominik Stadler 2018-10-26 11:00:12 UTC
We did not receive more information here for quite some time, so I am closing this as it is quite hard to reproduce this without the requested sample file.