Bug Hunting Session
Bug 80237 - FILEOPEN: Importing excel spreadsheet fails when other sheets are referenced
Summary: FILEOPEN: Importing excel spreadsheet fails when other sheets are referenced
Status: RESOLVED DUPLICATE of bug 92256
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.4.2 release
Hardware: Other All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks:
 
Reported: 2014-06-19 13:51 UTC by Pavel
Modified: 2015-07-18 02:03 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
sample excel sheet to import (12.08 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-06-19 13:51 UTC, Pavel
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Pavel 2014-06-19 13:51:13 UTC
Created attachment 101365 [details]
sample excel sheet to import

Problem description: 

references to other sheets doesn't import correctly (i get excel-like Sheet!A1 instead of libreoffice Sheet.A1)

Steps to reproduce:
1. open the attached file in libreoffice
2. look at list SEARCH, E2

Current behavior:
=IF(IF($D2="ne";"";INDIRECT("Kontakty!A" & $D2))=0;"";IF($D2="ne";"";INDIRECT("Kontakty!A" & $D2)))

Expected behavior:
=IF(IF($D2="ne";"";INDIRECT("Kontakty.A" & $D2))=0;"";IF($D2="ne";"";INDIRECT("Kontakty.A" & $D2)))
              
Operating System: All
Version: 4.2.4.2 release
Comment 1 m.a.riosv 2014-06-19 21:32:48 UTC
Hi Pavel, thanks for reporting.

I think there is not a bug.

I don't think we could expected calc is going to modify a literal inside quotes. When it must or when not?

There are other reported bugs about it with some options to get it working.

https://bugs.freedesktop.org/show_bug.cgi?id=62732

https://bugs.freedesktop.org/show_bug.cgi?id=75773
Comment 2 Pavel 2014-06-19 23:23:53 UTC
Hi m.a.riosv,

while I see the logic behind your reasoning and I see there are workarounds possible, I still consider this inconsistent behaviour, hence a bug. My reasoning goes as follows:

- a nonliteral gets contverted nicely (see column D, sheet SEARCH), a literal not
- the choice when to convert the literal or not could/should be based on wether the literal is a parameter to a function such as indirect(), which evaluates into a data refference.

I can see how this might end pretty convoluted given the fact that the literal itself can be passed by too many references but you actually point out a solution (the bugs and the workaround with setting the formula syntax). If the import were to open the file as if 

Menu/Tools/Option/LibreOffice calc/Formula/Formula options - formula syntax

were set to the excel standard (which is to be expected when opening a MSOffice generated file) but then changed to LibreOffice standard with the requirement of obtaining the same values after evaluating all functions, one should get the expected behaviour.
Comment 3 m.a.riosv 2014-06-19 23:52:49 UTC
IMO it's not a good idea use literals in this way, I always try to avoid it.

ut in any case there is the option to convert the bug in a request for enhancement, selecting it in status-importance.
Comment 4 Pavel 2014-06-20 00:03:14 UTC
May be, but as long as I have to work with spreadsheets from different sources, I'd pretty much appreciate if imports "just worked". Either way, thanks for the explanation, I wouldn't have figured out why there's this inconsistency between literals and nonliterals (didn't even notice the rule when the references translate right and when wrong). I guess it must be pretty confusing behaviour for others too, so I think that this really should be fixed / enhanced.
Comment 5 Robinson Tryon (qubit) 2014-12-22 07:26:23 UTC
Summary: Fix typo
Comment 6 Robinson Tryon (qubit) 2014-12-22 07:28:14 UTC
Comment on attachment 101365 [details]
sample excel sheet to import

Fix attachment mimetype
Comment 7 Robinson Tryon (qubit) 2014-12-22 07:32:10 UTC
(In reply to Pavel from comment #0)
> Problem description: 
> 
> references to other sheets doesn't import correctly (i get excel-like
> Sheet!A1 instead of libreoffice Sheet.A1)

What's the enhancement request here? The summary reads like a bug, not like a proposed enhancement.

Status -> NEEDINFO

(please change Status back to UNCONFIRMED after replying)
Comment 8 m.a.riosv 2015-07-18 02:03:51 UTC
The issue is taked in https://bugs.documentfoundation.org/show_bug.cgi?id=92256

*** This bug has been marked as a duplicate of bug 92256 ***