Bug Hunting Session
Bug 67571 - FILESAVE: Saving to XLS format omits to save formula expressions of cells where the formula result is an error
Summary: FILESAVE: Saving to XLS format omits to save formula expressions of cells whe...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.0.4 release
Hardware: All All
: highest blocker
Assignee: Eike Rathke
URL:
Whiteboard: BSA target:4.2.0 target:4.1.1
Keywords: regression
: 67221 68074 69273 (view as bug list)
Depends on:
Blocks: mab4.1
  Show dependency treegraph
 
Reported: 2013-07-31 09:04 UTC by eng.GeorgiGeorgiev
Modified: 2013-09-18 05:40 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
attached is the original document (81.25 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-07-31 09:04 UTC, eng.GeorgiGeorgiev
Details

Note You need to log in before you can comment on or make changes to this bug.
Description eng.GeorgiGeorgiev 2013-07-31 09:04:51 UTC
Created attachment 83351 [details]
attached is the original document

Problem description: 

Steps to reproduce:
1. File  -> save as...-->xls file format
2. When I open with Excel it says "File error: data may have been lost" 
3. The file in open in Excel, but it is with messed formulas and looks different than the original

Current behavior: bad 

Expected behavior: to be able to open on excel computer with correct formulas

              
Operating System: Windows XP
Version: 4.1.0.4 release
Comment 1 Thomas van der Meulen 2013-08-01 19:41:33 UTC
Thank you for your bug report, I can reproduce this bug running LibreOffice Version: 4.1.0.4
Build ID: 89ea49ddacd9aa532507cbf852f2bb22b1ace28 on Mac osx 10.8.4. 

I didn't use Excel but I saved it to .xls and reopend it and it was messed up.
Comment 2 Cor Nouws 2013-08-09 15:22:43 UTC
Hi Georgi / *

Any idea if this is a new problem, or if the problem happens in earlier versions too?

Also: any idea whitch formulas causes the problems?

Thanks,
Cor
Comment 3 eng.GeorgiGeorgiev 2013-08-10 21:48:18 UTC
(In reply to comment #2)
> Hi Georgi / *
> 
> Any idea if this is a new problem, or if the problem happens in earlier
> versions too?
> 
> Also: any idea whitch formulas causes the problems?
> 
> Thanks,
> Cor

Hello Cor,
I don't know if this was happening in earlier versions - it is just the first time I've tried it with the latest one 4.1.0.4
To come to your second question - I saw that the formula in column X is wrongly copied in some rows from column Z to the right and thus replacing there the original formulas as they are defined in the ODS file

Regards,
Georgi
Comment 4 eng.GeorgiGeorgiev 2013-08-10 22:04:15 UTC
(In reply to comment #3)
> (In reply to comment #2)
> > Hi Georgi / *
> > 
> > Any idea if this is a new problem, or if the problem happens in earlier
> > versions too?
> > 
> > Also: any idea whitch formulas causes the problems?
> > 
> > Thanks,
> > Cor
> 
> Hello Cor,
> I don't know if this was happening in earlier versions - it is just the
> first time I've tried it with the latest one 4.1.0.4
> To come to your second question - I saw that the formula in column X is
> wrongly copied in some rows from column Z to the right and thus replacing
> there the original formulas as they are defined in the ODS file
> 
> Regards,
> Georgi

Actually when I set the option "Treat empty strings as zero" to "True" it looks better, but still it is messed up with some formulas looks like it cannot handle #N/A value.
I think this is the problematic formula:
 =IF(ISNA($G16);"";IF(INDEX($data.$A$2:$T$5009;$G16;H$2)=0;"";INDEX($data.$A$2:$T$5009;$G16;H$2)))

the program replaces this formula =MATCH(A42;$data.$A$2:$A$5009;0) with something else like "=AT42"  

hope this will help
Georgi
Comment 5 Eike Rathke 2013-08-14 22:31:41 UTC
Saving to binary .xls format omits to save formula expressions of ALL cells where the formula result is an error, corrupting the BIFF record structure of a .xls file.

Simple steps to reproduce:
New document.
A1: =1/0    => #DIV/0!
Save as .xls
Reload.
A1 contains an empty formula, only = character.

This is serious data loss.
Comment 6 m.a.riosv 2013-08-14 23:09:33 UTC
*** Bug 68074 has been marked as a duplicate of this bug. ***
Comment 7 Commit Notification 2013-08-14 23:15:18 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=25763e59625ce83de4b82927359108f9e7878744

resolved fdo#67571 write flags and formula token array again



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 8 Eike Rathke 2013-08-14 23:25:59 UTC
Pending review
for 4-1 as https://gerrit.libreoffice.org/5429
for 4-1-1 as https://gerrit.libreoffice.org/5430
Comment 9 Commit Notification 2013-08-15 07:59:27 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8f86674214c9dc755302be163690528104dbc473&h=libreoffice-4-1

resolved fdo#67571 write flags and formula token array again


It will be available in LibreOffice 4.1.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 10 eng.GeorgiGeorgiev 2013-08-16 06:39:11 UTC
I've downloaded the latest patch from the link, but the program doesn't run well. Probably I'm downloading the correct one?
Never downloaded beta before so I'm not sure if I'm doing something wrong

Regards,
Georgi
Comment 11 Commit Notification 2013-08-16 15:34:28 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-1-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=17b9d3d0f951989495e9c15bf251a497ee713db4&h=libreoffice-4-1-1

resolved fdo#67571 write flags and formula token array again


It will be available already in LibreOffice 4.1.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 12 Mikeyy - L10n HR 2013-08-19 17:46:57 UTC
I reported same bug some time ago. If it's fixed with this patch please mark my bug duplicate of this bug.
https://bugs.freedesktop.org/show_bug.cgi?id=67221
Comment 13 Thomas van der Meulen 2013-08-19 17:49:47 UTC
*** Bug 67221 has been marked as a duplicate of this bug. ***
Comment 14 m.a.riosv 2013-09-12 14:47:26 UTC
*** Bug 69273 has been marked as a duplicate of this bug. ***
Comment 15 m.a.riosv 2013-09-17 11:01:17 UTC
*** Bug 69453 has been marked as a duplicate of this bug. ***
Comment 16 Cor Nouws 2013-09-17 18:30:46 UTC
indeed works fine in 4.1.1.2
Comment 17 eng.GeorgiGeorgiev 2013-09-18 05:40:43 UTC
Confirmed: in 4.1.1.2 is OK, I just tested it.