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
