Apache OpenOffice (AOO) Bugzilla – Issue 20857
R1C1 notation for the spreadsheet
Last modified: 2013-08-07 15:13:10 UTC
Could the R1C1 notation be added to oocalc ? I never used the A1 notation in Excel because of its lack of readability. For me the R1C1 notation is much more explicit and easy to read... and a bunch of identical formulas can be compared more quickly... Example : =RC(-1)+1 =RC(-1)+1 =RC(-1)+1 =RC(-1)+1 is easier to reread than =A1+1 =B1+1 =C1+1 =D1+1 As a matter of fact this R1C1 notation is even translated. I'm french and in the french version of Excel, it L1C1 (L=ligne (french)=row and C=colonne (french)=column). So in R1C1 notation, the letter for the rows and the letter for the columns could be parametered...
Hi Bettina, 1 4 u. IMHO the current behaviour is more convenied as the described one. Frank
For a well-reasoned rationale for using R1C1, see: http://media.wiley.com/product_data/excerpt/0X/07645412/076454120X.pdf Many people may well prefer A1 notation (even after having read the above), and of course the idea is to have a choice (ala Excel) rather than to force a switch. A further point in favor of the feature option is that it is likely to be something that power users and developers (i.e. users in a position to influence switchover decisions) may find to be a sticking point. I humbly suggest that it would be a good idea to remove objections before they result in users that decide against OO, rather than after.
I've got a preliminary patch that adds R1C1 style notation support. I'll attach i t here when the 3d ref support is fixed, and I've patched the rest of the calls to the Parser and Formater to use the correct convention. It will be interesting to discuss the best notation for sheet references in R1C1 format. MS Excel does not need to contend with relative vs absolute sheet refs, and the OOo std $sheet!addr seems inconsistent in R1C1 form for relative sheets where we would end up with sheet!r1c1 as opposed to something more consistent like (and I'm just making this up) S[-1]!R1C1 or S[-1]R1C1 While I'm in here I'm also adding support for full col and full row references so that things like R1:R2 or C:C4 work. Sheet!R1C1
Hmm... whatever happened to that patch? The inclusion of the full row / column referencing would be really nice (read better compatibility w/Excel)! Of course that brings up UI issues - row / column headers should be selectable when editing formulas.
It's not only compatibility: the points made in the Wiley book linked to by hardlogic are well-made, and they state all the things I would want to say about why R1C1 is a must for OOo (especially for spreadsheet power-users). Can we get that patch in sometime soon, please? Regards, Guy.
development is still underway on the patch. A CWS has been created for it, but only about 2/3 of the sc module currently builds. This change modifies quite a bit of low level api.
*** Issue 59362 has been marked as a duplicate of this issue. ***
Actually this is more a complete feature than an enhancement. Reassigning ownership to Jody, as he's already working on it.
*** Issue 66161 has been marked as a duplicate of this issue. ***
*** Issue 67219 has been marked as a duplicate of this issue. ***
Created subtask issue 69883.
This feature exists in Novell Edition.
@er: We have a patch for this feature that we've been using in ooo-build forever. Is anyone upstreaming it (or should I?)
@kohei: Having this still assigned to Jody IMHO doesn't make sense, so I reassigned to you. Note that the core part was already upstreamed, see issue 69883. Missing is the UI specification, for which there is a separate issue 72191. Unfortunately 'mmp' whom the issue was assigned to doesn't work anymore with the UX team, so that should get pushed again on the request@ux mailing list, see also http://wiki.services.openoffice.org/wiki/HowTo_Request_User_Experience_Assistance#How_to_contact_UX_.2F_how_to_request_assistance However, from the past I remember that there were several issues related to the R1C1 notation enabled in go-oo/Novell releases, don't know details though. Have these been fixed and are the fixes part of the patch mentioned?
I have created UI specification and attached it to http://www.openoffice.org/issues/show_bug.cgi?id=72191. Please take a look and comment ASAP, so that we can implement this for 3.0. Thanks a lot. WBR, K. Palagin.
Updated UI spec http://www.openoffice.org/nonav/issues/showattachment.cgi/51469/i72191spec3.odt . Please comment.
@er: I'm not sure what the issues were with the current R1C1 patch in the ooo-build/go-oo version, but since no one has done any work on it after Jody's departure, I'm pretty sure the issues still remain. I'll have to look into that. Schedule-wise, I'll be honest with you, I can't guarantee the inclusion of this feature for 3.0, but I'll do what I can as time permits. I'm also working on a feature that enables argument separator switch (so that those who are coming from Excel can use ',' instead of ';' to separate arguments in appropriate locales), so I'll try to combine that with the integration of R1C1. UI-wise, I think using the drop-down combo box instead of grouped radio buttons would make more sense, to make it easier to add a new address type in the future.
Set it to 3.0.
Kohei, thank you very much for being agressive with the target - OpenOffice.org project needs this agility!!! I will update the spec to reflect the proposed UI and correct typos.
Sorry folks. I won't be able to make it for 3.0, only because of the string freeze. :-( The game plan is to put the underlying implementation code in for 3.0, and put the UI in for 3.1. Work in ongoing in koheiformula01 CWS.
Kohei, could you, by chance, finish implementation sometime soon, so that we don't miss 3.1 too? Thanks a ton. WBR, KP.
Yeah I'm working on it as we speak, in koheiformula01. But I may have missed the deadline for 3.0 to integrate the core part of this feature, due to windows build difficulties.
This issue is important and listed on the quarterly review for Calc: http://wiki.services.openoffice.org/wiki/2008_Q2_Review_of_Spreadsheet_Project
I sorry for being so pushy about this issue, but I do beleive that great effort of introducing R1C1 into Calc should be put in use ASAP. So I really would like integration of koheiformula01 as early as possible, in order to avoid our usual last minute retargets.
@kpalagin: FWIW, koheiformula01 is already integrated into the main tree, so the remaining issue right now is to work out the UI as well as any other issues that might come up as we expose R1C1 to the masses. When is the feature freeze for 3.1? (does anyone know off hand?)
@kohei: There's no OOo3.1 schedule yet, stay tuned to http://wiki.services.openoffice.org/wiki/Product_Release
Kohei, sorry for confusion. I ment UI changes. UI freeze is at Novemeber 27th, 2008 (http://wiki.services.openoffice.org/wiki/OOoRelease31), which will come sooner than it appears.
Has there been any movement on this issue? Last milestone attempted was June 2008.
Is also very useful display the labels of the columns in R1C1 style. The R1C1 format is very convenient for working with record scheme (for example).
Regards
I opened this ticket *seven years ago* !!! Since then, R1C1 notation has never been fully functional in OpenOffice.org. Could a developer explain to me why this matter takes so long to be dealt with ? Please !!
It's a feature important for a spreadsheet, I'm waiting it 5 years ago, it's a scandal ! It was expected in 2.0, then 2.1, then 2.x, then 3.0, ... and now OOo 3.4 but when the 3.4 will be realesed, it'll be in 3.5 or 4.0 target milestone.
impossible d'utiliser openoffice tant que la notation R1C1 (a défaut de L1C1) ne sera pas opérationnelle. je le regrettes beaucoup, croyez moi
I too am disappointed that r1c1 UI has not yet been implemented in OpenOffice Calc and have been waiting for the now long delayed debut. I work in a professional environment and would love to advocate OpenOffice Calc as a serious contender with Microsoft Excel. But the lack of support for this common UI feature leads to an impression of the application being "not ready for the big leagues", that it isn't 'serious' software. An impression that is hard to explain away. I have enough of a technical background to understand that this is just a UI issue. The referencing to columns by letters vs numbers is irrelevant to program execution. The data of cell references are manipulated as binary values which are then translated to either '1A' or 'r1c1' for the user. And the user's cell reference entry is translated to binary values for purposes of program execution. I work with 'power users' who shun the '1A' referencing due to the need to frequently use relative column references in formulas. Using alphabetical column references would necessitate frequent manual conversion from alphabetic to the numeric value to perform the offset calculation. That is a ridiculous waste of time that doesn't stand up in productivity and performance reviews. I wonder if the postponing and low prioritization of this feature is indicative of a 'culture' problem. I see references in the OpenOffice 'blogosphere' to 'r1c1' notation as being 'old' and 'older'. I do accounting, among other things. Accountants daily use terms and concepts that were invented over five hundred years ago. 'Debits' and 'Credits' will not become obsolete due to age. Neither will 'r1c1' notation.
I have been voting for this issue for several years as well, and cannot leave Excel until it's fixed. I appreciate the voluntary labor that goes into OoO, and I realize that the limited programming time available has to go into the most urgent issues first. I notice that this issue, 20857, has 96 votes, but that it's resolution depends on issue 72191, which only has 15 votes. I would encourage all of you for whom R1C1 is important to cast as many votes for both issues as your allotment allows.
I'm not sure why R1C1 notation would be referred to as "old" or "older". If anything, A1 notation is the legacy - a relic from the days of Lotus-1-2-3 and Supercalc. I have voted for issue 72191. In a way that issue is slightly misleading as it presents this as a compatibility issue with Excel, which it is not. This is about getting OOo to be taken seriously by power users, a significant proportion of which actually use R1C1 (in Excel) because it makes sense even though that program does a good job of hiding the feature away. OOo can do better at promoting the feature I'm sure. It's not clear the dependency on 72191, some developer comment on this issue would be interesting to follow. Ok, rant over. ;) and thanks to all the developers for their hard work.
Novell's latest edition 3.2.1 (2010-09-16), does have support for matrix (R1C1) notation in Calc. That non-crippled build may be downloaded here: http://download.novell.com/index.jsp?product_id=&search=Search&families=3402
In case anyone missed this: LibreOffice 3.3.0, published about a week ago, has R1C1.
I made an account pretty much just so I could vote for this issue (and the issue that blocks this). Killer feature.
Reset assignee on issues not touched by assignee in more than 1000 days.