[Bug 58106] New: unreadable content after remove formula

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 58106] New: unreadable content after remove formula

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=58106

            Bug ID: 58106
           Summary: unreadable content after remove formula
           Product: POI
           Version: 3.12-FINAL
          Hardware: PC
            Status: NEW
          Severity: critical
          Priority: P2
         Component: XSSF
          Assignee: [hidden email]
          Reporter: [hidden email]

Created attachment 32882
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=32882&action=edit
test file

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

Workbook workbook = WorkbookFactory.create(new File("test.xlsx"));
workbook.getSheetAt(0).getRow(12).getCell(1).setCellType(Cell.CELL_TYPE_NUMERIC);
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>

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 58106] unreadable content after remove formula

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=58106

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All
           Keywords|                            |APIBug

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 58106] unreadable content after remove formula

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=58106

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Severity|critical                    |normal

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 58106] unreadable content after remove formula

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=58106

--- Comment #1 from Dominik Stadler <[hidden email]> ---
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.

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 58106] unreadable content after remove formula

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=58106

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |NEEDINFO

--- Comment #2 from Dominik Stadler <[hidden email]> ---
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!");
            }

            ...
        }
    }

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 58106] unreadable content after remove formula

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=58106

--- Comment #3 from Nick Burch <[hidden email]> ---
I wonder if the "shift rows" formula changing logic could be re-used for this?

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 58106] unreadable content after remove formula

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=58106

Yegor Kozlov <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
         Resolution|---                         |FIXED
             Status|NEEDINFO                    |RESOLVED

--- Comment #4 from Yegor Kozlov <[hidden email]> ---
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.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]