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. 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). - Dennis > -----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 --------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org For additional commands, e-mail: dev-h...@openoffice.apache.org
