Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||Support variable formula conventions|
|Component:||ui||Assignee:||AOO issues mailing list <issues>|
|Status:||ACCEPTED ---||QA Contact:|
|Priority:||P3||CC:||deriziotis, frank.loehmann, hashproduct, helenrussian, issues, kpalagin, kyoshida, nesshof, niklas.nebel, ooo, openoffice-bugs, pagalmes.lists, pascal1.lib|
|Issue Type:||FEATURE||Latest Confirmation in:||---|
|Issue Depends on:|
Description jodygoldberg 2006-12-01 18:56:50 UTC
The core changes to enable cell formulas to be displayed using XL style R1C1 and A1 notation are in place. However, there are no use visible hooks for selecting which of the 3 current styles to use - OOo - XL A1 ([book]Sheet1:Sheet2!A1:A3 and A:C) - XL R1C1 ( ....!RC:RC) there is a potential to eventually add Lotus style references here and if possible to use this selection to choose the entire formula convention rather than just the address spec. eg - Use a locale specific argument separator foo(a,b,c) Could someone on the usability/UI team make a proposal ?
Comment 1 frank 2006-12-01 21:17:55 UTC
Hi Matthias, please comment on this one and proceed as needed. Frank
Comment 2 kpalagin 2007-03-17 23:26:35 UTC
Dear developers, any progress with this issue? Thanks a lot for your attention.
Comment 3 Martin Hollmichel 2008-01-22 07:49:42 UTC
move target from 2.x to 3.0
Comment 4 Martin Hollmichel 2008-01-22 07:57:11 UTC
reassign to fl, set target 3.x, any volunteer to provide a proposal ?
Comment 5 pmike 2008-01-22 10:51:02 UTC
As a simple proposal: in main menu [View] - [Addressing (or Notation?)] - then radio-mark-checked submenu: Classic OOo Excel A1 Excel R1C1 etc
Comment 6 kpalagin 2008-01-26 10:36:40 UTC
Created attachment 51170 [details] Proposed UI selector for addressing type.
Comment 7 kpalagin 2008-02-05 20:57:05 UTC
So could somebody from developers take a look at my spec and either comment or proceed with implementation? Thanks a lot! WBR, KP.
Comment 8 hashproduct 2008-02-11 01:37:04 UTC
I strongly object to the names of the cell reference styles ("Absolute", "Relative (R1C1)", "A1", "Lotus") because they do not make the differences among styles clear. It is meaningless to describe styles as "absolute" or "relative" (and hence the third sentence of the first paragraph of section 1 is wrong) because an absolute or relative reference can be written in any of the styles (e.g., $A$1, A1, R1C1, RC). "A1" and "R1C1" are good; the names additionally need to distinguish among the OOo, Excel, and Lotus variants of A1. How about "OOo A1", "Excel A1", "Excel R1C1", and "Lotus A1", along the lines of pmike's suggestion? Or the menu could give an example in each of the styles (like the comments in "enum Convention" in source file "sc/inc/address.hxx"), followed by "(OOo)", "(Excel)", or "(Lotus)" as appropriate; the full-generality examples may be helpful to some users but overwhelm others. Also, I suggest moving the choices into a submenu "Format -> Sheet -> Cell Reference Style" so that it is clear to users what they are choosing.
Comment 9 ooo 2008-02-11 11:34:22 UTC
I second that the naming (Absolute, Relative, ...) doesn't make sense. It should be A1 vs. R1C1 instead. As the current implementation differs between OOo_A1, XL_A1 and XL_R1C1, where XL_... includes usage of the '!' sheet name separator as well, technically only these could be the options offered at UI. The Lotus style isn't much more than experimental. I also think this should not go into the Format menu, the options are not related to formatting. In fact it shouldn't go into any of the main menus, these easily get too crowded, and it is not an option you'd need to switch frequently. IMHO it should go somewhere under Tools.Options.Calc
Comment 10 kyoshida 2008-02-11 12:48:52 UTC
>IMHO it should go somewhere under Tools.Options.Calc I agree with er here. Perhaps Tools.Options.Calc.Calculate would be a perfect place for this?
Comment 11 kpalagin 2008-02-11 20:41:19 UTC
Created attachment 51460 [details] Updated to accomodate requests
Comment 12 kpalagin 2008-02-11 20:55:43 UTC
Please see updated spec (sorry for colors and fonts - could not match on mockup). If possible, could somebody specify i-Team Members? Thanks a lot.
Comment 13 hashproduct 2008-02-11 23:32:07 UTC
Two nits in the updated spec: - The first entry under "Suggested accelerator keys" should say "OpenOffice.org", not "OpenOffice". - In section 3, "Configuration", it isn't clear which style "standard addressing" refers to. If it's OpenOffice.org A1 style, say that. Also, I'm wondering why OOo Calc has Excel and OpenOffice.org variants of A1 but only one kind of R1C1, which (based on the comment in address.hxx) seems to be the analogue of the Excel A1. I prefer R1C1 on its merits and don't care personally about consistency with Excel; thus, if there were an OpenOffice.org R1C1, I would use that. Does the lack of an OpenOffice.org R1C1 indicate that the OOo team has decided to adopt the Excel formats as recommended and just keep the OpenOffice.org A1 for OOo backward compatibility? If so, perhaps the OpenOffice.org A1 should be labeled "Classic" or even "Legacy".
Comment 14 hashproduct 2008-02-11 23:36:29 UTC
The third sentence in Section 1, "Detailed Specification", is still wrong. It should say "A1 or R1C1", not "relative or absolute".
Comment 16 hashproduct 2008-02-12 18:24:41 UTC
The updated specification looks good to me. There are a few typos that don't affect the meaning: "R1C1addressing" in the Abstract needs a space, the Migration section needs a period, and "Default setting" in Configuration has an extra space. I don't know if you care about fixing them.
Comment 17 kyoshida 2008-02-12 19:43:25 UTC
@hashproduct: Let's put it this way. OO.o A1 style is what we have natively in OO.o today, so we're stuck with it. Excel A1 style is what the majority of spreadsheet users are used to, so we have a good reason to support it. The same reasoning applies to Excel R1C1. However, because there is no prior R1C1 equivalent address convention in Calc, I see no reason to add such support. If it were entirely up to me, I would just implement the Excel R1C1 without adding yet another R1C1 style address convention just to be different from Excel. Lesser number of styles means lesser effort to maintain the code.
Comment 18 hashproduct 2008-02-15 04:04:52 UTC
@kohei: OK. I guess my question is which set of conventions is considered "preferred" in current OOo Calc: Excel conventions or traditional OOo conventions? If OOo conventions, then it seems inconsistent not to offer an OOo variant of R1C1; if Excel conventions, then I think the OOo A1 style should be marked somehow as old/deprecated (even if it remains the default for best compatibility).
Comment 19 kyoshida 2008-02-15 14:22:51 UTC
@hashproduct: It is not a take-Excel-or-stick-with-OOO-style situation. Why do you want to pick only one convention and deprecate the rest? Ultimately it's not up to us to decide; if there is sufficient demand for either one it makes no sense to deprecate any of them. OTOH, if the majority of the users only use one convention and don't care about the rest at all, then there is a good reason to deprecate the rarely-used ones. But now is not the time to make that decision. Quite frankly I don't know the answer to that.
Comment 20 hashproduct 2008-02-15 14:36:57 UTC
@kohei: OK, I suppose it's reasonable to leave the three styles as they are for now.
Comment 21 kpalagin 2008-02-21 09:32:59 UTC
Created attachment 51624 [details] Updated spec to new selector control
Comment 22 kpalagin 2008-03-02 04:59:20 UTC
kohei, any progress?
Comment 23 kyoshida 2008-03-03 14:34:31 UTC
@kpalagin: sorry I have not started it yet. I'm on several other high-prio issues at the moment. I'll keep you informed.
Comment 24 kpalagin 2008-07-21 11:25:04 UTC
Comment 25 kyoshida 2008-07-21 18:21:03 UTC
I'm starting to work on writing a spec for this feature. Because this is a little more than just implementing a R1C1 address convention, it needs to cover broader range of topics. So, I'll try to get it spec'ed out on the wiki page with your submitted spec as the starting point. I hope you don't mind me switching to a wiki spec because that makes it easier for to update it. Also, I'd like some input on the proposed UI. I will attach a screenshot of the current UI implementation in go-oo to see if anything needs to be changed before upstreaming it.
Comment 26 kyoshida 2008-07-21 18:25:14 UTC
Created attachment 55259 [details] formula convention (syntax) option page
Comment 27 kyoshida 2008-07-21 18:26:29 UTC
Changing the title to something more generic.
Comment 28 kyoshida 2008-07-21 18:27:55 UTC
I'm taking ownership, and CC'ing fl.
Comment 29 kyoshida 2008-07-21 18:28:29 UTC
accepting the feature.
Comment 30 kyoshida 2008-07-22 14:24:25 UTC
Just FYI, there are still several known issues with the Excel A1 and R1C1 parser that I'd like to fix before adding the UI bit.
Comment 31 helenrussian 2008-09-08 16:35:10 UTC
kohei, any news? Thanks, Helen
Comment 32 kyoshida 2008-09-11 14:37:55 UTC
Yes, I'm still waiting for any input on my question in #desc26. Or does no input mean it's been approved? Internally, we already have the implementation. So, it's just a matter of finding issues and fixing them (which we are already doing) & taking care of the UI design bits.
Comment 33 kpalagin 2008-09-11 14:57:27 UTC
Looks fine, IMHO. I think you should upstream it ASAP, so that we can have it in 3.1. Thanks a lot! WBR, KP.
Comment 34 ooo 2008-09-11 17:07:38 UTC
@kohei: in #desc26 you mentioned "switching to a wiki spec", I didn't find any though, is the attached document the spec we have?
Comment 35 kyoshida 2008-09-11 17:15:43 UTC
No the spec is not there yet. Haven't had the time. But the question regarding the UI is a separate matter.
Comment 36 kyoshida 2008-09-11 17:24:03 UTC
BTW the spec will be at http://wiki.services.openoffice.org/wiki/Calc/Features/Variable_formula_convention It's still empty at the moment, but I will start filling it once I find the time.
Comment 37 kpalagin 2008-10-16 11:06:38 UTC
With just 6 weeks left until Feature freeze I think we will miss 3.1 as well. Somethin is very wrong with the project.
Comment 38 helenrussian 2008-11-07 13:57:03 UTC
@kohei: I supplemented the spec. Please comment. http://wiki.services.openoffice.org/wiki/Calc/Features/Variable_formula_convention Thanks, Helen.
Comment 39 kyoshida 2008-11-07 15:00:08 UTC
@helen_russian: Looks very good! Thank you. :-)
Comment 40 kyoshida 2008-11-07 15:03:37 UTC
The only thing is that, open issues are those issues that will *not* be addressed when this feature is introduced. Since this issue will be resolved once the feature is added, we can take out 72191 from the list of open issues. But it's not critical, you can edit it out, or I'll do it when I do my editing, whichever comes early.
Comment 41 helenrussian 2008-11-10 12:17:39 UTC
Kohei, Eike, please comment on updated spec. Thanks, Helen
Comment 42 kpalagin 2008-11-20 07:22:32 UTC
Kohei, Eike, taking into account that 3.0.1 and 3.1 releases are delayed by a month do we have a chance of having this feature in 3.1? Regards, Kirill.
Comment 43 kyoshida 2008-11-20 17:49:34 UTC
Sorry. I know you guys want this in ASAP, but I'm starting to doubt if we can make it for 3.1. This does not just involve the formula syntax, but also the separators as well (See Issue 92056). Looking at this thread we just had on the go-oo list http://lists.go-oo.org/pipermail/dev-go-oo.org/2008-November/000957.html We may need to change the UI a bit to allow toggling of localized separators. That will certainly delay inclusion of this feature upstream, I assume, unless I hear otherwise from someone from Sun.
Comment 44 ooo 2008-11-20 18:58:31 UTC
Aiya, the fine MS selection of carefully crafted separators.. Insane. I don't see though why the separator's issue 92056 should be involved for this Xl-R1C1/Xl-A1/OOo-A1 issue here? They can be treated independently. Anyway, parsing all three conventions isn't finished yet. Current implementation of the Xl-R1C1 convention is fairly untested. Xl-A1 convention will be implemented in CWS mooxlsc for MOOXML import, which changes also common parts of Xl-R1C1 that in turn then will be completely untested. Any other independent feature relying on the new address parser implementation will not make it into OOo3.1
Comment 45 kyoshida 2008-11-20 19:02:19 UTC
>I don't see though why the separator's issue 92056 should be involved for this Xl-R1C1/Xl-A1/OOo-A1 issue here? They can be treated independently. Yes, but they will likely share the same UI space in the configuration dialog. So, it's probably better to lump them together.
Comment 46 kpalagin 2008-11-20 19:02:28 UTC
I am not sure if we have commercial customers for 92056, but IMHO localized separators are at most P4 (if not P5). 92056 does not have any votes and was filed 4 month ago. OTOH, original request for R1C1 have been filed 5 years ago and collected 56 votes which tells us that our users do need it. Delaying R1C1 by at least 6 month just because of separators is mistake IMO.
Comment 47 kpalagin 2008-11-20 20:03:56 UTC
Eike, unless we release product the code would not get testing. Our users got used to issues, so a bit of untested code in x.y.0 should be fine and will provide required testing.
Comment 48 ooo 2008-11-20 20:22:26 UTC
@kpalagin: As I said in #desc45 another reason this should not make it into 3.1 is that the R1C1 parser will be completely untested then. CWS mooxlsc will get integrated shortly before feature freeze. The R1C1 feature should be integrated early to some dev-milestone to be publicly available for extensive testing before release.
Comment 49 kpalagin 2009-03-17 08:17:12 UTC
Eike, do we have any solid target for integration of this feature, like, say, m45, so that it gets testing? Thanks. Regards, KP.
Comment 50 ooo 2009-03-17 13:25:27 UTC
@kpalagin: No, there's no target for this. And what integration of the feature? There isn't even a CWS having it implemented. Or, Kohei, am I wrong?
Comment 51 kyoshida 2009-03-17 13:44:56 UTC
@er: no, you're not wrong. There is no cws for this yet. There are still more issues with non-default formula syntax options in the core implementation that I'm trying to work out.
Comment 52 helenrussian 2010-03-05 12:43:49 UTC
Kohei, any news? Thanks, Helen
Comment 53 kyoshida 2010-03-05 13:33:01 UTC
I'll try to squeeze this in for 3.4, together with several other related features (variable separators, and English function names). I've just created koheiformula04 for that.
Comment 54 grummund 2010-07-03 15:49:04 UTC
Voting for this issue - because R1C1 cell references would be really useful. Not sure why this has to be a Excel compatibility mode, imho R1C1 deserves to exist in it's own right.
Comment 55 opium13 2010-11-29 21:22:12 UTC
on attend toujours les R1C1 (et pourquoi pas L1C1), pour pouvoir utiliser OO.
Comment 56 kjgriffin 2010-12-01 02:37:56 UTC
Also voting for this in support of RC notation, a major block for usability, not just to be like excel.
Comment 57 Rob Weir 2013-07-30 02:39:31 UTC
Reset assignee on issues not touched by assignee in more than 1000 days.