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 ...
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.
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
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)
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
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.
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.
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
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.
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
@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
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.
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
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.)
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
@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.
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