[Solved] Calc: Select Data Area

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

[Solved] Calc: Select Data Area

Post by eeigor »

Looks like when trying to select the entire column and then trim the CurrentRegion (select DataArea) the cursor method stopped working:
oCursor.collapseToCurrentRegion(). Returns entire column.
However, before the update, I don't remember this ...

Code: Select all

Function GetCurrentRegion(oRange As Object)
	Dim oCursor As Object

	oCursor = oRange.Spreadsheet.createCursorByRange(oRange)  '.getSpreadsheet()
	' Expand the cursor into the region containing the cells to which
	' it currently points. A region is a cell range bounded by empty cells.
	oCursor.collapseToCurrentRegion()
	GetCurrentRegion = oCursor  '.getCellRangeByName(oCursor.AbsoluteName)
Rem	Xray oCursor
End Function
Who can look at the previous version. Is it a bug or not?
The menu command works correctly.
Last edited by eeigor on Sat Mar 13, 2021 6:30 pm, edited 1 time in total.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Calc: Select Data Area

Post by Lupp »

Did you use the posted code earlier with the resultt expected by you?
Supposing you passed a complete column to the parameter oRange I would expect exactly what you report: returned a cursor covering the complete range.
The method you are relying on has an extremely misleading name. Regarding its functionality the name should be expandToCurrentRegion().
See https://api.libreoffice.org/docs/idl/re ... b32e4984c4.

The whole spreadsheet has a UsedArea which you can get with the help of a cursor using .gotoStartOfUsedArea(False) and .gotoEndOfUsedArea(True). Then you can intersect a single range with the result using usedArea.queryIntersection(myRange.RangeAddress). What you get may already be what you want. If your column (or different range) has less used cells at its edges, you need to crop it again. You may use the .queryEmptyCells() for the purpose.
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: Calc: Select Data Area

Post by Villeroy »

Typically, you get the used range from a sheet and the current region from a single cell.
The used range is the rectangle that incloses all content cells.
The current region is the rectangle of adjacent content cells enclosed by empty cells or sheet borders.
oUsed = getUsedRange(mySheet)
oCurRegion = getCurrentRegion(myCell)

Code: Select all

Function getUsedRange(oSheet)
Dim oCursor
	oCursor = oSheet.createCursor()
	oCursor.gotoStartOfUsedArea(False)
	oCursor.gotoEndOfUsedArea(True)
	getUsedRange = oCursor
End Function

Function getCurrentRegion(oRange)
Dim oCursor
	oCursor = oRange.getSpreadSheet.createCursorByRange(oRange)
	oCursor.collapseToCurrentRegion
	getCurrentRegion = oCursor
End Function
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
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: Select Data Area

Post by eeigor »

I thought I was doing the same thing above.
Perhaps you are right: you need to put the cursor in the cell of the data area that you want to select. The collapseToCurrentRegion() method expands the cursor into the region containing the cells to which it currently points.
Then how do I briefly reproduce the dispatcher method for this task? The entire column was selected, and then we got the current region by running the dispatcher code below.

Code: Select all

sub UnoSelectData
	dim document   as object
	dim dispatcher as object

	document   = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	dispatcher.executeDispatch(document, ".uno:SelectData", "", 0, Array())
end sub
UPD
Villeroy wrote:Typically, you get the current region from a single cell.
I've already figured it out
Last edited by eeigor on Fri Mar 12, 2021 9:04 pm, edited 2 times in total.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: Select Data Area

Post by Villeroy »

Use getCurrentRegion with a single cell. Simple as that.
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
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: Select Data Area

Post by eeigor »

uno:SelectData works in some other way.

UPD:
It doesn't matter whether the entire column is selected or one of the cells in the current region.

However, there is one caveat: when we select the entire column, Calc makes the current cell in the first row of the visible area. So the current region is counted (expanded) from it.
Last edited by eeigor on Fri Mar 12, 2021 9:56 pm, edited 1 time in total.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: Select Data Area

Post by eeigor »

I think I should add these lines myself. The GetCurrentRegion() function works correctly.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: Select Data Area

Post by Villeroy »

The active cell of a view (aka controller):

Code: Select all

Function getActiveCell(oView)
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
	as1()  = Split(oView.ViewData, ";")
	lSheet = CLng(as1(1))
	sDum = as1(lSheet +3)
	as1() = Split(sDum, "/")
	on error goto errSlash
		lCol = CLng(as1(0))
		lRow = CLng(as1(1))
	on error goto 0
	getActiveCell = oView.Model.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
exit Function
errSlash:
	if NOT(bErr) then
		bErr = True
		as1() = Split(sDum, "+")
		resume
	endif
End Function
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
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: Select Data Area

Post by eeigor »

@Villeroy, thank you. It works. However, not everything is clear yet. The composition of the ViewData structure is not familiar to me. If possible, tell me where to read about it.

