Page 1 of 1

[Solved] How to loop through the ranges in SheetCellRanges?

PostPosted: Fri Oct 18, 2019 4:39 pm
by ooGuillaume
Hello,

I'm getting the last modified content in a spreadsheet through the Sheet Events, say with parameter ChangedContents.

Calling the functions below, I know whether I'm dealing with a single cell, a single range, or multiple ranges.
Code: Select all   Expand viewCollapse view
Dim SelectionIsCell, SelectionIsUniqueCellRange, SelectionIsMultipleCellRanges As Boolean

SelectionIsCell = ChangedContents.supportsService("com.sun.star.sheet.SheetCell")
SelectionIsUniqueCellRange = ChangedContents.supportsService("com.sun.star.sheet.SheetCellRange")
SelectionIsMultipleCellRanges = ChangedContents.supportsService("com.sun.star.sheet.SheetCellRanges")

But I can't find the right functions to loop through the multiple ranges.
For instance, I'd like to be able to call getRangeAddress or getCellRangeByPosition on each range of ChangedContents, when it supports SheetCellRanges.

Thank you.

Re: How to loop through the ranges in SheetCellRanges?

PostPosted: Fri Oct 18, 2019 5:24 pm
by Zizi64
Do you use one of the object inspection tools (MRI or XrayTool) for your programming tasks?

You can check the existing properties, methods, interfaces (and others) by usage an object inspection tool.

Re: How to loop through the ranges in SheetCellRanges?

PostPosted: Fri Oct 18, 2019 6:15 pm
by Lupp
In principle you don't need to treat "single cell" in a specific way because any single cell also supports the SheetCellRange service. There may be some effect concerning the efficiency, of course.

As an example I post a piece of code I just wrote in a playful way induced by a question in a different forum:
Code: Select all   Expand viewCollapse view
Sub doSomething(pEvent)
REM pEvent is supposed to pass all the cells having changed when a sheet event 'Content changed' was thrown.
rgs = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
If pEvent.supportsService("com.sun.star.sheet.SheetCellRange") Then
  REM Will also be executed if a single cell was changed.
  rgs.addRangeAddress(pEvent.RangeAddress, True)
Else
  rgs = pEvent
End If
u = rgs.Count - 1
For  j = 0 To u
  rg = rgs(j)
  For c = 0 To rg.Columns.Count - 1
    For r = 0 To rg.Rows.Count - 1
      oneCell = rg.getCellByPosition(c, r)
      '... Something to do
      'Probably a conditional Exit For
    Next r
    'Probably a conditional Exit For
  Next c
Next j
'... Some statements
End Sub

The loops are forced into a common structure here. Even a single cell is treated via a SheetCellRanges object.
(I would prefer Calc to handle selections this way. It doesn't however. Draw to the contrary always returns a collection even if a single shape is selected.

Re: How to loop through the ranges in SheetCellRanges?

PostPosted: Fri Oct 18, 2019 6:16 pm
by Villeroy
1) XEnumerationAccess:
viewtopic.php?f=20&t=23685&p=107958&hilit=sheetcellranges#p107958

2) XIndexAccess:
For i = 0 to obj.getCount()-1
rg = obj.getByIndex(i)

3) if you want to work with the "coordinates" of ranges:
a() = obj.getRangeAddresses() returns an array of range address structs describing the locations of ranges.

Re: [SOLVED] How to loop through the ranges in SheetCellRang

PostPosted: Mon Oct 21, 2019 11:44 am
by ooGuillaume
Three useful answers in less than two hours, thank you very much!

I installed MRI but still need to read through the documentation to find out what it's all about...

Re: [SOLVED] How to loop through the ranges in SheetCellRang

PostPosted: Mon Oct 21, 2019 12:24 pm
by Villeroy