Need help with misbehaving listener

[JohnV]

The following listener is installed in a Calc file. Among others, it listens to A1 & B1 of the active sheet and changes the text entered to uppercase.

The problem is that once it runs it will not quit. If I change B1 it works on that change and any others I make in B1 but I can't make a change that work in A1 that works (or vice versa). I have to close the Calc document to kill the listener. Sometime, depending on the change made, I had to log off and back on to kill it.

OOo3.3 under Window 7 HP 64b

Code: Select all

Global oSheet As Object
Global oL As Object 'The listener

Sub EventListenerOn
oSheet = ThisComponent.CurrentController.ActiveSheet  
oL = createUnoListener("Listener_","com.sun.star.chart.XChartDataChangeEventListener")
xray oL
aray = Array("A1","B1","F1","G1")
For  c = 0 to uBound(aray)
 oCell = oSheet.GetCellRangeByName(aray(c))
 oCell.addChartDataChangeEventListener(oL)
Next
End Sub

Sub Listener_chartDataChanged(oEV)
CellName = oEv.Source.AbsoluteName
oCell = oSheet.getCellRangeByName(CellName)
oCell.removeChartDataChangeEventListener(oL) 'This line seems to do nothing.
oCell.String = UCase(oCell.String)
'oCell.Value = oCell.Value+1 'Will continuously advance the number while you watch.
'oCell.addChartDataChangeEventListener(oL)
oEv = nothing
'xray oEV = Null Object
End Sub

Sub Listener_disposing(oEV)
 'oCell.removeChartDataChangeEventListener(oL)
End Sub 

Sub Listener_queryInterface(oEV)
'do nothing
End sub

[Villeroy]

The listener calls itself again when it changes the calling cell or when the calling cell modifies a dependent formula cell which also triggers the listener.
Do the 4 cells depend on each other anyhow?
Remove the listener from all dependents, change the cell value, add the listener.
I think there is no need to get the AbsoluteName and the RangeByName: oCell=oEv.Source

[JohnV]

Villeroy, this is the listener for the fellow in the other forum that we are working with that wants to change lowercase entries in a cell to uppercase.

Do the 4 cells depend on each other anyhow?

No, they are just 4 blank cells.

The listener calls itself again when it changes the calling cell or when the calling cell modifies a dependent formula cell which also triggers the listener.

Assuming I didn't have a line in the code that is suppose to remove the listener before the macro make a change in the cell then I would think that the listener would fire on the initial change and again when the macro changes the cell. But, because none listened to cells are dependent, it should "die" and silently wait for another change.

I think there is no need to get the AbsoluteName and the RangeByName: oCell=oEv.Source

Thanks, that works but doesn't solve the problem.

[JohnV]

Fooled around just a bit and this code appears to work. Interestingly, it's almost exactly what I started with before I tried other stuff to make it work, some of which is seen in my original post. I conclude that listeners are very touchy.

Code: Select all

Global oSheet As Object
Global oL As Object 'The listener

Sub EventListenerOn
oSheet = ThisComponent.CurrentController.ActiveSheet  
oL = createUnoListener("Listener_","com.sun.star.chart.XChartDataChangeEventListener")
aray = Array("A1","B1","F1","G1")
For  c = 0 to uBound(aray)
 oCell = oSheet.GetCellRangeByName(aray(c))
 oCell.addChartDataChangeEventListener(oL)
Next
End Sub

Sub Listener_chartDataChanged(oEV)
oCell = oEV.Source
oCell.removeChartDataChangeEventListener(oL) 
oCell.String = UCase(oCell.String)
oCell.addChartDataChangeEventListener(oL)
End Sub

Sub Listener_disposing(oEV)
 'do nothing
End Sub 

Sub Listener_queryInterface(oEV)
'do nothing
End sub

[hanya]

oCell.removeChartDataChangeEventListener(oL) 'This line seems to do nothing.

Re-querying the same cell makes another object causes the problem to add/remove listeners.

Code: Select all

