Bug 47570

Summary: Extraneous /xl/calcChain.xml part (Calculation properties) left behind in .xlsx file
Product: POI Reporter: David Agnew <prescindor>
Component: XSSFAssignee: POI Developers List <dev>
Severity: normal    
Priority: P2    
Version: 3.10-dev   
Target Milestone: ---   
Hardware: Macintosh   
OS: All   
Attachments: test script in groovy
test input to script
test output to script

Description David Agnew 2009-07-23 18:06:54 UTC
Use Mac Excel 2008 to create a workbook with a formula.

Run a POI-based program that reads in the workbook, deletes the sheet containing the formula, creates a new sheet, and writes the result out as a new workbook.

Mac Excel 2008 opens the new workbook fine, as does NeoOffice and the Finder's built-in file viewer. However, Excel 2007 on Windows complains of unreadable content and offers to remove it. It then reports removing the following:

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

after which it successfully opens the file.

If you modify the POI-based program to delete all the content in the sheet to be deleted before deleting the sheet, the problem still occurs. However, if you also set all the cell formulas to null, then the problem disappears.
Comment 1 Yegor Kozlov 2009-08-08 04:14:54 UTC
I can't reproduce it with files created in Excel 2007.

Could you please attach the following data:

 - source .xlsx file created by Mac Excel 2008
 - Java code that modifies it
 - output file that is unreadable by Excel 2007

Comment 2 Dominik Stadler 2013-08-05 09:43:23 UTC
no response in a long time => resolving for now, please reopen with more information if this is still an issue
Comment 3 Tommy Y Li 2013-10-25 06:46:14 UTC
(In reply to Dominik Stadler from comment #2)
> no response in a long time => resolving for now, please reopen with more
> information if this is still an issue

I can confirm this issue is still happening and I'm using 3.10-beta2.

I've attached a very simple test to verify this issue using Groovy.

Basically, test.xlsx is the original spreadsheet with no problems and contains two very simple formulas in cells A1 and A2.  testOut.xlsx is the output after running sheet.createRow(0) on test.xlsx.

What's expected in testOut.xlsx is the first becomes blank whilst second row still has the formula from cell A2.

What actually happens is that an error from Excel saying:
"Excel could not open testOut.xlsx because some content is unreadable.  Do you want to open and repair this workbook?

Selecting open and repair fixes the problem and the "Review Log File" from Excel contains this:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>Repair Result to testOut 03366.xml</logFileName><summary>Errors were detected in file 'Root:Development:tmp:testOut.xlsx'</summary><removedRecords summary="Following is a list of removed records:"><removedRecord>Removed Records: Formula from /xl/calcChain.xml (Calculation properties)</removedRecord></removedRecords></recoveryLog>

I also found related posts on previous POI mailing list:

A reply to this suggested "workbook.onDeleteFormulaCell wasn't being called"... not sure if it's related.

I also verified that instead of sheet.createRow(0), I do, sheet.removeRow() works and does not produce the error.
Comment 4 Tommy Y Li 2013-10-25 06:49:11 UTC
Created attachment 30963 [details]
test script in groovy
Comment 5 Tommy Y Li 2013-10-25 06:49:27 UTC
Created attachment 30964 [details]
test input to script
Comment 6 Tommy Y Li 2013-10-25 06:49:44 UTC
Created attachment 30965 [details]
test output to script
Comment 7 Ponte Andres 2014-06-18 20:06:21 UTC

I'm having the same problem with all the latest version I tested.
Do you have any feedback on this issue?

Thanks in advance
Comment 8 Yegor Kozlov 2017-12-21 14:04:23 UTC
This is no longer reproducible with 4.0.0-SNAPSHOT.
I ran the Groovy snippet and it produced a valid file. Excel 2013 opens it without issues.