Download it now!
Bug 42624 - FILEOPEN particular .XLS shows Err:520 instead of values
Summary: FILEOPEN particular .XLS shows Err:520 instead of values
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.4.4 release
Hardware: x86 (IA32) Windows (All)
: medium major
Assignee: Kohei Yoshida
URL:
Whiteboard: target:3.5
Keywords: regression
: 51669 (view as bug list)
Depends on:
Blocks:
 
Reported: 2011-11-05 09:57 UTC by bpski72
Modified: 2013-11-25 14:56 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Mortgage Calculator - some cells show err:520, instead of values (1.50 MB, application/vnd.ms-excel)
2011-11-05 09:57 UTC, bpski72
Details

Note You need to log in before you can comment on or make changes to this bug.
Description bpski72 2011-11-05 09:57:23 UTC
Created attachment 53187 [details]
Mortgage Calculator - some cells show err:520, instead of values

My Ubuntu  11.04, 11.10  and  Windows Computers XP and Windows 7, all give this Err:520 in some cells with formulas. Happens with all versions of Libra Office.

ALL versios of Libra Office give an Err:520 in cells that should be functioning okay.  This spreadsheet works fine with Open Office versions 3.0 to 3.3.  Also works fine with Ms Office excel versions. This spreadsheet also functions with Open Office on Ubuntu 10.10 and earlier.  
It doesn't seem to matter which operating system I am using. This spreadsheet does not work with Libra Office.
Comment 1 Rainer Bielefeld Retired 2011-11-06 00:18:07 UTC
[Reproducible] with reporter's sample document and "LibreOffice 3.4.4RC2  - WIN7 Home Premium (64bit) German UI [OOO340m1 (Build:402)]",

Also reporducible with parallel installation of MinGW Master "LibO-dev 3.5.0 – WIN7 Home Premium (64bit) English UI [(Build ID:  2ba5d12-e8c71c5-41e7bcd-4b83b90)] (daily/MinGW_cross-compilation2011-10-25_00.12.09)"

No Err:520 problem with "LibreOffice Portable 3.3.0  - WIN7  Home Premium (64bit) German UI [OOO330m19 (Build:6 Tag 3.3.0.4)]" with "LibreOffice Portable 3.3.0  - WIN7  Home Premium (64bit) German UI [OOO330m19 (Build:6 Tag 3.3.0.4)], but some others in D20:D25 and may be elsewhere." 

MS EXCEL Viewer, OOo 3.1.1, OOo 3.3 and Gnumeric without Err:520 problem, OOo 3.3 also shows wrong (no) values in D20:D25

To find out the real reason of the problem is beyond my skills and patience, the document contains lots of lots of lots of formulas referring to lots of other formulas referring ...., and if 1 formula has as result "Err:520", that will "infect" all other results referring to there.

Saving sample document as .ods, closing and reopening it changes the Err:520 problem to a #NAME#? problem; what ever that might mean

@Kohei
Please feel free to reassign (or reset Assignee to default) if it’s not your area or if provided information is not sufficient. Please set Status to ASSIGNED if you accept this Bug.
Comment 2 Markus Mohrhard 2011-11-06 20:54:38 UTC
problem seems to be the import of range name: nper

I'll try to have a look at that.
Comment 3 Markus Mohrhard 2011-11-06 21:02:47 UTC
Ok, quite easy problem:

Our xls import has the same problem our ods range name import had. When we import a range name which refers to a range name that is not yet imported we get an error.

I think we can reuse the ods solution for this problem.
Comment 4 Markus Mohrhard 2011-11-09 18:33:07 UTC
Solution will get really nasty. It is not that easy as I expected. The problem is already in the binary import and not the formula compilation.

I have not yet found a good solution to this problem.
Comment 5 Markus Mohrhard 2011-11-11 13:26:39 UTC
Analyzed the problem a bit with Kohei and we have maybe a solution for the problem that is neither ugly nor needs a rewrite of calc's formula compiler.

Main target will be 3.5 and depending on the needed changes I'll think about backporting to the 3-4 branch
Comment 6 Markus Mohrhard 2011-11-11 13:28:40 UTC
oh and just for the record: not only nper is wrong nearly all range names in range name formulas are skipped
Comment 7 Markus Mohrhard 2011-11-25 22:03:17 UTC
I have half of the fix but will not have time to work on it for the next two weeks.

The patch has still a problem with the import of matrix formulas in range names.
Comment 8 Kohei Yoshida 2012-01-09 07:50:36 UTC
I talked to Markus on IRC.  I'll take a look at this.
Comment 9 Kohei Yoshida 2012-01-10 11:31:18 UTC
Fixed on libreoffice-3-5 and master.

http://cgit.freedesktop.org/libreoffice/core/commit/?h=libreoffice-3-5&id=4f888c9c01c77500ca181890f69cbd9883324a74

I missed the boat for Beta3 just by a few hours.  So the fix will be reflected in the one after Beta 3 (RC1?).
Comment 10 Rainer Bielefeld Retired 2012-07-02 22:42:36 UTC
*** Bug 51669 has been marked as a duplicate of this bug. ***