Issue 30172 - ODFF: INDIRECT function additionally should interpret the exclamation mark as sheet-cell separator.
Summary: ODFF: INDIRECT function additionally should interpret the exclamation mark as...
Status: CLOSED FIXED
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: 680m40
Hardware: All All
: P3 Trivial with 4 votes (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
URL:
Keywords: ms_interoperability, rfe_eval_ok
: 76526 (view as issue list)
Depends on:
Blocks: 71455
  Show dependency tree
 
Reported: 2004-06-14 09:46 UTC by daniel.rentz
Modified: 2013-08-07 15:14 UTC (History)
5 users (show)

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

Attachments
patch 1. (1.40 KB, text/plain)
2008-08-26 08:56 UTC, lvyue 		no flags Details
Testdocuments for Test Case Specification (16.50 KB, application/vnd.ms-excel)
2008-12-17 10:52 UTC, oc 		no flags Details
TestCaseSpecification (6.43 KB, text/html)
2008-12-17 10:52 UTC, oc 		no flags Details
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description daniel.rentz 2004-06-14 09:46:09 UTC 
Calc's INDIRECT function additionally should interpret the exclamation mark as 
sheet-cell separator. This would improve the functionality of imported Excel 
documents.
Comment 1 ooo 2004-06-14 10:58:54 UTC 
Accepted.
Comment 2 erwin.tenhumberg 2004-10-27 17:23:54 UTC 
enhanced summary and set keywords according to RFE process
Comment 3 rpolach 2006-12-15 10:46:53 UTC 
Or should be enough to automatically translate
 =INDIRECT("xxxx!a1")
into
 =INDIRECT(SUBSTITUTE("xxxx!a1"; "!"; "."))
when importing excel document.
Comment 4 ooo 2006-12-15 12:20:58 UTC 
Not quite. We'll have to take into account that the sheet name may contain an
exclamation mark as well, or the reference string may actually consist of an
external reference and file name containing exclamation marks. Only the last
exclamation mark right before a cell address or range name can be converted.
Comment 5 villeroy 2006-12-15 23:06:41 UTC 
Allow me to propose a workaround using a name reference(Ctrl+F3)
shSep =MID(ADDRESS(1;1;1;"Foo");4;1)
=INDIRECT("xxxx"&shSep&"a1")
Now your formula works in both applications.
Same is possible with pattern matching:
anyChar =IF(shSep=".";".*";"*")
=FIND("F"&anyChar;A1)
Notice that regular expressions are turned off when you open xls.
Tools>Options>Calc>Calculation:"Allow Regexes in Formulae"
Comment 6 jodygoldberg 2007-04-19 17:54:09 UTC 
*** Issue 76526 has been marked as a duplicate of this issue. ***
Comment 7 jodygoldberg 2007-04-19 17:57:01 UTC 
There are XL compatible versions in mainline now, from the R1C1 work (although
they do not handle the .de style ZnSn localization).  They'll be generally
enabled as part of the OpenFormula work rather than the current INDIRECT_XL
approach used in oo-build.

Unfortunately none of this will help with the differences in allowable
sheetnames which may cause a sheet to be renamed on import.
Comment 8 lvyue 2008-08-22 09:12:57 UTC 
Hi, Eike
I have seen this issue several days,
but I can not find out a clear clue.
I feel a little confuzed.
can you point me a way to do this?
Yue
Comment 9 ooo 2008-08-25 12:37:43 UTC 
@lvyue: I'd try to use a ScAddress::Convention of ScAddress::CONV_XL_A1 if the
attempts to parse with ScAddress::CONV_OOO failed. This could be done nicely in
a loop, I guess. Note that the ScAddress::CONV_XL_R1C1 case (2nd parameter not
present or not True) should not be touched by this.
Comment 10 lvyue 2008-08-26 08:56:09 UTC 
Created attachment 56019 [details]
patch 1.
Comment 11 lvyue 2008-08-26 09:54:15 UTC 
Hi, Eike
Thanks for your comments. :)
I thought the question too complex, I thought I should convert the string to
the format Calc uses("TabName.ColRow") before the program goes into
ScInterpreter::ScIndirect, like other formula.

And, check this patch please. :)
Comment 12 ooo 2008-09-11 20:57:31 UTC 
In cws odff05:

sc/source/core/tool/interpr1.cxx  1.60.54.4

@lvyue: The final solution looks a little bit different from the approach the
patch took, since XL_A1 style should only be tried if OOO_A1 failed, not if
XL_R1C1 was requested by the second parameter. See revision checked in.
Comment 13 ooo 2008-12-11 14:28:52 UTC 
Reassigning to QA for verification.
Comment 14 oc 2008-12-17 10:52:10 UTC 
Created attachment 58884 [details]
Testdocuments for Test Case Specification
Comment 15 oc 2008-12-17 10:52:40 UTC 
Created attachment 58885 [details]
TestCaseSpecification
Comment 16 oc 2008-12-17 10:53:10 UTC 
verified in internal build cws_odff05
Comment 17 sgautier.ooo 2009-01-23 17:34:38 UTC 
Verified in DEV300_m39 .deb version - Closing - Sophie