UPD: In this example, the document contains only one sheet.
100/60/0;0;tw:1832;1/39/0/0/0/0/2/0/0/0/39
where:
0 is a sheet index;
1 is a column index of the active cell of the selected sheet;
39 is a row index of the active cell of the selected sheet.

When does the error occur?
as1() = Split(sDum, "/"): on error goto errSlash
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: Select Data Area

Post by Villeroy »

In 99% of all use cases you want to get the active cell of the currently active spreadsheet. In rare cases you may be interested in another spreadsheet's current cell. You do not need to understand the ViewData string. My function handles that. I use it since 15 years or so and don't remember the details neither but they are well documented somewhere here: https://wiki.openoffice.org/wiki/Docume ... nvironment
This gets the controller (view) of the current document, the view's currently active cell, then the current region around the cell and finally it lets the view select the current region.

Code: Select all

oView = ThisComponent.getCurrentController()
oCell = getActiveCell(oView)
oRange = getCurrentRegion(oCell)
oView.select(oRange)
A spreadsheet controller always has one active cell regardless of what the user has selected. The possible selections are one cell, one range, multiple ranges or some object. If you want to get the current region around the active input cell, you can disregard the current selection. My function always gets this particular cell and nothing else. In 15 years I never encounted any problems with this method as long as the view is a spreadsheet view.
Last edited by Villeroy on Sat Mar 13, 2021 1:51 pm, edited 2 times in total.
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: Calc: Select Data Area

Post by Lupp »

The error should occur if Row >= 8192.
The issue is very strange and I don't know of any specification to that effect, but was one informed by Villeroy about the problem. Villeroy in turn invoked information by some "uros" insofar. See code.
(I prefer the term "focus cell" to distinguish from code using VBAsupport.)

Code: Select all

Function focusCell(Optional pCtrl) As Object
REM Concept by "uros", "Villeroy"
REM Responsible for this variant: Wolfgang Jäger. No guarantee of any kind.
REM 2017-09-28 V0
If IsMissing(pCtrl) Then pCtrl = ThisComponent.CurrentController
If  NOT pCtrl.SupportsService("com.sun.star.sheet.SpreadsheetView") Then Exit Function
	Dim theSheet As Object, fC As Object, sheetNum As Long, sInfo As String, sInfoDelim As String 
	Dim vD, vDSplit, sInfoSplit
vD             = pCtrl.ViewData
vDSplit        = Split(vD, ";")
theSheet       = pCtrl.ActiveSheet
sheetNum       = theSheet.RangeAddress.Sheet
sInfo          = vDSplit(sheetNum + 3)
REM For CellAddress.Row >= 8192 the "+" is used as the subdelimiter in ViewData. WHY?
If InStr(sInfo, "+")>0 Then 
	sInfoDelim = "+"
Else
	sInfoDelim = "/"
End If
sInfoSplit     = Split(sInfo, sInfoDelim)
fC             = theSheet.GetCellByPosition(sInfoSplit(0), sInfoSplit(1))
focusCell      = fC
End Function 
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: Select Data Area

Post by eeigor »

@Lupp, @Villeroy, thank you all for your help.

Yes, this bullshit remained in place...
100/60/0;1;tw:1832;1/39/0/0/0/0/2/0/0/0/39;0+8192+0+0+0+0+2+0+0+0+8173
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: Select Data Area

Post by Villeroy »

@Lupp
"uros" was the user name of the guy who wrote the first version of getActiveCell. The function threw an error when the row limit was raised from 2^16 to 2^20. Since these days the ViewData string is split by "+" for the higher row numbers, therefore the error handler. I know, this is bad style but this function always worked for me since many years without error.

Same test routine with Lupp's function:

Code: Select all

oView = ThisComponent.getCurrentController()
oCell = focusCell() 'uses the current view by default
REM equivalen with explicit view: oCell = focusCell(oView) 
oRange = getCurrentRegion(oCell)
oView.select(oRange)
It has a different name. It is programmed better. It accepts a missing view object using the current view in case of missing.
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
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: Select Data Area

Post by eeigor »

These are the nuances that distinguish connoisseurs from the rest. And now I associate this bug, rather, with the features of the program.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: [Solved] Calc: Select Data Area

Post by eeigor »

ScViewData
https://docs.libreoffice.org/sc/html/vi ... tml#l03156
3158 // nZoom (until 364v) or nZoom/nPageZoom/bPageMode (from 364w)
3159 // nTab
3160 // Tab control width
3161 // per sheet:
3162 // CursorX/CursorY/HSplitMode/VSplitMode/HSplitPos/VSplitPos/SplitActive/
3163 // PosX[left]/PosX[right]/PosY[top]/PosY[bottom]

3164 // when rows bigger than 8192, "+" instead of "/"
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Calc: Select Data Area

Post by Lupp »

Concerning the original question you may try the attached example I made in a playful mood.
aoo104760expandFromFocus0.ods
(20.3 KiB) Downloaded 306 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: [Solved] Calc: Select Data Area

Post by eeigor »

@Lupp, thanks. It's funny and useful.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
Post Reply