Apache OpenOffice (AOO) Bugzilla – Issue 16277
Excel export: Corrupted Formulae when saving
Last modified: 2013-08-07 15:15:02 UTC
Formulae are being saved incorrectly when saving in the Excel formats. The following simple procedure demonstrates the problem: The attached document is a spreadsheet of numbers in a 85x85 grid. The numbers decrease in value by one as one spirals into the centre. Most of the cells calculate its value by subtracting one from its neighbour. The exception is the blue coloured lower left diagonal - the values are calculated using a different formula. I wanted to change this so that every cell is calculated by subtracting one from its neighbour. Easily done: 1) Remove formula from cell A85 by typing in its direct value (7225) 2) Change cell B84 to calculate its value from its neighbour - use the formula =A84-1 3) Copy B84 and paste into each cell along the blue diagonal up to and including the centre (AQ43) 4) Check everything okay by changing B84 to 7224 - the numbers still spiral in and the centre cell should read 0 instead of 1 Now save the document in any one of the Excel formats, then StarCalc5 format then OOo format. Close OOo. When the Excel format document is reopened in either 00o or Excel, many of the formulae are corrupted - (the centre cell no longer contains 0 and it is ultimately dependent on every other cell in the square). The OOo format document opens correctly, although it takes a long time to open. The StarCalc5 format also opens without any formulae corruption. Note that if the saved .sxc version is opened then saved in Excel format, no corruption occurs, the spreadsheet is saved perfectly. Also note this: Open original .xls file Save in .sxc format Close OOo Open OOo and the saved .xls document Make changes as described 1-5 above Save in .xls format Everything okay!
Created attachment 7293 [details] Zip file containing spiral.xls
I mentioned that if the cells are modified and saved in the .sxc format, it is really slow to re-load the file. This has been raised as issue #16283.
Reproduced (BTW: in step 4, you mean cell A85, and not B74, right?). Shared formulas break in export, i.e. AV38:BI38.
started
I do mean cell A85 and not B84, nor B74. I think we both have finger trouble :)
Well :-) You have already found a workaround that fixes this problem permanently: Save the file as Calc XML and reload it. Reason: The original Excel file contains "shared formulas". This is a formula saved once and used in several cells. This may save memory, if the formula is complex. Calc can preserve these shared formulas as long as the document is open, and restore them when exporting to Excel format, but loses them completely when writing to XML. In this case each shared formula cell is replaced by a regular formula cell. The Excel filter seems to be confused with some of the shared formulas, and writes a wrong formula. Therefore you will have for instance in cell BI38 a wrong reference to BJ38, and not to BI39. Seems that I didn't really start the issue. So - started again...
*** Issue 26041 has been marked as a duplicate of this issue. ***
OC: Due to high workload this issue is retargeted to office.later
Fixed in src680/dr20. Will be integrated in OOo 2.0.
target -> OOo 2.0
reopened
back to QA
fixed again
verified in internal build cws_dr20
Found fixed on Master src680m54 using Linux and Windows build