Issue 16277 - Excel export: Corrupted Formulae when saving
Summary: Excel export: Corrupted Formulae when saving
Status: CLOSED FIXED
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.1 Beta2
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
URL:
Keywords:
: 26041 (view as issue list)
Depends on:
Blocks:
 
Reported: 2003-07-01 09:41 UTC by wsfulton
Modified: 2013-08-07 15:15 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments
Zip file containing spiral.xls (58.16 KB, application/octet-stream)
2003-07-01 09:43 UTC, wsfulton
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description wsfulton 2003-07-01 09:41:35 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!
Comment 1 wsfulton 2003-07-01 09:46:44 UTC
Created attachment 7293 [details]
Zip file containing spiral.xls
Comment 2 wsfulton 2003-07-01 11:30:18 UTC
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.
Comment 3 daniel.rentz 2003-08-06 13:43:38 UTC
Reproduced (BTW: in step 4, you mean cell A85, and not B74, right?).
Shared formulas break in export, i.e. AV38:BI38.
Comment 4 daniel.rentz 2003-08-06 13:44:04 UTC
started
Comment 5 wsfulton 2003-08-06 17:47:41 UTC
I do mean cell A85 and not B84, nor B74. I think we both have finger
trouble :)
Comment 6 daniel.rentz 2003-08-07 07:01:06 UTC
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...
Comment 7 frank 2004-03-08 10:34:53 UTC
*** Issue 26041 has been marked as a duplicate of this issue. ***
Comment 8 oc 2004-05-28 11:12:03 UTC
OC: Due to high workload this issue is retargeted to office.later
Comment 9 daniel.rentz 2004-08-11 12:02:07 UTC
Fixed in src680/dr20. Will be integrated in OOo 2.0.
Comment 10 daniel.rentz 2004-08-20 09:52:19 UTC
target -> OOo 2.0
Comment 11 daniel.rentz 2004-08-23 16:26:10 UTC
reopened
Comment 12 daniel.rentz 2004-08-23 16:27:07 UTC
reopened
Comment 13 daniel.rentz 2004-08-23 16:30:04 UTC
back to QA
Comment 14 daniel.rentz 2004-08-23 16:34:58 UTC
fixed again
Comment 15 oc 2004-08-26 13:59:11 UTC
verified in internal build cws_dr20
Comment 16 frank 2004-10-05 13:17:08 UTC
Found fixed on Master src680m54 using Linux and Windows build