Bug 65464

Created attachment 37969 [details]
Stripped down excel template

looks related to https://bz.apache.org/bugzilla/show_bug.cgi?id=58106 and that that fix did not fix this use case

for some reason, V16 is stored in original doc without being marked as using a shared formula (t="shared")

```
         <c r="V16" s="5">
            <f>U16/R16</f>
            <v>0.75738345902523652</v>
         </c>
```

so when V15 is no longer a shared formula, the code makes V16 the new master but doesn't mark it as t="shared"

```
         <c r="V16" s="5">
            <f ref="V16:V29">U16/R16</f>
            <v>0.75738345902523652</v>
         </c>
```

if you add 

```
        writer.insertIntoWorkbook("SheetWithSharedFormula", 15, 21, 98765.);
```

this results in an out xlsx that is still corrupt (as far as Excel is concerned) but it repairs this xlsx ok.

The problem does appear to be with the input xlsx and its strange V16 cell.

I have a PR that fixes this issue (Excel is still not entirely happy with the calc-chain, as per my previous comment) but the file can be opened and automatically repaired. https://github.com/apache/poi/pull/241

committed with r1891849

Hello PJ Fanning,

thank you very much for your quick response and the implementation of the fix. 

I do not know how Excel was able to produce such a workbook with missing t="shared" entry, but as we are not in control of creating the workbook, it is important for us that manipulation of the workbook with Apache POI does not produce a corrupt workbook / or at least one that be repaired and opened.

Do I understand correctly then that this fix will go into the next POI release (5.1)?

Best regards
Nico

Release info is kept at https://poi.apache.org/changes.html

Release info is kept at https://poi.apache.org/changes.html

Hello PJ Fanning,

as you said, the created Excel File is still corrupt upon opening but can be repaired now.
However, the following code fix in XSSFSheet should create a non-corrupt Excel (at least when I tested it locally) without the need to be repaired:

Replace

```
    nextF.setT(STCellFormulaType.SHARED); //https://bz.apache.org/bugzilla/show_bug.cgi?id=65464
```

by

```
    nextF.setT(STCellFormulaType.SHARED); //https://bz.apache.org/bugzilla/show_bug.cgi?id=65464
    if (!nextF.isSetSi()) {
        nextF.setSi(f.getSi());
    }
```

I do not know whether the if-check is necessary, as I do not know the code base too well. However, the addition above created non-corrupt Excel files for us.

It would be great if this fix (or similar) can be added to 5.0.1. - is a new bug ticket needed?

Best regards,
Nico

Thanks Nico - committed with r1891962

No problem, thank you for implementing it so quickly :-)

I had to revert this change - it was causing problems with https://bz.apache.org/bugzilla/show_bug.cgi?id=65268

The test case was retained from this patch was retained and seems to behave ok except the anomalous cell in original file that did not have the right t=shared value - that is no longer corrected - but the cell still ends up with the right formula