Run a python macro when cell contents change

[jtolsen]

I'm trying to write a python macro that will add a listener so that when a given cell changes another python macro is executed. All of the other examples I've seem assume that the python code resides outside of OO and these example use UNO to communicate with OO -- this is way more than I need. I'm running my python macros from within OO calc. I'm also interested in a simple way to remove the listeners too.

Is there some simple example for how to do this? Thanks!

[Villeroy]

http://udk.openoffice.org/python/python-bridge.html
> help(unohelper.ImplementationHelper)
Implement a com.sun.star.util.XModifyListener and attach it to a SheetCell.

A cheap alternative, misusing the cell-validation feature: viewtopic.php?f=21&t=12575

[jtolsen]

Still not sure what I'm doing wrong here. This python code is not even close to executing. Do some things need to be global, or do I need to include other python helper libraries to make this work?

Code: Select all   Expand viewCollapse view

def AddMyListener():
    document = XSCRIPTCONTEXT.getDocument()
    sheets = document.getSheets()
    sheet = sheets.getByName( "Panview" )
    panlistener = CreateUnoListener( "MyApp_", "com.sun.star.util.XModifyListener" )
    cell = sheet.getCellByPosition("A1")
    cell.addModifyListener( panlistener )

def MyApp_Modified( dummy )
    # the stuff U want to do!
    print "cell has changed!"

[Villeroy]

Where is CreateUnoListener() defined? It is certainly not a Python runtime-functions like tuple(), int() or list().
CreateUnoListener(prefix, IfaceName) is a Basic function. It's working around the fact that you can not define classes in Basic. Thus you can not define listeners. The function creates some listener internally which then calls the routine with the specified name-prefix.

Code: Select all   Expand viewCollapse view

import uno
from com.sun.star.util import XModifyListener
class myChange(XModifyListener):
# XModifyListener
   def modified(oEvent):
       calling = oEvent.Source

# parent-interface XEventListener
   def disposing(oEvent):
       pass #normally not needed, but should be callable anyway

m = myChange()
cell.addModifyListener(m)


[jtolsen]

Thanks Villeroy for your code snippet. When I add it to a simple python macro main() and attempt to run it from within calc it throws an exception and complains that myChange instance has no attribute 'getTypes'. Does this need to be explicitly added to class myChange? I've tried searching for XModifyListener, getTypes, addModifyListener on this and other OO forums with no luck.

Code: Select all   Expand viewCollapse view

import uno
from com.sun.star.util import XModifyListener

class myChange(XModifyListener):
# XModifyListener
   def modified(oEvent):
       calling = oEvent.Source
       print "something changed"

# parent-interface XEventListener
   def disposing(oEvent):
       pass #normally not needed, but should be callable anyway

#m = myChange()
#cell.addModifyListener(m)

def main():
    document = XSCRIPTCONTEXT.getDocument()
    sheets = document.getSheets()
    sheet = sheets.getByName( "Panview" )
    cell = sheet.getCellRangeByName("F1")
    m = myChange()
    cell.addModifyListener(m)

[Villeroy]

Code: Select all   Expand viewCollapse view

import uno, unohelper

from com.sun.star.util import XModifyListener

class myChange(XModifyListener,unohelper.Base):
# XModifyListener
   def modified(oEvent):
       calling = oEvent.Source
       print "something changed"

# parent-interface XEventListener
   def disposing(oEvent):
       pass #normally not needed, but should be callable anyway

#m = myChange()
#cell.addModifyListener(m)

def main():
    document = XSCRIPTCONTEXT.getDocument()
    sheets = document.getSheets()
    sheet = sheets.getByName( "Panview" )
    cell = sheet.getCellRangeByName("F1")
    m = myChange()
    cell.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(myFoo, 'com.sun.star.module.XFooListener',)

btw: you can not run macros "from within Calc". It is one office process for all documents.

[jtolsen]

If the last two lines (g_ImplementationHelper) are present then I cannot run the macro. It is not even shown as an option in the Tools -> Macros -> Run Macros... dialog box. With the lines commented out I continue to get the error message about 'getTypes' when I try to run the main() macro.

[Villeroy]

