[Solved] Calc Listener: MouseClickHandler, Position Cell X Y

Creating a macro - Writing a Script - Using the API

[Solved] Calc Listener: MouseClickHandler, Position Cell X Y

Postby Kiel » Thu Jan 01, 2015 8:42 pm

[Calc] Listener: MouseClickHandler, Position Cell X and Y relactive a Rectangle, and PopupMenu

I will like to achieve in the end:
Click the cell “B6” and display the “PopupMenu” below.

Problem found:
The first image file attached in "Plan1.png" the “PopupMenu” appears just below the button. However, moving the active cell further down or rolling down the spreadsheet by clicking on the cell “B6” does not follow that, as seen in the second image.

Question:
How do I make pop-up menu and follow the button always appears below it?
Attachments
MouseClickHandler_PositionCellXY_PopupMenu.ods
(35.83 KiB) Downloaded 68 times
Plan1.png
Last edited by Kiel on Sun Jan 04, 2015 1:33 pm, edited 1 time in total.
OpenOffice 3.4 - LibreOffice 4.0 / 4.1 / 4.2 on Windows Seven
User avatar
Kiel
 
Posts: 44
Joined: Wed Nov 26, 2014 5:28 pm

Re: [Calc] Listener: MouseClickHandler, Position Cell X and

Postby Zizi64 » Thu Jan 01, 2015 11:43 pm

Code: Select all   Expand viewCollapse view
Function ShowPopup
     
    oPopup = CreateUnoService("com.sun.star.awt.PopupMenu")
    oPopup.insertItem(1, "Option 1                             ", 0, 0)
    oPopup.insertItem(2, "Option 2                             ", 0, 1)
    oPopup.insertItem(3, "Option 3                             ", 0, 2)
 
    Dim aRect As New com.sun.star.awt.Rectangle
    With aRect
      .X      = 130 ' ------------------------> ?
      .Y      = 199 ' ------------------------> ?
      .Width  = 0   ' ------------------------> ?
      .Height = 0   ' ------------------------> ?
    End With


The X, and Y coordinates have relative values to the "origo" of the aplication window, but not relative to the associated Cell.

Maybe this thread will help you:

https://forum.openoffice.org/en/forum/v ... =9&t=34544

(You can try anchor the "option rectangle" to a cell... I do not know if it is possible.)
Last edited by Zizi64 on Thu Jan 01, 2015 11:49 pm, edited 2 times in total.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7769
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Calc] Listener: MouseClickHandler, Position Cell X and

Postby RoryOF » Thu Jan 01, 2015 11:47 pm

There is some code in this issue listing which might be of help
https://issues.apache.org/ooo/show_bug.cgi?format=multiple&id=76767
Apache OpenOffice 4.1.6 on Xubuntu 18.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 28411
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Calc] Listener: MouseClickHandler, Position Cell X and

Postby hanya » Fri Jan 02, 2015 5:02 am

I need to change the following functions:
Code: Select all   Expand viewCollapse view
Sub Selection_Changed(event)
   
   Dim oCtrlWindow
   oCtrlWindow = ThisComponent.CurrentController.Frame.ContainerWindow
   oCtrlWindow.Enable=False
   If event.supportsservice("com.sun.star.sheet.SheetCell") Then
        aAddr = event.getCellAddress()
        sCell = event.AbsoluteName
        If sCell = "$Plan1.$B$6" Then
            async(Array("Plan1", aAddr.Row, aAddr.Column))
        EndIf
    EndIf
   oCtrlWindow.Enable=True
End Sub

Function ShowPopup(aData)
   
    oPopup = CreateUnoService("com.sun.star.awt.PopupMenu")
    oPopup.insertItem(1, "Option 1                             ", 0, 0)
    oPopup.insertItem(2, "Option 2                             ", 0, 1)
    oPopup.insertItem(3, "Option 3                             ", 0, 2)
   
    aBounds = GetCellBounds(aData(0), aData(1), aData(2))
    If IsNull(aBounds) Then Exit Function
   
    nFormulaBarHeight = GetFormulaBarHeight(ThisComponent)
    oColumnRowHeaderSize = GetColumnRowHeaderSize(ThisComponent)
    Dim aRect As New com.sun.star.awt.Rectangle
    With aRect
      .X      = aBounds.X + oColumnRowHeaderSize.Width + 1
      .Y      = aBounds.Y + aBounds.Height + nFormulaBarHeight + oColumnRowHeaderSize.Height + 1
      .Width  = 0   ' ------------------------> ?
      .Height = 0   ' ------------------------> ?
    End With
   
    oCompWin = ThisComponent.getCurrentController().getFrame().getComponentWindow()     
    n = oPopup.execute(oCompWin, aRect, 0)
   
    If n > 0 Then
       Ctrl = ThisComponent.CurrentController
       oSheet = ThisComponent.Sheets.getByIndex(n-1)
       Ctrl.setActiveSheet(oSheet)
    Else ' clicou fora do menu
       ThisComponent.CurrentController.Select(oSelect):
       oEmpty = ThisComponent.CreateInstance("com.sun.star.sheet.SheetCellRanges"):
       ThisComponent.CurrentController.Select(oEmpty):
    End If