Sub CompareTwoCell
  oSheet = ThisComponent.getSheets().getByIndex(0)
  oCell1 = oSheet.getCellByPosition(0, 0)
  oCell2 = oSheet.getCellByPosition(0, 0)
  
  oListener = CreateUnoListener("Listener_", _
      "com.sun.star.chart.XChartDataChangeEventListener")
  oCell1.addChartDataChangeEventListener(oListener)
  oCell2.removeChartDataChangeEventListener(oListener)
  
  'msgbox EqualUnoObjects(oCell1, oCell2)
End Sub

Sub Listener_chartDataChanged(oEV)
  msgbox ""
End Sub
Sub Listener_disposing(oEV)
End Sub 

I tried to find why this little problem causes. If we call getCellByPosition method through css.table.XCellRange interface, GetCellByPosition_Impl function is called [1] and new ScCellObj instance is created for each query in the function [2]. This means oCell1 and oCell2 are always different object internally.
The listener is added by addChartDataChangeEventListener method [3] and removed by the method defined in the following part. The listener is removed from the internal container ScChartListenerCollection instance in the removeChartDataChangeEventListener method, FreeUno of the container is called to remove the passed listener with own instance as second parameter. In the FreeUno fucntion [4], the listener and the source instance are checked both objects are the same with contained them. In this case, oCell1 and oCell2 are not the same object, and it failed.

[1]: http://svn.services.openoffice.org/open ... o.cxx#4879
[2]: http://svn.services.openoffice.org/open ... o.cxx#4855
[3]: http://svn.services.openoffice.org/open ... o.cxx#3260
[4]: http://svn.services.openoffice.org/open ... is.cxx#551

The listeners used on spreadsheets should be work with the same object, should be the same internally.
Bye.

[Villeroy]

Extract the attached zip to <profile>/user/Script/python/
Select some sheet cells. Multiple ranges on multiple sheets allowed. In case of an object selection the active cell only will listen.
Run Tools>Macros>Organize>Python...MyMacros>UpperCaseCorrection>startUpperCaseCorrection
Have fun.

The code is best viewed in some editor adjustable to Unix line feeds (LF). Writer can open it as "Text Encoded (*.txt")
It adds one listener to the range or ranges collection selected in the current controller.
The YesWeCan listener gets the active cell from the current controller, removes itself from the ranges, does what it has got to do, adds itself to the ranges. I have tested the listener with a selection of all cells on all sheets.
Unlike Basic code, the YesWeCan class keeps a reference to the document and the ranges it has been applied to. You can attach it to many spreadsheet files and get a new instances from the same class.
We get the same race condition as in Basic when 2 instances of this listener have been attached to a cell and the cell gets modified.
This is it:

Code: Select all

import uno, unohelper
from com.sun.star.util import XModifyListener
from com.sun.star.table.CellContentType import TEXT

def getActiveCell(oView):
    '''Desparately missing in API. We extract from view data.'''
    sData = oView.getViewData()
    oSheet = oView.getActiveSheet()
    as1  = sData.split(";")
    lSheet = long(as1[1])
    sDum = as1[lSheet +3]
    delim = '/' in sDum and '/' or '+'
    as2 = sDum.split(delim)
    lCol = (as2[0])
    lRow = (as2[1])
    return oSheet.getCellByPosition(lCol,lRow)

def getRangesSelection(oView):
    oSel = oView.getSelection()
    if oSel.supportsService('com.sun.star.sheet.SheetCellRange'):
        return oSel.queryIntersection(oSel.getRangeAddress())
    elif oSel.supportsService('com.sun.star.sheet.SheetCellRanges'):
        return oSel
    else:
        return None

class YesWeCan(XModifyListener,unohelper.Base):
    def __init__(self,d,r):
        self.doc = d
        self.ranges = r

    def modified(self,oEvent):
        calling = getActiveCell(self.doc.getCurrentController())
        if calling.getType() == TEXT:
            self.ranges.removeModifyListener(self)
            s = calling.getString()
            calling.setString(s.upper())
            self.ranges.addModifyListener(self)
        
    def disposing(self,oEvent):
        pass

def startUpperCaseCorrection():
    document = XSCRIPTCONTEXT.getDocument()
    view = document.getCurrentController()
    sel = getRangesSelection(view)
    if not sel: sel= getActiveCell(view)
    m = YesWeCan(document,sel)
    sel.addModifyListener(m)

# the helper implements all the vanilla UNO-interfaces that belong to every object.
# You may override them. See source code of unohelper module.
g_ImplementationHelper = unohelper.ImplementationHelper()
g_ImplementationHelper.addImplementation(
    XSCRIPTCONTEXT,YesWeCan,'com.sun.star.util.XModifyListener',
)
g_exportedScripts = startUpperCaseCorrection,

The same code is stored in the attached source code file.
When you copy the above code you must not use the "SELECT ALL" facility of this page. It adds empty spaces which spoils Python code. Select the code manually, copy it into Writer or any decent editor and save it with UNIX line feeds (LF) and name suffix .py.

One question to hanya, please
How can I remove the attached listener from the ranges without reloading the document? I don't have any persistent object like a global var in Basic.

[hanya]

How can I remove the attached listener from the ranges without reloading the document? I don't have any persistent object like a global var in Basic.

From the view of the above code, there is no way to remove it. Equality of the passed object and kept one is checked by == operator and ScCellObj class and its inherited classes do not override the operator==. This means we need the same instance to remove the listener from the added range or cell.
In the case of Basic, all objects taken from the office in the subroutine, which are kept in the wrapper object internally, are destructed after the execution of the code finished except for assigned in the global variables. I have mentioned in above post, we get newly created instance of the cell and it can not behave as older one. The listener instance is lived in the listener container but it does not exported through api.

To remove cell or ranges from the document does not help to remove the listeners.
Here is an example procedure to make problems (on OOo 3.3):
1. Make a new spreadsheet document.
2. Run the above code, CompareTwoCell with the following code. The listener has been set to the A1.

Code: Select all

Sub Listener_chartDataChanged(oEV)
  msgbox oEV.Source.AbsoluteName
End Sub

3. Delete Sheet1 (first sheet) from the document -> message box shows "Sheet2.A1"
4. Delete Sheet2 (2nd sheet) -> message shows "Sheet3.A1".
The listener is remained and its referenced cell is changed to a cell in the another sheet. Because the cell object knows only cell address (and document reference also) as the indexes of row, column and sheet.

[Villeroy]

Thank you for the quick answer. With the help of your excellent modified Python script provider I created a spreadsheet template where all cells are uppercased automagically when they are modified by a constant text value.

Install: Put it in one of your trusted directories according to Tools>Options...OOo>Security>Macro Security... Trusted Sources...

The template starts with 3 sheets, and the Python macro embedded (plus styles and names of mine). When you create a new document from this template or when you load an existing document created from this template then you get the listener attached to all cells on each sheet. This means that you can insert new sheets which behave normally until you reload the file and all the sheets get the listener attached.
The whole magic can be turned off when you remove the events in Tools>Customize..., save and reload the file.

[JohnV]

Gentlemen,

Thanks for your additional input although you getting somewhat over my head.

hanya, when I run your code the listener seems to fire about 8-9 times even if I add some code to remove and then add back the listener. To my suprise, if I add a MsgBox to my working code the listener fires continuously - I can't kill the document and have to log off and back on to regain control. Without the MsgBox I see no sign of continuous firing and the code appears to work correctly and monitors each of the cells it's attached to.

Villeroy, somewhat to my surprise I had to create the "python" sub-directory. Anyway I managed to run your code which works beautifully. I particularly appreciated the multiple selections on multiple sheets.

Haven't tried your latest upload yet.

[Villeroy]

Thanks for your additional input although you getting somewhat over my head.

Object oriented programming. A class is blueprint code for new objects. __init__ is called when a new object is born.

Haven't tried your latest upload yet.

That one might be interesting for your patient on the other forum. Seems to work fairly well with manual input cell by cell. A formula constant ="lower case" can be used to enter regular text.
Because the listener is focussed on the active cell it shows a strange side effect when you paste or import a range of data: Only the active cell becomes upper-cased if it happens to be constant text.

[JohnV]

Villeroy,

Yes, another nice one.

I think focus on the active cell would be overkill for my "patient" because he only wants to monitor 4 cells.
The first one would be better suited to his needs.