Please, learn to debug your Python code. There are plenty of editors and tools. If I copy&paste from somewhere or hack out of my head then there are certainly errors. If the macro is not availlable, then it does not even compile.
'com.sun.star.module.XFooListener' <<< OOops

[jtolsen]

Villeroy,

It's not a python code issue, it's trying to figure out the OpenOffice API which IMO is poorly documented and utterly non-intuitive.

Comments like "learn to debug your code" are not helpful. Assuming that everyone understands that when you say "foo" you really mean something like "com.sun.star.script.condescending.guru.NewbieSmackdown()" is not helpful.

A simple working example here would go a long way to showing that OpenOffice actually can be used as a platform for developing applications with python. I'm sure some of the folks (even newbies like me) reading this thread would start using this functionality if it were clearly explained/demonstrated.

[Villeroy]

Both, UNO and the office-API are perfectly well documented and there are plenty of introspection tools.
Are you complaining that I don't write your code?

Edit: Working example:

Code: Select all   Expand viewCollapse view

import uno, unohelper
from com.sun.star.util import XModifyListener

class myChange(XModifyListener,unohelper.Base):
    def __init__(self,):
        self.doc = None

    def setDocument(self, doc):
        self.doc = doc

# XModifyListener
    def modified(self,oEvent):
        calling = oEvent.Source
        cbc = calling.CellBackColor
        calling.CellBackColor = (cbc== -1) and 0xFF0000 or -1
        self.doc.setModified(False)

# parent-interface XEventListener
    def disposing(self,oEvent):
        pass #normally not needed, but should be callable anyway

def start():
    document = XSCRIPTCONTEXT.getDocument()
    sheets = document.getSheets()
    # top square of 4 in first sheet
    sheet = sheets.getByIndex(0)
    cell = sheet.getCellRangeByName("A1:B2")
    m = myChange()
    m.setDocument(document)
    cell.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(
    myChange,
    'com.sun.star.util.XModifyListener',()
)
g_exportedScripts = start,



Edit: [2] The above listener implementation causes a race condition if you put a volatile function such as NOW() or RAND() into the range. You should handle that situation or try to make the following actually working.

The PropertyChangeListener seems to be dysfunctional: http://www.oooforum.org/forum/viewtopic ... gelistener

Code: Select all   Expand viewCollapse view

import uno, unohelper
from com.sun.star.beans import XPropertyChangeListener

class myPropertyChangeListener(XPropertyChangeListener,unohelper.Base):
    def __init__(self,):
        self.doc = None

    def setDocument(self, doc):
        self.doc = doc

# XPropertyChangeListener
    def propertyChange(self,oEvent):
        '''gets a struct c.s.s.beans.PropertyChangeEvent'''
        calling = oEvent.Source
        # with the following additional elements:
        #sName = oEvent.PropertyName
        #bFurther = oEvent.Further
        #iHandler = oEvent.PropertyHandle
        #anyOldVal = oEvent.OldValue
        #anyNewVal = oEvent.NewValue
       
        calling.CellBackColor = (cbc== -1) and 0xFF0000 or -1
        self.doc.setModified(False)

# parent-interface XEventListener
    def disposing(self,oEvent):
        pass #normally not needed, but should be callable anyway