End Function

Type Size
  Width As Long
  Height As Long
End Type

Function GetColumnRowHeaderSize(oDoc As Variant) As Variant
  oRet = CreateObject("Size")
  If oDoc.getCurrentController().HasColumnRowHeaders Then
    oAccCtx = oDoc.getCurrentController().getFrame().getComponentWindow().getAccessibleContext()
    oScrollPaneAcc = FindChildByRole(oAccCtx, com.sun.star.accessibility.AccessibleRole.SCROLL_PANE)
    If not IsNull(oScrollPaneAcc) then
      aPanels = FindSimplePanels(oScrollPaneAcc.getAccessibleContext())
      CalculateSize(aPanels, oRet)
    End If
  End If
  GetColumnRowHeaderSize = oRet
End Function


Function CalculateSize(aChildren As Variant, oRet As Variant) As Variant
  aWidth = Array(0, 0, 0)
  aHeight = Array(0, 0, 0)
  For i = 0 to 2 step 1
    aSize = aChildren(i).getPosSize()
    aWidth(i) = aSize.Width
    aHeight(i) = aSize.Height
  Next

  For i = 0 to 2 step 1
    bWidthMatch = False
    bHeightMatch = False
    nWidth = aWidth(i)
    nHeight = aHeight(i)
    For j = 0 to 2 step 1
      If i <> j then
        If aWidth(j) = nWidth then bWidthMatch = True
        If aHeight(j) = nHeight then bHeightMatch = True
      end if
    next
    If bWidthMatch AND bHeightMatch then
      oRet.Width = nWidth
      oRet.Height = nHeight
      Exit For
    End If
  next
  CalculateSize = oRet
End Function


Function FindSimplePanels(oCtx As Variant) As Variant
  aRet = Array(NULL, NULL, NULL)
  nRole = com.sun.star.accessibility.AccessibleRole.PANEL
  n = 0
  m = oCtx.getAccessibleChildCount()
  for i = 0 to oCtx.getAccessibleChildCount() -1 step 1
    oChild = oCtx.getAccessibleChild(i)
    If NOT HasUnoInterfaces(oChild, "com.sun.star.accessibility.XAccessibleComponent") Then
      oChildCtx = oChild.getAccessibleContext()
      If oChildCtx.getAccessibleRole() = nRole then
        aRet(n) = oChild
        n = n + 1
        if n > 2 then Exit For
      End If
    End If
  next
  FindSimplePanels = aRet
End Function


Function GetFormulaBarHeight(oDoc As Variant) As Long
  nHeight = 0
  oAccCtx = oDoc.getCurrentController().getFrame().getComponentWindow().getAccessibleContext()
  oFormulaBarAcc = FindChildByRole(oAccCtx, com.sun.star.accessibility.AccessibleRole.TOOL_BAR)
  If not IsNull(oFormulaBarAcc) Then
    If oFormulaBarAcc.isVisible() = True then nHeight = oFormulaBarAcc.getSize().Height
  End If
  GetFormulaBarHeight = nHeight
End Function


Function GetCellBounds(sSheetName As String, nRow As Long, nColumn As Long) As Variant
  oResult = Null
  oAccCtx = ThisComponent.getCurrentController().getFrame().getComponentWindow().getAccessibleContext()
  oDocumentPanelAcc = FindChildByRole(oAccCtx, com.sun.star.accessibility.AccessibleRole.SCROLL_PANE)
  If not IsNull(oDocumentPanelAcc) Then
    oDocumentAcc = FindChildByRole(oDocumentPanelAcc.getAccessibleContext(), com.sun.star.accessibility.AccessibleRole.DOCUMENT)
    oFound = Null
    For i = 0 to oDocumentAcc.getAccessibleChildCount() -1 step 1
      oAccChild = oDocumentAcc.getAccessibleChild(i)
      If oAccChild.getAccessibleRole() = com.sun.star.accessibility.AccessibleRole.TABLE Then
        If Right(oAccChild.getAccessibleName(), Len(sSheetName)) = sSheetName Then
          oFound = oAccChild
          Exit For
        End If
      End If
    next
    If NOT IsNull(oFound) Then
      oCellAcc = oAccChild.getAccessibleCellAt(nRow, nColumn)
      oResult = oCellAcc.getBounds()
    End If
  End If
  GetCellBounds = oResult
