Bug 60227 - Overwriting all cells containing a shared formula results in a corrupted workbook
Summary: Overwriting all cells containing a shared formula results in a corrupted work...
Status: RESOLVED DUPLICATE of bug 61869
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.15-FINAL
Hardware: PC Windows NT
: P1 blocker (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-10-08 17:54 UTC by Hasitha
Modified: 2017-12-21 13:57 UTC (History)
1 user (show)



Attachments
all you need is a few formulas defined in the sheet. (8.09 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-10-08 21:12 UTC, Hasitha
Details
output workbook from unit test in comment 5 (6.30 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-10-08 22:18 UTC, Javen O'Neal
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Hasitha 2016-10-08 17:54:55 UTC
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.
Comment 1 Javen O'Neal 2016-10-08 21:07:34 UTC
Can you attach the file needed to reproduce this bug?
Comment 2 Hasitha 2016-10-08 21:12:24 UTC
Created attachment 34339 [details]
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
Comment 3 Javen O'Neal 2016-10-08 21:32:26 UTC
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.
Comment 4 Hasitha 2016-10-08 21:36:34 UTC
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 ?
Comment 5 Javen O'Neal 2016-10-08 21:57:55 UTC
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?
Comment 6 Javen O'Neal 2016-10-08 22:18:03 UTC
Created attachment 34341 [details]
output workbook from unit test in comment 5
Comment 7 Hasitha 2016-10-08 22:36:58 UTC
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>
Comment 8 Hasitha 2016-10-08 22:39:05 UTC
(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>
Comment 9 Hasitha 2016-10-08 22:41:48 UTC
(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>
Comment 10 Javen O'Neal 2016-10-09 02:30:52 UTC
Do you still get a corrupted workbook if you replace 
> cell.setCellFormula("SUM(200)");
with
> cell.setCellValue("text");
Comment 11 Hasitha 2016-10-09 04:36:24 UTC
(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");
Comment 12 Javen O'Neal 2016-10-09 11:17:31 UTC
Potential duplicate: bug 58106
Comment 13 Javen O'Neal 2016-10-18 16:23:38 UTC
Potentially related or duplicate: bug 47570
Comment 14 Yegor Kozlov 2017-12-21 13:53:17 UTC

*** This bug has been marked as a duplicate of bug 61869 ***
Comment 15 Yegor Kozlov 2017-12-21 13:57:03 UTC
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