[Solved] [Calc] Visually select multiple cells.

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

[Solved] [Calc] Visually select multiple cells.

Post by Sébastien C »

Hello There !

It is a little stupid, but I have been crashing for several days on an apparently very simple problem. So, for once, I ask the question...
We all know about multiple cell selection. We click on a first and, by adding the keys [Ctrl] or [Shift] we have several blocks of cells selected, contiguous or not.
Reading if one or more selections have been made is very easy; it’s already mentioned here.

Depending on the case, we can access to the selected cells perfectly. But we are talking here about ALREADY EXISTING visual selections. Do a visual selection of a single cell or block of cells is very simple as well:

Code: Select all

thisComponent.currentController.select(mySheet.getCellRangeByName("$A$7")
' or ...
thisComponent.currentController.select(mySheet.getCellRangeByName("$B20:$B$21")
But I am currently unable to select, by macro, AND "$A$7" AND "$B20:$B$21" when it is very simple to do with the keys [Ctrl] or [Shift]. Even the dispacher does not know how to do it and research in literature as on the Web leaves me empty of solutions… I feel it very hard to imagine that the API is not able to making multiple visual selection… I have no doubt badly sought but a little help from your respective parts would not perhaps do me harm either…

Thanks for this reading! :D
Last edited by Sébastien C on Thu Feb 27, 2020 7:41 am, edited 1 time in total.
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Calc] Visually select multiple cells.

Post by JeJe »

You can set the StatusSelectionMode to Add. from the macro recorder:

Code: Select all

dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "StatusSelectionMode"
args3(0).Value = 2
Y
dispatcher.executeDispatch(document, ".uno:StatusSelectionMode", "", 0, args3())

Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Calc] Visually select multiple cells.

Post by FJCC »

This macro selects two cell ranges. Run it from the menu Tools -> Macros -> Run to see it in action. If you run it from the IDE, it is easy to lose the selection when you click back to the spreadsheet.

Code: Select all

DIM RngAddrs(1) AS New com.sun.star.table.CellRangeAddress
CellRngs = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")

RngAddrs(0).Sheet = 0
RngAddrs(0).StartColumn = 2
RngAddrs(0).StartRow = 3
RngAddrs(0).EndColumn = 2
RngAddrs(0).EndRow = 9

RngAddrs(1).Sheet = 0
RngAddrs(1).StartColumn = 4
RngAddrs(1).StartRow = 1
RngAddrs(1).EndColumn = 7
RngAddrs(1).EndRow = 2

CellRngs.addRangeAddresses(RngAddrs, FALSE)
ThisComponent.CurrentController.select(CellRngs)
 Edit: And an alternative version 

Code: Select all

DIM RngAddrs(1) AS New com.sun.star.table.CellRangeAddress
CellRngs = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")

oSheet = ThisComponent.Sheets.getByIndex(0)
RngAddrs(0) = oSheet.getCellrangeByName("C4:C10").RangeAddress
RngAddrs(1) = oSheet.getCellrangeByName("E2:H3").RangeAddress

CellRngs.addRangeAddresses(RngAddrs, FALSE)
ThisComponent.CurrentController.select(CellRngs)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: [Calc] Visually select multiple cells.

Post by Sébastien C »

Thank you so much Jeje but it doesn’t work by code. I try

Code: Select all

sub test_01
 Dim mySheet As Object
 Dim    myDocument As Object, myDispatcher As Object
 Dim myArgument(0) As new com.sun.star.beans.PropertyValue

   myDocument = thisComponent.CurrentController.Frame
      mySheet = thisComponent.sheets.getByname(mySheetExplorator)
 mydispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

 myArgument(0).Name = "StatusSelectionMode" : myArgument(0).Value = 2
 myDispatcher.executeDispatch(myDocument, ".uno:StatusSelectionMode", "", 0, myArgument())

 myArgument(0).Name = "ToPoint" : myArgument(0).Value = "$B$20"
 myDispatcher.executeDispatch(myDocument, ".uno:GoToCell", "", 0, myArgument())

 myArgument(0).Name = "ToPoint" : myArgument(0).Value = "$A$7"
 myDispatcher.executeDispatch(myDocument, ".uno:GoToCell", "", 0, myArgument())

