On Sun, Dec 13, 2015 at 6:12 PM, Dennis E. Hamilton <hims...@orcmid.com> wrote:
> The TL;DR: For full row and column selections in formulas, the trick is to > arrange to accept something like A:A and to recognize the explicit OO.o > idiom (i.e., A$1:A$1048576) and show it as A:A. The trick is to always > write, in the OpenFormula, A$1:A$1048576, regardless of the form it was > read/input, and always display as A:A in the presented formula. That is, > always write the idiom but recognize both it and the general form (and all > variations of course) as the general form. > > This will work with ODF Spreadsheets across AOO, LibreOffice, and > ODF-1.2-supporting Excel versions. It also works down-level to older > versions of AOO, LibO, OpenOffice.org, etc., wherever they are still in use. > > PS: I looked for a Bugzilla issue on this enhancement but couldn't find > it. I will look again. > The issue was duplicated countless times, but the earliest report seems to be https://bz.apache.org/ooo/show_bug.cgi?id=20495 > PPS: It might work just to say "=A" instead of "A:A" in the case of a > single column. For rows it is trickier and might need the [.2] notation. > (In the UI, there is ambiguity with numerical values and named variables > otherwise.) Note that the way =A:A is recorded using OpenFormula in the > file itself is with <table:table-cell> element attribute > table:formula="of:=[.A$1:.A$1048576]", using the OO.o-specific idiom. When > Excel produces ODF spreadsheet documents, it records the user's =A:A using > the general form table:formula="of:=[.A:.A]", and it accepts the explicit > form too (a little nod to the OpenOffice.org idiom in the spirit of > interoperability). > > "=A" is also problematic if "A" is a named range. The patch Jatin sent me to review already allows entering A:A and 1:1, and writes A:A as [.A$1:.A$1048576], but re-reads it as A$1:A$1048576. It can read [.A:.A] but also converts it to A$1:A$1048576. > - Dennis > > Damjan > > -----Original Message----- > > From: Dennis E. Hamilton [mailto:orc...@apache.org] > > Sent: Saturday, December 12, 2015 19:50 > > To: dev@openoffice.apache.org > > Cc: dam...@apache.org; '#PATHANGI JANARDHANAN JATINSHRAVAN#' > > <jatinshr...@e.ntu.edu.sg> > > Subject: Calc Selections of Entire Rows and Columns > > > > The specification of entire rows and columns in cell range addresses is > > covered in the ODF 1.2 specification. Part 1 provides some detail in > > section 9.2.4 Column and Row Range Addresses. The OpenFormula > > specification (ODF 1.2 Part 2) provides syntax for it in section 5.8 > > References. > > > > This is what is communicated in the ODF for a Spreadsheet document. > > This is not necessarily the format shown for a cell formula in the UI, > > or as entered in the formula-entry field. > > > > It would be good to see what happen with these on entry (and where any > > intersection rules apply) and also when encountered in the document > > files. > > > > EXAMPLE > > > > I made an Excel 2016 .xlsx file that computers Fibonacci numbers in > > column A. (A1 is 1, A2 is 1, A3 = A1 + A2, further values in Column A > > by filling down from A3 to row 26 (arbitrary choice). I set B1 = A:A > > and then did a fill down to B26. Each of the cell still had the formula > > =A:A and the value was that of the adjacent A cell. > > > > I was able to save this as a *.ods file. When I reopened it in Excel, > > it had preserved the same formulas. > > > > When I opened the .xlsx in LibreOffice Calc, it also preserved the =A:A > > formulas in the opened sheet. Likewise, the .ods =A:A files were > > preserved. > > > > AOO Calc rewrote each =A:A from Excel as =A$1:A$1048576. AOO Calc > > preserved the =A:A formulas in the .ods but evaluated them as #NAME? > > > > INTEROPERABILITY ISSUES > > > > An interesting problem this creates in interchange is the fact that all > > previous versions of AOO will fail if the =A:A and other cases of full > > column/row selections are now produced in the .ods document. That will > > also be the case with documents from LibreOffice since =A:A and its > > cousins have been working. > > > > LibreOffice avoids this down-level interoperability problem by accepting > > =A:A in Excel and .ods, but when the .ods file is saved, the =A:A cell > > formulas are rewritten as =A$1:A$1048576. I checked, that is what is > > written in the OpenFormula values. > > > > Microsoft Excel 2016, when it sees these in an .ods file, it presents > > =A:A in the formula window. And so does LibreOffice on rereading even > > the one it appears to writes. Classy. > > > > So, when =A:A and related cell ranges are supported, they should be > > written out with the max ranges (i.e., A$1:A$1048576) in the spreadsheet > > file, and those can be presented as =A:A to users and accepted from > > users, etc. > > > > Verrrry interessssting. > > > > - Dennis > > > > > > > -----Original Message----- > > > From: Damjan Jovanovic [mailto:dam...@apache.org] > > > Sent: Thursday, December 10, 2015 09:07 > > > To: Apache OO <dev@openoffice.apache.org> > > > Cc: imout...@gmail.com > > > Subject: Re: Introducing my self and my goals. > > > > > [ ... ] > > > * Calc doesn't provide whole row/column references like C:C or 5:5 > > while > > > Excel does, which are helpful to use in implicit intersection, among > > > others > > > (eg. in cell E34, you don't need to use D34 to refer to the cell just > > to > > > the left, you can just type D:D and it will automatically use the > > > current > > > row). > > [ ... ] > > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org > > For additional commands, e-mail: dev-h...@openoffice.apache.org > >