def start():
    document = XSCRIPTCONTEXT.getDocument()
    sheets = document.getSheets()
    # first *single cell* in first sheet
    sheet = sheets.getByIndex(0)
    cell = sheet.getCellRangeByName("A1")
    m = myPropertyChangeListener()
    m.setDocument(document)
    # FormulaLocal is a true property (not a pseudo-property) of a single cell
    cell.addPropertyChangeListener('FormulaLocal', 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(myPropertyChangeListener, 'com.sun.star.beans.XPropertyChangeListener',())
g_exportedScripts = start,


The validation listener I already mentioned does work actually.
Think twice if you really want to tweak a spreadsheet in this way. You have also databases with input forms at hand.

[twohot]

DISCLAIMER: CASUAL CODER HERE! TREAT AS NEWBIE

Okay. Its good to see how listeners can be done in Python. I also noticed this thread is dated so there may be something more recent on this topic out there. Please redirect as necessary. Thank you. ... Meanwhile, I'd like to rephrase jtolsen's POV on the Developer's Guide. Yes, it is well documented ... probably over-documented, but for a class of people. Short version: Its hard to understand. Interfaces, Services, Models, implementations, .... big words that may need some explaining for casual macro developers. It may be easier to assimilate the hierarchy if there is a list of Classes and their embedded methods so people know which methods will be available when one wants to override or subclass things. Or perhaps have a better graphical representation of the OpenOffice Architecture. The whole thing seems biased to Java and C++ programmers and translating the examples to PyUNO equivalents isn't always intuitive.

That said, I copied Villeroy's example and tried to extend it. Results? No Results ... I couldn't deduce a way around it. I wanted to have the event write a value to another cell OR copy a sheet into another index position. I wish there is an easy way to find the right PyUNO methods to do the job. Please can someone throw more light.

[FJCC]

Having an object inspection tool is extremely helpful for writing macros. I use MRI and I used XRay in the past. These allow you to see the properties and methods of your objects without wading through the documentation. For example, I saw you wanted to copy a sheet, so I started MRI, got the Sheets container of a Calc document and found copyByName among its methods. I had to know enough to look at the Sheets container, so it requires some familiarity with the API, but you can start with the top level of the document and work your way down.
A very simple version of code to change the value of a cell or copy a sheet is below. This isn't meant to be useful code, just an example of the methods. I left in a call to MRI to illustrate one way to use it.

Code: Select all   Expand viewCollapse view

import uno, unohelper
from com.sun.star.util import XModifyListener

ctx = uno.getComponentContext()
odoc = XSCRIPTCONTEXT.getDocument()
osheets = odoc.getSheets()

class Listener(XModifyListener, unohelper.Base):
   def modified(self, oEv):
      val = oEv.Source.Value
      if val: #if the value of the calling cell is not zero
         osheets.copyByName('Sheet1', 'NewSheet', 2)
      else:
         osheet = osheets.getByName('Sheet1')
         ocell = osheet.getCellRangeByName('E1')
         ocell.Value = ocell.Value + 1
   def disposing(self, oEv):
      pass

def Start():
   x = Listener()
   mri(osheets)
   osheet =  osheets.getByName('Sheet1')
   ocell = osheet.getCellRangeByName('A1')
   ocell.addModifyListener(x)

def mri( target): 
   mri = ctx.ServiceManager.createInstanceWithContext("mytools.Mri",ctx)
   mri.inspect(target)

[twohot]

Having an object inspection tool is extremely helpful for writing macros. I use MRI and I used XRay in the past. These allow you to see the properties and methods of your objects without wading through the documentation. For example, I saw you wanted to copy a sheet, so I started MRI, got the Sheets container of a Calc document and found copyByName among its methods. I had to know enough to look at the Sheets container, so it requires some familiarity with the API, but you can start with the top level of the document and work your way down.

I have not explored or tested the code but I've just installed MRI extension.
You just made a newbie very happy. Now we're getting somewhere in this maze. Thanks I'll tinker around with this tool and see how far I can go in understanding macro development in OpenOffice. Thanks for the code snippet too.

[twohot]

OKAY!!!!

So, I've been tinkering and shifting bits of petty code around to see what can happen. So far, so good. The MRI extension is a blessing. Meanwhile, I hit a roadblock while trying more things. Here is what I wanted to achieve: Say, you start out with a Workbook with only two sheets (Sheet1, sheet2). I want changes in A1 to trigger the addition OR removal of more sheets based on its value. A value of 0 or 1 should do nothing but at 2, the macro should add 'Sheet3'; At 3, it should add 'sheet4' .... lowering the value of A1 back should remove corresponding amount of difference while values 0 and 1 should always get you back to two sheets (sheet1 and Sheet2).

I set out to do this by modifying the existing snippets above as part of getting acquainted with Listeners. I figured an iteration will be needed so I added a 'FOR' loop. I'm using E1 on sheet1 for visual feedback until I can get some stability. For some reason, I'm not getting any activity from #OPTION-B. Is it that listeners do not support iterators? I'm not done with the algorithm yet ... its messy, but I'm wondering why #OPTION-B does nothing? I must have shot myself on the foot somewhere.

Code: Select all   Expand viewCollapse view

import uno, unohelper
from com.sun.star.util import XModifyListener

ctx = uno.getComponentContext()
odoc = XSCRIPTCONTEXT.getDocument()
osheets = odoc.getSheets()

class myListener(XModifyListener, unohelper.Base):
    ioCell = None
    oldVal = 0
    theSheet = None
    vDelta = 0
   
    def __init__(self,val):
        #self.doc = None
        self.oldVal = val
        self.theSheet = osheets.getByName('Sheet1')
        self.ioCell = self.theSheet.getCellRangeByName('E1')

    #def setDocument(self, doc):
        #self.doc = doc
   
    def modified(self, oEv):
        newVal = oEv.Source.Value
        self.vDelta = newVal - self.oldVal
        sheetCnt = osheets.getCount()
        if (sheetCnt + self.vDelta) < 2 : #OPTION-A
            #the change is illegal, do nothing
            self.ioCell.FormulaLocal = "ILLEGAL OPERATION"
        elif (self.vDelta < 0):  #OPTION-B
            for count in range(abs(self.vDelta),0,-1):
                osheetName = osheets.getByIndex(sheetCnt-1).Name
                osheets.removeByName(osheetName)
                sheetCnt = sheetCnt
            self.oldVal = newVal
        elif (self.vDelta > 0):  #OPTION-C
            self.ioCell.FormulaLocal = "add-" + str(abs(self.vDelta))
            self.oldVal = newVal
        else:  #OPTION-D
            pass
           
    def disposing(self, oEv):
        pass
               

def Start():
    # mri(osheets)
    osheet =  osheets.getByName('Sheet1')
    ocell = osheet.getCellRangeByName('A1')
    iVal = ocell.Value
    monitor = myListener(iVal)
    ocell.addModifyListener(monitor)

def mri( target):
    mri = ctx.ServiceManager.createInstanceWithContext("mytools.Mri",ctx)
    mri.inspect(target)

# 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(
    myListener,
    'com.sun.star.util.XModifyListener',()
)
g_exportedScripts = Start,

