Download it now!
Bug 76611 - FILEOPEN: Error formula on .XLS file
Summary: FILEOPEN: Error formula on .XLS file
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.4 release
Hardware: Other All
: high major
Assignee: Kohei Yoshida
URL:
Whiteboard: BSA target:4.3.0 target:4.2.5
Keywords: possibleRegression, regression
Depends on:
Blocks: mab4.2
  Show dependency treegraph
 
Reported: 2014-03-26 02:01 UTC by Elcom
Modified: 2016-06-18 19:41 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
xls file that have formula error (769.66 KB, application/zip)
2014-03-26 02:01 UTC, Elcom
Details
very small test document (13.50 KB, application/vnd.ms-excel)
2014-04-25 20:39 UTC, Kohei Yoshida
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Elcom 2014-03-26 02:01:38 UTC
Created attachment 96393 [details]
xls file that have formula error

Problem description: 
After upgrade to LO 4.2, formula error occur in xls file if formula contain reference cell on another sheet. In old version LO and MS Excel the formula is ok. 

in file attached the problem in sheet "SR" column G and L
Column G reference to $TPM.FA.
In sheet TPM : column FA refer to column EM column EM refer to 

Problem :
In sheet TPM : column FA → EM → JD 

Expected behavior:
In sheet TPM : column FA → EM → H 
              
Operating System: Windows XP
Version: 4.2.0.4 release
Last worked in: 4.1.3.2 release
Comment 1 m.a.riosv 2014-03-26 09:33:58 UTC
I think this is a duplicate of https://bugs.freedesktop.org/show_bug.cgi?id=74345, please if you are not agree, reopen it.

*** This bug has been marked as a duplicate of bug 74345 ***
Comment 2 Elcom 2014-03-27 01:54:22 UTC
I use LO 4.2.2.1, and the problem still exists.
Comment 3 Elcom 2014-03-27 02:05:55 UTC
I use LO 4.2.2.1, and the problem still exists. I just open xls file, not save as to xls file. The xls file is ok if open with the older version of LO (4.0, 3.5)
Comment 4 Abhi 2014-03-30 12:39:56 UTC
I can confirm this bug on Ubuntu 12.04 LTS.
Comment 5 Elcom 2014-04-03 09:22:39 UTC
When I try to open it in the new LO 4.2.3.2, the problem still persists.
Comment 6 ape 2014-04-05 20:15:30 UTC
This file (attachment 96393 [details]) shows two bugs. 1st bug has already been described.
But there's still the 2nd blocking error - unable to open this file in the Windows operating system from the Start Center:
- Run the ‘soffice.exe’ file;
- Click the ‘Open’ button;
- Select the ‘DEH Sr31 Feb  2014.xls’ file;
- LibreOffice crashes.
I have reproduced the 2nd bug in Windows XP (x32 and x64 edition) for the following software versions:
- 4.1.6.0.0 (ID: b6d9aa63ab63f3236712b2b68155e6b0d129a68);
- 4.2.3.3 (ID: 6c3586f855673fa6a1576797f575b31ac6fa0ba3);
- 4.3.0.0.alpha0 (ID: ee947932eadd08682bb37eb265a6fdd0672d0888, TinderBox: Win-x86 @ 47 -TDF, Branch: MASTER, Time: 2014 -04- 04_23 : 25:56 ).
No error in LibreOffice-4.0.6.2.
I have not singled out this particular bug, because these (1st and 2nd) errors could be related.
Comment 7 ape 2014-04-05 20:56:49 UTC
This file can only be opened from The Windows Explorer. You will not be able to open this file, if it is not registered in The Windows Registry for the program (e.g., LibreOfficeDev-4. x. x. x) that you use.
Comment 8 Kohei Yoshida 2014-04-25 17:58:14 UTC
ape, for future reference, please open a new bug and add reference to the attached in this bug rather than reopening a unrelated bug report.
Comment 9 Kohei Yoshida 2014-04-25 18:01:22 UTC
Removing this from MAB.  The bug report mixes 2 different issues, none of which is reproducible here.
Comment 10 Kohei Yoshida 2014-04-25 18:17:33 UTC
Ah, the crash happens only on Windows.
Comment 11 Kohei Yoshida 2014-04-25 18:41:24 UTC
Ah, actually, you mentioned your reason for keeping the same bug report in Comment 6.  That's good enough for me.
Comment 12 Kohei Yoshida 2014-04-25 19:00:53 UTC
(In reply to comment #9)
> Removing this from MAB.  The bug report mixes 2 different issues, none of
> which is reproducible here.

Actually I can still reproduce the 1st issue too.  Weird.  And on Linux the crash doesn't happen.
Comment 13 Kohei Yoshida 2014-04-25 20:39:07 UTC
Created attachment 97981 [details]
very small test document

This test document is much much smaller and still shows the problem.

It only has 1 sheet and 2 columns: Column H and EM.

Formula in Column EM should all reference cells in Column H.  But 4.2 Calc changes that to JD upon import.
Comment 14 Kohei Yoshida 2014-04-25 22:54:13 UTC
I'll put it back to MAB.
Comment 15 Kohei Yoshida 2014-04-25 22:54:43 UTC
I'll take this.
Comment 16 Kohei Yoshida 2014-04-26 01:51:04 UTC
Ah I got it.  When importing an xls file prior to 2007, we are supposed to wrap the column index at 256 boundary.  Here is the math.

Column EM is numerically Column 143 (in R1C1).  The Excel file stores the reference column to be 121 to the right.  143 + 121 = 264 which is Column JD in A1 syntax.  However, Excel 2003 and older supports columns only up to 256, so we need to wrap that around from Column 256 back to Column 1, which means 264 - 256 = 8.  Column 8 is equivalent of Column H in A1 syntax.

We just forgot to do the wrapping here, hence the bug.
Comment 17 Kohei Yoshida 2014-04-26 01:55:23 UTC
No idea whether that's related to the crash on windows.  Probably not, but we'll see.
Comment 18 Commit Notification 2014-04-26 03:43:02 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=1f70bfcd415cde8d0e0423525b3bdc1f471f1894

fdo#76611: Write test for this.



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 19 Commit Notification 2014-04-26 03:43:16 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#76611: Wrap reference addresses at max boundaries.



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 20 Kohei Yoshida 2014-04-26 03:47:54 UTC
4.2 backport: https://gerrit.libreoffice.org/9167

This is only for the reference wrapping issue.  I'll take a look at the crasher issue some other time.
Comment 21 Kohei Yoshida 2014-04-26 12:11:05 UTC
The crasher on Windows is not related to the original report.  I think we should handle that as a separate bug.
Comment 22 Kohei Yoshida 2014-04-26 12:57:32 UTC
Bug 77967 filed for the crasher.
Comment 23 Commit Notification 2014-04-27 13:04:36 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=da739f729223908516deb1c2564d0713231abb5b&h=libreoffice-4-2

fdo#76611: Wrap reference addresses at max boundaries.


It will be available in LibreOffice 4.2.5.

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 24 Kohei Yoshida 2014-04-27 13:26:56 UTC
The original bug now fixed.
Comment 25 Robinson Tryon (qubit) 2015-12-15 22:10:52 UTC
Migrating Whiteboard tags to Keywords: (PossibleRegression)
[NinjaEdit]