Calc: Non-contiguous ranges + cell count of these.

[Jan Holst Jensen]

I am trying to work with ranges in Calc. However, I cannot figure out
how to instantiate a non-contiguous cell range from its address string.
The code below (result of AllRanges.AbsoluteName) may for example show
"$Sheet1.$C$3:$C$5;$Sheet1.$E$3:$E$4" if my selection consists of two
separate ranges.

1) I can't figure out how to instantiate a range from the string
"$Sheet1.$C$3:$C$5;$Sheet1.$E$3:$E$4". getCellRangeByName won't do it
and neither will insertByName("", "$Sheet1....").

2) Can I easily get the total cell count of my AllRanges object ?
AllRanges.Cells.Count seemed intuitive but does not exist.

Cheers
-- Jan Holst Jensen
__________________

Sub Main
  ThisDoc = ThisComponent
  ThisSheet = ThisDoc.CurrentController.ActiveSheet

  SelRange = ThisComponent.getCurrentSelection
' This can be enumerated too - great:  SelRange =
ThisSheet.getCellRangeByName("A3:A6")

  AllRanges =
ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
  AllRanges.insertByName("", SelRange)
 
  c = AllRanges.AbsoluteName
  MsgBox c

  AllCells = AllRanges.Cells.createEnumeration
  While AllCells.hasMoreElements
    Content = AllCells.nextElement.String
    MsgBox Content
  Wend
End Sub


---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[Niklas Nebel]

On 06/10/09 11:28, Jan Holst Jensen wrote:
> I am trying to work with ranges in Calc. However, I cannot figure out
> how to instantiate a non-contiguous cell range from its address string.
> The code below (result of AllRanges.AbsoluteName) may for example show
> "$Sheet1.$C$3:$C$5;$Sheet1.$E$3:$E$4" if my selection consists of two
> separate ranges.
>
> 1) I can't figure out how to instantiate a range from the string
> "$Sheet1.$C$3:$C$5;$Sheet1.$E$3:$E$4". getCellRangeByName won't do it
> and neither will insertByName("", "$Sheet1....").

There's addRangeAddresses with a sequence of CellRangeAddress, but not
directly with a string. You can parse a single CellRangeAddress with
com.sun.star.table.CellRangeAddressConversion, if you find the
separators yourself (a sheet name can contain a semicolon, so if you
want to be perfect in finding the separators, you have to handle quoting).

> 2) Can I easily get the total cell count of my AllRanges object ?
> AllRanges.Cells.Count seemed intuitive but does not exist.

To count the non-empty cells, you can use
AllRanges.computeFunction(com.sun.star.sheet.GeneralFunction.COUNT).

Niklas

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[Jan Holst Jensen]

Niklas Nebel wrote:

> On 06/10/09 11:28, Jan Holst Jensen wrote:
>> I am trying to work with ranges in Calc. However, I cannot figure out
>> how to instantiate a non-contiguous cell range from its address
>> string. The code below (result of AllRanges.AbsoluteName) may for
>> example show "$Sheet1.$C$3:$C$5;$Sheet1.$E$3:$E$4" if my selection
>> consists of two separate ranges.
>>
>> 1) I can't figure out how to instantiate a range from the string
>> "$Sheet1.$C$3:$C$5;$Sheet1.$E$3:$E$4". getCellRangeByName won't do it
>> and neither will insertByName("", "$Sheet1....").
>
> There's addRangeAddresses with a sequence of CellRangeAddress, but not
> directly with a string. You can parse a single CellRangeAddress with
> com.sun.star.table.CellRangeAddressConversion, if you find the
> separators yourself (a sheet name can contain a semicolon, so if you
> want to be perfect in finding the separators, you have to handle
> quoting).
>

Sort of what I feared. I think I will defer that feature :-}.
>> 2) Can I easily get the total cell count of my AllRanges object ?
>> AllRanges.Cells.Count seemed intuitive but does not exist.
>
> To count the non-empty cells, you can use
> AllRanges.computeFunction(com.sun.star.sheet.GeneralFunction.COUNT).
Excellent! Works like a charm. Thank you!

Cheers
-- Jan Holst Jensen

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...