[Solved] Calc: Select Data Area

Creating a macro - Writing a Script - Using the API

[Solved] Calc: Select Data Area

Postby eeigor » Fri Mar 12, 2021 2:32 pm

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.
Last edited by eeigor on Sat Mar 13, 2021 6:30 pm, edited 1 time in total.
Ubuntu 18.04 LTS • LibreOffice 7.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: Select Data Area

Postby Lupp » Fri Mar 12, 2021 8:13 pm

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

Re: Calc: Select Data Area

Postby Villeroy » Fri Mar 12, 2021 8:51 pm

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
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
Villeroy
Volunteer
 
Posts: 29887
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: Select Data Area

Postby eeigor » Fri Mar 12, 2021 8:54 pm

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
Last edited by eeigor on Fri Mar 12, 2021 9:04 pm, edited 2 times in total.
Ubuntu 18.04 LTS • LibreOffice 7.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: Select Data Area

Postby Villeroy » Fri Mar 12, 2021 8:57 pm

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

Re: Calc: Select Data Area

Postby eeigor » Fri Mar 12, 2021 9:03 pm

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.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: Select Data Area

Postby eeigor » Fri Mar 12, 2021 9:22 pm

I think I should add these lines myself. The GetCurrentRegion() function works correctly.
Ubuntu 18.04 LTS • LibreOffice 7.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: Select Data Area

Postby Villeroy » Sat Mar 13, 2021 3:45 am

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
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
Villeroy
Volunteer
 
Posts: 29887
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: Select Data Area

Postby eeigor » Sat Mar 13, 2021 9:38 am

@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.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: Select Data Area

Postby Villeroy » Sat Mar 13, 2021 1:41 pm

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.
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
Villeroy
Volunteer
 
Posts: 29887
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: Select Data Area

Postby Lupp » Sat Mar 13, 2021 1:42 pm

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

Re: Calc: Select Data Area

Postby eeigor » Sat Mar 13, 2021 2:08 pm

@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.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc: Select Data Area

Postby Villeroy » Sat Mar 13, 2021 2:42 pm

@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.
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
Villeroy
Volunteer
 
Posts: 29887
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc: Select Data Area

Postby eeigor » Sat Mar 13, 2021 6:26 pm

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.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm

Re: [Solved] Calc: Select Data Area

Postby eeigor » Sun Mar 14, 2021 5:19 pm

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.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm

Re: [Solved] Calc: Select Data Area

Postby Lupp » Sun Mar 14, 2021 11:45 pm

Concerning the original question you may try the attached example I made in a playful mood.
aoo104760expandFromFocus0.ods
(20.3 KiB) Downloaded 99 times
On Windows 10: LibreOffice 7.1 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3071
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Calc: Select Data Area

Postby eeigor » Mon Mar 15, 2021 7:29 pm

@Lupp, thanks. It's funny and useful.
Ubuntu 18.04 LTS • LibreOffice 7.1.3.2 Community
eeigor
 
Posts: 143
Joined: Sun Apr 12, 2020 10:56 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 8 guests