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

Creating a macro - Writing a Script - Using the API

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

Postby ooGuillaume » Fri Oct 18, 2019 4:39 pm

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.
Last edited by ooGuillaume on Mon Oct 21, 2019 11:39 am, edited 3 times in total.
LibreOffice 6.2.7.1 on Xubuntu 16.04
ooGuillaume
 
Posts: 13
Joined: Sat Jul 20, 2019 10:03 am

Re: How to loop through the ranges in SheetCellRanges?

Postby Zizi64 » Fri Oct 18, 2019 5:24 pm

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.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.3.2; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8464
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to loop through the ranges in SheetCellRanges?

Postby Lupp » Fri Oct 18, 2019 6:15 pm

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.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2562
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: How to loop through the ranges in SheetCellRanges?

Postby Villeroy » Fri Oct 18, 2019 6:16 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27295
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby ooGuillaume » Mon Oct 21, 2019 11:44 am

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...
LibreOffice 6.2.7.1 on Xubuntu 16.04
ooGuillaume
 
Posts: 13
Joined: Sat Jul 20, 2019 10:03 am

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

Postby Villeroy » Mon Oct 21, 2019 12:24 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27295
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests