Re: Calc Selections of Entire Rows and Columns

[#PATHANGI JANARDHANAN JATINSHRAVAN#]

By the way, if I want to display A:A as ‘A:A’ instead of 'A$1:A$1048576’ when 
opening a saved spreadsheet file, would ‘sc/source/core/tool/compiler.cxx' be 
the right file to touch? This is where I could find the most relevant functions.




On 12/16/15, 12:36 AM, "Dennis E. Hamilton" <orc...@apache.org> wrote:

>+1
>
>Good idea.  The current issues are is around interoperability and improvement 
>of OpenFormula implementation.  The other is about usability and UI behavior.  
>No need to tailgate one onto the other.
>
>> -Original Message-
>> From: #PATHANGI JANARDHANAN JATINSHRAVAN#
>> [mailto:jatinshr...@e.ntu.edu.sg]
>> Sent: Tuesday, December 15, 2015 03:23
>> To: dev@openoffice.apache.org
>> Subject: Re: Calc Selections of Entire Rows and Columns
>> 
>> Hi Andre,
>>  Yes, I can take a look at implementing that, but can we make that a
>> separate issue and file another ticket for that with a new patch? That
>> would probably be better suited for a different patch to this one.
>> That’s what I feel.
>> 
>> Thanks
>> Jatin
>> 
>> 
>> 
>> 
>> On 12/15/15, 4:10 AM, "Joost Andrae" <joost.and...@gmx.de> wrote:
>> 
>> >of
>> 
>> -
>> 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
>

RE: Calc Selections of Entire Rows and Columns

[Dennis E. Hamilton]

+1

Good idea.  The current issues are is around interoperability and improvement 
of OpenFormula implementation.  The other is about usability and UI behavior.  
No need to tailgate one onto the other.

> -Original Message-
> From: #PATHANGI JANARDHANAN JATINSHRAVAN#
> [mailto:jatinshr...@e.ntu.edu.sg]
> Sent: Tuesday, December 15, 2015 03:23
> To: dev@openoffice.apache.org
> Subject: Re: Calc Selections of Entire Rows and Columns
> 
> Hi Andre,
>   Yes, I can take a look at implementing that, but can we make that a
> separate issue and file another ticket for that with a new patch? That
> would probably be better suited for a different patch to this one.
> That’s what I feel.
> 
> Thanks
> Jatin
> 
> 
> 
> 
> On 12/15/15, 4:10 AM, "Joost Andrae" <joost.and...@gmx.de> wrote:
> 
> >of
> 
> -
> 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

Re: Calc Selections of Entire Rows and Columns

[#PATHANGI JANARDHANAN JATINSHRAVAN#]

Hi Andre,   
Yes, I can take a look at implementing that, but can we make that a 
separate issue and file another ticket for that with a new patch? That would 
probably be better suited for a different patch to this one. That’s what I feel.

Thanks
Jatin




On 12/15/15, 4:10 AM, "Joost Andrae"  wrote:

>of

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Calc Selections of Entire Rows and Columns

[Joost Andrae]

Hi Jatin,

it would be great to have that functionality in Calc but did you 
consider to support this within the Excel import filter as it is an 
Excel functionality ?


Alternatively a user can use this:
When using the magic type range names (adding a column header you can 
parse over the whole column that has this header) you can reference the 
whole input area within calc...


Example: Add eg. the sting "banana" as a column header somewhere within 
a sheet and add some numerical data downside of it then at a place 
somewhere else within the sheet you can use it like =sum(banana)


Differently to Excel you can add data whereelse you want within that 
range (at the beginning, within that range (adding rows within that 
area) and adding columns at the end of that range)


just my 2 cents


Kind regards, Joost


Am 14.12.2015 um 04:23 schrieb #PATHANGI JANARDHANAN JATINSHRAVAN#:

Hi Dennis,
  I have filed one here: 
https://bz.apache.org/ooo/show_bug.cgi?id=126734 with the patch available in 
that issue

Thanks
Jatin





-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Calc Selections of Entire Rows and Columns

[#PATHANGI JANARDHANAN JATINSHRAVAN#]

Hi Dennis, 
 I have filed one here: 
https://bz.apache.org/ooo/show_bug.cgi?id=126734 with the patch available in 
that issue

Thanks
Jatin


From: Dennis E. Hamilton <orc...@apache.org>
Sent: Monday, December 14, 2015 09:13 AM
To: dev@openoffice.apache.org
Cc: #PATHANGI JANARDHANAN JATINSHRAVAN#
Subject: RE: Calc Selections of Entire Rows and Columns

Is there a Bugzilla issue with Jatin's patch?

> -Original Message-
> From: Damjan Jovanovic [mailto:dam...@apache.org]
> Sent: Sunday, December 13, 2015 08:57
> To: Dennis Hamilton <dennis.hamil...@acm.org>
> Cc: Apache OO <dev@openoffice.apache.org>; #PATHANGI JANARDHANAN
> JATINSHRAVAN# <jatinshr...@e.ntu.edu.sg>
> Subject: Re: Calc Selections of Entire Rows and Columns
[ ... ]
> "=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.
[ ... ]


-
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

Re: Calc Selections of Entire Rows and Columns

[Damjan Jovanovic]

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  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 

RE: Calc Selections of Entire Rows and Columns

[Dennis E. Hamilton]

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 
 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.
> 
> Vey intereting.
> 
>  - 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 inters

RE: Calc Selections of Entire Rows and Columns

[Dennis E. Hamilton]

+1 Good enough.

Display the general form instead of the OO.o idiom would be better, in terms of 
what is probably a more-desirable form recognized as such in a wider community, 
and what would be good to encourage.  

The key thing is to no longer fail on receiving A:A and its counterparts.

 - Dennis

> -Original Message-
> From: Damjan Jovanovic [mailto:dam...@apache.org]
> Sent: Sunday, December 13, 2015 08:57
> To: Dennis Hamilton <dennis.hamil...@acm.org>
> Cc: Apache OO <dev@openoffice.apache.org>; #PATHANGI JANARDHANAN
> JATINSHRAVAN# <jatinshr...@e.ntu.edu.sg>
> Subject: Re: Calc Selections of Entire Rows and Columns
> 
> On Sun, Dec 13, 2015 at 6:12 PM, Dennis E. Hamilton <hims...@orcmid.com>
> wrote:
[ ... ]
> > 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
> 
[ ... ]
> "=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.
> 
[ ... ]


-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

RE: Calc Selections of Entire Rows and Columns

[Dennis E. Hamilton]

> -Original Message-
> From: Andreas Säger [mailto:saege...@t-online.de]
> Sent: Sunday, December 13, 2015 13:15
> To: dev@openoffice.apache.org
> Subject: Re: Calc Selections of Entire Rows and Columns
> 
> Am 13.12.2015 um 17:12 schrieb Dennis E. Hamilton:
> > 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.
> >
> 
> There is a difference between A:A and A$1:A$1048576 in Excel and
> Gnumeric: When you delete rows, A:A remains A:A whereas A$1:A$1048576
> shrinks. Any reference to the last cell A$1048576 moves up but any A:A
> reference remains the same regardless how many and how often you delete
> cells. In the age of spreadsheet databases this may be a problem when
> you count on this behaviour.
[orcmid] 
Thanks Andreas,

Do you recommend the A:A unchanging behavior or the A$1:A$1048576 shrinking 
behavior?  Do folks depend on the shrinking behavior of the idiom?

Won't the unchanging A:A type of behavior be more complicated to achieve?  It 
means having true full column and full row recognized and displayed, including 
input of the idiom form files, but using the idiom on output of ODF 1.2 open 
formulas to preserve interoperability with older implementations.

PS: I notice if I insert rows, the range doesn't change.  Only if I delete rows 
does the range shrink.  So if I add rows and then take them out, the range 
decreases.  Not exactly marvelous.  

> 
> -
> 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

Re: Calc Selections of Entire Rows and Columns

[Andreas Säger]

Am 13.12.2015 um 17:12 schrieb Dennis E. Hamilton:
> 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.  
> 

There is a difference between A:A and A$1:A$1048576 in Excel and
Gnumeric: When you delete rows, A:A remains A:A whereas A$1:A$1048576
shrinks. Any reference to the last cell A$1048576 moves up but any A:A
reference remains the same regardless how many and how often you delete
cells. In the age of spreadsheet databases this may be a problem when
you count on this behaviour.

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

RE: Calc Selections of Entire Rows and Columns

[Dennis E. Hamilton]

Is there a Bugzilla issue with Jatin's patch?

> -Original Message-
> From: Damjan Jovanovic [mailto:dam...@apache.org]
> Sent: Sunday, December 13, 2015 08:57
> To: Dennis Hamilton <dennis.hamil...@acm.org>
> Cc: Apache OO <dev@openoffice.apache.org>; #PATHANGI JANARDHANAN
> JATINSHRAVAN# <jatinshr...@e.ntu.edu.sg>
> Subject: Re: Calc Selections of Entire Rows and Columns
[ ... ]
> "=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.
[ ... ]


-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

RE: Calc Selections of Entire Rows and Columns

[Dennis E. Hamilton]

I took another look at the A$1:A$1048576 shrinkage by deletion of rows having 
such cells and also having them in surrounding rows.

If an OpenOffice.org descendant, including AOO, saves a result of such 
shrinkage, it stays shrunk.  That is, AOO, LibreOffice, and Excel2016 will 
accept the shrunken range as exact and *not* treat it as A:A.

If enough insertions are made to such a file to get to A$1:A$1048576 in either 
LibreOffice 5.0 or Excel 2016, the range is displayed as A:A again.  AOO simply 
maxes out at the idiom value.

If LibreOffice opens a .ods having A:A produced by Excel, and deletes rows as 
above, the range remains A:A.  Ditto if Excel does that with a .ods produced by 
LibreOffice.

There appears to be a clear pattern on how this works for interoperability 
among those ODF-supporting products that recognize A:A in their UI and in 
spreadsheets that they open where there is either =[.A:.A] or 
=[.A$1:.A$1048576] being taken as A:A on input of the OpenFormula in the .ods.

There are more test cases to nail down the apparent principle.  The pattern 
seems clear enough to see if that is confirmed with other tests.

 - Dennis


> -Original Message-
> From: Dennis E. Hamilton [mailto:orc...@apache.org]
> Sent: Sunday, December 13, 2015 13:54
> To: dev@openoffice.apache.org
> Subject: RE: Calc Selections of Entire Rows and Columns
> 
> > -Original Message-
> > From: Andreas Säger [mailto:saege...@t-online.de]
> > Sent: Sunday, December 13, 2015 13:15
> > To: dev@openoffice.apache.org
> > Subject: Re: Calc Selections of Entire Rows and Columns
> >
> > Am 13.12.2015 um 17:12 schrieb Dennis E. Hamilton:
> > > 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.
> > >
> >
> > There is a difference between A:A and A$1:A$1048576 in Excel and
> > Gnumeric: When you delete rows, A:A remains A:A whereas A$1:A$1048576
> > shrinks. Any reference to the last cell A$1048576 moves up but any A:A
> > reference remains the same regardless how many and how often you
> delete
> > cells. In the age of spreadsheet databases this may be a problem when
> > you count on this behaviour.
> [orcmid]
> Thanks Andreas,
> 
> Do you recommend the A:A unchanging behavior or the A$1:A$1048576
> shrinking behavior?  Do folks depend on the shrinking behavior of the
> idiom?
> 
> Won't the unchanging A:A type of behavior be more complicated to
> achieve?  It means having true full column and full row recognized and
> displayed, including input of the idiom form files, but using the idiom
> on output of ODF 1.2 open formulas to preserve interoperability with
> older implementations.
> 
> PS: I notice if I insert rows, the range doesn't change.  Only if I
> delete rows does the range shrink.  So if I add rows and then take them
> out, the range decreases.  Not exactly marvelous.
> 
> >
> > -
> > 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


-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Calc Selections of Entire Rows and Columns

[Dennis E. Hamilton]

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.

Vey intereting.

 - Dennis


> -Original Message-
> From: Damjan Jovanovic [mailto:dam...@apache.org]
> Sent: Thursday, December 10, 2015 09:07
> To: Apache OO 
> 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