[Solved] [Calc] Visually select multiple cells.

Creating a macro - Writing a Script - Using the API

[Solved] [Calc] Visually select multiple cells.

Postby Sébastien C » Thu Feb 27, 2020 4:19 am

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   Expand viewCollapse view
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 5.2.7.2 under GNU-Linux ARMbian on the Rock64 and M$-W XP
User avatar
Sébastien C
 
Posts: 92
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: [Calc] Visually select multiple cells.

Postby JeJe » Thu Feb 27, 2020 6:36 am

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

Code: Select all   Expand viewCollapse view
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())

Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 794
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Calc] Visually select multiple cells.

Postby FJCC » Thu Feb 27, 2020 7:15 am

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   Expand viewCollapse view
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   Expand viewCollapse view
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)
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7534
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Calc] Visually select multiple cells.

Postby Sébastien C » Thu Feb 27, 2020 7:40 am

Thank you so much Jeje but it doesn’t work by code. I try
Code: Select all   Expand viewCollapse view
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 5.2.7.2 under GNU-Linux ARMbian on the Rock64 and M$-W XP
User avatar
Sébastien C
 
Posts: 92
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

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

Postby Lupp » Thu Feb 27, 2020 11:39 am

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   Expand viewCollapse view
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 6.4 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2726
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby Villeroy » Thu Feb 27, 2020 1:04 pm

Code: Select all   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27880
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Lupp » Thu Feb 27, 2020 2:06 pm

@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 6.4 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2726
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby Villeroy » Thu Feb 27, 2020 2:27 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27880
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Sébastien C » Thu Feb 27, 2020 5:59 pm

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 5.2.7.2 under GNU-Linux ARMbian on the Rock64 and M$-W XP
User avatar
Sébastien C
 
Posts: 92
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests

cron