End Function


Function FindChildByRole(oCtx As Variant, nRole As Integer) As Variant
  oFound = NULL
  For i = 0 to oCtx.getAccessibleChildCount() - 1 step 1
    oChild = oCtx.getAccessibleChild(i)
    oChildCtx = oChild.getAccessibleContext()
    If oChildCtx.getAccessibleRole() = nRole then
      oFound = oChild
      exit for
    End If
  next
  FindChildByRole = oFound
End Function


Sub Callback_notify( aData )
   If oActiveMenu = Empyt or oActiveMenu = True Then
      ShowPopup(aData)
      oActiveMenu = False
   EndIf
End Sub
Please, edit this thread's initial post and add "[Solved]" to the subject line if your problem has been solved.
Apache OpenOffice 4-dev on Xubuntu 14.04
hanya
Volunteer
 
Posts: 884
Joined: Fri Nov 23, 2007 9:27 am
Location: Japan

Re: [Calc] Listener: MouseClickHandler, Position Cell X and

Postby Kiel » Sun Jan 04, 2015 2:42 am

Zizi64, thank you for the answer, and I liked the idea of using the anchor cell, which intend to study a little more this option. I do not think I will use to address this topic, but can be useful in a future routine.

RoryOf, the material that has passed is interesting, will help me, because I'm looking for a lot of material on mouse events at that time.

Hanya, like much of the material to send, it is very good. Tested with OOo 3.4 and it worked perfectly. Also tested with the loo 4.3.2.2 and generated the error in the image that is attached. But that's just a detail, considering the distance that I was the solution before this code.

I thank you all,

Kiel
Attachments
Erro LOo 4.3.png
OpenOffice 3.4 - LibreOffice 4.0 / 4.1 / 4.2 on Windows Seven
User avatar
Kiel
 
Posts: 44
Joined: Wed Nov 26, 2014 5:28 pm

Re: [Calc] Listener: MouseClickHandler, Position Cell X and

Postby hanya » Sun Jan 04, 2015 7:10 am

Use this function on LibreOffice:
Code: Select all   Expand viewCollapse view
Function GetCellBounds(sSheetName As String, nRow As Long, nColumn As Long) As Variant
  oResult = Null
  oAccCtx = ThisComponent.getCurrentController().getFrame().getComponentWindow().getAccessibleContext()
  oDocumentPanelAcc = FindChildByRole(oAccCtx, com.sun.star.accessibility.AccessibleRole.SCROLL_PANE)
  If not IsNull(oDocumentPanelAcc) Then
    oDocumentAcc = FindChildByRole(oDocumentPanelAcc.getAccessibleContext(), com.sun.star.accessibility.AccessibleRole.DOCUMENT)
    If IsNull(oDocumentAcc) Then oDocumentAcc = FindChildByRole(oDocumentPanelAcc.getAccessibleContext(), com.sun.star.accessibility.AccessibleRole.DOCUMENT_SPREADSHEET)
    oFound = Null
    For i = 0 to oDocumentAcc.getAccessibleChildCount() -1 step 1
      oAccChild = oDocumentAcc.getAccessibleChild(i)
      If oAccChild.getAccessibleRole() = com.sun.star.accessibility.AccessibleRole.TABLE Then
        If Right(oAccChild.getAccessibleName(), Len(sSheetName)) = sSheetName Then
          oFound = oAccChild
          Exit For
        End If
      End If
    next
    If NOT IsNull(oFound) Then
      oCellAcc = oAccChild.getAccessibleCellAt(nRow, nColumn)
      oResult = oCellAcc.getBounds()
    End If
  End If
  GetCellBounds = oResult
End Function
Please, edit this thread's initial post and add "[Solved]" to the subject line if your problem has been solved.
Apache OpenOffice 4-dev on Xubuntu 14.04
hanya
Volunteer
 
Posts: 884
Joined: Fri Nov 23, 2007 9:27 am
Location: Japan

[Calc] Listener: MouseClickHandler, Position Cell X and Y re

Postby Kiel » Sun Jan 04, 2015 1:32 pm

Thank Hanya the code works now both in OOo as in LibreOffice.

Grateful,

Kiel
OpenOffice 3.4 - LibreOffice 4.0 / 4.1 / 4.2 on Windows Seven
User avatar
Kiel
 
Posts: 44
Joined: Wed Nov 26, 2014 5:28 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 9 guests