I prepared a template for testing this. See attachment. Is there a way to trace/step-into values at runtime with OOo/LibreOffice? Any such debug tool will be appreciated. Thanks

[FJCC]

The for loop will work in the listener, but I think the current state of your code makes it easy to get tangled up. I did the following:
Opened a fresh file and added two sheets to it to have a total of four sheets.
Entered the number three in cell A1
Ran the Start function of your macro to add the listener to A1
Changed A1 to have the value 1

The number of sheets was reduced to two, as expected. Since your current code does not add sheets and it allows invalid values (like zero) to be passed to oldValue during initialization of the listener, weird things can happen.

Here is the code I ran. I think it is the same as yours, but I did some editing and may not have remembered everything I changed.

Code: Select all   Expand viewCollapse view

import uno, unohelper
from com.sun.star.util import XModifyListener

ctx = uno.getComponentContext()
odoc = XSCRIPTCONTEXT.getDocument()
osheets = odoc.getSheets()

class myListener(XModifyListener, unohelper.Base):
   ioCell = None
   oldVal = 0
   theSheet = None
   vDelta = 0

   def __init__(self,val):
      #self.doc = None
      self.oldVal = val
      self.theSheet = osheets.getByName('Sheet1')
      self.ioCell = self.theSheet.getCellRangeByName('E1')

   def modified(self, oEv):
      newVal = oEv.Source.Value
      self.vDelta = newVal - self.oldVal
      sheetCnt = osheets.getCount()
      if (sheetCnt + self.vDelta) < 2 : #OPTION-A
      #the change is illegal, do nothing
         self.ioCell.FormulaLocal = "ILLEGAL OPERATION"
      elif (self.vDelta < 0): #OPTION-B
         for count in range(abs(self.vDelta),0,-1):
            osheetName = osheets.getByIndex(sheetCnt-1).Name
            osheets.removeByName(osheetName)
            sheetCnt = sheetCnt - 1
         self.oldVal = newVal
      elif (self.vDelta > 0): #OPTION-C
         self.ioCell.String = "add-" + str(self.vDelta)
         self.oldVal = newVal
      else: #OPTION-D
         pass

   def disposing(self, oEv):
      pass

def Start():
   # mri(osheets)
   osheet = osheets.getByName('Sheet1')
   ocell = osheet.getCellRangeByName('A1')
   iVal = ocell.Value
   monitor = myListener(iVal)
   ocell.addModifyListener(monitor)

