Page 1 of 1

[Solved] Calc: Select Data Area

PostPosted: Fri Mar 12, 2021 2:32 pm
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   Expand viewCollapse view
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.

Re: Calc: Select Data Area

PostPosted: Fri Mar 12, 2021 8:13 pm
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.

Re: Calc: Select Data Area

PostPosted: Fri Mar 12, 2021 8:51 pm
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   Expand viewCollapse view
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

Re: Calc: Select Data Area

PostPosted: Fri Mar 12, 2021 8:54 pm
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   Expand viewCollapse view
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

Re: Calc: Select Data Area

PostPosted: Fri Mar 12, 2021 8:57 pm
by Villeroy
Use getCurrentRegion with a single cell. Simple as that.

Re: Calc: Select Data Area

PostPosted: Fri Mar 12, 2021 9:03 pm
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.

Re: Calc: Select Data Area

PostPosted: Fri Mar 12, 2021 9:22 pm
by eeigor
I think I should add these lines myself. The GetCurrentRegion() function works correctly.

Re: Calc: Select Data Area

PostPosted: Sat Mar 13, 2021 3:45 am
by Villeroy
The active cell of a view (aka controller):
Code: Select all   Expand viewCollapse view
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

Re: Calc: Select Data Area

PostPosted: Sat Mar 13, 2021 9:38 am
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

Re: Calc: Select Data Area

PostPosted: Sat Mar 13, 2021 1:41 pm
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   Expand viewCollapse view
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.

Re: Calc: Select Data Area

PostPosted: Sat Mar 13, 2021 1:42 pm
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   Expand viewCollapse view
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

Re: Calc: Select Data Area

PostPosted: Sat Mar 13, 2021 2:08 pm
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

Re: Calc: Select Data Area

PostPosted: Sat Mar 13, 2021 2:42 pm
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   Expand viewCollapse view
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.

Re: Calc: Select Data Area

PostPosted: Sat Mar 13, 2021 6:26 pm
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.

Re: [Solved] Calc: Select Data Area

PostPosted: Sun Mar 14, 2021 5:19 pm
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 "/"

Re: [Solved] Calc: Select Data Area

PostPosted: Sun Mar 14, 2021 11:45 pm
by Lupp
Concerning the original question you may try the attached example I made in a playful mood.
aoo104760expandFromFocus0.ods
(20.3 KiB) Downloaded 122 times

Re: [Solved] Calc: Select Data Area

PostPosted: Mon Mar 15, 2021 7:29 pm
by eeigor
@Lupp, thanks. It's funny and useful.