Bug 58106 - unreadable content after remove formula
Summary: unreadable content after remove formula
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.12-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Keywords: APIBug
Depends on:
Reported: 2015-07-06 05:59 UTC by guoh0115
Modified: 2017-12-30 16:36 UTC (History)
0 users

test file (8.90 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-07-06 05:59 UTC, guoh0115

Note You need to log in before you can comment on or make changes to this bug.
Description guoh0115 2015-07-06 05:59:22 UTC
Created attachment 32882 [details]
test file

Here's my code. test.xlsx is attached.

Workbook workbook = WorkbookFactory.create(new File("test.xlsx"));
workbook.write(new FileOutputStream("test2.xlsx"));

the workbook contains some formular cells.
after I remove one of the formular cell.

below error occurs.
Removed Records: Shared formular from /xl/worksheets/sheet1.xml part
Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)

I checked the sheet1.xml in the xlsx file and found the formular with shared properties.If I remove the formular of B13. the result will be abnormal.

<f ref="B13:G13" si="0" t="shared">SUM(B1:B3)</f>
Comment 1 Dominik Stadler 2015-12-04 15:24:09 UTC
This happens when formulas are "shared", e.g. by draging the cell with a formula over other cells. 

Excel stores this in the copied-to cells as 

<f si="0" t="shared"/>

without storing the actual formula multiple times.

The source-cell stores the original formula:

<f ref="B13:G13" si="0" t="shared">SUM(B1:B3)</f>

So when the formula with the shared formula is removed, either the next one needs to be made the "master"-formula or the sharing needs to be undone for all formulas.
Comment 2 Dominik Stadler 2015-12-04 15:39:24 UTC
Not sure how we can solve this, it would require to "transpose" the formula from one Cell to the next one depending on the reference in the Formula of the removed cell

We can detect this case with the following in XSSFWorkbook.onDeleteFormula(), but I am not sure how we would go about fixing this :(

    protected void onDeleteFormula(XSSFCell cell){
        if(calcChain != null) {
            // TODO: check for shared formulas!
            CTCellFormula f = cell.getCTCell().getF();
            if(f.getT().equals(org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType.SHARED) &&
                    f.getRef() != null && 
                    f.getStringValue() != null) {
                // shared formula and needs adjustment as the formula is stored here...
                System.out.println("Need to adjust for shared formula here!");
Comment 3 Nick Burch 2015-12-04 15:45:41 UTC
I wonder if the "shift rows" formula changing logic could be re-used for this?
Comment 4 Yegor Kozlov 2017-12-30 16:36:26 UTC
Fixed in r1819623

Dominik is right, when a cell with a 'master' shared formula is removed,  the next cell in the range should become the master. Excel does exactly this when you remove a shared formula and save the file.