Bug 65268

Summary: [PATCH] XmlValueDisconnectedException thrown when setting blank on a sheet with nested shared formulas
Product: POI Reporter: Jakub <jakub.vojtisek>
Component: XSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: normal CC: jakub.vojtisek
Priority: P2 Keywords: PatchAvailable
Version: 5.0.0-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: OOXML workbook for reproducing the bug
patch for the bug which fixes the issue and adds the described test case

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.