Created attachment 39042 [details] The workbook contains a 2D range of shared formulas which is "interrupted" with nested shared formulas and normal formula cells. When iterating the attached workbook and setting each of its cell to blank the XmlValueDisconnectedException is thrown. The workbook contains a 2D range of shared formulas which is "interrupted" with nested shared formulas and normal formula cells. The issue is related to the https://bz.apache.org/bugzilla/show_bug.cgi?id=65268 where the same problem was happening. The fix provided there works reliably only for 1D ranges of shared formulas. The issue is in the XSSFSheet.onDeleteFormula method. While the method seems to work for 1D ranges of shared formulas 2D ranges are more complex. If their "master" shared formula is removed (e.g. by setting the cell to blank) a non trivial algorithm is necessary to adjust the remaining shared formula cells.
Created attachment 39043 [details] Patch for the bug including test cases and sample workbook In the attached patch a new implementation of the onDeleteFormula is suggested. See the javadoc explaining the new algorithm for fixing remaining shared formulas. The patch contains the testing workbook which is used by a new test added to the TestXSSFBugs. This test just checks if the setBlank method thows the exception or not. Further the patch also contains a new test class also evaluating the same workbook. The idea is to check also the results of shared formulas evaluation on a range with complex nested shared formulas.
That patch does not apply correctly to the latest POI code. Is there any chance you could use GitHub? Much easier to review PRs than patch files which so often are out of date.
I added the test using r1912366 and it seems to pass without any runtime code changes.
Apologies, I thought that Ant patch is the preferred way (as per https://poi.apache.org/devel/guidelines.html). I can try the git but it will take me some time to set things up and get used to it... What is the conflict? Note that in the patch package I've included the Ant generated patch (patch_orig.txt) as well as a cleaned version of it (patch_cleaned.txt) which has only relevant changes (mostly svn ignore differences). There is also 67442_TortoiseSVN.patch for reference. If you processing the patch automatically maybe just renaming the patch_cleaned.txt to patch.txt will do the job?
(In reply to PJ Fanning from comment #3) > I added the test using r1912366 and it seems to pass without any runtime > code changes. That is strange. Locally when I run the TestXSSFBugs.testSharedFormulasRangeSetBlankBug67442() with the old onDeleteFormula method implementation the test fails (expected 0 but was 16 XmlValueDisconnectedException thrown.) The new implementation fixes the test.
Created attachment 39045 [details] Perhaps a cleaner version of the previous patch, based on the most recent code. OK, I see what is wrong. The r1912366 added the workbook but not the correct test case, i.e. the TestXSSFBugs.testSharedFormulasRangeSetBlankBug67442() method. I've created a new patch based on the most recent version of the code, please see attached. It should also add the TestXSSFSharedFormulasRangeEvaluate which is a new test class also testing this issue. Please let me know if there is any issue with the new patch.
The tests in the 18 September patch fail for me. The patch applies ok this time but the tests do not work. testSharedFormulasRangeSetBlankBug6744 ends up with a org.apache.xmlbeans.impl.values.XmlValueDisconnectedException TestXSSFSharedFormulasRangeEvaluate testSharedFormulasRangeEvaluateAndSetGradually also fails with an assertion failure. I'm not going to spend any more time on this issue. I have higher priority work items and don't have time for this issue.
OK, I see. The fact that these tests fail is a prove that the bug is there. The last patch has the new method which fixes the bug named "onDeleteFormula_new". The old "onDeleteFormula" was still left there and was used by the tests. That's why they failed. I had that to double check that issue is reproducible and included it in the patch by mistake... Quickest would be if you just remove the "onDeleteFormula" and rename the "onDeleteFormula_new" to "onDeleteFormula". Or I can give you another patch where it will be done if that suits you better. Please note that I believe that the change I'm suggesting is really fixing the onDeleteFormula logic which clearly is wrong and it is likely to cause other problems in similar scenarios (i.e. removing a cell which contains a master formula referenced by a 2D range). I too have spent quite a lot of time on the issue. Setting up a local POI environment also wasn't as smooth as I'd like to (the gradle plugin in eclipse didn't work well :-( ). So I hope that my effort was not in vain and I will be able to further help you in improving POI.