' myArgument(0).Name = "StatusSelectionMode" : myArgument(0).Value = 0
' myDispatcher.executeDispatch(myDocument, ".uno:StatusSelectionMode", "", 0, myArgument())


' thisComponent.currentController.select(mySheet.getCellRangeByName("$A$7")
' thisComponent.currentController.select(mySheet.getCellRangeByName("$B20:$B$21")
End Sub
for select "$B$20" AND "$A$7" BY CODE and it is always the last one to be selected … without the previous.

And that with or without the API. :(

@FJCC
A huge THANKS for you. It is exactly what I searched. A very good way, for me, to work on “createInstance” that is foreign for me. I have to understand, too, why yo set False to “addRangeAddresses” that Xray say us that “MergeRanges”.

@all : solved ! THANKS!!!
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] [Calc] Visually select multiple cells.

Post by Lupp »

LibO 6.2 SDK API Reference wrote:bMergeRanges defines how the range should be added. To merge the ranges takes more time, but the memory usage is lower.
Concerning the second parameter of .addRangeAddress I cannot find any difference. If I understood the API reference correctly insofar, the method is buggy. As the behaviour is the same in AOO V 4.1.5 the bug (if it is) seems to be common heritage.

Is somebody here knowing better?

Detailed description of an experiment:

Code: Select all

Sub example2()
doc   = ThisComponent
sheet = doc.Sheets(0)
rgs   = doc.createInstance("com.sun.star.sheet.SheetCellRanges")
rgs.addRangeAddress(sheet.getCellRangeByName("K1:L10").RangeAddress, True)
rgs.addRangeAddress(sheet.getCellRangeByName("L6:M15").RangeAddress, True)
MsgBox(rgs.AbsoluteName)
End Sub
The output is "$Sheet1.$K$1:$L$10;$Sheet1.$L$6:$M$15" (My. ranges were in Sheet1).
This is exactly the result I got with 'False'.
Expected: "$Sheet1.$K$1:$L$10;$Sheet1.$M$6:$M$15".

I also checked with K1:L10 adding K6:L15. The ranges were NOT merged to K1:L15!
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] [Calc] Visually select multiple cells.

Post by Villeroy »

Code: Select all

Sub Main
Const s1 = "A1:F10"
Const s2 = "E5:F10"
    doc   = ThisComponent
    sheet = doc.Sheets(0)
    rgs0   = doc.createInstance("com.sun.star.sheet.SheetCellRanges")
    rgs1   = doc.createInstance("com.sun.star.sheet.SheetCellRanges")
    rgs0.addRangeAddress(sheet.getCellRangeByName(s1).RangeAddress, False)
    rgs0.addRangeAddress(sheet.getCellRangeByName(s2).RangeAddress, False)
    rgs1.addRangeAddress(sheet.getCellRangeByName(s1).RangeAddress, True)
    rgs1.addRangeAddress(sheet.getCellRangeByName(s2).RangeAddress, True)
    MsgBox(rgs0.getRangeAddressesAsString() & Chr(10) & rgs1.getRangeAddressesAsString())
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] [Calc] Visually select multiple cells.

Post by Lupp »

@Villeroy:
-1- Thanks.
-2- Is this what you would expect based on the API reference?

Leaving ranges with non-empty intersection untouched isn't acceptable under the term "merge" in my world.
This in specific if no additional explanation is available.
What actually is done would run under "absorb" or "absorbSubRanges". (There are longer names.)
Well, I also don't understand the usage of "collapse" and many other things concerning names from the API.

Seems my brain has constructed an unrealistic world...
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] [Calc] Visually select multiple cells.

Post by Villeroy »

A single range is a rectangle of cells as described by a RangeAddress. 2 ranges can be merged if the result is a rectangle.

The example with s2="A5:F20" expands A1:F10 vertically, s2="A1:X10" expands A1:F10 horizontally if bMerge=True
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: [Solved] [Calc] Visually select multiple cells.

Post by Sébastien C »

I had wished an exchange like this one between guys like all of you; I am served…
Sometimes it is good to say that people can, to my own contrary, go further than the simple surface or the pretty appearance… :bravo:

I am going with this cheerful step to dig my head on the subject of Instances and their divine creation…
Pray for me; please. :ouch:
:D
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
Post Reply