def mri( target):
   mri = ctx.ServiceManager.createInstanceWithContext("mytools.Mri",ctx)
   mri.inspect(target)

# 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(
   myListener,
   'com.sun.star.util.XModifyListener',()
)
g_exportedScripts = Start,

[twohot]

So it worked in OpenOffice. I'm beginning to think there is a bug in LibreOffice. I tweaked the code a bit and this version has been consistent. It works fine as is ... with the 'FOR' loop disabled/commented-out. At a value of 1 or 0, no additions or removals happen, which means there is always 2 sheets left at minimum. I find it puzzling that the code breaks once the comments are removed. Even the feedback on E1 becomes wrong when the Loop becomes activated. Is this not a bug? Please can someone test this on LibreOffice?

Code: Select all   Expand viewCollapse view

import uno, unohelper
from com.sun.star.util import XModifyListener

ctx = uno.getComponentContext()
odoc = XSCRIPTCONTEXT.getDocument()
osheets = odoc.getSheets()

class myListener(XModifyListener, unohelper.Base):
    diff = None
    hotSheet = None #remove later
    formerVal = None
    numOfSheets = None
    outputCell = None #remove later
    minSheets = 2

    def __init__(self,currentVal):
        #self.doc = None
        self.formerVal = currentVal #TODO: code to check that value is correct
        self.numOfSheets = osheets.getCount()
        self.hotSheet = osheets.getByName('Sheet1') #remove later
        self.outputCell = self.hotSheet.getCellRangeByName('E1') #for feedback-remove later
   
    def modified(self, oEv):
        currentVal = oEv.Source.Value
        self.diff = currentVal - self.formerVal
        if (self.formerVal < self.minSheets) and (currentVal < self.minSheets):
            self.outputCell.FormulaLocal = "STILL AT MINIMUM" #pass
        elif self.diff > 0:
            self.outputCell.FormulaLocal = "ADDED " + str(self.diff)
            #for x in range(0, abs(self.diff)):
                #osheets.copyByName('Sheet2','sheet' + str(self.numOfSheets+1),\
                #self.numOfSheets)
                #self.numOfSheets+=1
        elif self.diff < 0:
            self.outputCell.FormulaLocal = "REMOVED " + str(abs(self.diff))
            #for x in range(0, abs(self.diff)):
                #lastSheetName = osheets.getByIndex(self.numOfSheets-1).Name
                #osheets.removeByName(lastSheetName)
                #self.numOfSheets-=1
        else: #zero difference
            self.outputCell.FormulaLocal = "NOTHING TO DO" #pass
        self.formerVal = currentVal
       
    def disposing(self, oEv):
        pass
               

def Start():
    mri(osheets)
    osheet =  osheets.getByName('Sheet1')
    ocell = osheet.getCellRangeByName('A1')
    startValue = ocell.Value
    monitor = myListener(startValue)
    ocell.addModifyListener(monitor)

def mri(target):
    mri = ctx.ServiceManager.createInstanceWithContext("mytools.Mri",ctx)
    mri.inspect(target)

# 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(
    myListener,
    'com.sun.star.util.XModifyListener',()
)
g_exportedScripts = Start,

[_savage]

Helpful thread, thanks!

I’m confused though: both, XEventListener and XModifyListener are documented (amongst others) as part of the com.sun.star.util module, but…

Code: Select all   Expand viewCollapse view

>>> import uno, unohelper
>>> from com.sun.star.util import XModifyListener  # Import succeeds.
>>> from com.sun.star.util import XEventListener
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/…/uno.py", line 425, in _uno_import
    raise uno_import_exc
  File "/…/uno.py", line 347, in _uno_import
    return _builtin_import(name, *optargs, **kwargs)
ImportError: No module named 'com' (or 'com.sun.star.util.XEventListener' is unknown)


Any idea why Python doesn’t implement the XEventListener class?

[Villeroy]

You may use an ordinary script event from the sheet tab.

Code: Select all   Expand viewCollapse view

def SheetContentChanged(obj):
    mri = uno.getComponentContext().ServiceManager.createInstance('mytools.Mri')
    mri.inspect(obj)