Bug 59361 - Opening existing workbook and writing to new file changes the formulae inside <f> tags
Summary: Opening existing workbook and writing to new file changes the formulae inside...
Status: RESOLVED WONTFIX
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.14-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-04-20 15:00 UTC by mate.marjai
Modified: 2018-10-26 11:00 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
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.