Bug 65268 - [PATCH] XmlValueDisconnectedException thrown when setting blank on a sheet with nested shared formulas
Summary: [PATCH] XmlValueDisconnectedException thrown when setting blank on a sheet wi...
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 5.0.0-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Keywords: PatchAvailable
Depends on:
Reported: 2021-04-27 14:08 UTC by Jakub
Modified: 2021-04-28 14:32 UTC (History)
1 user (show)

OOXML workbook for reproducing the bug (8.68 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2021-04-27 14:08 UTC, Jakub
patch for the bug which fixes the issue and adds the described test case (7.40 KB, application/x-compressed)
2021-04-27 14:14 UTC, Jakub

Note You need to log in before you can comment on or make changes to this bug.
Description Jakub 2021-04-27 14:08:44 UTC
Created attachment 37832 [details]
OOXML workbook for reproducing the bug

I'm getting XmlValueDisconnectedException (see bellow) when setting certain formula cells to blank. Possibly other modifications which are changing formula cells to other types does the same. It happens only with some xlsx workbook.

I've found out that it happens when there are "nested shared formulas" in the sheet definition. Please see the attached workbook where I've created such sheet (using Excel 2010 and copy pasting formula cells).

The test case using the attached workbook is:

    void testSetBlankOnNestedSharedFormulas() throws IOException {
        try (XSSFWorkbook wb1 = XSSFTestDataSamples.openSampleWorkbook("testSharedFormulasSetBlank.xlsx")) {
            XSSFSheet s1 = wb1.getSheetAt(0);
            Iterator<Row> rowIterator = s1.rowIterator();
            int count = 0;
            StringBuilder sb = new StringBuilder();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();

                    // the toString is needed to exhibit the broken state

                    // breaks the sheet state
            assertEquals(10, count);
            assertEquals("2-1,2-1,1+2,2-1,2-1,3+3,3+3,3+3,2-1,2-1,", sb.toString());

The test throws following exception:

	at org.apache.xmlbeans.impl.values.XmlObjectBase.check_dated(XmlObjectBase.java:1243)
	at org.apache.xmlbeans.impl.values.XmlObjectBase.getStringValue(XmlObjectBase.java:1509)
	at org.apache.poi.xssf.usermodel.XSSFCell.convertSharedFormula(XSSFCell.java:494)
	at org.apache.poi.xssf.usermodel.XSSFCell.getCellFormula(XSSFCell.java:472)
	at org.apache.poi.xssf.usermodel.XSSFCell.getCellFormula(XSSFCell.java:445)
	at org.apache.poi.xssf.usermodel.XSSFCell.toString(XSSFCell.java:988)
	at org.apache.poi.xssf.usermodel.TestXSSFSheet.testSetBlankOnNestedSharedFormulas(TestXSSFSheet.java:2040)

I also have a fix for the issue. The problem is in the method XSSFSheet#onDeleteFormula where I've added following line:
if (nextF.getT() == STCellFormulaType.SHARED && nextF.getSi() == f.getSi()) {

I'll try to attach a patch for that...
Comment 1 Jakub 2021-04-27 14:14:25 UTC
Created attachment 37833 [details]
patch for the bug which fixes the issue and adds the described test case

Attaching a patch for the bug which fixes the issue and adds the described test case to the TestXSSFSheet. Please let me know if the patch is usable. It is my first contribution to POI but I'd like to add some more. I've used eclipse for creating the patch so I'm not sure if it has the right format or if I shouldn't use a different option.