Run a python macro when cell contents change
Run a python macro when cell contents change
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!
Is there some simple example for how to do this? Thanks!
OOo 3.0.X on Ms Windows XP + linux
Re: Run a python macro when cell contents change
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: http://user.services.openoffice.org/en/ ... 21&t=12575
> help(unohelper.ImplementationHelper)
Implement a com.sun.star.util.XModifyListener and attach it to a SheetCell.
A cheap alternative, misusing the cell-validation feature: http://user.services.openoffice.org/en/ ... 21&t=12575
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Run a python macro when cell contents change
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
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!"
OOo 3.0.X on Ms Windows XP + linux
Re: Run a python macro when cell contents change
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.
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
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)
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Run a python macro when cell contents change
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
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)
OOo 3.0.X on Ms Windows XP + linux
Re: Run a python macro when cell contents change
Code: Select all
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',)
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Run a python macro when cell contents change
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.
OOo 3.0.X on Ms Windows XP + linux
Re: Run a python macro when cell contents change
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
'com.sun.star.module.XFooListener' <<< OOops
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Run a python macro when cell contents change
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.
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.
OOo 3.0.X on Ms Windows XP + linux
Re: Run a python macro when cell contents change
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?
The PropertyChangeListener seems to be dysfunctional: http://www.oooforum.org/forum/viewtopic ... gelistener
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.
Are you complaining that I don't write your code?
Edit: Working example: |
Code: Select all
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. |
Code: Select all
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,
Think twice if you really want to tweak a spreadsheet in this way. You have also databases with input forms at hand.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Run a python macro when cell contents change
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.
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.
LibreOffice 4.3.0.3 on Fedora Rawhide
Re: Run a python macro when cell contents change
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.
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
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)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Run a python macro when cell contents change
I have not explored or tested the code but I've just installed MRI extension.FJCC wrote: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.
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.
LibreOffice 4.3.0.3 on Fedora Rawhide
Re: Run a python macro when cell contents change
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.
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
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
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,
- Attachments
-
- testListenerMacro.ots
- Put Macro in the Framework, Download template, Double-click on it, and run macro. Try incrementing and decrementing A1. Things should happen on E1 ... and more sheets should be created when A1>=2 but its not.
- (8.03 KiB) Downloaded 489 times
LibreOffice 4.3.0.3 on Fedora Rawhide
Re: Run a python macro when cell contents change
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.
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
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,
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Run a python macro when cell contents change
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
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,
LibreOffice 4.3.0.3 on Fedora Rawhide
Re: Run a python macro when cell contents change
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…
Any idea why Python doesn’t implement the XEventListener class?
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
>>> 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)
Mac 10.14 using LO 7.2.0.2, Gentoo Linux using LO 7.2.3.2 headless.
Re: Run a python macro when cell contents change
You may use an ordinary script event from the sheet tab.
Code: Select all
def SheetContentChanged(obj):
mri = uno.getComponentContext().ServiceManager.createInstance('mytools.Mri')
mri.inspect(obj)
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice