[Bug 60227] New: Cannot update more than 2 existing formulas

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

[Bug 60227] New: Cannot update more than 2 existing formulas

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

            Bug ID: 60227
           Summary: Cannot update more than 2 existing formulas
           Product: POI
           Version: 3.15-FINAL
          Hardware: PC
                OS: Windows NT
            Status: NEW
          Severity: blocker
          Priority: P2
         Component: XSSF
          Assignee: [hidden email]
          Reporter: [hidden email]

I am reading a file using the following code
String strFile = "C:\\usr\\_excel_\\test.xlsx";
FileInputStream file = new FileInputStream(new File(strFile));
XSSFWorkbook workbook = new XSSFWorkbook(file);

Then I try to update an existing formula on column 1 for more than 2 rows using

  for(int index = 0 ; index < 10; index++){
     Row row = sheet.getRow(index);
     Cell cell = row.getCell(0);    
     cell.setCellFormula("SUM(200)");
  }

It updates the cell with the new formula, but when we try to open the excel
file, I get

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error072480_01.xml</logFileName><summary>Errors
were detected in file 'D:\downloads\eurest (21).xlsm'</summary><removedRecords
summary="Following is a list of removed records:">

<removedRecord>Removed Records: Shared formula from /xl/worksheets/sheet6.xml
part</removedRecord>

<removedRecord>Removed Records: Formula from /xl/calcChain.xml part
(Calculation properties)</removedRecord></removedRecords></recoveryLog>

NOTE: it works well if I change the for loop condition to index<3 and update
only 2 cells.

--
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 60227] Cannot update more than 2 existing formulas

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

Hasitha <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Priority|P2                          |P1

--
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 60227] Cannot update more than 2 existing formulas

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=60227

Hasitha <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |hasitha.gunasinghe@iicorpor
                   |                            |ate.com

--
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 60227] Cannot update more than 2 existing formulas

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=60227

Javen O'Neal <[hidden email]> changed:

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

--- Comment #1 from Javen O'Neal <[hidden email]> ---
Can you attach the file needed to reproduce this bug?

--
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 60227] Cannot update more than 2 existing formulas

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=60227

--- Comment #2 from Hasitha <[hidden email]> ---
Created attachment 34339
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=34339&action=edit
all you need is a few formulas defined in the sheet.

all you need is a few formulas defined in the sheet. Try to update more than 3
existing formulas and we get the error when trying to open the .xlsx file.

Thanks

--
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 60227] Cannot update more than 2 existing formulas

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=60227

Hasitha <[hidden email]> changed:

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

--
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 60227] Cannot update more than 2 existing formulas

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=60227

Javen O'Neal <[hidden email]> changed:

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

--- Comment #3 from Javen O'Neal <[hidden email]> ---
Are you sure that the error message Excel gives you is from the attached test
file? The error record refers to sheet6.xml and Eutelsat (21).xlsm

Seems like the error message may be coming from a different 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]

Reply | Threaded
Open this post in threaded view
|

[Bug 60227] Cannot update more than 2 existing formulas

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=60227

Hasitha <[hidden email]> changed:

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

--- Comment #4 from Hasitha <[hidden email]> ---
The error message was copied over from a different file. But you should be able
to reproduce the error from the attached file. Were you able to reproduce ?

--
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 60227] Cannot update more than 2 existing formulas

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=60227

Javen O'Neal <[hidden email]> changed:

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

--- Comment #5 from Javen O'Neal <[hidden email]> ---
I had no issues opening the file in LibreOffice after executing the following
code.
Looking at the error message from comment 0, there may be an issue with
overwritten shared formulas not getting removed from the workbook. However, the
example workbook (attachment 34339) does not appear to have these shared
formulas.

Added to TestXSSFBugs.java:
@Test
public void test60227() throws Exception {
    XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(new
File("/tmp/bug60227.xlsx"));
    Sheet sheet = wb.getSheetAt(0);
    for (int index = 0 ; index < 10; index++){
        Row row = sheet.getRow(index);
        Cell cell = row.getCell(0);
        cell.setCellFormula("SUM(200)");
    }
    OutputStream fos = new FileOutputStream("/tmp/bug60227-out.xlsx");
    wb.write(fos);
    fos.close();
    wb.close();
}

Using the test.xlsx file from attachment 34339 and the unit test that I wrote
based on your description from comment 0, do you still get an error in Excel,
and if you do, can you submit that error message?

--
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 60227] Cannot update more than 2 existing formulas

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=60227

--- Comment #6 from Javen O'Neal <[hidden email]> ---
Created attachment 34341
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=34341&action=edit
output workbook from unit test in comment 5

--
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 60227] Cannot update more than 2 existing formulas

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=60227

Hasitha <[hidden email]> changed:

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

--- Comment #7 from Hasitha <[hidden email]> ---
Yes. I do get the following error.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error157560_01.xml</logFileName><summary>Errors
were detected in file 'D:\downloads\test (1).xlsx'</summary><removedRecords
summary="Following is a list of removed records:"><removedRecord>Removed
Records: Shared formula from /xl/worksheets/sheet1.xml
part</removedRecord><removedRecord>Removed Records: Formula from
/xl/calcChain.xml part (Calculation
properties)</removedRecord></removedRecords></recoveryLog>

--
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 60227] Cannot update more than 2 existing formulas

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=60227

--- Comment #8 from Hasitha <[hidden email]> ---
(In reply to Javen O'Neal from comment #6)
> Created attachment 34341 [details]
> output workbook from unit test in comment 5

I get the following error when I try to open the attached excel file

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error157560_03.xml</logFileName><summary>Errors
were detected in file 'D:\downloads\bug60227-out
(1).xlsx'</summary><additionalInfo><info>Excel completed file level validation
and repair. Some parts of this workbook may have been repaired or
discarded.</info></additionalInfo><removedRecords summary="Following is a list
of removed records:"><removedRecord>Removed Records: Shared formula from
/xl/worksheets/sheet1.xml part</removedRecord><removedRecord>Removed Records:
Formula from /xl/calcChain.xml part (Calculation
properties)</removedRecord></removedRecords></recoveryLog>

--
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 60227] Cannot update more than 2 existing formulas

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=60227

--- Comment #9 from Hasitha <[hidden email]> ---
(In reply to Javen O'Neal from comment #5)

> I had no issues opening the file in LibreOffice after executing the
> following code.
> Looking at the error message from comment 0, there may be an issue with
> overwritten shared formulas not getting removed from the workbook. However,
> the example workbook (attachment 34339 [details]) does not appear to have
> these shared formulas.
>
> Added to TestXSSFBugs.java:
> @Test
> public void test60227() throws Exception {
>     XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(new
> File("/tmp/bug60227.xlsx"));
>     Sheet sheet = wb.getSheetAt(0);
>     for (int index = 0 ; index < 10; index++){
>         Row row = sheet.getRow(index);
>         Cell cell = row.getCell(0);
>         cell.setCellFormula("SUM(200)");
>     }
>     OutputStream fos = new FileOutputStream("/tmp/bug60227-out.xlsx");
>     wb.write(fos);
>     fos.close();
>     wb.close();
> }
>
> Using the test.xlsx file from attachment 34339 [details] and the unit test
> that I wrote based on your description from comment 0, do you still get an
> error in Excel, and if you do, can you submit that error message?

yes. I do get the error when I ran your unit test code and opened the file in
Microsoft Excel.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error157560_01.xml</logFileName><summary>Errors
were detected in file 'D:\downloads\test (1).xlsx'</summary><removedRecords
summary="Following is a list of removed records:"><removedRecord>Removed
Records: Shared formula from /xl/worksheets/sheet1.xml
part</removedRecord><removedRecord>Removed Records: Formula from
/xl/calcChain.xml part (Calculation
properties)</removedRecord></removedRecords></recoveryLog>

--
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 60227] Overwriting all cells containing a shared formula results in a corrupted workbook

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=60227

Javen O'Neal <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|Cannot update more than 2   |Overwriting all cells
                   |existing formulas           |containing a shared formula
                   |                            |results in a corrupted
                   |                            |workbook

--
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 60227] Overwriting all cells containing a shared formula results in a corrupted workbook

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=60227

Javen O'Neal <[hidden email]> changed:

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

--- Comment #10 from Javen O'Neal <[hidden email]> ---
Do you still get a corrupted workbook if you replace
> cell.setCellFormula("SUM(200)");
with
> cell.setCellValue("text");

--
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 60227] Overwriting all cells containing a shared formula results in a corrupted workbook

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=60227

Hasitha <[hidden email]> changed:

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

--- Comment #11 from Hasitha <[hidden email]> ---
(In reply to Javen O'Neal from comment #10)
> Do you still get a corrupted workbook if you replace
> > cell.setCellFormula("SUM(200)");
> with
> > cell.setCellValue("text");

No.I do not get the error if I do cell.setCellValue("text");

--
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 60227] Overwriting all cells containing a shared formula results in a corrupted workbook

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=60227

--- Comment #12 from Javen O'Neal <[hidden email]> ---
Potential duplicate: bug 58106

--
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 60227] Overwriting all cells containing a shared formula results in a corrupted workbook

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=60227

--- Comment #13 from Javen O'Neal <[hidden email]> ---
Potentially related or duplicate: bug 47570

--
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 60227] Overwriting all cells containing a shared formula results in a corrupted workbook

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=60227

Yegor Kozlov <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
         Resolution|---                         |DUPLICATE
             Status|NEW                         |RESOLVED

--- Comment #14 from Yegor Kozlov <[hidden email]> ---


*** This bug has been marked as a duplicate of bug 61869 ***

--
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 60227] Overwriting all cells containing a shared formula results in a corrupted workbook

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=60227

--- Comment #15 from Yegor Kozlov <[hidden email]> ---
Fixed in r1818818

Updating a shared formula should now preserve all references to it from other
cells. I ran your test and Excel 2013 opens the output without any issues.

Yegor

--
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]